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.

Scripting a database

I would like to run a job to script a database in case of the database being
deleted. Is there a command to script a databse?
http://www.nigelrivett.net/DMOScriptAllDatabases.html
But you should really rely on backups for that.
"John" wrote:

> I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?
|||Hi,
Why dont you backup your database daily once using the below command.
backup database <dbname> to disk='d:\backup\dbname.bak' with init
This backup fine can used to restore the entire data and objects incase ur
database is corrupted or deleted.
Thanks
Hari
MCDBA
"John" <John@.discussions.microsoft.com> wrote in message
news:06FF51AC-E494-463B-826A-25C5977EA89D@.microsoft.com...
>I would like to run a job to script a database in case of the database
>being
> deleted. Is there a command to script a databse?
|||You may want to try SCRPTXFR tool that comes along with SQL Server 2000.
This can help you script out database or any objects you want.
HTH
Saleem@.sqlnt.com
"John" wrote:

> I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?
|||A bunch of options listed here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.discussions.microsoft.com> wrote in message
news:06FF51AC-E494-463B-826A-25C5977EA89D@.microsoft.com...
>I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?

Scripting a database

I would like to run a job to script a database in case of the database being
deleted. Is there a command to script a databse?http://www.nigelrivett.net/DMOScriptAllDatabases.html
But you should really rely on backups for that.
"John" wrote:
> I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?|||Hi,
Why dont you backup your database daily once using the below command.
backup database <dbname> to disk='d:\backup\dbname.bak' with init
This backup fine can used to restore the entire data and objects incase ur
database is corrupted or deleted.
Thanks
Hari
MCDBA
"John" <John@.discussions.microsoft.com> wrote in message
news:06FF51AC-E494-463B-826A-25C5977EA89D@.microsoft.com...
>I would like to run a job to script a database in case of the database
>being
> deleted. Is there a command to script a databse?|||You may want to try SCRPTXFR tool that comes along with SQL Server 2000.
This can help you script out database or any objects you want.
HTH
Saleem@.sqlnt.com
"John" wrote:
> I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?|||A bunch of options listed here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.discussions.microsoft.com> wrote in message
news:06FF51AC-E494-463B-826A-25C5977EA89D@.microsoft.com...
>I would like to run a job to script a database in case of the database being
> deleted. Is there a command to script a databse?sql

Scripting "INSERT"s in an SQL database?

Hi there,

Getting ready for deployment and I would like to be able to run an install
script that will not just generate the schema, but fill in some of the
tables with default data. Is there a tool out there that will scan user
tables in an SQL database and generate the script required in order to
create a database with this default data?

Thanks

Robin"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robin

http://vyaskn.tripod.com/code.htm#inserts

If you have a lot of default data, it might be more efficient to have the
data in flat files and load it with bcp.exe or BULK INSERT.

Simon|||Hi

Another alternative would be to ship a default data and log file that is
already populated. For upgrades then a similar container but you would need
to use a tool such as those produced by www.red-gate.com sqlcompare or
www.innovartis.co.uk/Home.aspx dbghost or your home grown application.

John

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robinsql

Script works in DTS, but has problems as a Stored Procedure

I have a script that builds multiple tables and then builds tables from those tables, etc..

Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence.

As a result, some of the tables are blank when this script is run as a stored procedure.

Do I need to use transactions to prevent this compilation problem, or is there an easier setting that I can use to keep everything in the original sequence?

Thanks in advance.Anyone have any ideas on this? I can't use "GO", but I am still still having this compilation issue.sql

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 User Rights

Is there an easy way to capture existing user rights from a SQL Server
instance in a scripted format and simply run the script in a new SQL Server
instance to set all of the user privileges? Thank you.Hi ,
You could use the Enterprise Manager to Script user Rights , Right click on
the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab and
look at the security options.
Hope this helps
Cheers
Vishal Gandhi
"CR" wrote:

