Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED Expand / Collapse
Author
Message
Posted Friday, October 24, 2008 2:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:52 PM
Points: 55, Visits: 157
Comments posted to this topic are about the item AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED
Post #591519
Posted Friday, November 21, 2008 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 06, 2013 8:07 AM
Points: 109, Visits: 489
Shouldn't it be CHAR(13) + CHAR(10) (i.e. CR + LF)?

http://en.wikipedia.org/wiki/Newline
Post #606587
Posted Sunday, November 23, 2008 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 1:51 PM
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

Post #607173
Posted Sunday, November 23, 2008 7:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:57 PM
Points: 56, Visits: 1,026
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
Post #607268
Posted Sunday, November 23, 2008 8:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 06, 2013 8:07 AM
Points: 109, Visits: 489
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)
Post #607269
Posted Sunday, November 23, 2008 8:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:57 PM
Points: 56, Visits: 1,026
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 :P, 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.


  Post Attachments 
GetSchemaSummary.zip (19 views, 1.08 KB)
Post #607273
Posted Sunday, November 23, 2008 8:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:57 PM
Points: 56, Visits: 1,026
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....
Post #607278
Posted Monday, November 24, 2008 12:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:52 PM
Points: 55, Visits: 157
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
Post #607319
Posted Monday, November 24, 2008 12:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:52 PM
Points: 55, Visits: 157
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
)

Post #607328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse