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

Code Generation: Using SQL to Generate SQL and Other Code Expand / Collapse
Author
Message
Posted Monday, February 7, 2005 4:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/codegenerationusingsqltogeneratesqlandothercode.asp


Post #160169
Posted Monday, February 14, 2005 1:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1
Excellent article. I have tinkered for quite some time with a QA alternative, and the thing that has bugged me most is the generation of SQL statements. This article has given me a great leg up.



"See how deep the rabbit hole goes..."
Post #161366
Posted Monday, February 14, 2005 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 22, 2011 8:18 AM
Points: 29, Visits: 8

Cool. Especially the way to automate BCP. Very useful as a backup tool.

However, I have a problem  with this bcp feature itself : I have a database which holds many French words, spelled with the nice é, è, ê, û aso characters.

When I bcp them out, and then re-bcp them in, they are converted to silly other things , due to the non-unicode-compliance of wordpad flat text documents . Any idea how I could conserve the code page stored in nvarchar fields, in a bcp file?

Maybe, a pro like you guys knows an answer here ?

Post #161388
Posted Monday, February 14, 2005 7:50 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:01 PM
Points: 295, Visits: 287

Great article. I love code generation myself and started writing an article on it but it sucked so I have abandoned it. Maybe I will pick it up again sometime. I was going to focus more on tools like CodeSmith and why people would want to use code generators. Maybe someday...

Olivier: I have a script for generating insert statements for a given table and there might be others on the site and just posted it (should be available within 48 hours). The script is called Script Table Data to Insert Statements and is in the Maintenance and Misc. categories. It's not bcp but it has been very useful to me.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #161431
Posted Monday, February 14, 2005 1:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Olivier,

Thanks for your compliment. I'm glad you like this article.

In addition to -c -q -S switches in my code, try adding -w in both bcp scripts. -w is the unicode switch. With that switch, BCP will perform the bulk copy operation using Unicode characters. It uses nchar as the storage type. Hopefully that will take care of accented letters like é, è, ê, û. According to Books on Line, -w will not work for SQL Server 6.5.

All systems I have are in American English collation/code page, therefore I couldn't test that for you. I am curious to know if that solves your problem.

Haidong



Post #161559
Posted Wednesday, February 16, 2005 2:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 12, 2005 1:45 AM
Points: 1, Visits: 1

I read a lot of brilliant comments but using system table (SYSOBJECTS and other) is not the better choice if one is working on SQL server 2000 or 2005 where we have the information_schema views that offer the same behaviour giving us a good level of abstraction from the "implementation" into db.

I don't know how it works on other DB but I think there are similar stuffs.

Any way we are talking about common technique used to “generate code”. For instance, for MS SQL server, is available an object model form .NET version 2.0 more flexible and powerful. It’s true that it’s important having a good programming skills.

I don’t the article is good but it doesn’t express new idea or, for me, the better way to do this kind of operation.

Thanks

Marittiello

Post #161964
Posted Wednesday, February 16, 2005 1:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 22, 2011 8:18 AM
Points: 29, Visits: 8

Cool man. -w solves all my problems.

Finally an adequate way to backup my years of hard work

Post #162158
Posted Thursday, February 17, 2005 9:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Very good Olivier, glad it worked out for you.

Haidong



Post #162431
Posted Wednesday, February 15, 2006 8:08 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 113, Visits: 84

I very much enjoyed the article and think it will be very useful in my developement work.  To switch to using catalog views in SQL Server 2005 isn't difficult at all.

For the first example on getting counts you just have to make a small change from sysobjects to sys.objects and "type" instead of "xtype" like this:

select 'select ' + char(39) + name + char(39) + ' as dbtable, ' +
'count(*) as rows from [' + name + ']'
from sys.objects where type = 'u' and name not in ('dtproperties')
order by name

Thanks for the article!




Post #258679
Posted Wednesday, February 15, 2006 7:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Excellent! I haven't really tried it in Microsoft SQL Server 2005 yet. Now I learned something from your example


Post #258907
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse