Showing posts with label script. Show all posts
Showing posts with label script. Show all posts

Monday, March 26, 2012

how to apply a sql script file on Sql server 2000 without OSql.exe

Hi guys

I need to apply a sql script file on Sql server 2000 by .net 2.0 program. but on the current running machine, there is not Osql.exe file. Do you guys know how to execute the sql file without the command? Is there any way provided in .net library ?

Thanks for you response!

This Should help.|||

Hi Ken

thanks for your response.

if my script file is long with comments,translation. if I combine all text lines in the file into one string to execute on sql, an error will be thrown out. how do you think that situation?

|||

Hi andi,

When you have installed a SQL Server instance, the osql.exe tool will always be installed. If you don't have SQL Server instance, the scripts cannot be applied to any SQL Server instance.

sql

Friday, March 23, 2012

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- Charlie
Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>
|||Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- CharlieUpdate table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- CharlieUpdate table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming Lu
EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>
|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to
> the
>
sql

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
to[vbcol=seagreen]
> the
>

How to alter a column name by using script

I am trying to write a script to change a column name (from "User_Name" to
"Customer_Name") with the same datatype, etc.
Is there a stored procedure that I can use to do this? or I have to drop the
old column, adding the new column and copy over the data?
Thanks.
--C. Ming LuEXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
If you have CHECK or other constraints on the column, you might see this:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in
enforced dependencies.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> I am trying to write a script to change a column name (from "User_Name" to
> "Customer_Name") with the same datatype, etc.
> Is there a stored procedure that I can use to do this? or I have to drop
the
> old column, adding the new column and copy over the data?
> Thanks.
> --C. Ming Lu
>|||Thank you very much.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:u%23Hor1%23QEHA.3140@.tk2msftngp13.phx.gbl...
> EXEC sp_rename 'table.oldcolname', 'newcolname', 'COLUMN'
> If you have CHECK or other constraints on the column, you might see this:
> Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
> Object 'splunge.blat' cannot be renamed because the object participates in
> enforced dependencies.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "C. Ming Lu" <cminglu@.qmxs.com> wrote in message
> news:eJKYbw#QEHA.252@.TK2MSFTNGP10.phx.gbl...
> > I am trying to write a script to change a column name (from "User_Name"
to
> > "Customer_Name") with the same datatype, etc.
> > Is there a stored procedure that I can use to do this? or I have to drop
> the
> > old column, adding the new column and copy over the data?
> >
> > Thanks.
> >
> > --C. Ming Lu
> >
> >
>

Monday, March 19, 2012

How to add sproc to multiple databases

Hi all. Lets say I had a script to run (create sproc) and I needed to
run it on several different databases on one sql server. Can someone
give me an idea how I could do that short of changing the "use databse"
line each time?
ThanksSee if this helps:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm
But be warned that this is an undocumented and unsupported system procedure.
ML
http://milambda.blogspot.com/|||You can also consider instead of having multiple copies of the proc,
just put one copy in the master database... make sure it is prefixed
with "sp_" so it can be executed from any database on that server.
I haven't tried this out myself. Be careful of any name collisions
from future service packs or upgrades.|||Personally, while I have done this in the past, I am starting to shy away
from this practice. It does work, but the problem here is the same problem
as DLL hell. So I have a utility procedure named sp_stringParse, or
whatever. I use this in three databases, and I really think it needs to be
improved for a task in database 3. So do I have a sp_stringParse_version1,
sp_stringParse_version2? Maybe, but instead I just put utility procedures
in the database and upgrade them as required in each system. It also saves
me in that if I don't use the new version immediately, I don't have to test
the code in the other databases until I upgrade it.
It is essential however that you use some sort of versioning process with
SourceSafe, CVS, or even just naming the files differently so you can apply
the latest versions as you discover a need to upgrade the proc in another
database.
Just my $.03 worth :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<InfoSponge3000@.gmail.com> wrote in message
news:1137783245.156785.87200@.g43g2000cwa.googlegroups.com...
> You can also consider instead of having multiple copies of the proc,
> just put one copy in the master database... make sure it is prefixed
> with "sp_" so it can be executed from any database on that server.
> I haven't tried this out myself. Be careful of any name collisions
> from future service packs or upgrades.
>

how to add rows with Script Component?

Why am I getting this design-time error from my script:

'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'

Here's my script:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim output_row As Buffer = Row

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

output_row = Row.AddRow()

End If

output_row.seq = seq

output_row.value = code

seq += 1

Next

End Sub

End Class

And here's the error it generates:

Error 3 Validation error. Convert Bib (ISC) to Prep Item_Detail: Split ISC codes [52]: Error 30311: Value of type 'ScriptComponent_d021a3fd485946868f5f0daadaf0e57c.Input0Buffer' cannot be converted to 'System.Buffer'. Line 15 Column 36 through 38 Error 30390: 'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'. Line 19 Column 30 through 39 Error 30456: 'seq' is not a member of 'System.Buffer'. Line 21 Column 13 through 26 Error 30456: 'value' is not a member of 'System.Buffer'. Line 22 Column 13 through 28 ConvertDIN.dtsx 0 0

You are getting the error because your trying to get async script behavior from a synchronous script. This is clear from the for loop per input row, which could produce multiple output rows per input row. This is practically the definition of an asynchronous component.

So, make your component asynchronous.

1. On your output, set the SynchronousInputID to 0. Now you have an async component.
2. Add columns to your output, like seq and value.
3. Add rows to the output, as in the following.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

Output0Buffer.AddRow()
Output0Buffer.seq = seq
Output0Buffer.value = code
End If
seq += 1
Next
End Sub|||

Thanks, now it compiles! But how does the object returned by Output0Buffer.AddRow() get its seq and code properties set? When I ran the above code, I got 0 output rows.

Also, how do I output a row when seq = 0, that is, the output row that corresponds to the first code in the input Row.IC string?

|||

I took out the test for seq > 0, and replaced the Recordset destination I was using for debugging with an OLE destination, and now my data view shows 1 row being output for each input row. Thanks!

How to add reference to SSIS script task

Hi ALL

I cannot add the reference to the following:
"Imports Microsoft.AnalysisServices.AdomdClient"
in SSIS script task. All the objects that i declared become underlined because there is no reference to the above library.
When i go in SSIS script task and open the code to add the reference it does not the list the above library.
I am able to add reference in Visualstudio but not in SSIS.

Please adivse, it is very critical and urgent for my task

Thanks.

Does this help?

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Ed

|||

As Ed says, you will need to copy the DLL. Note that you only need to do this at design-time so you won't need to do it on your production server.

-Jamie

|||

Hi Jamie / Ed

Thanks for the reply, it did work for me. Appreciate your help.

Thanks

Friday, March 9, 2012

How to add Fill Factor in the SQL script

I am trying to set up the relationship (Primary Key and Forign Key) in several tables. I would like to find a way also be able to set 'Fill Factor = 90%' in the script. Here is the code that i have so far:

ALTER TABLE Req
ADD CONSTRAINT FK_Req_Bow FOREIGN KEY ( BowID )
REFERENCES Bow ( BowID )

Any help would be greatly appreciated!

J827FILLFACTOR pertains to an index that you need to create for your FOREGN KEY constraint, not to the constraint itself.

How to add DTS

How can I Add a DTS by Script?
I run a batch script with osql or Query Analyser...
:D
ThanksYou can use SQL-DMO either with VB/VBScript or through sp_OAxxx