January 8, 2008 at 3:03 pm
I'm trying to create a .bat file that will run a script that will create a script. I got this script from someone else :Whistling: & it's just not working for me. Where am I going wrong?
d:\mssql7\Binn\isql -U sa -P password -S DBTest -n -w 260
D:\Backup\Migrat_Scripts\Created_Script.sql
January 8, 2008 at 3:08 pm
Sorry, that script isn't correct - here is the correct script:
rem d:\mssql7\Binn\isql -U sa -P password -S DBTest -n -w 260
<e:\Backup\Migrat_Scripts\Build_Script.sql >e:\Backup\Migrat_Scripts\Created_Script.sql
I guess it would be helpful to show the script I'm using to build the script
set nocount on
go
print 'set nocount on'
print 'go'
print "sp_configure 'allow updates',1"
print "go"
print 'reconfigure with override'
print 'go'
go
use dist_db
go
print "use dist_db"
print "go"
print "select db_name()"
print "print ''"
print "set nocount on"
print "go"
print "delete sysalternates"
print "go"
set rowcount 0
select name into #d
from sysusers
where uid>3
and uid < 16000
order by name
declare @n varchar(30),@line varchar(255)
set rowcount 1
while 1=1
begin
select @n=name from #d
if @@rowcount=0
break
delete #d
select @line="sp_dropuser "+@n
print @line
print "go"
end
set rowcount 0
drop table #d
January 8, 2008 at 3:48 pm
You will have to use either OSQL or SQLCMD, ISQL is not the correct command liine utility.
SQLCMD -i "C:\TEST.SQL" -S SERVERNAME -d DATABASENAME -U USERNAME -P PASSWORD -o "c:\TestOutput.txt"
Note you can also include the -E switch for a trusted connection.
Place this into a bat file and you can run it.
January 9, 2008 at 12:21 pm
OSQL -i "E:\Backup\Migrat_Scripts\Build_Script.sql" -S SERVERNAME -d DATABASENAME -U USERNAME -P PASSWORD -o "c:\TestOutput.txt"
This worked great except when I run it using the Build_Script.sql, the TestOutput.txt displays 1> >2 etc and exludes some of the code. See below:
Build_Script.sql
-------------------------------------------------------------------
set nocount on
go
/* Dist_db Users */
print 'set nocount on'
print 'go'
print "sp_configure 'allow updates',1"
print "go"
print 'reconfigure with override'
print 'go'
go
/* The cleanup section*/
use dist_db
go
print "use dist_db"
print "go"
print "select db_name()"
print "print ''"
print "set nocount on"
print "go"
print "delete sysalternates"
print "go"
TestOutput.txt
-------------------------------------------------------------------
1> 2> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> set nocount on
go
sp_configure 'allow updates',1
go
reconfigure with override
go
1> 2> 3> 4> 1> 2> 3> 4> 5> 6> 7> 8> 9> use dist_db
go
select db_name()
print ''
set nocount on
go
delete sysalternates
go
-------------------------------------------------------------------
Can anyone tell what is happening and how to fix it? It looks like every time "set nocount on" is used in the Build_Script.sql, it acts up.
January 9, 2008 at 5:38 pm
add the -n swich to remove numbering.
January 10, 2008 at 8:41 am
set nocount on
select cmd.Contents + char(13) + char(10) + 'go' as "--commands"
from (select 1 AS Seq, '' as "name", 'set nocount on' as Contents union all
select 2, '', 'sp_configure ''allow updates''' union all
select 3, '', 'reconfigure with override' union all
select 4, '', 'use dist_db' union all
select 5, '', 'select db_name()' union all
select 6, '', 'delete sysalternates' union all
select 7, "name", 'exec sp_dropuser ' + "name"
from sysusers
where uid>3
and uid < 16000) cmd
order by cmd.Seq, cmd.Name
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply