Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx
.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =
convert(char(10),dateadd(dd,-1,getdate()),101)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx
.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.sql

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.
getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.
|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =
convert(char(10),dateadd(dd,-1,getdate()),101)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =convert(char(10),dateadd(dd,-1,getdate()),101)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.

Script your data in a T-SQL format (which can be executed in Query Analyzer)

Hi all,

i'm working with SQL Server for about 4 years, and i have ever
searching for a tool which can script my DATA in a T-SQL format to
copy them very easy from one server to another.

For example scripts like that:
IF NOT EXISTS (...)
INSERT INTO (...)
ELSE
UPDATE (...)

I have never found a tool like that, so i write a it by myself.
This software is a free version in the moment, you can download it on
my website:

http://www.sqlscripter.com

..NET Framework on your client PC is required.
Have fun with it ...

ThomasThomas (postmaster@.sqlscripter.com) writes:
> i'm working with SQL Server for about 4 years, and i have ever
> searching for a tool which can script my DATA in a T-SQL format to
> copy them very easy from one server to another.
> For example scripts like that:
> IF NOT EXISTS (...)
> INSERT INTO (...)
> ELSE
> UPDATE (...)
>
> I have never found a tool like that, so i write a it by myself.
> This software is a free version in the moment, you can download it on
> my website:
> http://www.sqlscripter.com
> .NET Framework on your client PC is required.
> Have fun with it ...

I don't want to belittle your work, but the standard reference for this kind
of thing is SQL Server MVP Naryana Vyas Kondreddi's utility at
http://vyaskn.tripod.com/code.htm#inserts.

I don't think he supports UPDATE though.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93B2EBD6198ECYazorman@.127.0.0.1>...
> Thomas (postmaster@.sqlscripter.com) writes:
> > i'm working with SQL Server for about 4 years, and i have ever
> > searching for a tool which can script my DATA in a T-SQL format to
> > copy them very easy from one server to another.
> > For example scripts like that:
> > IF NOT EXISTS (...)
> > INSERT INTO (...)
> > ELSE
> > UPDATE (...)
> > I have never found a tool like that, so i write a it by myself.
> > This software is a free version in the moment, you can download it on
> > my website:
> > http://www.sqlscripter.com
> > .NET Framework on your client PC is required.
> > Have fun with it ...
> I don't want to belittle your work, but the standard reference for this kind
> of thing is SQL Server MVP Naryana Vyas Kondreddi's utility at
> http://vyaskn.tripod.com/code.htm#inserts.
> I don't think he supports UPDATE though.

Hi Erland,

thank you for your comment, the SP from "SQL Server MVP Naryana Vyas
Kondreddi" is very powerful ! Unfortunately only INSERTS are
supported, and in most of my cases, i need more functionality.

However, thanks for this link, good to know !

Thomas

Script Won't Run in Job But Runs in Query Analyzer

I have a script that is basically a cursor that uses
sp_addrolemember to add all logins on the server to the
db_datareader role in every database. I use a variable
for each login. The script is in a job and scheduled to
run daily. I receive the following error: Msg 913, Sev
16: Could not find database ID %. Database may not be
activated yet or may be in transition. The problem is
that the EXACT same script will run in Query Analyzer with
NO errors. Why wouldn't the script give the same error in
Query Analyzer?
Thanks.I would have to see the script in order to (possibly) suggest anything...
:-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...

quote:

---

> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.

---

|||DECLARE @.lname varchar(100)
DECLARE @.uname varchar(100)
Declare @.mystr varchar(1000)
Declare @.dbname varchar(100)
DECLARE MacroVal CURSOR FOR Select name from syslogins
where name like 'MyDomain\
%' order by name
OPEN MacroVal
FETCH NEXT FROM MacroVal INTO @.lname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.uname = substring(@.lname,charindex('',@.lname)
+1,50)
DECLARE DBName CURSOR FOR Select name from
sysdatabases order by name
OPEN DBName
FETCH NEXT FROM DBName INTO @.dbname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.mystr = 'USE '+@.dbname+' If not
exists (select * from sysusers where SUSER_SNAME(sid)
= ''' +@.lname+''') Exec
sp_grantdbaccess '''+@.lname+''', '''+@.uname+''''
if @.uname not like 'student%'
BEGIN
Print (@.mystr)
Exec (@.mystr)
Set @.mystr = 'USE '+@.dbname+' If
exists (select * from sysusers where SUSER_SNAME(sid)
= '''+@.lname+''' and name <> ''dbo'') BEGIN EXEC
sp_addrolemember ''db_datareader'', '''+@.uname+''' EXEC
sp_addrolemember''db_backupoperator'', '''+@.uname+''' END'
Print (@.mystr)
Exec (@.mystr)
END
END
FETCH NEXT FROM DBName INTO @.dbname
END
CLOSE DBName
DEALLOCATE DBName
END
FETCH NEXT FROM MacroVal INTO @.lname
END
CLOSE MacroVal
DEALLOCATE MacroVal
GO