> Is there an easy way to capture existing user rights from a SQL Server
> instance in a scripted format and simply run the script in a new SQL Serve
r
> instance to set all of the user privileges? Thank you.
>
>|||Thanks. However, I get "[SQL-DMO]CreateFile error on
"servername.databasename.DP1' error when I try to generate script?
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:0C04E96C-CDD9-4F54-B5CA-C213CAD97127@.microsoft.com...
> Hi ,
> You could use the Enterprise Manager to Script user Rights , Right click
on
> the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab
and[vbcol=seagreen]
> look at the security options.
> Hope this helps
> Cheers
> Vishal Gandhi
>
> "CR" wrote:
>
Server[vbcol=seagreen]

Script to stop a SQL service

Is there a script that I can run to stop a particular SQL server service on
Win2003 server? I'm looking for a similar script to restart that service as
well. Thanks.NET START <service>
NET STOP <service
If you have a default instance install of SQL Server 200: "NET START
MSSQLSERVER"

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Paul O. Morris" <pomorris@.comcast.net> wrote in message
news:K8GdnQw9easBP1vfRVn-gg@.comcast.com...
> Is there a script that I can run to stop a particular SQL server service
> on Win2003 server? I'm looking for a similar script to restart that
> service as well. Thanks.|||You can use the NET STOP and NET START commands from the command-prompt to
stop/start any Windows service. For a default SQL Server instance:

NET STOP MSSQLSERVER
NET START MSSQLSERVER

For a named instance:

NET STOP MSSQL$MyInstance
NET START MSSQL$MyInstance

You can also use SQL-DMO to stop/start a SQL Server instance. VbScript
Examples:

'stop example
Option Explicit
Dim oSQLServer
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.Name = "MyServer"
oSQLServer.Stop

'start example
Option Explicit
Dim oSQLServer
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.Start False, "MyServer"

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul O. Morris" <pomorris@.comcast.net> wrote in message
news:K8GdnQw9easBP1vfRVn-gg@.comcast.com...
> Is there a script that I can run to stop a particular SQL server service
> on Win2003 server? I'm looking for a similar script to restart that
> service as well. Thanks.|||Would the syntax be the same if I ran the command line script from within a
batch file?

I'm planning on running this batch file from within the BackUp Exec
software. I have two database programs that run SQL services, preventing
the backup jobs from doing a thorough job. Automatically shutting down
those SQL services will allow jobs to complete properly.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Blyxe.3037$cb6.823@.newssvr30.news.prodigy.com ...
> You can use the NET STOP and NET START commands from the command-prompt to
> stop/start any Windows service. For a default SQL Server instance:
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> For a named instance:
> NET STOP MSSQL$MyInstance
> NET START MSSQL$MyInstance
> You can also use SQL-DMO to stop/start a SQL Server instance. VbScript
> Examples:
> 'stop example
> Option Explicit
> Dim oSQLServer
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.Name = "MyServer"
> oSQLServer.Stop
> 'start example
> Option Explicit
> Dim oSQLServer
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.Start False, "MyServer"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul O. Morris" <pomorris@.comcast.net> wrote in message
> news:K8GdnQw9easBP1vfRVn-gg@.comcast.com...
>> Is there a script that I can run to stop a particular SQL server service
>> on Win2003 server? I'm looking for a similar script to restart that
>> service as well. Thanks.
>>
>>|||> Would the syntax be the same if I ran the command line script from within
> a batch file?

Yes.

> I'm planning on running this batch file from within the BackUp Exec
> software. I have two database programs that run SQL services, preventing
> the backup jobs from doing a thorough job. Automatically shutting down
> those SQL services will allow jobs to complete properly.

Rather than cold backups, consider backing up your databases to disk using
Transact-SQL BACKUP DATABASE and then archiving the disk backup files to
tape using your backup software. This allows you to perform backups while
the databases are online and uses less tape storage. Many backup vendors
also provide a specialized backup agent for SQL Server that allows you to
backup SQL Server databases online.

