Hi,
I am dealing with merge replication.
For identity columns EVEN seed value is set in Publisher database
and ODD seed value is set in Subscriber database(increment value 2) to avoid
insertion conflicts.
For a particular table, usually the records are inserted from
subscriber(through application) and rarely from publisher. During this change
an error 'Primary Violation' occurs.
(1) What is the reason for this?
(2) Is there any way to avoid this?
(3) How can I get the next identity value to be generated?
Thanks,
Soura
Its hard to say. Use the conflict viewer to see if you can figure out where
the two rows are coming from. Partitioning is the way to avoid this, but it
looks like you have done this.
A DBCC Checkident('tablename') will give you the current value, so add the
increment to it to get the next value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:4208FC88-5A06-46F8-BE66-64EFF7ECDBC4@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> For identity columns EVEN seed value is set in Publisher database
> and ODD seed value is set in Subscriber database(increment value 2) to
> avoid
> insertion conflicts.
> For a particular table, usually the records are inserted from
> subscriber(through application) and rarely from publisher. During this
> change
> an error 'Primary Violation' occurs.
> (1) What is the reason for this?
> (2) Is there any way to avoid this?
> (3) How can I get the next identity value to be generated?
> Thanks,
> Soura
>
>
sql
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Monday, March 26, 2012
Primarykey Fields
I'm try to get table columns name & primary key to generat Dlete/Insert
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 21, 2012
Primary Key on Two Columns
How can I create a primary key on two columns? I could not find any sensible code sample or description how it should be done. It is clear from MSDN that it is possible but there are no examples.
There is a textbox "Included Columns" in one of the tools for setting primary keys for one column but it does not allow me to enter anything.
Thanks.
Drop table T1
CREATE TABLE [dbo].[T1](
[c1] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
one way is as above mentioned , here your pk will be created on column a and b
from Management studio , open the table highlight the multiple colum and press right button. you can select set primary key option
Madhu
|||This is cute, esp the management studio approach. Thank you.Tuesday, March 20, 2012
Primary Key Column
Does position of primary key column in a table has impact on perfromance ? I
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.
No impact. What has impact is what indexes you have and how they are defined. And remember that a PK
definition creates a unique index (by default as a clustered index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ? I
> have always used first column(or columns) for primary key. I am looking at a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.
No impact. What has impact is what indexes you have and how they are defined. And remember that a PK
definition creates a unique index (by default as a clustered index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ? I
> have always used first column(or columns) for primary key. I am looking at a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
Primary Key Column
Does position of primary key column in a table has impact on perfromance ? I
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.No impact. What has impact is what indexes you have and how they are defined. And remember that a PK
definition creates a unique index (by default as a clustered index).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ? I
> have always used first column(or columns) for primary key. I am looking at a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.No impact. What has impact is what indexes you have and how they are defined. And remember that a PK
definition creates a unique index (by default as a clustered index).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ? I
> have always used first column(or columns) for primary key. I am looking at a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
Primary Key Column
Does position of primary key column in a table has impact on perfromance ? I
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.No impact. What has impact is what indexes you have and how they are defined
. And remember that a PK
definition creates a unique index (by default as a clustered index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ?
I
> have always used first column(or columns) for primary key. I am looking at
a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
have always used first column(or columns) for primary key. I am looking at a
system right now which has primary keys defined in the middle or end of
table. Does it impact performance ?
Any help is appreciated. Thanks.No impact. What has impact is what indexes you have and how they are defined
. And remember that a PK
definition creates a unique index (by default as a clustered index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B2" <B2@.discussions.microsoft.com> wrote in message
news:D6C7E1FB-CAAF-4275-9FBF-F5C6F235B2E0@.microsoft.com...
> Does position of primary key column in a table has impact on perfromance ?
I
> have always used first column(or columns) for primary key. I am looking at
a
> system right now which has primary keys defined in the middle or end of
> table. Does it impact performance ?
> Any help is appreciated. Thanks.
>
Primary key at the beggning of each record
Are primary keys always the first columns in each record? What's the
di
vantage of having them for example in the middle of the records? what
happens?
ThanksOnly cosmetics. No technical difference. Check out what standards you want t
o follow, if there
already is a standard in place etc. From a technical standpoint, column orde
ring is irrelevant
(since no-one should do SELECT * or INSERT without a column name list in pro
duction code). Most
people find tables easier to read with PK as the first column though, I'm gu
ilty as charged, for
instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"J-T" <J-T@.microsft.com> wrote in message news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Are primary keys always the first columns in each record? What's the di
vantage of having them
> for example in the middle of the records? what happens?
> Thanks
>[/color]|||As an add-on to Tibor's statement...the ordering of the columns used in the
PK IS important as SQL Server will automatically add a clustered index
(default) for each PK...first column if composite key is used should be most
selective.
HTH
J
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
> Only cosmetics. No technical difference. Check out what standards you want
> to follow, if there already is a standard in place etc. From a technical
> standpoint, column ordering is irrelevant (since no-one should do SELECT *
> or INSERT without a column name list in production code). Most people find
> tables easier to read with PK as the first column though, I'm guilty as
> charged, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "J-T" <J-T@.microsft.com> wrote in message
> news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...
>|||<
Are primary keys always the first columns in each record?
>
Not necessarily.
<
What's the divantage of having them for example in the middle of the
records? what happens?
>
To my knowledge, there are no physical divantages. However, people
are familiar with the first columns being the primary key.
When IBM made available the Indexed Sequential Access Method (ISAM) in
the 1960s, there was a restriction that the unqiue index bits had to be
at the start of the record.
This restriction has now become a tradition.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***|||what do yuo mean by *most selective*?
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
> As an add-on to Tibor's statement...the ordering of the columns used in
> the PK IS important as SQL Server will automatically add a clustered index
> (default) for each PK...first column if composite key is used should be
> most selective.
> HTH
> J
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
>|||most unique - increases the likelyhood that the index will be utilized to
increase the performance of queries.
"J-T" <J-T@.microsft.com> wrote in message
news:uiqdy2RbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> what do yuo mean by *most selective*?
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
>
di
vantage of having them for example in the middle of the records? whathappens?
ThanksOnly cosmetics. No technical difference. Check out what standards you want t
o follow, if there
already is a standard in place etc. From a technical standpoint, column orde
ring is irrelevant
(since no-one should do SELECT * or INSERT without a column name list in pro
duction code). Most
people find tables easier to read with PK as the first column though, I'm gu
ilty as charged, for
instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"J-T" <J-T@.microsft.com> wrote in message news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Are primary keys always the first columns in each record? What's the di
vantage of having them
> for example in the middle of the records? what happens?
> Thanks
>[/color]|||As an add-on to Tibor's statement...the ordering of the columns used in the
PK IS important as SQL Server will automatically add a clustered index
(default) for each PK...first column if composite key is used should be most
selective.
HTH
J
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
> Only cosmetics. No technical difference. Check out what standards you want
> to follow, if there already is a standard in place etc. From a technical
> standpoint, column ordering is irrelevant (since no-one should do SELECT *
> or INSERT without a column name list in production code). Most people find
> tables easier to read with PK as the first column though, I'm guilty as
> charged, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "J-T" <J-T@.microsft.com> wrote in message
> news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...
>|||<
Are primary keys always the first columns in each record?
>
Not necessarily.
<
What's the di
vantage of having them for example in the middle of therecords? what happens?
>
To my knowledge, there are no physical di
vantages. However, peopleare familiar with the first columns being the primary key.
When IBM made available the Indexed Sequential Access Method (ISAM) in
the 1960s, there was a restriction that the unqiue index bits had to be
at the start of the record.
This restriction has now become a tradition.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***|||what do yuo mean by *most selective*?
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
> As an add-on to Tibor's statement...the ordering of the columns used in
> the PK IS important as SQL Server will automatically add a clustered index
> (default) for each PK...first column if composite key is used should be
> most selective.
> HTH
> J
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
>|||most unique - increases the likelyhood that the index will be utilized to
increase the performance of queries.
"J-T" <J-T@.microsft.com> wrote in message
news:uiqdy2RbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> what do yuo mean by *most selective*?
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
>
Monday, March 12, 2012
Primary Key - Multiple Columns
Friends,
In Access I can set Primary Keys on two columns so that, together, those two
columns comprise a Primary Key.
Is there a way, through T-SQL, to define two or more columns as the Primary
Key on a table? For example, it may be that the [sku] column contains
duplicate values and the [salesman] column contains duplicate values, but if
I GROUP BY [sku] and [salesman] then those two columns taken together contain
no duplicate values and can act as a Primary Key (i.e., there are no records
that contain the same [sku] AND [salesman]. Can I make two columns the
Primary Key?
Thanks for your help ...
Bill MorganIf you look in the documentation (Books Online) under
"PRIMARY KEY", you'll find the article "Creating and Modifying
PRIMARY KEY Constraints", which sort of shows how to do this
when you create the table and also how to do this after the table
is created.
But it's not easy to dig through the entire CREATE/ALTER TABLE
syntax to see how to do it, so...
-- When you create the table:
CREATE TABLE T (
sku int not null,
salesman int not null,
other varchar(20) not null,
primary key (sku,salesman)
)
-- later (the columns must be NOT NULL)
ALTER TABLE T
ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
PRIMARY KEY (sku, salesman)
If you do not have Books Online installed, download the current
version here:
http://www.microsoft.com/sql/techin.../2000/books.asp
Steve Kass
Drew University
bill_morgan_3333 wrote:
>Friends,
>In Access I can set Primary Keys on two columns so that, together, those tw
o
>columns comprise a Primary Key.
>Is there a way, through T-SQL, to define two or more columns as the Primary
>Key on a table? For example, it may be that the [sku] column contains
>duplicate values and the [salesman] column contains duplicate values, but if
>I GROUP BY [sku] and [salesman] then those two columns taken together contain
>no duplicate values and can act as a Primary Key (i.e., there are no record
s
>that contain the same [sku] AND [salesman]. Can I make two columns the
>Primary Key?
>Thanks for your help ...
>Bill Morgan
>
>|||Steve,
Perfectly clear. Thanks for your assistance ...
"Steve Kass" wrote:
> If you look in the documentation (Books Online) under
> "PRIMARY KEY", you'll find the article "Creating and Modifying
> PRIMARY KEY Constraints", which sort of shows how to do this
> when you create the table and also how to do this after the table
> is created.
> But it's not easy to dig through the entire CREATE/ALTER TABLE
> syntax to see how to do it, so...
> -- When you create the table:
> CREATE TABLE T (
> sku int not null,
> salesman int not null,
> other varchar(20) not null,
> primary key (sku,salesman)
> )
> -- later (the columns must be NOT NULL)
> ALTER TABLE T
> ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
> PRIMARY KEY (sku, salesman)
> If you do not have Books Online installed, download the current
> version here:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Steve Kass
> Drew University
> bill_morgan_3333 wrote:
>
>
In Access I can set Primary Keys on two columns so that, together, those two
columns comprise a Primary Key.
Is there a way, through T-SQL, to define two or more columns as the Primary
Key on a table? For example, it may be that the [sku] column contains
duplicate values and the [salesman] column contains duplicate values, but if
I GROUP BY [sku] and [salesman] then those two columns taken together contain
no duplicate values and can act as a Primary Key (i.e., there are no records
that contain the same [sku] AND [salesman]. Can I make two columns the
Primary Key?
Thanks for your help ...
Bill MorganIf you look in the documentation (Books Online) under
"PRIMARY KEY", you'll find the article "Creating and Modifying
PRIMARY KEY Constraints", which sort of shows how to do this
when you create the table and also how to do this after the table
is created.
But it's not easy to dig through the entire CREATE/ALTER TABLE
syntax to see how to do it, so...
-- When you create the table:
CREATE TABLE T (
sku int not null,
salesman int not null,
other varchar(20) not null,
primary key (sku,salesman)
)
-- later (the columns must be NOT NULL)
ALTER TABLE T
ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
PRIMARY KEY (sku, salesman)
If you do not have Books Online installed, download the current
version here:
http://www.microsoft.com/sql/techin.../2000/books.asp
Steve Kass
Drew University
bill_morgan_3333 wrote:
>Friends,
>In Access I can set Primary Keys on two columns so that, together, those tw
o
>columns comprise a Primary Key.
>Is there a way, through T-SQL, to define two or more columns as the Primary
>Key on a table? For example, it may be that the [sku] column contains
>duplicate values and the [salesman] column contains duplicate values, but if
>I GROUP BY [sku] and [salesman] then those two columns taken together contain
>no duplicate values and can act as a Primary Key (i.e., there are no record
s
>that contain the same [sku] AND [salesman]. Can I make two columns the
>Primary Key?
>Thanks for your help ...
>Bill Morgan
>
>|||Steve,
Perfectly clear. Thanks for your assistance ...
"Steve Kass" wrote:
> If you look in the documentation (Books Online) under
> "PRIMARY KEY", you'll find the article "Creating and Modifying
> PRIMARY KEY Constraints", which sort of shows how to do this
> when you create the table and also how to do this after the table
> is created.
> But it's not easy to dig through the entire CREATE/ALTER TABLE
> syntax to see how to do it, so...
> -- When you create the table:
> CREATE TABLE T (
> sku int not null,
> salesman int not null,
> other varchar(20) not null,
> primary key (sku,salesman)
> )
> -- later (the columns must be NOT NULL)
> ALTER TABLE T
> ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
> PRIMARY KEY (sku, salesman)
> If you do not have Books Online installed, download the current
> version here:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Steve Kass
> Drew University
> bill_morgan_3333 wrote:
>
>
Primary Key
Through code i am trying to script a table that has multiple columns as the primary key
CONSTRAINT col1, col2 PRIMARY KEY doesn't work
and when put primary key after each column that doesn't work either.
Any ideas?
CREATE TABLE [dbo].MyTest(test1 int not null, test2 int not null)
ALTER TABLE MyTest
ADD CONSTRAINT NEW_PK PRIMARY KEY(test1, test2)
Adamus
primary key
hi guys,
i have a doubt. i have a table t1 with columns say c1 and c2. now i want to make the column c1 as my primary key. how to issue a query to do so in sql server 2000 to get executed in sql query analyzer? every time i do with alter cmds, it shows that the column c1 already exists!!!you should be using the ALTER TABLE and ALTER COLUMN commands.
i have a doubt. i have a table t1 with columns say c1 and c2. now i want to make the column c1 as my primary key. how to issue a query to do so in sql server 2000 to get executed in sql query analyzer? every time i do with alter cmds, it shows that the column c1 already exists!!!you should be using the ALTER TABLE and ALTER COLUMN commands.
ALTER TABLE MyTable ALTER COLUMN myColumn [int] IDENTITY (1, 1) NOT NULL
Take a look at Books on Line (BOL) some time.
Saturday, February 25, 2012
Preview has data but no columns returned
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet
I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786591&SiteID=1
In case you need it this is the xml code of my package
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2593</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="19156" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="7594" top="2593" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>ETLRafLab</dwd:DataSourceID></ConnectionManager></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1667</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="9" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="18230" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11192" top="1667" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/1" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Copies columns." left="11192" top="4233" logicalid="7" controlid="7" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/83" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="12592" top="2432" logicalid="8" controlid="8" masterid="0" hint1="0" hint2="0" width="799" height="2301" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/paths/86" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="7" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="12991" y="2831" />
<point x="12991" y="4233" />
</connector>
</ddscontrol>
</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Copy Column Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">
<components>
<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">
<properties>
<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>
<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>
<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property>
<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>
<connections>
<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{82682565-2AB7-4AE8-AB84-A06FBB937955}"/></connections>
<outputs>
<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="32" name="ProductCategoryID" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"/>
<outputColumn id="35" name="Name" description="" lineageId="35" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"/>
<outputColumn id="38" name="rowguid" description="" lineageId="38" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"/>
<outputColumn id="41" name="ModifiedDate" description="" lineageId="41" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"/></outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="31" name="ProductCategoryID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>
<externalMetadataColumn id="34" name="Name" description="" precision="0" scale="0" length="50" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="37" name="rowguid" description="" precision="0" scale="0" length="0" dataType="guid" codePage="0"/>
<externalMetadataColumn id="40" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0"/></externalMetadataColumns></output>
<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="33" name="ProductCategoryID" description="" lineageId="33" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="36" name="Name" description="" lineageId="36" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="39" name="rowguid" description="" lineageId="39" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="42" name="ModifiedDate" description="" lineageId="42" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
<component id="83" name="Copy Column" componentClassID="{9A9C066E-59CB-4332-B899-8783F6049B08}" description="Copies columns." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Copy Column;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">
<inputs>
<input id="84" name="Copy Column Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>
</inputs>
<outputs>
<output id="85" name="Copy Column Output" description="" exclusionGroup="0" synchronousInputId="84" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
</components>
<paths>
<path id="86" name="OLE DB Source Output" description="" startId="11" endId="84"/>
</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet
I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:
Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)
Try the workaround that I suggested on the other thread.
-Jamie
|||
ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet
|||ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet
There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.
-Jamie
|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..
Preview has data but no columns returned
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet
I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786591&SiteID=1
In case you need it this is the xml code of my package
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2593</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="19156" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="7594" top="2593" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>ETLRafLab</dwd:DataSourceID></ConnectionManager></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1667</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="9" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="18230" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11192" top="1667" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/1" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Copies columns." left="11192" top="4233" logicalid="7" controlid="7" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/83" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="12592" top="2432" logicalid="8" controlid="8" masterid="0" hint1="0" hint2="0" width="799" height="2301" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/paths/86" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="7" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="12991" y="2831" />
<point x="12991" y="4233" />
</connector>
</ddscontrol>
</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Copy Column Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">
<components>
<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">
<properties>
<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>
<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>
<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property>
<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>
<connections>
<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{82682565-2AB7-4AE8-AB84-A06FBB937955}"/></connections>
<outputs>
<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="32" name="ProductCategoryID" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"/>
<outputColumn id="35" name="Name" description="" lineageId="35" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"/>
<outputColumn id="38" name="rowguid" description="" lineageId="38" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"/>
<outputColumn id="41" name="ModifiedDate" description="" lineageId="41" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"/></outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="31" name="ProductCategoryID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>
<externalMetadataColumn id="34" name="Name" description="" precision="0" scale="0" length="50" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="37" name="rowguid" description="" precision="0" scale="0" length="0" dataType="guid" codePage="0"/>
<externalMetadataColumn id="40" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0"/></externalMetadataColumns></output>
<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="33" name="ProductCategoryID" description="" lineageId="33" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="36" name="Name" description="" lineageId="36" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="39" name="rowguid" description="" lineageId="39" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="42" name="ModifiedDate" description="" lineageId="42" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
<component id="83" name="Copy Column" componentClassID="{9A9C066E-59CB-4332-B899-8783F6049B08}" description="Copies columns." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Copy Column;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">
<inputs>
<input id="84" name="Copy Column Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>
</inputs>
<outputs>
<output id="85" name="Copy Column Output" description="" exclusionGroup="0" synchronousInputId="84" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
</components>
<paths>
<path id="86" name="OLE DB Source Output" description="" startId="11" endId="84"/>
</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet
I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:
Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)
Try the workaround that I suggested on the other thread.
-Jamie
|||
ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet
|||ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet
There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.
-Jamie
|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..
Subscribe to:
Posts (Atom)