Change Indentity Increment

I need to change the seed and increment for an identity field in an existing
table and would like to make the change in a script rather then in Enterpris
e
Manager. I understand that I can use DBCC CHECKIDENT to reseed the field.
Is there a function to change the identity seed? My question applies to MS
SQL 2000.
Thanks in advance for any help.
JeremySounds scary!
Without thinking, one way to do it is to move the data off to a temporary
location, truncate your table, alter the ID column to be identity(x,y), and
then repopulate it (in the appropriate order).
I repeat my earlier comment - sounds scary!
Rob|||Yeah, especially if you have any foreign key constraints set up...
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:4F74A9C4-559C-49FF-A36E-159CBF2022AF@.microsoft.com...
> Sounds scary!
> Without thinking, one way to do it is to move the data off to a temporary
> location, truncate your table, alter the ID column to be identity(x,y),
> and
> then repopulate it (in the appropriate order).
> I repeat my earlier comment - sounds scary!
> Rob|||Jeremy - do you mind explaining WHY you want to do such a thing?|||Thanks for the reply Rob.
One the requirements I've been given for this database project is that new
identity values in TableA should be even, and in TableB should be odd.
"Rob Farley" wrote:

> Jeremy - do you mind explaining WHY you want to do such a thing?|||What about the records that currently exist? Are you going to divvy them up
between even records in one table, odd in the other? Or are you going to
re-number all existing records? This whole thing sounds a little painful
from here...
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
> Thanks for the reply Rob.
> One the requirements I've been given for this database project is that new
> identity values in TableA should be even, and in TableB should be odd.
> "Rob Farley" wrote:
>|||Thanks for the reply. The identity values for existing rows would remain th
e
same. New records for TableA would have an even identity value with a new
seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
increment 2 to have odd identity values for new records.
"Michael C#" wrote:

> What about the records that currently exist? Are you going to divvy them u
p
> between even records in one table, odd in the other? Or are you going to
> re-number all existing records? This whole thing sounds a little painful
> from here...
>
> "jeremy@.nospamwardlawclaims.com"
> < jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
> news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
>
>|||OK. Do both of these tables currently exist, and do both currently have
both even and odd identity values in them? Would it be easier to just add
another column to your PK (CHAR(1) possibly) indicating different identity
value sources? This solution just sounds a little shaky... what happens if
they add a third table in the future? Re-define all identities to +3
beginning with record x,xxxx?
Thanks.
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:3603F85C-41FB-4930-8E7A-6822E763058F@.microsoft.com...
> Thanks for the reply. The identity values for existing rows would remain
> the
> same. New records for TableA would have an even identity value with a new
> seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
> increment 2 to have odd identity values for new records.
> "Michael C#" wrote:
>|||To me it sounds like you actually want a view, with an insert trigger. The
trigger puts the inserted values into either TableA or TableB, depending on
which one has the higher ID field. But the view shows all of them.
This is the type of thing you might do if you wanted to set up replication
scenarios, or divide the tables between two separate databases and devices.
I think you create the new tables from scratch. Create the view (which is
just a 'select * from TableA union all select * from TableB') and the
trigger, and then populate the view from your old table. Let the trigger
handle the distribution of records.
Of course, you will need to make sure that the trigger handles the situation
where there are a whole bunch of rows in the 'Inserted' table. But there are
easy ways around this.
Rob

Change filename for flat file connection manager at runtime

I need to add the current date to the end of the filename of a flat file at runtime.

This was previously done with ActiveX script in SQL Server 2000.

oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing

I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!

Regards,
Sara

Basically, use property expressions: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

|||

Hi Sara
I am working with SSIS packages and am having the same problem that you faced some time back
Am getting an error message in SSIS package in the following lines:

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename

Set oConn = Nothing

Could you tell me what you did to fix your code. Would really appreciate your time and response.

Thanks

Rishi...

|||

Hi Rishi,

I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file.

I hope this gives you an idea of how to solve your problem!

Regards,
Sara
--

Script Task
Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt
File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt

Imports System

Imports System.IO

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim theDate As String

Dim i As Integer

strPath = "L:\vpkba\laddning\EDBkjerne\"