In any case, be sure to thoroughly test your recovery procedure.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul O. Morris" <pomorris@.comcast.net> wrote in message
news:Y_-dncZAa9tM3lbfRVn-hQ@.comcast.com...
> Would the syntax be the same if I ran the command line script from within
> a batch file?
> I'm planning on running this batch file from within the BackUp Exec
> software. I have two database programs that run SQL services, preventing
> the backup jobs from doing a thorough job. Automatically shutting down
> those SQL services will allow jobs to complete properly.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Blyxe.3037$cb6.823@.newssvr30.news.prodigy.com ...
>> You can use the NET STOP and NET START commands from the command-prompt
>> to stop/start any Windows service. For a default SQL Server instance:
>>
>> NET STOP MSSQLSERVER
>> NET START MSSQLSERVER
>>
>> For a named instance:
>>
>> NET STOP MSSQL$MyInstance
>> NET START MSSQL$MyInstance
>>
>> You can also use SQL-DMO to stop/start a SQL Server instance. VbScript
>> Examples:
>>
>> 'stop example
>> Option Explicit
>> Dim oSQLServer
>> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
>> oSQLServer.Name = "MyServer"
>> oSQLServer.Stop
>>
>> 'start example
>> Option Explicit
>> Dim oSQLServer
>> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
>> oSQLServer.Start False, "MyServer"
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Paul O. Morris" <pomorris@.comcast.net> wrote in message
>> news:K8GdnQw9easBP1vfRVn-gg@.comcast.com...
>>> Is there a script that I can run to stop a particular SQL server service
>>> on Win2003 server? I'm looking for a similar script to restart that
>>> service as well. Thanks.
>>>
>>>
>>
>>|||Thanks, guys. I'll give it a shot.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:zOQye.2686$vT3.2657@.newssvr30.news.prodigy.co m...
>> Would the syntax be the same if I ran the command line script from within
>> a batch file?
> Yes.
>> I'm planning on running this batch file from within the BackUp Exec
>> software. I have two database programs that run SQL services, preventing
>> the backup jobs from doing a thorough job. Automatically shutting down
>> those SQL services will allow jobs to complete properly.
> Rather than cold backups, consider backing up your databases to disk using
> Transact-SQL BACKUP DATABASE and then archiving the disk backup files to
> tape using your backup software. This allows you to perform backups while
> the databases are online and uses less tape storage. Many backup vendors
> also provide a specialized backup agent for SQL Server that allows you to
> backup SQL Server databases online.
> In any case, be sure to thoroughly test your recovery procedure.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul O. Morris" <pomorris@.comcast.net> wrote in message
> news:Y_-dncZAa9tM3lbfRVn-hQ@.comcast.com...
>> Would the syntax be the same if I ran the command line script from within
>> a batch file?
>>
>> I'm planning on running this batch file from within the BackUp Exec
>> software. I have two database programs that run SQL services, preventing
>> the backup jobs from doing a thorough job. Automatically shutting down
>> those SQL services will allow jobs to complete properly.
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:Blyxe.3037$cb6.823@.newssvr30.news.prodigy.com ...
>>> You can use the NET STOP and NET START commands from the command-prompt
>>> to stop/start any Windows service. For a default SQL Server instance:
>>>
>>> NET STOP MSSQLSERVER
>>> NET START MSSQLSERVER
>>>
>>> For a named instance:
>>>
>>> NET STOP MSSQL$MyInstance
>>> NET START MSSQL$MyInstance
>>>
>>> You can also use SQL-DMO to stop/start a SQL Server instance. VbScript
>>> Examples:
>>>
>>> 'stop example
>>> Option Explicit
>>> Dim oSQLServer
>>> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
>>> oSQLServer.Name = "MyServer"
>>> oSQLServer.Stop
>>>
>>> 'start example
>>> Option Explicit
>>> Dim oSQLServer
>>> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
>>> oSQLServer.Start False, "MyServer"
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Paul O. Morris" <pomorris@.comcast.net> wrote in message
>>> news:K8GdnQw9easBP1vfRVn-gg@.comcast.com...
>>>> Is there a script that I can run to stop a particular SQL server
>>>> service on Win2003 server? I'm looking for a similar script to restart
>>>> that service as well. Thanks.
>>>>
>>>>
>>>
>>>
>>
>>

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say twice the normal run time?
I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say twice the normal run time?I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say t
wice the normal run time?I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script to cause high CPU

