Need help creating a temporary table in MS SQL Server.

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end userstarts entering the contents for a file that is going to be generated once hefinishes entering the data, but the problem is that if more than one user isdoing the same process the data would get mixed up. To avoid this Ithought in creating a temporary table (its name will consist of a stringand the current date time).

I would like to see any tutorialabout creating and working with temp tables. Or if you have anysuggestions, I will appreciate them. Thanks

There are two types of temp tables in SQL Server # local temp table scope very limted but also the most used because it uses less resources and SQL Server may clean up if you forget it, the other ## global temp table longer scope but you must drop it explicitly or it can harm your application. Try the link below for some tricks of how to use the temp tables, I am assuming you know they are created in the Temp Db. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

Need help comparing tables Query

I have two tables that I need to be compared. The first table <prams>
consists of many fields (DBName, TBName, IName, Other fields) The first
three (DBName, TBName, IName) work together to form the primary key.
The second table <temp> will also contain the same structure.
I need to take the first table <prams> and compare it to the second
table <temp>. If the Row is missing from the second table <temp>, I
need to copy the information form the first table <prams> into third
"Purgatory" table. If the row exists in the first table <prams> I
need to copy the other fields into the second table <prams>.
Once it has gone though and compared all the table entries the <temp>
table will replace the <prams> table.
Thanks
-Matt-Whoops forgot, I am running SQL2005, and would like to keep it SQL not
VB or C#.
Thanks again,|||INSERT INTO [Purgatory]
SELECT t1.[dbname],t1.[tbname],t1.[iname],t1.[other fields]
FROM [Prams] WHERE NOT EXISTS
(SELECT t2.[dbname] FROM [Temp] t2 WHERE t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname AND t1.[iname] = t2.[iname])
UPDATE [Temp] --is this what you meant? You repeated the same table name
SET [Other Field1] = t1.[Other Field1]
/* Repeat for other fields that need to be copied */
FROM [Prams] t1 INNER JOIN [Temp] t2 ON
t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname
AND t1.[iname] = t2.[iname]
If the temp table replaces the prams table after the update, shouldn't you
be keeping prams in sync with temp instead of the other way around?
"MKruer@.gmail.com" wrote:

> I have two tables that I need to be compared. The first table <prams>
> consists of many fields (DBName, TBName, IName, Other fields) The first
> three (DBName, TBName, IName) work together to form the primary key.
> The second table <temp> will also contain the same structure.
> I need to take the first table <prams> and compare it to the second
> table <temp>. If the Row is missing from the second table <temp>, I
> need to copy the information form the first table <prams> into third
> "Purgatory" table. If the row exists in the first table <prams> I
> need to copy the other fields into the second table <prams>.
> Once it has gone though and compared all the table entries the <temp>
> table will replace the <prams> table.
> Thanks
> -Matt-
>|||Maybe my logic is totally fired but what I am trying to do it rebuild
all the DBs, Tables, and Indexes, and only store those that are on the
system, any DBs, Tables, or Indexes that are no longer there get sent
to a new file.
So I take what was good <prams> compare it to what is known good <temp>
then once everything is processed, <temp> should become the new <prams>
correct?|||Not necessarily, because I am only running this comparison once a day
and I do not know what entities may have changed, but I still want to
know that they changed. So it does not need to be real time.
PS
Thanks you so much for this.

Need help changing database dynamically

I have the following code. I basically want to dynamically empty all data
from every user table in every user database. I have the code that processe
s
all the databases I want and then gets all of the tables I want. My problem
is using the GO. I can't get this to run by writing USE @.dbname GO or
USE @.dbname
GO
Does anyone have a recommendation? I'm sure I have just been looking at it
to long at this point and it is easy. Thanks in advance.
-- Sydem
begin tran ProcessDataClean
declare @.dbname varchar(255)
declare @.dbtablename varchar(255)
declare @.sql varchar(255)
DECLARE ProcessDataClean_CURSOR CURSOR FOR
select name
from master.dbo.sysdatabases
where dbid > 6 and
name not in ('pubs', 'Northwinds')
order by name
OPEN ProcessDataClean_CURSOR
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'USE ' + @.dbname
exec sp_sqlexec @.sql
--i want to use GO right here to switch the database
--for now i'm just printing what i want to do
PRINT ''
PRINT @.sql
PRINT 'GO'
PRINT ''
DECLARE GetDBTableName_CURSOR CURSOR FOR
select name
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
order by name
OPEN GetDBTableName_CURSOR
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'DELETE ' + @.dbtablename
END
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
END
CLOSE GetDBTableName_CURSOR
DEALLOCATE GetDBTableName_CURSOR
END
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
END
CLOSE ProcessDataClean_CURSOR
DEALLOCATE ProcessDataClean_CURSOR
commit tran ProcessDataClean[USE] does not accept a parameter / variable. You will have to execute the
rest of the code inside the context where you are suing "use database_name".
Example:
exec sp_msforeachdb '
if ''?'' not in (
''master'',
''msdb'',
''model'',
''tempdb'',
''pubs'',
''northwind''
)
begin
use [?]
select
table_catalog,
table_name
from
information_schema.tables
end
'
This is an example (may be a bad one), i am not recommending to use the
undocumented and unsupported sp sp_msforeachdb.
Anyway if you try to delete from tables being referenced by other tables,
you will get an error. I think it will be better if you drop the databases
and recreate them.
AMB
"Sydem" wrote:

> I have the following code. I basically want to dynamically empty all data
> from every user table in every user database. I have the code that proces
ses
> all the databases I want and then gets all of the tables I want. My probl
em
> is using the GO. I can't get this to run by writing USE @.dbname GO or
> USE @.dbname
> GO
> Does anyone have a recommendation? I'm sure I have just been looking at i
t
> to long at this point and it is easy. Thanks in advance.
> -- Sydem
>
> begin tran ProcessDataClean
> declare @.dbname varchar(255)
> declare @.dbtablename varchar(255)
> declare @.sql varchar(255)
> DECLARE ProcessDataClean_CURSOR CURSOR FOR
> select name
> from master.dbo.sysdatabases
> where dbid > 6 and
> name not in ('pubs', 'Northwinds')
> order by name
> OPEN ProcessDataClean_CURSOR
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.sql = 'USE ' + @.dbname
> exec sp_sqlexec @.sql
> --i want to use GO right here to switch the database
> --for now i'm just printing what i want to do
> PRINT ''
> PRINT @.sql
> PRINT 'GO'
> PRINT ''
> DECLARE GetDBTableName_CURSOR CURSOR FOR
> select name
> from dbo.sysobjects
> where OBJECTPROPERTY(id, N'IsUserTable') = 1
> order by name
> OPEN GetDBTableName_CURSOR
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT 'DELETE ' + @.dbtablename
> END
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> END
> CLOSE GetDBTableName_CURSOR
> DEALLOCATE GetDBTableName_CURSOR
> END
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> END
> CLOSE ProcessDataClean_CURSOR
> DEALLOCATE ProcessDataClean_CURSOR
> commit tran ProcessDataClean|||Correction,

> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error.
Anyway, if you try to delete from tables being referenced by other tables
and there are rows related in the child table, you will get an error.
Example:
exec ('use northwind delete [dbo].[orders]')
go
AMB
"Alejandro Mesa" wrote:
> [USE] does not accept a parameter / variable. You will have to execute the
> rest of the code inside the context where you are suing "use database_name
".
> Example:
> exec sp_msforeachdb '
> if ''?'' not in (
> ''master'',
> ''msdb'',
> ''model'',
> ''tempdb'',
> ''pubs'',
> ''northwind''
> )
> begin
> use [?]
> select
> table_catalog,
> table_name
> from
> information_schema.tables
> end
> '
> This is an example (may be a bad one), i am not recommending to use the
> undocumented and unsupported sp sp_msforeachdb.
> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error. I think it will be better if you drop the databases
> and recreate them.
>
> AMB
> "Sydem" wrote:
>|||Great Alejandro. I will take a look at that.
"Alejandro Mesa" wrote:
> Correction,
>
> Anyway, if you try to delete from tables being referenced by other tables
> and there are rows related in the child table, you will get an error.
> Example:
> exec ('use northwind delete [dbo].[orders]')
> go
>
> AMB
> "Alejandro Mesa" wrote:
>

Need help assigning runway using time

Hi there,
I am trying to model a simple air traffic control system and have plane, flight_info and runway tables. Flight_info is a relational table and 2 of its attributes are pointers to the plane and runway tables so that I can allocate planes and flights to runways.

The length of the runway needs to be more than the stopping distance of the plane and I have a getStoppingDistance method to use for this. I guess one part will be to compare the length of the runway with the stopping distance.

I also need to assign a runway to a flight for a fixed time for each flight. I'm not sure how I can achieve this. I am thinking that I will need to test the 'state' attribute of the runway table to see if a particular runway is allocated or not and if it isn't, assign it to a flight. But I'm not sure how I would assign it for a fixed time.

Any help or suggestions on this would be great.reset the "state" attribute of runway everytime the flight completes landing. you could write a trigger on flight_info table which shall toggle this runway.state when you update flight_info

Need help approximating how much hard drive space I need for my DB table.

In our SQL Server database we will have a table that will be populated with about 2000 records
per day. That is2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytes
of available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,
based solely on the number of records entered into this table per day. The problem is I don't
know how to calculate how much hard drive space we need. I think I read that using varchar,
sql server 2005 really optimizes a database. Here is a typical example of data in our
database. I put dots on three lines between the first and last sample record to just
illustrate that there are many records in between.

Basically we only need 8 months of data at a time in the table and then we can purge
records older than 8 months.
Can someone help me approximate how much hard drive space I might need for 8 months of data,
given the following sample record in the database?

Sample: -->34.5 4.08 10.6 .0012

Sample Table in my DB just for illustration:

(PPsquare inch) (Diameter) (Weight gm) (coeffOfSatFriction)