theDate = Now.Year & IIf(CInt(Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf(CInt(Now.Day) < 10, "0" & Now.Day, Now.Day).ToString

For i = 0 To Dts.Connections.Count - 1

If Dts.Connections(i).Name.Contains("YYYYMMDD") Then

Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace("YYYYMMDD", theDate)

End If

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Change filename for flat file connection manager at runtime

I need to add the current date to the end of the filename of a flat file at runtime.

This was previously done with ActiveX script in SQL Server 2000.

oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing

I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!

Regards,
Sara

Basically, use property expressions: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

|||

Hi Sara
I am working with SSIS packages and am having the same problem that you faced some time back
Am getting an error message in SSIS package in the following lines:

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename

Set oConn = Nothing

Could you tell me what you did to fix your code. Would really appreciate your time and response.

Thanks

Rishi...

|||

Hi Rishi,

I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file.

I hope this gives you an idea of how to solve your problem!

Regards,
Sara
--

Script Task
Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt
File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt

Imports System

Imports System.IO

Imports System.Data

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim theDate As String

Dim i As Integer

strPath = "L:\vpkba\laddning\EDBkjerne\"

theDate = Now.Year & IIf(CInt(Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf(CInt(Now.Day) < 10, "0" & Now.Day, Now.Day).ToString

For i = 0 To Dts.Connections.Count - 1

If Dts.Connections(i).Name.Contains("YYYYMMDD") Then

Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace("YYYYMMDD", theDate)

End If

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Change Field Name

I got 30 tables and I want to change one field name ,
Can I use SQL script to do that ?
Thanks a lot .Hi,
look for the procedure sp_rename which is described in the BOL in
detail.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql

Change Email Task Properties

HI,

How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.

If the Script Task can't do it, Is there any alternative to do?

Thanks

All those questions are answered here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

Change default value

Hi,
How can i change default value in sql server through SQL script if i haven't
given default constraint name and using system default generated constraintg
name ?
I have 50 tables to change the default value of a particular field...pls
advice what to do...i don't want to open every table in design mode and
change the default value...pls suggest some script to do it.
With regards,
Gurmeet SinghThis scenario is exactly why you should name your constraint in the first
place...
If you want to automate this, you can use the system tables to get the
constraint name and use that name in your ALTER TABLE ... DROP CONSTRAINT
... statements. Note that SQL Server doesn't accept a variable for table or
constraint name, so you have do use dynamic SQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||Hi,
Refer books online for the below commands,
Alter table drop constraint <Const_name>
Alter table add constraint
Thanks
Hari
MCDBA
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||You can remove the default from a column using the following script:
-- remove the default
DECLARE @.constraint_name SYSNAME
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.name = '<table name>'
AND c_obj.xtype = 'D'
AND cols.[name] = '<column name>')
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
You can adjust this to use multiple tables and to add the new constraint as
well.
Jacco Schalkwijk
SQL Server MVP
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>

Change collation with osql

Hi,
I have an MS SQL Server 2000 database with collation
'SQL_Hungarian_CP1250_CI_AS'. I wanted to run a script file to insert
values into one of my table by osql.exe the following way:
OSQL.EXE -U myUserID -P mypassword -d myDB -i "C:\myscript.sql" -n
The script was run successfully but the inserted character fields
looked as if it were in different collation.
For example the script contains the following insert command:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled])
VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)
but the inserted 'FunctionName' field contained this after running the
script:
'Vevi ajnlat'
I suppose it is a collation problem but i don't know how to solve it.
Any suggestion?
Thanks in advance
szoli
---
szoli's Profile: http://www.msmcse.ms/member.php?userid=1477
View this thread: http://www.msmcse.ms/t-1870445621If FunctionName is of type nvarchar, try the following:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled]) VALUES (1,N'Vevői ajnlat',8,85,1,1,100,1)Joo Ara
jo"szoli" <szoli.1pc1ga@.no-mx.msmcse.ms> wrote in messagenews:szoli.1pc1g
a@.no-mx.msusenet
.com...>> Hi,>> I have an MS SQL Server 2000 database with collation> 'SQL_H
ungarian_CP1250_CI_AS'. I wanted to run a script file to insert> values into
one of my table by osql.exe the following way:>> OSQL.EXE -U myUserID -P my
password -d myDB -i "C:\mys
cript.sql" -n>> The script was run successfully but the inserted character fields> looked as if it wer
e in different collation.> For example the script contains the following insert command:>> INSERT INTO
admFunction>([FunctionID],[FunctionName],[Function
Modes],[ParentFunctionID],[OrderNo],[Mod
ulID],[Extra1ID],[Enabled])> VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)>> but
the inserted 'FunctionName' field contained this after running the> script:
> 'Vevi ajnlat'>> I suppose it is a collation problem bu
t i don't know how to solve it.> Any suggestion?>> Thanks in advance>>> --> szoli> --
----> szoli's Profile: http://www.msusen
et.com/member.php?userid=1477> View this thread: http://www.msus
enet.com/t-1870445621>|||If FunctionName is of type nvarchar, try the following:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled])VALUES (1,N'Vevői ajnlat',8,85,1,1,100,1)Joo Araj
o"szoli" <szoli.1pc1ga@.no-mx.msmcse.ms> wrote in messagenews:szoli.1pc1ga
@.no-mx.msusenet.
com...>> Hi,>> I have an MS SQL Server 2000 database with collation> 'SQL_Hu
ngarian_CP1250_CI_AS'. I wanted to run a script file to insert> values into
one of my table by osql.exe the following way:>> OSQL.EXE -U myUserID -P myp
assword -d myDB -i "C:\mysc
ript.sql" -n>> The script was run successfully but the inserted character fields> looked as if it were
in different collation.> For example the script contains the following insert command:>> INSERT INTO
admFunction>([FunctionID],[FunctionName],[FunctionM
odes],[ParentFunctionID],[OrderNo],[Modu
lID],[Extra1ID],[Enabled])> VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)>> but
the inserted 'FunctionName' field contained this after running the> script:>
'Vevi ajnlat'>> I suppose it is a collation problem but
i don't know how to solve it.> Any suggestion?>> Thanks in advance>>> --> szoli> --
----> szoli's Profile: http://www.msusenet
.com/member.php?userid=1477> View this thread: http://www.msuse
net.com/t-1870445621>|||Finallly i found the solution.
I had to save my sql script in Query Analyzer in Unicode format and
then it worked well.
szoli
---
szoli's Profile: http://www.msmcse.ms/member.php?userid=1477
View this thread: http://www.msmcse.ms/t-1870445621