quote:

---

>--Original Message--
>I would have to see the script in order to (possibly)

---

suggest anything...

quote:

---

>:-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
with[QUOTE]
in[QUOTE]
>
>.
>

---

|||There doesn't seem to be a problem with the script per se. I just ran it on
my SQL Server and the
outcome was success. Did you specify an output file for the jobstep? This ca
n sometimes give more
info about the problem. In my output file, I only had messages such as:
USE master If not exists (select * from sysusers where SUSER_SNAME(sid)
= 'TIBORK\Tibor') Exec
sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
Granted database access to 'TIBORK\Tibor'. [SQLSTATE 01000]
You might want to go though the job in Agent so you didn't get any unfortuna
te linebreaks anywhere.
Although, the error message you posted didn't indicate that this is the prob
lem.
My guess is that some database isn't accessible when you run the script. Thi
s is what the error
message say. You might want to search KB for the error message. Or possibly
open a case with MS
support.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...

quote:

---

> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.

---

|||I restarted the server and the job ran successfully. ?
Go figure.

quote:

---

>--Original Message--
>There doesn't seem to be a problem with the script per

---

se. I just ran it on my SQL Server and the

quote:

---

>outcome was success. Did you specify an output file for

---

the jobstep? This can sometimes give more

quote:

---

>info about the problem. In my output file, I only had

---

messages such as:

quote:

---

>USE master If not exists (select * from sysusers where

---

SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec

quote:

---

>sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
>Granted database access to 'TIBORK\Tibor'. [SQLSTATE

---

01000]

quote:

---

>You might want to go though the job in Agent so you

---

didn't get any unfortunate linebreaks anywhere.

quote:

---

>Although, the error message you posted didn't indicate

---

that this is the problem.

quote:

---

>My guess is that some database isn't accessible when you

---

run the script. This is what the error

quote:

---

>message say. You might want to search KB for the error

---

message. Or possibly open a case with MS

quote:

---

>support.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
with[QUOTE]
in[QUOTE]
>
>.
>

---

Script Won't Run in Job But Runs in Query Analyzer

I have a script that is basically a cursor that uses
sp_addrolemember to add all logins on the server to the
db_datareader role in every database. I use a variable
for each login. The script is in a job and scheduled to
run daily. I receive the following error: Msg 913, Sev
16: Could not find database ID %. Database may not be
activated yet or may be in transition. The problem is
that the EXACT same script will run in Query Analyzer with
NO errors. Why wouldn't the script give the same error in
Query Analyzer?
Thanks.I would have to see the script in order to (possibly) suggest anything...
:-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||DECLARE @.lname varchar(100)
DECLARE @.uname varchar(100)
Declare @.mystr varchar(1000)
Declare @.dbname varchar(100)
DECLARE MacroVal CURSOR FOR Select name from syslogins
where name like 'MyDomain\
%' order by name
OPEN MacroVal
FETCH NEXT FROM MacroVal INTO @.lname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.uname = substring(@.lname,charindex('\',@.lname)
+1,50)
DECLARE DBName CURSOR FOR Select name from
sysdatabases order by name
OPEN DBName
FETCH NEXT FROM DBName INTO @.dbname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.mystr = 'USE '+@.dbname+' If not
exists (select * from sysusers where SUSER_SNAME(sid)
= ''' +@.lname+''') Exec
sp_grantdbaccess '''+@.lname+''', '''+@.uname+''''
if @.uname not like 'student%'
BEGIN
Print (@.mystr)
Exec (@.mystr)
Set @.mystr = 'USE '+@.dbname+' If
exists (select * from sysusers where SUSER_SNAME(sid)
= '''+@.lname+''' and name <> ''dbo'') BEGIN EXEC
sp_addrolemember ''db_datareader'', '''+@.uname+''' EXEC
sp_addrolemember''db_backupoperator'', '''+@.uname+''' END'
Print (@.mystr)
Exec (@.mystr)
END
END
FETCH NEXT FROM DBName INTO @.dbname
END
CLOSE DBName
DEALLOCATE DBName
END
FETCH NEXT FROM MacroVal INTO @.lname
END
CLOSE MacroVal
DEALLOCATE MacroVal
GO
>--Original Message--
>I would have to see the script in order to (possibly)
suggest anything...
>:-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>|||There doesn't seem to be a problem with the script per se. I just ran it on my SQL Server and the
outcome was success. Did you specify an output file for the jobstep? This can sometimes give more
info about the problem. In my output file, I only had messages such as:
USE master If not exists (select * from sysusers where SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
Granted database access to 'TIBORK\Tibor'. [SQLSTATE 01000]
You might want to go though the job in Agent so you didn't get any unfortunate linebreaks anywhere.
Although, the error message you posted didn't indicate that this is the problem.
My guess is that some database isn't accessible when you run the script. This is what the error
message say. You might want to search KB for the error message. Or possibly open a case with MS
support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||I restarted the server and the job ran successfully. ?
Go figure.
>--Original Message--
>There doesn't seem to be a problem with the script per
se. I just ran it on my SQL Server and the
>outcome was success. Did you specify an output file for
the jobstep? This can sometimes give more
>info about the problem. In my output file, I only had
messages such as:
>USE master If not exists (select * from sysusers where
SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
>sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
>Granted database access to 'TIBORK\Tibor'. [SQLSTATE
01000]
>You might want to go though the job in Agent so you
didn't get any unfortunate linebreaks anywhere.
>Although, the error message you posted didn't indicate
that this is the problem.
>My guess is that some database isn't accessible when you
run the script. This is what the error
>message say. You might want to search KB for the error
message. Or possibly open a case with MS
>support.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed becau
se
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run. I cannot run the trace coz its slowing down. There are lot of procs and triggers written over several years.
TIA
Are you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA
|||Do you by any chance know which systables can I look up to get the same info as from the server procs.
or else by any chance do you know how to look at the script contained in the below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On the
> client or server?
> You might want to trace using system stored procedures, on the server side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>
|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

script for trace form Query analyzer ?

I want to know which procs and triggers that are taking a lot of time to run
. I cannot run the trace coz its slowing down. There are lot of procs and t
riggers written over several years.
TIAAre you saying that Profiler is resulting in performance degradation? On the
client or server?
You might want to trace using system stored procedures, on the server side.
This is much better than running Profiler interactively.
For more info:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
I want to know which procs and triggers that are taking a lot of time to
run. I cannot run the trace coz its slowing down. There are lot of procs
and triggers written over several years.
TIA|||Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On t
he
> client or server?
> You might want to trace using system stored procedures, on the server side
.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>|||This info is not stored in any of the system tables.
I think the server side trace stored procs are implemented as extended
stored procs, so the source code is not available
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:AA76511D-FD1C-4670-B083-78FF5A3F65E7@.microsoft.com...
Do you by any chance know which systables can I look up to get the same info
as from the server procs.
or else by any chance do you know how to look at the script contained in the
below procs
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_getfilterinfo
fn_trace_getinfo Returns
fn_trace_gettable Returns
thanks !!
"Narayana Vyas Kondreddi" wrote:

> Are you saying that Profiler is resulting in performance degradation? On
the
> client or server?
> You might want to trace using system stored procedures, on the server
side.
> This is much better than running Profiler interactively.
> For more info:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
> news:DB0143FE-769C-486D-8473-EA2CD95DBE17@.microsoft.com...
> I want to know which procs and triggers that are taking a lot of time to
> run. I cannot run the trace coz its slowing down. There are lot of procs
> and triggers written over several years.
> TIA
>
>

Script for headers in Query Analyzer

I'm looking for a script that will turn off the headers option in query analyzer. The header option can be turned off by going into TOOLS>OPTIONS>Results Tab.
If anyone know how to turn on/off headers using a script please help!!
Thanks in advanceOriginally posted by vmlal
I'm looking for a script that will turn off the headers option in query analyzer. The header option can be turned off by going into TOOLS>OPTIONS>Results Tab.

If anyone know how to turn on/off headers using a script please help!!

Thanks in advance I doubt this is possible.

I turned on SQL Profiler and added every possible trace. Then I went to TOOLS>OPTIONS>Results, turned off 'Print column headers' and clicked OK. Nothing came out on the trace.

Script for Creating a Stored Procedure??

Hello,
I'm trying to create a store procedure from an interface which is similiar to Query Analyzer, but I can't get it to work and I don't get an error message...so I'm not sure what error I am receiving. Please see the below sp_get_topics (#1) which is not saving and sp_get_topics (#2 works). Thanks in advance...

--#1--
CREATE PROCEDURE [sp_get_topics] AS
SELECT t_1.Topic, t_1.Message, t_1.Create_Date, t_2.Username
FROM t_1 WITH (READUNCOMMITTED) LEFT OUTER JOIN
t_2 WITH (READUNCOMMITTED) ON t_1.User_ID = t_2.ID
GO

--#2--
CREATE PROCEDURE [sp_get_topics]
AS
SELECT Topic, Message, Create_Date
FROM t_1
GOWhen you're saying that it "is not saving" you mean you can't compile it?|||I hope this is intentional to demonstrate your dilemna but both procs have the same name.|||Which version of sql server are you using ?|||Originally posted by rnealejr
Which version of sql server are you using ?

1) I hope this is intentional to demonstrate your dilemna but both procs have the same name. -- right...I'm trying to compile it with different names, so there's no conflict.

2) Which version of sql server are you using ? -- 2000|||Originally posted by rdjabarov
When you're saying that it "is not saving" you mean you can't compile it?

Yes, I meant "compile it".|||Replace READUNCOMMITTED with NOLOCK|||uuummmmmmmmmmmmmmmmmmmmmmmmmm

Why aren't you using QA?|||Originally posted by rdjabarov
Replace READUNCOMMITTED with NOLOCK

Yeah...I've tried with & without WITH (READUNCOMMITTED) and WITH (NOLOCK), still doesn't work. I'm working with a crappy Entreprise Manager which is web based interface, so that's probably the problem.

Any ideas? Thanks again...|||Originally posted by Brett Kaiser
uuummmmmmmmmmmmmmmmmmmmmmmmmm

Why aren't you using QA?

please read below...thanks.|||Why do you want uncommitted data?

Don't you think that would be dangerous and be a problem with data integrity?

And why can't you install the client tools locally?|||Originally posted by Brett Kaiser
Why do you want uncommitted data?

Don't you think that would be dangerous and be a problem with data integrity?

And why can't you install the client tools locally?

I'm not planning on sticking with the "WITH (readuncommitted)"...I'm just trying every possible way to get this annoying interface to work.

You're right if I was worried about data integrity I wouldn't want to go this route...but it's just a personal site of mine. And still I don't want to use it.

Install the client tools locally? -- I'm using a my web hosts crappy ASP.Net Enterprise Manager (www.aspenterprisemanager.com) version to manipulate my sql tables (which is I guess an old version), so I have to use scripts to create all tables & stored procedures within their version of Query Analyzer. I guess there is a better version out there somewhere, but I haven't been able to get the feedback to know how to setup it up. I would love to use my SQL Server Enterprise Manager locally, but don't know how. If you have any ideas...please let me know.

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
NewishNo, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegroups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>|||Here's an example from BOL:
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
If you want to get the syntax 'for free' you can create the FK in EM and
rather than actually save the change, simply take a look at the script that
is generated. Cheers, Paul Ibison SQL Server MVP,
www.replicationanswers.com

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
NewishNo, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint
+ ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-
34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegroups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>|||Here's an example from BOL:
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
If you want to get the syntax 'for free' you can create the FK in EM and
rather than actually save the change, simply take a look at the script that
is generated. Cheers, Paul Ibison SQL Server MVP,
www.replicationanswers.com

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
Newish
No, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegro ups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>

SCOPE_IDENTITY() SqlCe problem

I unable to do the SCOPE_IDENTITY on a insert query at SQL Mobile.

I need to perform on this query the insert on Client table and to get the ID.

I am programming on Visual Basic on Visual Studio 2005

My code is:

Dim ClientID as Integer

Dim sql As String = "INSERT INTO Client(Number) VALUES('5'); SELECT scope_identity()"

Dim cmd As New SqlCeCommand(sql, connection)

connection.Open()

ClientID = Convert.ToInt32(cmd.ExecuteNonQuery())

connection.Close()

And I get an error like this:

There was an error parsing the query. [ Token line number = 1,Token line offset = 76,Token in error = SELECT ]

Thanks!

You cannot have more queries in the same batch and scope_identity is not supported by SQL CE/Mobile. Use "SELECT @.@.IDENTITY" in a second .ExecuteNonQuery call.

scope_identity()

I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:

SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off

This query displays the entry number of the record just entered, but inserts
a 0 in to entry field of the 2nd table. Any help would be great.

Thanks,
Darren>SELECT scope_identity() AS entry
does not assign the identity to a variable named entry, it just
returns a recordset like any other select

either
declare @.Entry int
set @.Entry = (select scope_identity())
insert into table (field) values (@.Entry)

or
insert int table (field) values (select scope_identity())

also move the set nocount off to the top

On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<celtics@.lan-specialist.com> wrote:

>I have an ASP front end on SQL 2000 database. I have a form that submits to
>an insert query. The entry field is an "identity" and the primary key. I
>have used scope_identity() to display the entry# of the record just entered
>on the confirmation page. Now I need to insert the entry into another
>table. This is my query:
>SET NOCOUNT ON
>INSERT wo_main
> (site_id, customer, po_number)
>VALUES ('::site_id::', '::customer::', '::po_number::')
>SELECT scope_identity() AS entry
>INSERT INTO wo_combo_body
>(entry) VALUES ('::entry::')
>SET nocount off
>This query displays the entry number of the record just entered, but inserts
>a 0 in to entry field of the 2nd table. Any help would be great.
>Thanks,
>Darren|||Hi

If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
This variable can then be used in the second insert statement. You may also
want to add some error checking! Rather than returning a result set it may
also be better(faster) to return the value as a parameter

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:aptZb.32366$um1.10431@.twister.nyroc.rr.com...
> I have an ASP front end on SQL 2000 database. I have a form that submits
to
> an insert query. The entry field is an "identity" and the primary key. I
> have used scope_identity() to display the entry# of the record just
entered
> on the confirmation page. Now I need to insert the entry into another
> table. This is my query:
> SET NOCOUNT ON
> INSERT wo_main
> (site_id, customer, po_number)
> VALUES ('::site_id::', '::customer::', '::po_number::')
> SELECT scope_identity() AS entry
> INSERT INTO wo_combo_body
> (entry) VALUES ('::entry::')
> SET nocount off
> This query displays the entry number of the record just entered, but
inserts
> a 0 in to entry field of the 2nd table. Any help would be great.
> Thanks,
> Darren|||Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?

Also...

On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> >SELECT scope_identity() AS entry
> does not assign the identity to a variable named entry, it just
> returns a recordset like any other select
> either
> declare @.Entry int
> set @.Entry = (select scope_identity())
> insert into table (field) values (@.Entry)
> or
> insert int table (field) values (select scope_identity())
> also move the set nocount off to the top
> On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> <celtics@.lan-specialist.com> wrote:
> >I have an ASP front end on SQL 2000 database. I have a form that submits
to
> >an insert query. The entry field is an "identity" and the primary key.
I
> >have used scope_identity() to display the entry# of the record just
entered
> >on the confirmation page. Now I need to insert the entry into another
> >table. This is my query:
> >SET NOCOUNT ON
> >INSERT wo_main
> > (site_id, customer, po_number)
> >VALUES ('::site_id::', '::customer::', '::po_number::')
> >SELECT scope_identity() AS entry
> >INSERT INTO wo_combo_body
> >(entry) VALUES ('::entry::')
> >SET nocount off
> >This query displays the entry number of the record just entered, but
inserts
> >a 0 in to entry field of the 2nd table. Any help would be great.
> >Thanks,
> >Darren|||Hi

You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm

You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:bnxZb.71491$n62.1826@.twister.nyroc.rr.com...
> Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
> any pitfalls with doing it with a trigger?
> Also...
> On the same confirmation page I want to diplay links to a page for each
> table. Basically I need to select the entry field from each table that I
> have inserted to with the trigger. I can then use this as a hyperlink to
> the each page. Any ideas on this?
>
> "Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
> news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> > >SELECT scope_identity() AS entry
> > does not assign the identity to a variable named entry, it just
> > returns a recordset like any other select
> > either
> > declare @.Entry int
> > set @.Entry = (select scope_identity())
> > insert into table (field) values (@.Entry)
> > or
> > insert int table (field) values (select scope_identity())
> > also move the set nocount off to the top
> > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> > <celtics@.lan-specialist.com> wrote:
> > >I have an ASP front end on SQL 2000 database. I have a form that
submits
> to
> > >an insert query. The entry field is an "identity" and the primary key.
> I
> > >have used scope_identity() to display the entry# of the record just
> entered
> > >on the confirmation page. Now I need to insert the entry into another
> > >table. This is my query:
> > > >SET NOCOUNT ON
> > >INSERT wo_main
> > > (site_id, customer, po_number)
> > >VALUES ('::site_id::', '::customer::', '::po_number::')
> > >SELECT scope_identity() AS entry
> > >INSERT INTO wo_combo_body
> > >(entry) VALUES ('::entry::')
> > >SET nocount off
> > > >This query displays the entry number of the record just entered, but
> inserts
> > >a 0 in to entry field of the 2nd table. Any help would be great.
> > > >Thanks,
> > >Darren
>