hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.|||thanks, urm...what's a bubble sort and how do I do it?
and how do I create an index?
the columns to sort by are text and updated regularly by the CMS.
I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.|||Yeah? Go read the IBM or Oracle Manuals....
In any case, are we talking about SQL Server in the first place?
Do you have a dba or are you "it"
And why aren't you using stored procedures?|||This nifty asp you've written is wide open to sql injection. Do you know what that is?|||Oh God, Oracle manuals are the worst...|||I think the bubble sort thing was a little joke by Sean that went over your head. :)
bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.
Brett, what do you mean by "Do you have a dba or are you "it""
Feel free to direct me in the right direction|||Who administers your database?
Like, who creates tables for eaxmple?|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html
Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).|||Parameterise your data accces (don't try cleaning your strings).
Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...|||Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...
Oh yeah...like what?|||replacing apostrophes, equals signs, asterix' and percentages...
Eg. replace(<fieldname>,"'","")|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)|||Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?|||DECLARE @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.x varchar(8000) '
SELECT @.sql = @.sql + 'SET @.x =
CHAR(68)
+CHAR(82)
+CHAR(79)
+CHAR(80)
+CHAR(32)
+CHAR(68)
+CHAR(65)
+CHAR(84)
+CHAR(65)
+CHAR(66)
+CHAR(65)
+CHAR(83)
+CHAR(69)
+CHAR(32)
+CHAR(78)
+CHAR(111)
+CHAR(114)
+CHAR(116)
+CHAR(104)
+CHAR(119)
+CHAR(105)
+CHAR(110)
+CHAR(100)
SELECT @.x'
EXEC(@.sql)|||suffice it to say that scrubbing strings will address only the most elementary injection attacks.
the real fix is: NEVER execute sql that was pasted together based on user input. if you do this, you are vulnerable no matter how tricky your parsing code is.|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
Ahh clever... I did say I was using access but I never considered this|||Great..parse this
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
actually, that statement would probably fail because of the GO. :)
remove the GO and you are in business.|||I think the bubble sort thing was a little joke by Sean that went over your head. :)
you guys ruin all of my fun. i am too busy these days to lead the posters around by the nose for several posts anyways.|||Haha I remember learning (not to) use bubble sorts 2 years ago... Man those were long lessons!
Imagine your old, grey, chunky, droning computing teacher mumbling on for an hour and a half just to turn round and go "..and that's why you won't ever use this" >:(|||bubble sort is actually ok if you only have a few items to sort. in practice even the most hairbrained algorithm will be fast if there are only 10 items.
as soon as you have 100k to sort though, your code will fall over if you use it.|||Thanks jezemine
is it likely that my small database of 30 or 40 cars with descriptions and specs will slow to the point of timeouts when I ask it to order by 'price' for instance?
my DB seems to have suddenly done this, could it be thatthe amount of data has suddenly broken some threshold? or as I suspect, could 1and1 have changed my DB at a low level and cripled its sort capabilty. It was fine last week.
NB: please keep it simple, all this archane terminology is just making matters worse for my little brain!|||I don't think so...
http://www.autotrader.com/fyc/searchresults.jsp?num_records=&search_type=both&distance=10&address=07052&style_flag=1&make=ASTON&model=&make2=&start_year=1981&end_year=2008&min_price=&max_price=&transmission=&engine=&drive=&doors=&fuel=&max_mileage=&color=&keywords_display=&sort_type=priceDESC&body_code=0&certified=&advanced=&default_sort=priceDESC&keywordsrep=&keywordsfyc=|||actually, that statement would probably fail because of the GO. :)
remove the GO and you are in business.
Ummm, no
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)|||I think you can always substitute a semicolon for the GO, anyway.|||Guys...what are you talking about?
what does this do?
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)
- is this part of the thread or are you off on a tangent?|||Ummm, no
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)
I stand corrected!
I was under the impression that "GO" was only understood by the client tools like osql/sqlcmd/QA as a batch separator. apparently it's also understood by EXEC and sp_executesql.|||Guys...what are you talking about?
what does this do?
- is this part of the thread or are you off on a tangent?
They are off on a semi-tangent, regarding whether the code you are using is safe from SQL Injection attacks.|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...
Ummmm...you...will be...you will...be...
In any case there are ways...but you need control, you must learn control
Use stored procs access only and you'll be fine|||a poorly written proc is just as vulnerable to sql injection. consider this proc:
create proc ExecSql (@.sql varchar(max)) as exec(@.sql)
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Tuesday, March 6, 2012
"Order by" clause
I run the script below in sql2005 and am receiving the error msg 209. Do I
need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
them displyed in front.
Thank you.
Charlie
select STATE, TAXAMNT, SUBTOTAL, *
from sop30200
where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO
FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.
While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.
There are two solutions. One is to specify
ORDER BY sop30200.STATE
and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
chas2006 wrote:
>I run the script below in sql2005 and am receiving the error msg 209. Do I
>need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
>them displyed in front.
>Thank you.
>Charlie
>select STATE, TAXAMNT, SUBTOTAL, *
>from sop30200
>where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
>AND SOPTYPE IN (3,4)
>AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
>--AND TAXEXMT1 = ' '
>AND SUBTOTAL <> 0
>--AND SOPNUMBE LIKE 'NCINV%'
>AND VOIDSTTS = 0
>AND SOPNUMBE NOT IN
>(SELECT INVNO
>FROM VERTEX..REGPRERETURNSTBL
>WHERE COMPCD = 'ABS'
>and invno between '20070201' and '20070228')
>--AND SHIPTOGEOCODE LIKE '31%')
>ORDER BY STATE DESC
>
>Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'STATE'.
>
need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
them displyed in front.
Thank you.
Charlie
select STATE, TAXAMNT, SUBTOTAL, *
from sop30200
where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO
FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.
While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.
There are two solutions. One is to specify
ORDER BY sop30200.STATE
and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
chas2006 wrote:
>I run the script below in sql2005 and am receiving the error msg 209. Do I
>need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
>them displyed in front.
>Thank you.
>Charlie
>select STATE, TAXAMNT, SUBTOTAL, *
>from sop30200
>where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
>AND SOPTYPE IN (3,4)
>AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
>--AND TAXEXMT1 = ' '
>AND SUBTOTAL <> 0
>--AND SOPNUMBE LIKE 'NCINV%'
>AND VOIDSTTS = 0
>AND SOPNUMBE NOT IN
>(SELECT INVNO
>FROM VERTEX..REGPRERETURNSTBL
>WHERE COMPCD = 'ABS'
>and invno between '20070201' and '20070228')
>--AND SHIPTOGEOCODE LIKE '31%')
>ORDER BY STATE DESC
>
>Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'STATE'.
>
"Not enough server storage is available to process this command"
Hi,
We have several customers using replication on similar databases - all with the same schema, published using essentially the same script. One of them has been getting the "Not enough server storage" message when they try to access any network shares on th
e server with the published database on it. This starts happening between 24-36 hours after they first run the snapshot agent.
The application log contains an entry saying "The process could not create file \\[SERVER]\REPLDATA\unc",
and the system log has one saying "The server was unable to allocate from the system paged pool because the pool was empty".
When they turn off the replication everything is fine.
I suggested increasing the IRPStackSize value in the registry, as this was mentioned in the closest article I could find on MSDN/KB (something about Norton AntiVirus), but this didn't make any difference.
This is SQL Server 2000 running on Windows 2000 Server, all with up-to-date service packs.
Any ideas or suggestions would be appreciated.
Mark
from your earlier post i understand that the snapshot creation part gives
out this message.
If the error occurs while the snapshot is being generated, check the
following"
1. Check if the sql server service account has appropriate NTFS and share
level permissions to write to the repldata directory.
2. Check if there is enough storage space on the disk for creating files
in repldata directory.
3. Check if you can run xp_cmdshell "dir
\\distributorname\c$\mssql\repldata"
and dir \\distributor\c$\mssql\repldata from command prompt.
If the error occurs after the snapshot has been completely generated, Refer
-
* KB : 285089 IRPStackSize Parameter in Windows 2000
http://support.microsoft.com/?id=285089
Pls do let me know of the SQL server version (build number). and the event
id's of the event log messages.
Does this message stop replication? are the subscribers and publisher in
sync? - Just to confirm if this message is obstructing replication from
being in sync.
We have several customers using replication on similar databases - all with the same schema, published using essentially the same script. One of them has been getting the "Not enough server storage" message when they try to access any network shares on th
e server with the published database on it. This starts happening between 24-36 hours after they first run the snapshot agent.
The application log contains an entry saying "The process could not create file \\[SERVER]\REPLDATA\unc",
and the system log has one saying "The server was unable to allocate from the system paged pool because the pool was empty".
When they turn off the replication everything is fine.
I suggested increasing the IRPStackSize value in the registry, as this was mentioned in the closest article I could find on MSDN/KB (something about Norton AntiVirus), but this didn't make any difference.
This is SQL Server 2000 running on Windows 2000 Server, all with up-to-date service packs.
Any ideas or suggestions would be appreciated.
Mark
from your earlier post i understand that the snapshot creation part gives
out this message.
If the error occurs while the snapshot is being generated, check the
following"
1. Check if the sql server service account has appropriate NTFS and share
level permissions to write to the repldata directory.
2. Check if there is enough storage space on the disk for creating files
in repldata directory.
3. Check if you can run xp_cmdshell "dir
\\distributorname\c$\mssql\repldata"
and dir \\distributor\c$\mssql\repldata from command prompt.
If the error occurs after the snapshot has been completely generated, Refer
-
* KB : 285089 IRPStackSize Parameter in Windows 2000
http://support.microsoft.com/?id=285089
Pls do let me know of the SQL server version (build number). and the event
id's of the event log messages.
Does this message stop replication? are the subscribers and publisher in
sync? - Just to confirm if this message is obstructing replication from
being in sync.
Saturday, February 25, 2012
"must declare variable" when variable has been declared!
Any idea why the following message is returned?
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@.table'.
The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).
excuse the poorly laid out SQL - it's hard to copy and paste into this thing...
Thanks
Yal
------------------------
SET NOCOUNT ON
DECLARE @.table VARCHAR(32)
,@.column VARCHAR(32)
,@.x_table VARCHAR(32)
,@.x_column VARCHAR(32)
,@.x_type VARCHAR(32)
,@.problem VARCHAR(32)
,@.count VARCHAR(32)
DECLARE xref_check CURSOR FOR
SELECT table_name, column_name, xref_table, xref_column, xref_type
FROM xref_check
OPEN xref_check
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.x_table = 'PS_XREF'
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')
END'
)
END
ELSE
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')
END')
END
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
END
CLOSE xref_check
DEALLOCATE xref_checkMake sure your references to these variables exist outside the string. For example:
@.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'
should have @.table, @.column ... outside the string like you did before this statement.
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@.table'.
The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).
excuse the poorly laid out SQL - it's hard to copy and paste into this thing...
Thanks
Yal
------------------------
SET NOCOUNT ON
DECLARE @.table VARCHAR(32)
,@.column VARCHAR(32)
,@.x_table VARCHAR(32)
,@.x_column VARCHAR(32)
,@.x_type VARCHAR(32)
,@.problem VARCHAR(32)
,@.count VARCHAR(32)
DECLARE xref_check CURSOR FOR
SELECT table_name, column_name, xref_table, xref_column, xref_type
FROM xref_check
OPEN xref_check
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.x_table = 'PS_XREF'
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')
END'
)
END
ELSE
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')
END')
END
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
END
CLOSE xref_check
DEALLOCATE xref_checkMake sure your references to these variables exist outside the string. For example:
@.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'
should have @.table, @.column ... outside the string like you did before this statement.
Thursday, February 16, 2012
"GO" statement in SQK2K
Hi,
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>
"Generate SQL Script" functionality
By default, if you use the "Generate SQL Script" menu item when
right-clicking on a table, SQL Server will generate a scripts such as:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Simple and straightforward, but presents a problem when the table exists and
contains data. Is there a way to modify SQL Server (e.g., is there a template
somewhere?) such that all future scripts generated using this functionality
would have a check to verify that the table does not contain data, such as in:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
exists (select count(*) from dbo.zztest having count(*) = 0))
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The above is just an example, if there is some other way to NOT drop the
table if if contains data please advise.
Thank you!
--
Brad AshforthAdd:
if count(*) > 0 from table
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Hi Jay ... thank you, but my question was not clear. I am looking for a way
to alter SQL Server such that when we use the "Script" function it would
automatically change the output script to inlude this test, which is already
in the code I posted.
This way we can avoid having to manually alter the scripts each time a table
is created/changed.
Thanks
--
Brad Ashforth
"Jay" wrote:
> Add:
> if count(*) > 0 from table
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> > By default, if you use the "Generate SQL Script" menu item when
> > right-clicking on a table, SQL Server will generate a scripts such as:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[zzTest]
> > GO
> >
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > Simple and straightforward, but presents a problem when the table exists
> > and
> > contains data. Is there a way to modify SQL Server (e.g., is there a
> > template
> > somewhere?) such that all future scripts generated using this
> > functionality
> > would have a check to verify that the table does not contain data, such as
> > in:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> > and
> > exists (select count(*) from dbo.zztest having count(*) = 0))
> > drop table [dbo].[zzTest]
> > GO
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > The above is just an example, if there is some other way to NOT drop the
> > table if if contains data please advise.
> >
> > Thank you!
> >
> > --
> > Brad Ashforth
>
>|||Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
particular feature is there, though. You can wish this for a future version:
http://connect.microsoft.com/sqlserver.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> Hi Jay ... thank you, but my question was not clear. I am looking for a way
> to alter SQL Server such that when we use the "Script" function it would
> automatically change the output script to inlude this test, which is already
> in the code I posted.
> This way we can avoid having to manually alter the scripts each time a table
> is created/changed.
> Thanks
> --
> Brad Ashforth
>
> "Jay" wrote:
>> Add:
>> if count(*) > 0 from table
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> > By default, if you use the "Generate SQL Script" menu item when
>> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> > drop table [dbo].[zzTest]
>> > GO
>> >
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > Simple and straightforward, but presents a problem when the table exists
>> > and
>> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> > template
>> > somewhere?) such that all future scripts generated using this
>> > functionality
>> > would have a check to verify that the table does not contain data, such as
>> > in:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> > and
>> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> > drop table [dbo].[zzTest]
>> > GO
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > The above is just an example, if there is some other way to NOT drop the
>> > table if if contains data please advise.
>> >
>> > Thank you!
>> >
>> > --
>> > Brad Ashforth
>>|||Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
2000, where I did not find anything under Tools/Options. I'll send it on as a
wish ... thank you!
--
Brad Ashforth
"Tibor Karaszi" wrote:
> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
> particular feature is there, though. You can wish this for a future version:
> http://connect.microsoft.com/sqlserver.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
> > to alter SQL Server such that when we use the "Script" function it would
> > automatically change the output script to inlude this test, which is already
> > in the code I posted.
> >
> > This way we can avoid having to manually alter the scripts each time a table
> > is created/changed.
> >
> > Thanks
> > --
> > Brad Ashforth
> >
> >
> > "Jay" wrote:
> >
> >> Add:
> >>
> >> if count(*) > 0 from table
> >>
> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> >> > By default, if you use the "Generate SQL Script" menu item when
> >> > right-clicking on a table, SQL Server will generate a scripts such as:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> >
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > Simple and straightforward, but presents a problem when the table exists
> >> > and
> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
> >> > template
> >> > somewhere?) such that all future scripts generated using this
> >> > functionality
> >> > would have a check to verify that the table does not contain data, such as
> >> > in:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> >> > and
> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > The above is just an example, if there is some other way to NOT drop the
> >> > table if if contains data please advise.
> >> >
> >> > Thank you!
> >> >
> >> > --
> >> > Brad Ashforth
> >>
> >>
> >>
>
>|||Sorry, I'm referring to 2005. No such option in 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:396AC362-1D58-4E5B-8E16-A106DB86A522@.microsoft.com...
> Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
> 2000, where I did not find anything under Tools/Options. I'll send it on as a
> wish ... thank you!
> --
> Brad Ashforth
>
> "Tibor Karaszi" wrote:
>> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that
>> this
>> particular feature is there, though. You can wish this for a future version:
>> http://connect.microsoft.com/sqlserver.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
>> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
>> > to alter SQL Server such that when we use the "Script" function it would
>> > automatically change the output script to inlude this test, which is already
>> > in the code I posted.
>> >
>> > This way we can avoid having to manually alter the scripts each time a table
>> > is created/changed.
>> >
>> > Thanks
>> > --
>> > Brad Ashforth
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> Add:
>> >>
>> >> if count(*) > 0 from table
>> >>
>> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> >> > By default, if you use the "Generate SQL Script" menu item when
>> >> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > Simple and straightforward, but presents a problem when the table exists
>> >> > and
>> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> >> > template
>> >> > somewhere?) such that all future scripts generated using this
>> >> > functionality
>> >> > would have a check to verify that the table does not contain data, such as
>> >> > in:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> >> > and
>> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > The above is just an example, if there is some other way to NOT drop the
>> >> > table if if contains data please advise.
>> >> >
>> >> > Thank you!
>> >> >
>> >> > --
>> >> > Brad Ashforth
>> >>
>> >>
>> >>
>>|||Why are you manually scripting tables and then running the DDL on your
server?
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Here is a bit of T-SQL code from a Data Dictionary program I'm writing.
Shouldn't be too hard to make it produce basic DDL.
--USE msdb Change to DB you want. The proc will be stored there too.
IF EXISTS (SELECT * FROM dbo.sysobjects where id =object_id(N'[dbo].[ScriptTable]') and OBJECTPROPERTY(id, N'IsProcedure') =1)
DROP PROCEDURE [dbo].[ScriptTable]
GO
CREATE PROCEDURE [ScriptTable]
AS
DECLARE @.Table SYSNAME
DECLARE @.TableID INT
DECLARE @.Column SYSNAME
DECLARE @.ColID SMALLINT
DECLARE @.VarType TINYINT
DECLARE @.VarName SYSNAME
DECLARE @.VarLen INT
DECLARE @.ColStatus TINYINT
DECLARE @.StatusDesc CHAR(8)
DECLARE @.IsNullable INT
DECLARE @.ConstraintName SYSNAME
DECLARE @.ColumnDefault CHAR(50)
DECLARE @.Pad1 CHAR(5)
DECLARE @.Pad2 CHAR(5)
DECLARE @.ColNameLen SMALLINT
DECLARE @.ObjName SYSNAME
DECLARE @.ObjType CHAR(4) -- Yes I know it's 2
DECLARE @.PKFK CHAR(11)
DECLARE @.DispPKFK CHAR(6)
DECLARE cTables CURSOR FOR
SELECT so.name, so.id
FROM sysobjects so
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
ORDER BY so.name
OPEN cTables
FETCH FROM cTables INTO @.Table, @.TableID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT 'Table: ' + RTRIM(CONVERT(CHAR(30), @.Table)) --+ ' id: ' +
CONVERT(CHAR(12), @.TableID)
PRINT 'Stored procedures (P), Triggers (TR) and Views (V) that access: "' +
RTRIM(@.Table) + '"'
PRINT 'Type Name'
PRINT '-- --'
DECLARE cProcedures CURSOR FOR
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
OPEN cProcedures
FETCH cProcedures INTO @.ObjName, @.ObjType
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT @.ObjType + ' ' + CONVERT(CHAR(40), @.ObjName)
FETCH cProcedures INTO @.ObjName, @.ObjType
END
CLOSE cProcedures
DEALLOCATE cProcedures
PRINT ''
PRINT 'PK/FK Column Null Type Default Constraint Comment'
PRINT
'-- -- -- -- -- -- --'
DECLARE cColumns CURSOR FOR
SELECT sc.name, sc.colid, st.name, sc.length, sc.xtype, sc.status,
sc.isnullable
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE id = @.TableID
ORDER BY sc.colorder
OPEN cColumns
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Get the column constraint (if available)
SET @.ConstraintName = ''
SELECT @.ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
-- Get the default value
SET @.ColumnDefault = ' '
SELECT @.ColumnDefault = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
IF @.ColumnDefault IN ('('''')', 'NULL') SET @.ColumnDefault = ' '
IF @.ColumnDefault IS NULL
SET @.ColumnDefault = ' '
ELSE
BEGIN
IF CHARINDEX('(', @.ColumnDefault, 1) = 1
SET @.ColumnDefault = SUBSTRING(@.ColumnDefault, 2, LEN(@.ColumnDefault)-2)
END
-- Find IDENTITY, NOT NULL
SET @.StatusDesc = ' '
IF @.ColStatus = 0
SET @.StatusDesc = ' '
IF @.ColStatus = 128
SET @.StatusDesc = 'IDENTITY'
ELSE IF @.IsNullable = 0
SET @.StatusDesc = 'NOT NULL'
-- Define the pad
SET @.ColNameLen = LEN(RTRIM(@.Column))
IF @.ColNameLen >= 24
SET @.Pad1 = ''
ELSE IF @.ColNameLen >= 16
SET @.Pad1 = ' '
ELSE IF @.ColNameLen >= 8
SET @.Pad1 = ' '
ELSE
SET @.Pad1 = ' '
SET @.ColNameLen = LEN(RTRIM(@.VarName))
IF @.VarType IN (175, 167)
BEGIN
SET @.Pad2 = ''
END
ELSE
BEGIN
IF @.ColNameLen >= 16
SET @.Pad2 = ''
ELSE IF @.ColNameLen >= 8
SET @.Pad2 = ''
ELSE
SET @.Pad2 = ' '
END
-- Is PKFK?
DECLARE cPKFK CURSOR FOR
SELECT TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME =KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
ORDER BY TC.CONSTRAINT_TYPE DESC
OPEN cPKFK
SET @.DispPKFK = ''
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- PRINT '[' + @.PKFK + ']'
IF @.PKFK = 'PRIMARY KEY' SET @.DispPKFK = 'PK'
IF @.PKFK = 'FOREIGN KEY' SET @.DispPKFK = ' FK'
IF @.PKFK = 'UNIQUE ' SET @.DispPKFK = ' U'
-- PRINT 'XXX[' + @.DispPKFK + ']XXX'
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
END
CLOSE cPKFK
DEALLOCATE cPKFK
IF @.VarType IN (175, 167)
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ UPPER(RTRIM(CONVERT(CHAR(20), @.VarName))) + '('
+ RTRIM(CONVERT(CHAR(5), @.VarLen)) + ')' + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + ' s: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
ELSE
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ RTRIM(UPPER(CONVERT(CHAR(20), @.VarName))) + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + 's: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
END
PRINT ''
CLOSE cColumns
DEALLOCATE cColumns
--BREAK
FETCH FROM cTables INTO @.Table, @.TableID
PRINT
'==============================================================================================================================='
PRINT ''
PRINT ''
END
CLOSE cTables
DEALLOCATE cTables
GO
EXECUTE [ScriptTable]
/*
*/|||Hi Brad,
This feature is not included in both SQL Server 2000 SP4 and SQL Server
2005 SP2. I agree with Tibor that you give Microsoft feedback so that your
voice will be heard by the product team and hope that this feature can be
included in the next release of SQL Server. Appreciate your understanding
that by the original design of SQL Server, it could not think all the
aspects of customers needs. Your feedback is a great impetus to empower
Microsoft product to become better and better.
Now if you are very concerned with this issue, you may consider developing
a tool by yourself to do this. Per my experiences, this should not be a
hard work.
If you have any other questions or concerns, please feel free to let us
know. Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Here's an open source console app you could easily modify to give you
want you are looking for:
http://www.codeplex.com/scriptdb
It will work against any 2000 or 2005 db, requires SMO to be installed
on the client.
On Sep 5, 6:38 am, Brad Ashforth <banos...@.nospam.nospam> wrote:
> By default, if you use the "GenerateSQLScript" menu item when
> right-clicking on a table,SQLServer willgeneratea scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists and
> contains data. Is there a way to modifySQLServer (e.g., is there a template
> somewhere?) such that all future scripts generated using this functionality
> would have a check to verify that the table does not contain data, such as in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth
right-clicking on a table, SQL Server will generate a scripts such as:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Simple and straightforward, but presents a problem when the table exists and
contains data. Is there a way to modify SQL Server (e.g., is there a template
somewhere?) such that all future scripts generated using this functionality
would have a check to verify that the table does not contain data, such as in:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
exists (select count(*) from dbo.zztest having count(*) = 0))
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The above is just an example, if there is some other way to NOT drop the
table if if contains data please advise.
Thank you!
--
Brad AshforthAdd:
if count(*) > 0 from table
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Hi Jay ... thank you, but my question was not clear. I am looking for a way
to alter SQL Server such that when we use the "Script" function it would
automatically change the output script to inlude this test, which is already
in the code I posted.
This way we can avoid having to manually alter the scripts each time a table
is created/changed.
Thanks
--
Brad Ashforth
"Jay" wrote:
> Add:
> if count(*) > 0 from table
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> > By default, if you use the "Generate SQL Script" menu item when
> > right-clicking on a table, SQL Server will generate a scripts such as:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[zzTest]
> > GO
> >
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > Simple and straightforward, but presents a problem when the table exists
> > and
> > contains data. Is there a way to modify SQL Server (e.g., is there a
> > template
> > somewhere?) such that all future scripts generated using this
> > functionality
> > would have a check to verify that the table does not contain data, such as
> > in:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> > and
> > exists (select count(*) from dbo.zztest having count(*) = 0))
> > drop table [dbo].[zzTest]
> > GO
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > The above is just an example, if there is some other way to NOT drop the
> > table if if contains data please advise.
> >
> > Thank you!
> >
> > --
> > Brad Ashforth
>
>|||Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
particular feature is there, though. You can wish this for a future version:
http://connect.microsoft.com/sqlserver.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> Hi Jay ... thank you, but my question was not clear. I am looking for a way
> to alter SQL Server such that when we use the "Script" function it would
> automatically change the output script to inlude this test, which is already
> in the code I posted.
> This way we can avoid having to manually alter the scripts each time a table
> is created/changed.
> Thanks
> --
> Brad Ashforth
>
> "Jay" wrote:
>> Add:
>> if count(*) > 0 from table
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> > By default, if you use the "Generate SQL Script" menu item when
>> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> > drop table [dbo].[zzTest]
>> > GO
>> >
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > Simple and straightforward, but presents a problem when the table exists
>> > and
>> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> > template
>> > somewhere?) such that all future scripts generated using this
>> > functionality
>> > would have a check to verify that the table does not contain data, such as
>> > in:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> > and
>> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> > drop table [dbo].[zzTest]
>> > GO
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > The above is just an example, if there is some other way to NOT drop the
>> > table if if contains data please advise.
>> >
>> > Thank you!
>> >
>> > --
>> > Brad Ashforth
>>|||Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
2000, where I did not find anything under Tools/Options. I'll send it on as a
wish ... thank you!
--
Brad Ashforth
"Tibor Karaszi" wrote:
> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
> particular feature is there, though. You can wish this for a future version:
> http://connect.microsoft.com/sqlserver.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
> > to alter SQL Server such that when we use the "Script" function it would
> > automatically change the output script to inlude this test, which is already
> > in the code I posted.
> >
> > This way we can avoid having to manually alter the scripts each time a table
> > is created/changed.
> >
> > Thanks
> > --
> > Brad Ashforth
> >
> >
> > "Jay" wrote:
> >
> >> Add:
> >>
> >> if count(*) > 0 from table
> >>
> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> >> > By default, if you use the "Generate SQL Script" menu item when
> >> > right-clicking on a table, SQL Server will generate a scripts such as:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> >
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > Simple and straightforward, but presents a problem when the table exists
> >> > and
> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
> >> > template
> >> > somewhere?) such that all future scripts generated using this
> >> > functionality
> >> > would have a check to verify that the table does not contain data, such as
> >> > in:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> >> > and
> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > The above is just an example, if there is some other way to NOT drop the
> >> > table if if contains data please advise.
> >> >
> >> > Thank you!
> >> >
> >> > --
> >> > Brad Ashforth
> >>
> >>
> >>
>
>|||Sorry, I'm referring to 2005. No such option in 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:396AC362-1D58-4E5B-8E16-A106DB86A522@.microsoft.com...
> Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
> 2000, where I did not find anything under Tools/Options. I'll send it on as a
> wish ... thank you!
> --
> Brad Ashforth
>
> "Tibor Karaszi" wrote:
>> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that
>> this
>> particular feature is there, though. You can wish this for a future version:
>> http://connect.microsoft.com/sqlserver.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
>> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
>> > to alter SQL Server such that when we use the "Script" function it would
>> > automatically change the output script to inlude this test, which is already
>> > in the code I posted.
>> >
>> > This way we can avoid having to manually alter the scripts each time a table
>> > is created/changed.
>> >
>> > Thanks
>> > --
>> > Brad Ashforth
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> Add:
>> >>
>> >> if count(*) > 0 from table
>> >>
>> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> >> > By default, if you use the "Generate SQL Script" menu item when
>> >> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > Simple and straightforward, but presents a problem when the table exists
>> >> > and
>> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> >> > template
>> >> > somewhere?) such that all future scripts generated using this
>> >> > functionality
>> >> > would have a check to verify that the table does not contain data, such as
>> >> > in:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> >> > and
>> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > The above is just an example, if there is some other way to NOT drop the
>> >> > table if if contains data please advise.
>> >> >
>> >> > Thank you!
>> >> >
>> >> > --
>> >> > Brad Ashforth
>> >>
>> >>
>> >>
>>|||Why are you manually scripting tables and then running the DDL on your
server?
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Here is a bit of T-SQL code from a Data Dictionary program I'm writing.
Shouldn't be too hard to make it produce basic DDL.
--USE msdb Change to DB you want. The proc will be stored there too.
IF EXISTS (SELECT * FROM dbo.sysobjects where id =object_id(N'[dbo].[ScriptTable]') and OBJECTPROPERTY(id, N'IsProcedure') =1)
DROP PROCEDURE [dbo].[ScriptTable]
GO
CREATE PROCEDURE [ScriptTable]
AS
DECLARE @.Table SYSNAME
DECLARE @.TableID INT
DECLARE @.Column SYSNAME
DECLARE @.ColID SMALLINT
DECLARE @.VarType TINYINT
DECLARE @.VarName SYSNAME
DECLARE @.VarLen INT
DECLARE @.ColStatus TINYINT
DECLARE @.StatusDesc CHAR(8)
DECLARE @.IsNullable INT
DECLARE @.ConstraintName SYSNAME
DECLARE @.ColumnDefault CHAR(50)
DECLARE @.Pad1 CHAR(5)
DECLARE @.Pad2 CHAR(5)
DECLARE @.ColNameLen SMALLINT
DECLARE @.ObjName SYSNAME
DECLARE @.ObjType CHAR(4) -- Yes I know it's 2
DECLARE @.PKFK CHAR(11)
DECLARE @.DispPKFK CHAR(6)
DECLARE cTables CURSOR FOR
SELECT so.name, so.id
FROM sysobjects so
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
ORDER BY so.name
OPEN cTables
FETCH FROM cTables INTO @.Table, @.TableID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT 'Table: ' + RTRIM(CONVERT(CHAR(30), @.Table)) --+ ' id: ' +
CONVERT(CHAR(12), @.TableID)
PRINT 'Stored procedures (P), Triggers (TR) and Views (V) that access: "' +
RTRIM(@.Table) + '"'
PRINT 'Type Name'
PRINT '-- --'
DECLARE cProcedures CURSOR FOR
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
OPEN cProcedures
FETCH cProcedures INTO @.ObjName, @.ObjType
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT @.ObjType + ' ' + CONVERT(CHAR(40), @.ObjName)
FETCH cProcedures INTO @.ObjName, @.ObjType
END
CLOSE cProcedures
DEALLOCATE cProcedures
PRINT ''
PRINT 'PK/FK Column Null Type Default Constraint Comment'
'-- -- -- -- -- -- --'
DECLARE cColumns CURSOR FOR
SELECT sc.name, sc.colid, st.name, sc.length, sc.xtype, sc.status,
sc.isnullable
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE id = @.TableID
ORDER BY sc.colorder
OPEN cColumns
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Get the column constraint (if available)
SET @.ConstraintName = ''
SELECT @.ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
-- Get the default value
SET @.ColumnDefault = ' '
SELECT @.ColumnDefault = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
IF @.ColumnDefault IN ('('''')', 'NULL') SET @.ColumnDefault = ' '
IF @.ColumnDefault IS NULL
SET @.ColumnDefault = ' '
ELSE
BEGIN
IF CHARINDEX('(', @.ColumnDefault, 1) = 1
SET @.ColumnDefault = SUBSTRING(@.ColumnDefault, 2, LEN(@.ColumnDefault)-2)
END
-- Find IDENTITY, NOT NULL
SET @.StatusDesc = ' '
IF @.ColStatus = 0
SET @.StatusDesc = ' '
IF @.ColStatus = 128
SET @.StatusDesc = 'IDENTITY'
ELSE IF @.IsNullable = 0
SET @.StatusDesc = 'NOT NULL'
-- Define the pad
SET @.ColNameLen = LEN(RTRIM(@.Column))
IF @.ColNameLen >= 24
SET @.Pad1 = ''
ELSE IF @.ColNameLen >= 16
SET @.Pad1 = ' '
ELSE IF @.ColNameLen >= 8
SET @.Pad1 = ' '
ELSE
SET @.Pad1 = ' '
SET @.ColNameLen = LEN(RTRIM(@.VarName))
IF @.VarType IN (175, 167)
BEGIN
SET @.Pad2 = ''
END
ELSE
BEGIN
IF @.ColNameLen >= 16
SET @.Pad2 = ''
ELSE IF @.ColNameLen >= 8
SET @.Pad2 = ''
ELSE
SET @.Pad2 = ' '
END
-- Is PKFK?
DECLARE cPKFK CURSOR FOR
SELECT TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME =KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
ORDER BY TC.CONSTRAINT_TYPE DESC
OPEN cPKFK
SET @.DispPKFK = ''
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- PRINT '[' + @.PKFK + ']'
IF @.PKFK = 'PRIMARY KEY' SET @.DispPKFK = 'PK'
IF @.PKFK = 'FOREIGN KEY' SET @.DispPKFK = ' FK'
IF @.PKFK = 'UNIQUE ' SET @.DispPKFK = ' U'
-- PRINT 'XXX[' + @.DispPKFK + ']XXX'
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
END
CLOSE cPKFK
DEALLOCATE cPKFK
IF @.VarType IN (175, 167)
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ UPPER(RTRIM(CONVERT(CHAR(20), @.VarName))) + '('
+ RTRIM(CONVERT(CHAR(5), @.VarLen)) + ')' + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + ' s: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
ELSE
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ RTRIM(UPPER(CONVERT(CHAR(20), @.VarName))) + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + 's: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
END
PRINT ''
CLOSE cColumns
DEALLOCATE cColumns
--BREAK
FETCH FROM cTables INTO @.Table, @.TableID
'==============================================================================================================================='
PRINT ''
PRINT ''
END
CLOSE cTables
DEALLOCATE cTables
GO
EXECUTE [ScriptTable]
/*
*/|||Hi Brad,
This feature is not included in both SQL Server 2000 SP4 and SQL Server
2005 SP2. I agree with Tibor that you give Microsoft feedback so that your
voice will be heard by the product team and hope that this feature can be
included in the next release of SQL Server. Appreciate your understanding
that by the original design of SQL Server, it could not think all the
aspects of customers needs. Your feedback is a great impetus to empower
Microsoft product to become better and better.
Now if you are very concerned with this issue, you may consider developing
a tool by yourself to do this. Per my experiences, this should not be a
hard work.
If you have any other questions or concerns, please feel free to let us
know. Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Here's an open source console app you could easily modify to give you
want you are looking for:
http://www.codeplex.com/scriptdb
It will work against any 2000 or 2005 db, requires SMO to be installed
on the client.
On Sep 5, 6:38 am, Brad Ashforth <banos...@.nospam.nospam> wrote:
> By default, if you use the "GenerateSQLScript" menu item when
> right-clicking on a table,SQLServer willgeneratea scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists and
> contains data. Is there a way to modifySQLServer (e.g., is there a template
> somewhere?) such that all future scripts generated using this functionality
> would have a check to verify that the table does not contain data, such as in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth
Monday, February 13, 2012
"Default bound to column
I am running a script against a couple of databases on my SQL Server 2000 Standard Edition Instance and I am getting the following in the results pane:
"Default bound to column" I have searched the MS Knowledge base and found a couple of vague references to this. Does anyone know why I might be getting this? the script 'seems' to run fine.. except for the funky error in the results pane. Script is attached. Thank you!!maybe no one has answered because zip files make people nervous (viruses). Just post the code using the tags. I know I am not opening your zip file.|||IIRC the sp_bindefault will give that message; just run one of them to confirm that.
ALSO never zip up a SQL script as a DOC file and think you are going to get an answer.
Change it to a text file and then only if it is very large zip it up.
doc files are the most common file used to tranmit virii to other people followed by zip files so very few people word open your file.
I have both zip & doc files set to open with non-MS software so I should be safe but I almost did not open the doc.
Tim S
"Default bound to column" I have searched the MS Knowledge base and found a couple of vague references to this. Does anyone know why I might be getting this? the script 'seems' to run fine.. except for the funky error in the results pane. Script is attached. Thank you!!maybe no one has answered because zip files make people nervous (viruses). Just post the code using the tags. I know I am not opening your zip file.|||IIRC the sp_bindefault will give that message; just run one of them to confirm that.
ALSO never zip up a SQL script as a DOC file and think you are going to get an answer.
Change it to a text file and then only if it is very large zip it up.
doc files are the most common file used to tranmit virii to other people followed by zip files so very few people word open your file.
I have both zip & doc files set to open with non-MS software so I should be safe but I almost did not open the doc.
Tim S
Friday, January 27, 2012
"..Transform requires script Text and Language.." error?
Hi,
I'm trying setup a package to replicate a database table into a another
database's table.
I thought a Data Driven Query Task would work. I got as far as making the
two connections and setting up the Data Driven Query's source SELECT (with
stored procedure I created in the source database) and destination table
under the Bindings tab.
However, when I run the task I get the error below
"ActiveX Scripting Transform requires script Text and Language and at least
one Phase function to be specified."
When I preview the data in under the task's Source tab, I see data AND the
destination table gets created but not data gets transfered. Can someone
please help on what this error means?
Also, I eventually want to update this destination table with several other
subsequent queries from other databases in order to conslidate related data
into one large reporting table but I have to get the initial population of
data to work first. This will be refreshed daily
Does this approach/package task seem to be applicable?
Thanks, Dave.
Dave,
you'd be best off posting in the separate DTS discussion group.
Rgds,
Paul Ibison
|||Open up your package, save it as a bas file and post it here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||I have the exact same problem as this guy. Could you please help?
thanks
marlene
url:http://www.ureader.com/msg/1156295.aspx
I'm trying setup a package to replicate a database table into a another
database's table.
I thought a Data Driven Query Task would work. I got as far as making the
two connections and setting up the Data Driven Query's source SELECT (with
stored procedure I created in the source database) and destination table
under the Bindings tab.
However, when I run the task I get the error below
"ActiveX Scripting Transform requires script Text and Language and at least
one Phase function to be specified."
When I preview the data in under the task's Source tab, I see data AND the
destination table gets created but not data gets transfered. Can someone
please help on what this error means?
Also, I eventually want to update this destination table with several other
subsequent queries from other databases in order to conslidate related data
into one large reporting table but I have to get the initial population of
data to work first. This will be refreshed daily
Does this approach/package task seem to be applicable?
Thanks, Dave.
Dave,
you'd be best off posting in the separate DTS discussion group.
Rgds,
Paul Ibison
|||Open up your package, save it as a bas file and post it here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||I have the exact same problem as this guy. Could you please help?
thanks
marlene
url:http://www.ureader.com/msg/1156295.aspx
Database Scripting Question
I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
Database Scripting Question
I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
Subscribe to:
Posts (Atom)