Does anyone have a script that could be run against say AdventureWorks or
Northwind that would peg the CPU to 100% on the servers ?
I want to use it for stress testing. The environment is SQL 2005 and we have
multiple procs.
Thanks
What would be the point of that? What exactly would you be testing for, the
fact you can run at 100% CPU? That would tell you virtually nothing in
regards to your real world system. Here are some tools that can maybe get
you started:
http://support.microsoft.com/default.aspx/kb/231619
http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en
http://www.sql-server-performance.com/articles/per/stress_test_part3_p1.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/09/7597.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Doe" <Johndoe@.jd.com> wrote in message
news:eJiq2A9ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Does anyone have a script that could be run against say AdventureWorks or
> Northwind that would peg the CPU to 100% on the servers ?
> I want to use it for stress testing. The environment is SQL 2005 and we
> have multiple procs.
> Thanks

Script to cause high CPU

Does anyone have a script that could be run against say AdventureWorks or
Northwind that would peg the CPU to 100% on the servers ?
I want to use it for stress testing. The environment is SQL 2005 and we have
multiple procs.
ThanksWhat would be the point of that? What exactly would you be testing for, the
fact you can run at 100% CPU? That would tell you virtually nothing in
regards to your real world system. Here are some tools that can maybe get
you started:
http://support.microsoft.com/default.aspx/kb/231619
http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en
http://www.sql-server-performance.com/articles/per/stress_test_part3_p1.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/09/7597.aspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Doe" <Johndoe@.jd.com> wrote in message
news:eJiq2A9ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Does anyone have a script that could be run against say AdventureWorks or
> Northwind that would peg the CPU to 100% on the servers ?
> I want to use it for stress testing. The environment is SQL 2005 and we
> have multiple procs.
> Thanks

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi

> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>

Script tasks don't work in Production

Hallo,

In SSIS Version 9.00.2047.00 I have build a few packages using script tasks. These packages work well in development.

When I try to run them Using DTUtil the systems displays in the Package Excecution Progress-window the message: 'Error: The tasks "reading registry" cannot run on this edition of Integration services. It requires a higher level edition.'

Emptying the script task from its variables and script doesn't make a difference. I did not find an explanation in BOL. Concerning script tasks I only find

The Script task uses VSA as its engine for writing and running scripts. To run a script, you must have VSA installed on the computers where the package runs. But since I even get these messages when I try to run on the machine where I build the package,I guess this can't be the problem.

Any ideas?

Kind regards,

Paul

Check out the solutions by Daniel Read @. http://www.developerdotstar.com/community/node/312

Thanks,
Loonysan

|||It looks like you only installed Tools component on Production server. You need to install SSIS component (a separate checkbox during Setup and different licensing requirements) to run packages stand-alone (outside of the designer). Actually, you may not need to Tools component on the production server at all - Tools are needed to design and debug packages, but for execution the SSIS server component should be enough.|||

Hi Michael,

Thanks for your swift reply.

I admit i'm not a SQL server specilist but while installing SQL Server 2005 from CD (Microsoft SQL Server 2005 Enterprise Edition December 2005) or DVD the option 'Integration services' is disabled. So I can not single it out for installation. . Is there still an other installation dvd I need or can I download the SSIS server component from somewhere? I did not seem to find it in the download center

With kind regards,

Paul

|||What is the OS on the production machine? Server components in Enterprise Edition can only be installed on Server operating system, not on Windows XP or Windows 2000 workstation. For production server, use server OS like Windows 2003 Server.

Script Task working in Visual Studio but not when the package is run by a job?

I have a script that changes the name of a file after a data upload. The script works fine if I execute the package in Visual Studio but when I run the file package from a SQL server job it does not rename the file. The data does get uploaded it just does not run the final script.

Any help would be appreciated.

Steve

Have you enabled logging to get the error?

The problems when running under Agent are mostly related to authentication and permissions issues - as the job probably runs under different credentials compared to interactive execution. Your options are either configure job to run under your account, or fix the permissions to allow job account to perform the operation that is failing.

See this KB for troubleshooting steps:
http://support.microsoft.com/kb/918760