Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED


AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED

Author
Message
kodracon
kodracon
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 162
Comments posted to this topic are about the item AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 491
Shouldn't it be CHAR(13) + CHAR(10) (i.e. CR + LF)?

http://en.wikipedia.org/wiki/Newline
LarsPL
LarsPL
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allways
this error msg.

Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177
unknown Objectname '#sp'.

is there something missing in your script? ... creation of this "#sp" temp table?
i have nothing found .. pls send me a reply.

thx

Sincerely Lars
Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1179
RE: CR/LF/NewLine (Cade Roux)

Actually, with Transact-SQL, it's even easier, just embed the newline in your string. Try this in your Management Studio/Query Analyzer

SELECT 'This is a string
with some
embedded new line characters'
AS Message

(have output to text to see it properly).

Unlike most programming languages, SQL doesn't require strings to be terminated on the same line, and the newlines that are embedded in the string are preserved. (I know C-based languages have better multi-line support for strings, but I don't think any of them treat the embedded new lines the way that SQL does).

/Ryan
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 491
ryan.price (11/23/2008)
RE: CR/LF/NewLine (Cade Roux)

Actually, with Transact-SQL, it's even easier, just embed the newline in your string.


Yeah, I use that occasionally. I would prefer if there was a \r escape sequence or something, though. (I usually DECLARE @CRLF and SET @CRLF = CHAR(13) + CHAR(10) when I need that)
Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1179
I went through a similar exercise not long ago...

I haven't actually run the code from this article, since I'm quite happy with mine Tongue, but looked through it and have a couple of general comments/suggestions (more to do with the SQL than the auto-generation logic)

1) lose all the CHAR(10) + CHAR(13) stuff, and use embedded linefeeds in your constants (see my other reply). it'll make it much easier to read and maintain (once you can get your head around the fact that you can actually do it - it took me a while).

2) if you're using this on SQL 2005 or later, you can switch all your varchar(8000)s for varchar(MAX)s, and the 8000 character restriction will go.

3) you should probably declare your '@Tablename' as nvarchar(128) or sysname - which is what SQL stores them as. Even if 50 already seems extremely long for a table name...

4) nchars and nvarchars have a length that is half the 'datalength' that you get from sys.columns (I may have missed that in your code).

5) rather than use a multi-line CASE..WHEN..WHEN..WHEN construct, you could use an 'IN' statement instead e.g.: select case when 'a' in ('a','b','c') then 'yes' else 'no' end.

/Ryan

P.S. just for interest' sake, I'll attach the SQL 2005+ code for a view and udf that I use for generating the 'metadata' in my equivalent sproc. In my database, I've left the view there permanently, but there's no reason why it couldn't be used as a temp table-populating query inside a sproc.
Attachments
GetSchemaSummary.zip (23 views, 1.00 KB)
Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1179
lars (11/23/2008)
if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allways
this error msg.

Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177
unknown Objectname '#sp'.

is there something missing in your script? ... creation of this "#sp" temp table?
i have nothing found .. pls send me a reply.

thx

Sincerely Lars



Thought I'd better try the procedure before I rate the article.... and I got the same problem....
kodracon
kodracon
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 162
perhaps your particular login does not have permissions top create temp tables. instead of using #sp create a table perhaps to accomodate the contents of #sp and modify the code appropiately...

something like this:


CREATE TABLE sp
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(100) NOT NULL
)

then use the find and replace function to replace #sp with sp
kodracon
kodracon
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 162
Thanks for the suggestions. i am actually not that experienced in sql and your suggestions have helped me advance my skill. thanks. also please see the post about the temporary table. as a matter of interest the code that creates the temp table is in _SPLOG_SPConstructor

CREATE TABLE #sp
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(100) NOT NULL
)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search