Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD
has the :r option. The
sub-script cannot have GO in it, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com..
.
> The problem I am having is that I want to replace the sql script language
in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single sc
ript file?
>

Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD has the :r option. The
sub-script cannot have GO in it, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com...
> The problem I am having is that I want to replace the sql script language in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single script file?
>sql

Scripting

I'm used to using Unix to do scripting. It's a pretty powerful scripting
language with some looping, conditions, text editing/modification, etc. Does
DOS have all that built in as well? Is that you guys use to script? If so,
does it have looping, conditions, etc somewhat like Unix?CLM,
Try VBScript. SQL Server has the capability to used ActiveX scripts in DTS
packages and jobs.
For more information on VBScript see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:0CCA63F8-34C0-40DE-A772-10A218F5F58A@.microsoft.com...
> I'm used to using Unix to do scripting. It's a pretty powerful scripting
> language with some looping, conditions, text editing/modification, etc.
> Does
> DOS have all that built in as well? Is that you guys use to script? If
> so,
> does it have looping, conditions, etc somewhat like Unix?|||One last question: can you execute it from the command line though?
"Jerry Spivey" wrote:
> CLM,
> Try VBScript. SQL Server has the capability to used ActiveX scripts in DTS
> packages and jobs.
> For more information on VBScript see:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
> HTH
> Jerry
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:0CCA63F8-34C0-40DE-A772-10A218F5F58A@.microsoft.com...
> > I'm used to using Unix to do scripting. It's a pretty powerful scripting
> > language with some looping, conditions, text editing/modification, etc.
> > Does
> > DOS have all that built in as well? Is that you guys use to script? If
> > so,
> > does it have looping, conditions, etc somewhat like Unix?
>
>|||Yes use WScript or CScript (can be wrapped in .bat file as well).
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:5653274C-128F-4D53-A024-14EC4434E831@.microsoft.com...
> One last question: can you execute it from the command line though?
> "Jerry Spivey" wrote:
>> CLM,
>> Try VBScript. SQL Server has the capability to used ActiveX scripts in
>> DTS
>> packages and jobs.
>> For more information on VBScript see:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
>> HTH
>> Jerry
>> "CLM" <CLM@.discussions.microsoft.com> wrote in message
>> news:0CCA63F8-34C0-40DE-A772-10A218F5F58A@.microsoft.com...
>> > I'm used to using Unix to do scripting. It's a pretty powerful
>> > scripting
>> > language with some looping, conditions, text editing/modification, etc.
>> > Does
>> > DOS have all that built in as well? Is that you guys use to script?
>> > If
>> > so,
>> > does it have looping, conditions, etc somewhat like Unix?
>>|||Thx. That ought to keep me busy. :)
"Jerry Spivey" wrote:
> Yes use WScript or CScript (can be wrapped in .bat file as well).
> HTH
> Jerry
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:5653274C-128F-4D53-A024-14EC4434E831@.microsoft.com...
> > One last question: can you execute it from the command line though?
> >
> > "Jerry Spivey" wrote:
> >
> >> CLM,
> >>
> >> Try VBScript. SQL Server has the capability to used ActiveX scripts in
> >> DTS
> >> packages and jobs.
> >>
> >> For more information on VBScript see:
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
> >>
> >> HTH
> >>
> >> Jerry
> >> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> >> news:0CCA63F8-34C0-40DE-A772-10A218F5F58A@.microsoft.com...
> >> > I'm used to using Unix to do scripting. It's a pretty powerful
> >> > scripting
> >> > language with some looping, conditions, text editing/modification, etc.
> >> > Does
> >> > DOS have all that built in as well? Is that you guys use to script?
> >> > If
> >> > so,
> >> > does it have looping, conditions, etc somewhat like Unix?
> >>
> >>
> >>
>
>|||You can use the CSCRIPT from command line to execute ActiveX Script / if you
are using DTS packages , you could use DTSWIZ
"CLM" wrote:
> One last question: can you execute it from the command line though?
> "Jerry Spivey" wrote:
> > CLM,
> >
> > Try VBScript. SQL Server has the capability to used ActiveX scripts in DTS
> > packages and jobs.
> >
> > For more information on VBScript see:
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/scriptinga.asp
> >
> > HTH
> >
> > Jerry
> > "CLM" <CLM@.discussions.microsoft.com> wrote in message
> > news:0CCA63F8-34C0-40DE-A772-10A218F5F58A@.microsoft.com...
> > > I'm used to using Unix to do scripting. It's a pretty powerful scripting
> > > language with some looping, conditions, text editing/modification, etc.
> > > Does
> > > DOS have all that built in as well? Is that you guys use to script? If
> > > so,
> > > does it have looping, conditions, etc somewhat like Unix?
> >
> >
> >sql

Script Task

How do I specify a different script language in my "Script Task"?. The default is Visual Basic .NET. I would like to use c# for all my scripting task.

Thanks,

Sergio

You can only use VB.NET in 2005. You will be able to use C# in SQL Server 2008.
|||

Thanks for your reply, I guess I will be forced to learn vb.net. I am a c# developer and unfortunally this will slow me down since there will be a learning curve.

Thanks,

Sergio

|||

Sergio wrote:

Thanks for your reply, I guess I will be forced to learn vb.net. I am a c# developer and unfortunally this will slow me down since there will be a learning curve.

Thanks,

Sergio

I promise you, if you know C# its not a very steep learning curve at all.

Script for Publishing

Can the script files be written in any .net language? Writing code in VB
makes me ill.You could write a C# custom app using SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"?" <?@.discussions.microsoft.com> wrote in message
news:A37994BD-2958-457F-9C93-295B93406201@.microsoft.com...
> Can the script files be written in any .net language? Writing code in VB
> makes me ill.

Script component - column iteration within

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil Brammer wrote:

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil,

This should put you on the right path:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

|||

Question. Why on earth do you want to do this?

|||

Jamie Thomson wrote:

Question. Why on earth do you want to do this?

I'm trying to build an example for a poster from last night...

So the above works great for iterating the columns, now I need to assign a value to the output columns by iterating them.

Code Snippet

While counter < maxSegments
For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

If col.Name.Contains("Segment" + (counter + 1).ToString) Then
' Below I need to take the current "column name" and assign a value to it in the output collection
Row.(col.Name) = myStringArray(counter)
Exit For
End If

Next
counter = counter + 1
End While

I just cut and paste with the indentions and it just works for me!|||

Jamie Thomson wrote:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

How do you get color?

|||

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?|||

Phil Brammer wrote:

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?

I just copy and paste as well

I get colour but no indents. Phil gets indents but no colour. Weird.

I'm on Vista and IE7.

-Jamie

P.S. color not colour. Whoops - better not get into that!!!

|||

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.
|||

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.|||

Phil Brammer wrote:

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.

really? dang. I didn't know that.

|||

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

|||

Rafael Salas wrote:

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

Yes, I'm a moderator. Hence I have the ability to 'mark as answered'. Hence I didn't know that none-moderators do not have the ability to do this.

-Jamie

|||jaegd has a wonderful example of a script component that does what I'm looking at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

scptxfr.exe

Hello Experts,
I have some Table names and fields in my language (Greek)
and when I use the scptxfr.exe utility to script my DB
then I see "chinese" (Greek argot and maybe yours too). I
try place the Code Page but it didn't worked.
Do you have any suggestions how to resolve this problem?
Is it a good (which scripts hierachical) tool to script
my whole DB?
PS: I got SQL Server 2000 (latest Service Pack)
Thanks in advance.
Konstantinos MichasHi Konstantinos,
Have you tried running the program with the UNICODE
switch ?
That should make it collation independant.
J
>--Original Message--
>Hello Experts,
>I have some Table names and fields in my language (Greek)
>and when I use the scptxfr.exe utility to script my DB
>then I see "chinese" (Greek argot and maybe yours too). I
>try place the Code Page but it didn't worked.
>Do you have any suggestions how to resolve this problem?
>Is it a good (which scripts hierachical) tool to script
>my whole DB?
>PS: I got SQL Server 2000 (latest Service Pack)
>Thanks in advance.
>Konstantinos Michas
>.
>|||Hello Julie and thanks for your reply, it works fine know.
Thanks again!
Konstantinos Michas
>--Original Message--
>Hi Konstantinos,
>Have you tried running the program with the UNICODE
>switch ?
>That should make it collation independant.
>J
>
>>--Original Message--
>>Hello Experts,
>>I have some Table names and fields in my language
(Greek)
>>and when I use the scptxfr.exe utility to script my DB
>>then I see "chinese" (Greek argot and maybe yours too).
I
>>try place the Code Page but it didn't worked.
>>Do you have any suggestions how to resolve this
problem?
>>Is it a good (which scripts hierachical) tool to script
>>my whole DB?
>>PS: I got SQL Server 2000 (latest Service Pack)
>>Thanks in advance.
>>Konstantinos Michas
>>.
>.
>