Chain transact sql scripts

I have two Transact SQL scripts and I want to call the second script from the first –
is this possible in SQL server 2005?
I am trying to do a port from Oracle (where this is possible) but cannot find the mechanism to do so in SQL Server.

Eg if Script1.sql is
BEGIN
PRINT (‘Inside Script1’)
//Invoke Script2.sql – how do I do this?
END

where Script2.sql is
BEGIN
PRINT (‘Inside Script2’)
END

When I execute Script1.sql - I need it to print both 'Inside Script2' and 'Inside Script1'

You can do this using the :r command if you invoke the script using the new SQLCMD command-line utility. It also has other script pre-processing features. You can take a look at the Books Online topic at link below:

http://msdn2.microsoft.com/en-us/library/ms162773(SQL.90).aspx

|||We can use the :r option inside sqlcmd - but was looking for Transact SQL support (without using xp_cmdshell to spawn "sqlcmd" inside a larger script) - Inside a Oracle pl/sql script, we use "@.<scriptname> from a enclosing script" - was looking for similar functionality in Transact SQL|||

I would suggest the best way would be to place the code inside two stored procedures, and call one from the other.

HTH

|||

shuges is right

store procedure can call another sp

using the exec "spname" syntax

also sql queries can be nested so it is possible to

rewrite two sps into one.

moreover you can also make use of functions

|||The :r SQLCMD command is the equivalent of @. command in SQLPLUS. These are client-side features not part of the TSQL or PL/SQL language.

Ceate Database Question

Hi,
I am trying to write an installtion sql script that creates a database.
However, I am running into problems because the potential of sql servers
having different setups (log and db files on different partitions...). How
do I handle this?
ThanksHow do you plan to run the script? I have used a batch file that calls
a different creation script for each possible environment. The batch
file handles the branching based on a command line parameter. So the
admin just has to type: "DBSETUP TEST" or "DBSETUP STAGE" the DOS
prompt.
If you want to make the file locations, sizes, etc totally configurable
by a non-DBA type user then I think you'll have to create a
user-interface of some kind.
David Portas
SQL Server MVP
--|||What exactly is the problem? Did you receive any error messages when you
executed the script? Can you post your script here? With CREATE DATABASE
statements, you can include the physical location of the database & log
files irrespective of the partition they are on.
Anith|||David,
Sorry I have been out of town. Is there a way to find out how the SQL
server is configured and change the script accordingly?
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129309258.498992.121140@.g49g2000cwa.googlegroups.com...
> How do you plan to run the script? I have used a batch file that calls
> a different creation script for each possible environment. The batch
> file handles the branching based on a command line parameter. So the
> admin just has to type: "DBSETUP TEST" or "DBSETUP STAGE" the DOS
> prompt.
> If you want to make the file locations, sizes, etc totally configurable
> by a non-DBA type user then I think you'll have to create a
> user-interface of some kind.
> --
> David Portas
> SQL Server MVP
> --
>

Cast COM object error on OleDb Destination (Access 2003)

Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.

In the Script Transformation I get this error.

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.

Private sqlConn As OleDb.OleDbConnection

Private sqlCmd As OleDb.OleDbCommand

Private sqlParam As OleDb.OleDbParameter

Private connstring As String

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.ConnectionOLE

'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

connstring = connMgr.ConnectionString

sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)

End Sub

Any help would be appreciated.

The OLEDB connection manager is not using managed OleDb classes but the native OleBD interfaces. AcquireConnection returns a COM object that is created from IDBCreateSession::CreateSession call.

HTH,
Ovidiu Burlacu

|||

You are correct. The destination connection was using the Native OLE DB for Jet 4.

I created a Data Source using .NET OLE DB provider for Jet 4 and I got connected in the Script Component OK.

New Problem.

My insert OLE DB command worked fine which uses the original destination connection (Native), But my update OLE DB command using same Native connection is erroring out on each input row of the OLE DB Command component.

[UpdateRow [2727]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Numeric field overflow.".

Connection string on Destination OLE DB connection is:

Data Source=C:\Projects\Data\TSLists.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;OLE DB Services=-1;

UPDATE: The table in the Access database I am trying to update is a linked table representing a SharePoint 2003 list. What is interesting is you can push (insert) data into this table, but you can not update data in the same linked table from the same OLE DB connection in SSIS.