34.5 4.08 10.6 .0012
.
.
.
21.7 3.54 6.22 .019

Why dont you monitor your db size. Get the size on a sunday evening and check the same information on friday evening, you will see how much your db has increased. monitor this over a couple of week and you will get a better estimate. Also, create a job that will alert you if the db size reaches 70% of allocated space.|||Well actually right now we are in design mode and no dat is in the database yet. The software to populate the tables in the database has not even be written at this point in time. Basically we are required to calculate the estimated hard drive space over an 8 month period. Then based on the approximated database size we will make a decision from there. So I need to figure out a way to calculate a good estimate of the size of that data table in the database. I don't know if SQL server does any type of data compression if necessary?|||

Hi,

The estimate depends entirely on the type of data you want to store. For an article on data types sizes, you can have a look at this article:

http://msdn2.microsoft.com/en-us/library/ms187752.aspx

Based on the sample in your post, you could use a decimal with a precision of 9 (http://msdn2.microsoft.com/en-us/library/ms187746.aspx for decimal data type) which takes 5 bytes for each field. So, your estimate would be something like this:

5 (bytes) x 4 (fields) x 2000 (records) x 5 (days) x 36(approx 36 weeks in 8 months) = 7 200 000 bytes = 6.87 Megs of data. So those 50 gigs of data would be more than enough to keep your table.

|||

Thank you very much for the help!

Kind regards

Need help adding 3rd table to a Join

Hi can someone help me I'm brainless, I need to add a field from a third database to this view

CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

The field I need to add is COLLECTION, with Left Outer Join

mCollection
ITEM_ID
COLLECTIONAnd COLLECTION is a member of which table?|||I need the COLLECTION field from the mCollection Table to be included in the mProduct_Search view|||Nevermind my brain came back to life, I have answered my own question...
CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH], B.COLLECTIONS as [COLLECTIONS],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

Left Outer Join (Select * from mCollections) B

on mProduct.ITEM_ID = B.ITEM_ID

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since we
are running this query against view so we can’t apply index hints so we are
wondering what are the other ways we can force optimizer to use non-clustered
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--Harvinder
Hi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>
|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever we
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since we
are running this query against view so we canâ't apply index hints so we are
wondering what are the other ways we can force optimizer to use non-clustered
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--HarvinderHi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever we
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since w
e
are running this query against view so we can’t apply index hints so we ar
e
wondering what are the other ways we can force optimizer to use non-clustere
d
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--HarvinderHi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever w
e
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]
%')
)
GO
John
"Robert Lie" wrote:

> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert Lie
Hi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:

> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
sql

Need help / suggestion

Hey guys

I have to implement a dynamic Parent - > Child Scenario ... but the catch is
as Follows :

I need to create this Table Design so that I can have multiple Parent ->
child - > parent Relationships

(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folder
that holds another Folder etc to Infinite )

So if the Above makes any sense ... Suggestions would be Welcome

Thanx1) Get a copy of TREES & HIERARCHIES IN SQL for several different
methods

2) Google for "nested sets", "path enumeration" and "adjacency list"
models in SQL|||Thanx !!!

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL|||Nice 1 celko !!! :P
http://www.intelligententerprise.co...equestid=315563

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL

Need Help - Custome Paging Using ROW_NUMBER()

Hi,

I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:

<Error messages>

These are on the first SELECT Line..

Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "mort.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "FUNERAL.NUMBER" could not be bound.

</Error Messages>

<Sotred Procedure>

CREATE PROCEDURE proc_NAMEGetPaged
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER
FROM
(SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER,
ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum
FROM Name n) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

</Stored Procedure>

Any assistance in resolving this would be greatly appreciated..

Regards..

Peter.

Hi,

Have resolved my problem see code below..

<Stored Procedure>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Peter Annandale
-- Create date: 21/10/2006
-- Description: Return Subset from Names table
-- =============================================
CREATE PROCEDURE proc_NAMEGetPaged
-- Add the parameters for the stored procedure here
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT CODE, LAST_NAME, Name, TYPE, NUMBER
FROM
(SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,
ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum
FROM dbo.NAME n
LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE
LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

< /Stored Procedure>

Regards..

Peter.

need help

CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]

INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALL'.

can anyone point out what mistake that i've made here?

The ALL is a parameter that passed to the stored procedure when invoked.

|||

oops, in fact i've left out the CHAR(39) on both side of the parameter...

now the problem is solved.

sql

Need help

This may seem like a dumb question, but here is what I am trying to do,
Lets say, I have a TABLE that has colums
A B C D
Now, I have added a 5th colum E
I want to populate E with D
I can't seem to get the insert command right,
Anyone ??Hello, please try this:

UPDATE myTable SET E=D;

Originally posted by ChrisWBates
This may seem like a dumb question, but here is what I am trying to do,

Lets say, I have a TABLE that has colums

A B C D

Now, I have added a 5th colum E

I want to populate E with D

I can't seem to get the insert command right,

Anyone ??|||Or more fully

UPDATE A SET E=D FROM myTable99 A
GOsql