Hi,
I am in the process of moving my back end to the SQL server. I have a test
version in the SQLserver and the front end is in Access 2000 with linked
tables.
When the user enters a duplicate key (primary key) and the user tries to
save the record in Access (using a save button), an error message is
displayed by Jet.
The same kind of error message does not appear when the back end is in SQL
server. The duplicate record does not get stored as well.
Why is this happening?> The same kind of error message does not appear when the back end is in SQL
> server. The duplicate record does not get stored as well.
> Why is this happening?
Well, what does the statement look like? Did you use profiler to see what
is going into and coming out of SQL Server?|||Hi,
Thanks for responsing.
No...I am a newbie to SQL server and don't know how to do it.
Access (jet) gives an error message when I try to save a duplicate record. -
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship".
This message appears when the f/e and the b/e are Access.
When the b/e is SQL for the same table and Access is the front end..no error
message appears.
Do you suggest I learn how to use a profiler and figure it out?
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, what does the statement look like? Did you use profiler to see what
> is going into and coming out of SQL Server?
>
>|||Hi
Since you have not provided your INSERT statement ,see Itzik Ben-Gan's
exanple to deal with dulicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Priya Henry" <PriyaHenry@.discussions.microsoft.com> wrote in message
news:3A756C75-F872-4168-A497-905031937F0B@.microsoft.com...
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate
> record. -
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no
> error
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Does your table in SQL Server have a unique index on the relevant column?
"Priya Henry" wrote:
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate record.
-
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no err
or
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Yes...My table does have an unique index. Is it possible that Jet is
stopping the message?
"NH" wrote:
> Does your table in SQL Server have a unique index on the relevant column?
> "Priya Henry" wrote:
>|||I havnt seen this before. Our system has a SQL Server backend and Access
front end and the error message do appear when trying to add in a duplicate
into a unique undexed column.
Have you checked the linked tables in Access, are they pointing to the
correct SQL Server database?
"Priya Henry" wrote:
> Yes...My table does have an unique index. Is it possible that Jet is
> stopping the message?
> "NH" wrote:
>sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
dumb question
Sorry about this. But I've worked primarily in access for years.
Does SQL Server have a boolean or a yes/no data type for its table columns?
Thanks in advance,
Bill>Does SQL Server have a boolean or a yes/no data type for its table columns? <<
No, and you should not write SQL that way -- bit flags are too low
level for a high level language. We left BOOLEANs out for good
reasons having to do with 3VL, NULLs and consistent language model.|||Hi Bill,
No it doesn't, people usually use the BIT data type which holds 0 or 1; some
folk like myself use Y or N / T or F in a char(1) column.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"news" <wjfwjr@.hotmail.comwrote in message
news:eZOdnUO6lMk4AmDbnZ2dnUVZ_oCvnZ2d@.comcast.com. ..
Quote:
Originally Posted by
Sorry about this. But I've worked primarily in access for years.
>
Does SQL Server have a boolean or a yes/no data type for its table
columns?
>
Thanks in advance,
>
Bill
>
>
>
DUH!
Some users would ONLY have access through an application role, while
elevated access is controlled through normal logins and roles.
"Bob Castleman" <nomail@.here> wrote in message
news:uiRahnt3EHA.3092@.TK2MSFTNGP10.phx.gbl...
> Some users would ONLY have access through an application role, while
> elevated access is controlled through normal logins and roles.
>
Exactly. ;-)
Rick Sawtell
elevated access is controlled through normal logins and roles.
"Bob Castleman" <nomail@.here> wrote in message
news:uiRahnt3EHA.3092@.TK2MSFTNGP10.phx.gbl...
> Some users would ONLY have access through an application role, while
> elevated access is controlled through normal logins and roles.
>
Exactly. ;-)
Rick Sawtell
Dublicates
can someone please explain to me how to append data to current database tables?
If I have infromation from access and want to had the (NEW) information to current SQL tables how to I append without writing over current table information and without creating dups if the infromation currently exists within the table?
I would like to keep the current table information and append anything new only.
ThanksA dup key in a sproc will raise by it's self to the calling sproc..
But heres an example
USE Northwind
GO
CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO
DECLARE @.Error int, @.Col1 int, @.Col2 char(1)
SELECT @.Col1 = 1, @.Col2 = 'A'
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2
SELECT @.error = @.@.ERROR
SELECT 'Error code: ' + CONVERT(varchar(5),@.Error)
SELECT @.Col2 = 'B'
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2
SELECT @.error = @.@.ERROR
IF @.Error <> 0
BEGIN
UPDATE myTable99 SET Col2 = @.Col2 WHERE Col1 = @.Col1
SELECT @.Error = @.@.ERROR
END
SELECT 'Error code: ' + CONVERT(varchar(5),@.Error)
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
If I have infromation from access and want to had the (NEW) information to current SQL tables how to I append without writing over current table information and without creating dups if the infromation currently exists within the table?
I would like to keep the current table information and append anything new only.
ThanksA dup key in a sproc will raise by it's self to the calling sproc..
But heres an example
USE Northwind
GO
CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO
DECLARE @.Error int, @.Col1 int, @.Col2 char(1)
SELECT @.Col1 = 1, @.Col2 = 'A'
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2
SELECT @.error = @.@.ERROR
SELECT 'Error code: ' + CONVERT(varchar(5),@.Error)
SELECT @.Col2 = 'B'
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2
SELECT @.error = @.@.ERROR
IF @.Error <> 0
BEGIN
UPDATE myTable99 SET Col2 = @.Col2 WHERE Col1 = @.Col1
SELECT @.Error = @.@.ERROR
END
SELECT 'Error code: ' + CONVERT(varchar(5),@.Error)
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
Wednesday, March 7, 2012
DTSTransform.DataConvert programmatic access?
I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.
The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.
I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.
I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?
Here is what is left of my code:
IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}
Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.
Phil Burns
Aptify
DTSTransform.DataConvert programmatic access?
I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.
The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.
I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.
I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?
Here is what is left of my code:
IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}
Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.
Phil Burns
Aptify
Friday, February 24, 2012
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Error
I have an SSIS Package that first goes out using an ActiveX script to search to see if an access database exists. If it exists it deletes it and recreates the database shell, if not it creates a new one. The second step is to create the tables the Access database is going to need. The last step is to populate the Access tables with data from a SQL Server database. I have changed the package from running in 64Bit to False. I have tried EVERY security mode for the package and the package still fails with the:
[Access - Definition [3781]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "Access - Definition" (3781) failed the pre-execute phase and returned error code 0xC020801C.
Please help!
Thanks!
The error message may be appearing for one of the following reasons:
· The connection string specifies a provider that is not supported.
· You do not have access to the specified data source.
· The specified data source is being used by another application.
|||Thanks for your help! What I had to end up doing is...I have 83 tables I need to copy over to Access...so come to find out I had to split half of the tables over to another data flow and now I see nothing but green when I execute the package...its the weirdest thing...but it works so I am going with it...thanks for you help tho!
|||I know that this question is update dbut for others who have the same problem need to check out the security option. If you're using a password to access a dB then you have to use Persist Security info=true otherwise use Integrated security=trueGood luck
DTS: import large database
Importing data from an Access database, I cannot overcome the limit of 1,000 records.
In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries.
Where can I set a max size of ~1,500 in my sql target base?There's a limit on what you can copy? By default, I have been able to transfer (even from Access) more than 100,000 records at a time. Heck, from other db's, I have been able to transfer MILLIONS of records by default.
In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries.
Where can I set a max size of ~1,500 in my sql target base?There's a limit on what you can copy? By default, I have been able to transfer (even from Access) more than 100,000 records at a time. Heck, from other db's, I have been able to transfer MILLIONS of records by default.
A quick workaround would be to do an export to text (preferrably .csv) and import that into SQL. You can even do that through SQL, I think, by selecting your source as Access and your target as Text file through DTS.
DTS: How can I process each row in result set to access properties on another package obje
(SQL Server 2000, SP3a)
Hello all!
I have a DTS package that I'm working with, in which I have a query that I want to invoke
on a target SQL Server that will return a handful of rows. For each row, I want to set
some package properties (on another object in the package). What would be the best
approach to this? I thought that I might use the "Transform Data Task", even though I
don't really have a "Destination", per se (that is, I want to process each "Source" record
via an ActiveX script).
However, when I try and do this, I seem to be getting an error when I execute that
"Transform Data Task" step (something akin to "Execution Cancelled by User").
Is there some other way that I should approach this?
Regards,
John PetersonTake a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>|||Thanks, Dan -- but I can't seem to get my head around your suggestion. Basically, what I
want is to be able to specify a Source Query that would return a bunch of rows. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a "Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specify
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package that I'm working with, in which I have a query that I
> want to invoke
> > on a target SQL Server that will return a handful of rows. For each row,
> I want to set
> > some package properties (on another object in the package). What would be
> the best
> > approach to this? I thought that I might use the "Transform Data Task",
> even though I
> > don't really have a "Destination", per se (that is, I want to process each
> "Source" record
> > via an ActiveX script).
> >
> > However, when I try and do this, I seem to be getting an error when I
> execute that
> > "Transform Data Task" step (something akin to "Execution Cancelled by
> User").
> >
> > Is there some other way that I should approach this?
> >
> > Regards,
> >
> > John Peterson
> >
> >
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrote:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of rows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > Take a look at the DynamicProperties task. This will allow you to set
DTS
> > properties based query that returns a scalar value. You'll need to
specify
> > a separate query for each property.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package that I'm working with, in which I have a query
that I
> > want to invoke
> > > on a target SQL Server that will return a handful of rows. For each
row,
> > I want to set
> > > some package properties (on another object in the package). What
would be
> > the best
> > > approach to this? I thought that I might use the "Transform Data
Task",
> > even though I
> > > don't really have a "Destination", per se (that is, I want to process
each
> > "Source" record
> > > via an ActiveX script).
> > >
> > > However, when I try and do this, I seem to be getting an error when I
> > execute that
> > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > User").
> > >
> > > Is there some other way that I should approach this?
> > >
> > > Regards,
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of existing DTS
objects, rather than having to write a lot of code. Alas, it seems like it's not quite
the case, even though DTS seems uniquely qualified to do this type of thing (almost).
Since it can use a Connection to issue a query on that remote server and process the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Query Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX transformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> Sorry, but I don't understand what you mean by <withOUT doing anything to a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > > Take a look at the DynamicProperties task. This will allow you to set
> DTS
> > > properties based query that returns a scalar value. You'll need to
> specify
> > > a separate query for each property.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package that I'm working with, in which I have a query
> that I
> > > want to invoke
> > > > on a target SQL Server that will return a handful of rows. For each
> row,
> > > I want to set
> > > > some package properties (on another object in the package). What
> would be
> > > the best
> > > > approach to this? I thought that I might use the "Transform Data
> Task",
> > > even though I
> > > > don't really have a "Destination", per se (that is, I want to process
> each
> > > "Source" record
> > > > via an ActiveX script).
> > > >
> > > > However, when I try and do this, I seem to be getting an error when I
> > > execute that
> > > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > > User").
> > > >
> > > > Is there some other way that I should approach this?
> > > >
> > > > Regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you can
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
http://www.amazon.co.uk/exec/obidos/ASIN/0672320118/qid=1086594526/sr=1-1/ref=sr_1_2_1/202-5145180-8774263
Regards,
Paul Ibison
Hello all!
I have a DTS package that I'm working with, in which I have a query that I want to invoke
on a target SQL Server that will return a handful of rows. For each row, I want to set
some package properties (on another object in the package). What would be the best
approach to this? I thought that I might use the "Transform Data Task", even though I
don't really have a "Destination", per se (that is, I want to process each "Source" record
via an ActiveX script).
However, when I try and do this, I seem to be getting an error when I execute that
"Transform Data Task" step (something akin to "Execution Cancelled by User").
Is there some other way that I should approach this?
Regards,
John PetersonTake a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>|||Thanks, Dan -- but I can't seem to get my head around your suggestion. Basically, what I
want is to be able to specify a Source Query that would return a bunch of rows. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a "Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specify
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package that I'm working with, in which I have a query that I
> want to invoke
> > on a target SQL Server that will return a handful of rows. For each row,
> I want to set
> > some package properties (on another object in the package). What would be
> the best
> > approach to this? I thought that I might use the "Transform Data Task",
> even though I
> > don't really have a "Destination", per se (that is, I want to process each
> "Source" record
> > via an ActiveX script).
> >
> > However, when I try and do this, I seem to be getting an error when I
> execute that
> > "Transform Data Task" step (something akin to "Execution Cancelled by
> User").
> >
> > Is there some other way that I should approach this?
> >
> > Regards,
> >
> > John Peterson
> >
> >
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrote:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of rows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > Take a look at the DynamicProperties task. This will allow you to set
DTS
> > properties based query that returns a scalar value. You'll need to
specify
> > a separate query for each property.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package that I'm working with, in which I have a query
that I
> > want to invoke
> > > on a target SQL Server that will return a handful of rows. For each
row,
> > I want to set
> > > some package properties (on another object in the package). What
would be
> > the best
> > > approach to this? I thought that I might use the "Transform Data
Task",
> > even though I
> > > don't really have a "Destination", per se (that is, I want to process
each
> > "Source" record
> > > via an ActiveX script).
> > >
> > > However, when I try and do this, I seem to be getting an error when I
> > execute that
> > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > User").
> > >
> > > Is there some other way that I should approach this?
> > >
> > > Regards,
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of existing DTS
objects, rather than having to write a lot of code. Alas, it seems like it's not quite
the case, even though DTS seems uniquely qualified to do this type of thing (almost).
Since it can use a Connection to issue a query on that remote server and process the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Query Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX transformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> Sorry, but I don't understand what you mean by <withOUT doing anything to a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > > Take a look at the DynamicProperties task. This will allow you to set
> DTS
> > > properties based query that returns a scalar value. You'll need to
> specify
> > > a separate query for each property.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package that I'm working with, in which I have a query
> that I
> > > want to invoke
> > > > on a target SQL Server that will return a handful of rows. For each
> row,
> > > I want to set
> > > > some package properties (on another object in the package). What
> would be
> > > the best
> > > > approach to this? I thought that I might use the "Transform Data
> Task",
> > > even though I
> > > > don't really have a "Destination", per se (that is, I want to process
> each
> > > "Source" record
> > > > via an ActiveX script).
> > > >
> > > > However, when I try and do this, I seem to be getting an error when I
> > > execute that
> > > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > > User").
> > > >
> > > > Is there some other way that I should approach this?
> > > >
> > > > Regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you can
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
http://www.amazon.co.uk/exec/obidos/ASIN/0672320118/qid=1086594526/sr=1-1/ref=sr_1_2_1/202-5145180-8774263
Regards,
Paul Ibison
DTS/SSIS too many tables warning and subsequent error
I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.
First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:
"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"
Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.
This was never an issue with SQL 2000 DTS.
Thanks
"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".
The wizard told you that it may not be able to do it.
Hey,
Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?
Thanks
DTS.Pipeline Information - Can I Access this?
Is there any way I can capture the below information? I want to capture this to get the no of rows processed by each transformation.
[DTS.Pipeline] Information: "component "abc" (3798)" wrote 2142 rows.
[DTS.Pipeline] Information: "component "xyz" (4223)" wrote 1026 rows.
[DTS.Pipeline] Information: "component "abc2" (4324)" wrote 7875 rows.
Thanks
Jamie, as usual has you covered:http://blogs.conchango.com/jamiethomson/archive/2007/07/03/SSIS-Nugget_3A00_-Output-the-number-of-processed-rows.aspx|||
Phil Brammer wrote:
Jamie, as usual has you covered:
http://blogs.conchango.com/jamiethomson/archive/2007/07/03/SSIS-Nugget_3A00_-Output-the-number-of-processed-rows.aspx
Thanks Phil.|||This works if the transformation is in Script task, or rather if we want to generate our rows processed message in script or custom task. I dont think this can be used to access the information messages for a task.
Thanks
|||
This is what I meant to paste:
http://blogs.conchango.com/jamiethomson/archive/2007/03/08/SSIS_3A00_-OnPipelineRowsSent.aspx
Why not just use a row count transformation though?
|||
Why not just use a row count transformation though?
I already have a row count transformation, I'm just trying to see if I can knock-off the additional transformation.
Thanks
|||
Phil Brammer wrote:
Why not just use a row count transformation though?
I already have a row count transformation, I'm just trying to see if I can knock-off the additional transformation.
Thanks
I don't understand. That's what it's for though. It's not really additional.
Sunday, February 19, 2012
DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!
|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!
|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
Friday, February 17, 2012
DTS utilizing stored proc
I am trying to create a DTS package to send results from an extended stored
procedure [master.dbo.xp_fixeddrives] to an Access table.
What is the best Task to use in order to call an extended stored procedure
in DTS?
Message posted via http://www.webservertalk.comhi,
Just a Execute SQL Task
cheers,
"Robert Richards via webservertalk.com" wrote:
> I am trying to create a DTS package to send results from an extended store
d
> procedure [master.dbo.xp_fixeddrives] to an Access table.
> What is the best Task to use in order to call an extended stored procedure
> in DTS?
> --
> Message posted via http://www.webservertalk.com
>|||Since this extended procedure returns multiple rows, is there a way to
insert these rows all at once into my Access connection? I am not seeing
how to do that in a Execute SQL Task.
Message posted via http://www.webservertalk.com
procedure [master.dbo.xp_fixeddrives] to an Access table.
What is the best Task to use in order to call an extended stored procedure
in DTS?
Message posted via http://www.webservertalk.comhi,
Just a Execute SQL Task
cheers,
"Robert Richards via webservertalk.com" wrote:
> I am trying to create a DTS package to send results from an extended store
d
> procedure [master.dbo.xp_fixeddrives] to an Access table.
> What is the best Task to use in order to call an extended stored procedure
> in DTS?
> --
> Message posted via http://www.webservertalk.com
>|||Since this extended procedure returns multiple rows, is there a way to
insert these rows all at once into my Access connection? I am not seeing
how to do that in a Execute SQL Task.
Message posted via http://www.webservertalk.com
DTS transfert AS400 --> SQL Server 2000
I have an ODBC link to DB2 Database. I can see the values of the DB2's
tables in Access but not in SQL Server DTS.
IN DTS, I connect the Source but when i want to see the values in the
Data transformation task, i have this message :
tables in Access but not in SQL Server DTS.
IN DTS, I connect the Source but when i want to see the values in the
Data transformation task, i have this message :
HResult of 0x90040e37 (-2147217865) returned. Erreur inattendue. Un
rsultat d'erreur a t renvoy sans message d'erreur.
Someone have a solution?dchaumeil@.yahoo.fr (David Chaumeil) wrote in message news:<89a69082.0307212321.7a291786@.posting.google.com>...
> I have an ODBC link to DB2 Database. I can see the values of the DB2's
> tables in Access but not in SQL Server DTS.
> IN DTS, I connect the Source but when i want to see the values in the
> Data transformation task, i have this message :
> HResult of 0x90040e37 (-2147217865) returned. Erreur inattendue. Un
> rsultat d'erreur a t renvoy sans message d'erreur.
> Someone have a solution?
What version is your client access ?
I had the similar error message before with older version client
access and in the ODBC configuration, you need to check the setting of
those parameters.
Good Luck !
Wednesday, February 15, 2012
DTS to do SQL to MS Access
Okay guys, maybe I'm making this much more complex than it needs to be. I've got a SQL DB and want to export certain tables to Access. My first thought was DTS, right? So I started down the road of the DTS - build the DTS on my local SQL box, made the Access DB, and everything was good.
Then I thought about my deployment environment: there are several clients that will use this. Some will have their own environment, so no problem. Yet others will host at an ISP, which typically means that the SQL box only has TCP/IP access to the webserver, so where do you tell the DTS package to put the Access DB? Then I thought about using the FTP task, but this blasted thing only gives you a mapped network location to put the resulting file. DAMN! (Now why wouldn't you have the ability to put an FTP ADDRESS AS THE DESTINATION OF THE FREAKING FTP TASK - I suppose that would make too much f-ing sense!)
So here I am, at a loss for what to do. I thought about coding the transformation in .NET, but what a major pain in the ****! Then maybe thought about using DTS from within C#, but can't find any solid examples of doing this.
HELP!!!Hi Rob
Maybe this will get you on the right track. http://www.sqlteam.com/item.asp?ItemID=12408
If not, there are a couple of other FTP and DTS articles on SQLTeam|||Okay, I've worked up a little C# routine to actually execute the DTS packages and it gets so far, but then get an error that it doesn't like the userID because it's null (although I am passing the userID into the DTS execution). I've got to be missing something pretty simple here - any ideas?
Here's the C# code that calls the DTS package:
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;package.LoadFromSQLServer("sqlmachinenamehere", "sa", "passwordhere", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection.DTSSQLStgFlag_Default, null, null, null, "Export_Association_Table", ref pVarPersistStgOfHost);
And the error that is produced:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
DTS to Access mdb - updates only
Is there a way to flow updates only to an Access mdb from SQL DTS. We have
a sql table that is a copy of an Access table, all adds/updates/deletes are
made to the sql table and replicated to the Access table. The problem is
the only options for replicating the data to the Access table are a.)Append
or b.) Full Table Delete then Insert. Append is out because we would get
duplicates, Full Table Delete/Insert works but this means for several
seconds we have an empty Access table (problem because many apps are reading
from the Access table 24/7). Is there a way to create a DTS job (or some
other strategy) to perform updates to an Access table in addition to inserts
and individual deletes. My goal is also avoid writing a full blown
sql/Access replication program.
Thanks,
JimHi
You may want to look at SQL Servers own replication options.
If your access database was a linked server your could write a T-SQL query
to insert only the rows that did not exist already in the table.
e.g.
INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
SELECT pkcol, col1, col2 FROM SQLServerTable s
WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
s.pkcol )
John
"Jims" wrote:
> Is there a way to flow updates only to an Access mdb from SQL DTS. We hav
e
> a sql table that is a copy of an Access table, all adds/updates/deletes ar
e
> made to the sql table and replicated to the Access table. The problem is
> the only options for replicating the data to the Access table are a.)Appen
d
> or b.) Full Table Delete then Insert. Append is out because we would get
> duplicates, Full Table Delete/Insert works but this means for several
> seconds we have an empty Access table (problem because many apps are readi
ng
> from the Access table 24/7). Is there a way to create a DTS job (or some
> other strategy) to perform updates to an Access table in addition to inser
ts
> and individual deletes. My goal is also avoid writing a full blown
> sql/Access replication program.
> Thanks,
> Jim
>
>|||John - it was my understanding that linked access databases were read-only.
Have you heard different?
Thanks,
Jim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
> Hi
> You may want to look at SQL Servers own replication options.
> If your access database was a linked server your could write a T-SQL query
> to insert only the rows that did not exist already in the table.
> e.g.
> INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
> SELECT pkcol, col1, col2 FROM SQLServerTable s
> WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
> s.pkcol )
> John
> "Jims" wrote:
>|||Hi
The easiest way to check this out is to create yourself a test linked
server. I have certainly managed to insert/update data in a linked access
database. It could be that you have not set up permissions correctly . See
example B in the "sp_addlinkedserver" topic in books online on how to create
a linked access server.
John
"Jims" wrote:
> John - it was my understanding that linked access databases were read-only
.
> Have you heard different?
> Thanks,
> Jim
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
>
>|||This is definitely possible.
There are a series of permissions that need to be set in order to do this.
RSH
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABFC3E5D-2C84-471A-BB5F-622A540ED955@.microsoft.com...
> Hi
> The easiest way to check this out is to create yourself a test linked
> server. I have certainly managed to insert/update data in a linked access
> database. It could be that you have not set up permissions correctly . See
> example B in the "sp_addlinkedserver" topic in books online on how to
> create
> a linked access server.
> John
> "Jims" wrote:
>
a sql table that is a copy of an Access table, all adds/updates/deletes are
made to the sql table and replicated to the Access table. The problem is
the only options for replicating the data to the Access table are a.)Append
or b.) Full Table Delete then Insert. Append is out because we would get
duplicates, Full Table Delete/Insert works but this means for several
seconds we have an empty Access table (problem because many apps are reading
from the Access table 24/7). Is there a way to create a DTS job (or some
other strategy) to perform updates to an Access table in addition to inserts
and individual deletes. My goal is also avoid writing a full blown
sql/Access replication program.
Thanks,
JimHi
You may want to look at SQL Servers own replication options.
If your access database was a linked server your could write a T-SQL query
to insert only the rows that did not exist already in the table.
e.g.
INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
SELECT pkcol, col1, col2 FROM SQLServerTable s
WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
s.pkcol )
John
"Jims" wrote:
> Is there a way to flow updates only to an Access mdb from SQL DTS. We hav
e
> a sql table that is a copy of an Access table, all adds/updates/deletes ar
e
> made to the sql table and replicated to the Access table. The problem is
> the only options for replicating the data to the Access table are a.)Appen
d
> or b.) Full Table Delete then Insert. Append is out because we would get
> duplicates, Full Table Delete/Insert works but this means for several
> seconds we have an empty Access table (problem because many apps are readi
ng
> from the Access table 24/7). Is there a way to create a DTS job (or some
> other strategy) to perform updates to an Access table in addition to inser
ts
> and individual deletes. My goal is also avoid writing a full blown
> sql/Access replication program.
> Thanks,
> Jim
>
>|||John - it was my understanding that linked access databases were read-only.
Have you heard different?
Thanks,
Jim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
> Hi
> You may want to look at SQL Servers own replication options.
> If your access database was a linked server your could write a T-SQL query
> to insert only the rows that did not exist already in the table.
> e.g.
> INSERT INTO linkedsvr...accesstbl ( pkcol, col1, col2 )
> SELECT pkcol, col1, col2 FROM SQLServerTable s
> WHERE NOT EXISTS ( SELECT * FROM linkedsvr...accesstbl a WHERE a.pkcol =
> s.pkcol )
> John
> "Jims" wrote:
>|||Hi
The easiest way to check this out is to create yourself a test linked
server. I have certainly managed to insert/update data in a linked access
database. It could be that you have not set up permissions correctly . See
example B in the "sp_addlinkedserver" topic in books online on how to create
a linked access server.
John
"Jims" wrote:
> John - it was my understanding that linked access databases were read-only
.
> Have you heard different?
> Thanks,
> Jim
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:2FC8D1AB-6784-455B-98F6-2231C2357228@.microsoft.com...
>
>|||This is definitely possible.
There are a series of permissions that need to be set in order to do this.
RSH
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABFC3E5D-2C84-471A-BB5F-622A540ED955@.microsoft.com...
> Hi
> The easiest way to check this out is to create yourself a test linked
> server. I have certainly managed to insert/update data in a linked access
> database. It could be that you have not set up permissions correctly . See
> example B in the "sp_addlinkedserver" topic in books online on how to
> create
> a linked access server.
> John
> "Jims" wrote:
>
Subscribe to:
Posts (Atom)