Losing double quotes?

Hi,

I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.

I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").

The vast majority of the records are formatted properly, and have the double quotes in the expected locations.

The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...

i apologize for being the worst ever at posting questions here, please let me know if i can add anything

Could you please post your code, its hard to uess what is goind wron without seeing your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

it is over 500 lines, but i could certainly post it once i get to work...

in the meantime, however, perhaps this might be useful:

I set a breakpoint at my function's final return statement and have observed the return value as follows:

Author of "Some random book." Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

This is how I'd like, and expect, the value to appear in my database, but after SELECTING the particular row, I get:

Author of Some random book. Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

I doubt this is much more revealing, and will be happy to post my code later, if thought to be any help.

|||

Another thing to add (probably useless):

The problem only occurs during an UPDATE. If I do:

SELECT dbo.EventBuilder([Title], [Author], [Published]) FROM MyTable

I get the correct output.

...getting desperate here

|||

Check your database hold the same result.

I can't able to understand where & what is your problem occurs..

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping in a stored proceedure

What I would like to be able to do but am not sure if I can is the following
.
I need to set a variable = to the results of a single field recordset:
@.X = select EmployeeID from Employees where DepartmentID = 1
Then I need to build a dynamic sql statement based on the above results.
If the first recordset has three records. I need to loop through it three
times and concatinate the results of the sql that would look like this:
This or something like it would be in the loop...
@.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
END RESULT: @.SQL would now look like this
select * from customers where EmpID = 1, select * from customers where EmpID
= 2, select * from customers where EmpID = 3
The text between the comma's can increase or decrease depending on the
number of records in the first sql statement at the top of the page.
Any thoughts
Thank you
KentHi Kent.
You could try:
select ' select * from customers where EmpID=' + EmployeeID from
Employees where DepartmentID = 1
Bryce|||Why not just:
SELECT *
FROM Customers
WHERE empid IN
(SELECT employeeid
FROM Employees
WHERE departmentid = 1)
Dynamic SQL is bad news in a production application. Also, avoid SELECT
*. Code is safer, easier to maintain and maybe more efficient if you
list just the required column names.
David Portas
SQL Server MVP
--|||Kent,
What is the reason of doing this?
why not:
select employeeid, departmentid, ...
from employees
where departmentid = 1
AMB
"Kent Prokopy" wrote:

> What I would like to be able to do but am not sure if I can is the followi
ng.
> I need to set a variable = to the results of a single field recordset:
> @.X = select EmployeeID from Employees where DepartmentID = 1
> Then I need to build a dynamic sql statement based on the above results.
> If the first recordset has three records. I need to loop through it three
> times and concatinate the results of the sql that would look like this:
> This or something like it would be in the loop...
> @.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
>
> END RESULT: @.SQL would now look like this
> select * from customers where EmpID = 1, select * from customers where Emp
ID
> = 2, select * from customers where EmpID = 3
> The text between the comma's can increase or decrease depending on the
> number of records in the first sql statement at the top of the page.
> Any thoughts
> Thank you
> Kent|||I need to build an Excel report that has each Department on a diferant sheet
.
On each sheet will be a column for each employee. The number of column will
vary depending on the number of employee's in each department. So if
Department Dep1 has 5 employee's the sheet will have five columns. I could
populate each column one at a time, but would like to be able to do this
dynamicly. If posable.
"bd" wrote:

> Hi Kent.
> You could try:
> select ' select * from customers where EmpID=' + EmployeeID from
> Employees where DepartmentID = 1
> Bryce
>|||Correction,
select c.*
from customers as c inner join employees as e
on c.empid = e.employeeid and e.departmentid = 1
AMB
"Alejandro Mesa" wrote:
> Kent,
> What is the reason of doing this?
> why not:
> select employeeid, departmentid, ...
> from employees
> where departmentid = 1
>
> AMB
>
> "Kent Prokopy" wrote:
>|||My bad. Sorry I do not need * from... I need
For each employee I need a column/field.
select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
This will give me three columns in an Excel report. or two columns or XXXXX
"David Portas" wrote:

> Why not just:
> SELECT *
> FROM Customers
> WHERE empid IN
> (SELECT employeeid
> FROM Employees
> WHERE departmentid = 1)
> Dynamic SQL is bad news in a production application. Also, avoid SELECT
> *. Code is safer, easier to maintain and maybe more efficient if you
> list just the required column names.
> --
> David Portas
> SQL Server MVP
> --
>|||select EmpID , count(*) from DataTable
where DataDate = yesterday
GROUP BY EmpID
and do the pivoting in Excel.
Jacco Schalkwijk
SQL Server MVP
"Kent Prokopy" <KentProkopy@.discussions.microsoft.com> wrote in message
news:2126E40D-33DA-4389-A865-73E33F716A0D@.microsoft.com...
> My bad. Sorry I do not need * from... I need
> For each employee I need a column/field.
> select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
> This will give me three columns in an Excel report. or two columns or
> XXXXX
> "David Portas" wrote:
>|||Thank you all for your help/thoughts.
I have come up with a solution that will work.
I am going to build the sql statement in vb code and pass it tp the SP as a
varchar.
"David Portas" wrote:

> Use an Excel Pivot Table for that. You can query the database directly
> and it will create the columns for you. Alternatively you could use
> DTS.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have come up with a solution that will work.
> I am going to build the sql statement in vb code and pass it tp the SP as
a
> varchar.
Ugh, WHY? Sure, that will *work* but it is far and away from the best
solution. This is like going to the grocery store with a list of bar codes
for the products you want to buy.

Lookup with multiple hits

In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?

Try changing your lookup query to only return the rows you are interesting in.

Code Block

select

colA

,colB

,max or min (colC)

from

tableA

group by

colA

,colB

|||

The lookup component does this by default does it not?

Edit: that is return only the first result that it runs into. Just make sure that you return the correct row first and you should be fine...

Try the following to prove:

select 19000101 as datekey, '01/01/1900' as datename

UNION

select 19000102 as datekey, '01/02/1900' as datename

UNION

select 19000103 as datekey, '01/03/1900' as datename

UNION

select 19000104 as datekey, '01/04/1900' as datename

UNION

select 19000105 as datekey, '01/05/1900' as datename

in an oledb source

and then in the lookup

select 19000101 as datekey, 'myname11' as name

UNION

select 19000102 as datekey, 'myname21' as name

UNION

select 19000103 as datekey, 'myname31' as name

UNION

select 19000104 as datekey, 'myname41' as name

UNION

select 19000101 as datekey, 'myname12' as name

UNION

select 19000102 as datekey, 'myname22' as name

UNION

select 19000102 as datekey, 'myname23' as name

Notice, there is no match for 19000105, it will redirect.

You will return the values

19000101, '01/01/1900', 'myname11'

19000102, '01/02/1900', 'myname21'

19000103, '01/03/1900', 'myname31'

19000104, '01/04/1900', 'myname41'

|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

I believe so. The message about duplicate values is just a warning, not an error. I don't think there's a guaranteed order -- it just picks the first one it comes across.|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

Yes. Lookup is a synchronous component; hence the number of rows in the output is the same than the number of rows in the input. If your lookup query returns more than one row for an incoming row; then 'the first' one would be used; and you don't have control over which one would be used.|||SQL query returns result set of multiple contact persons for multiple companies. On output I need for each company to filter out just one of the contact persons. Result set is sorted (ORDER BY) so that if many contact persons are found per comapny one contact person that should be chosen as highest in order. On input of the Lookup component I've put OLE_SRC component which fetches all the companies.

I couldn't use just GROUP BY because I need columns in resultset which aren't used in aggregate function nor should be be group on.
|||

I would not feel comfortable relying in SSIS picking the 1st from the list. If the query in the lookup component is against SQL Server, Oracle or other RDBS where the rank function I would use a query like:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Notice that you could mimic the result set even without the rank() function; but the query could get little complex|||Great, thank you all, especially to Rafael.

In an OLE_SRC I manually entered SQL command. I had to do it manually because it seems that Query Builder doesn't (yet) support OVER construct - when I click Build Query on OLE_SRC component it says "The OVER SQL construct or statement is not supported.".
|||

Is your source sql server 2005?

If not, you'll have to use another technique to get the row number

Code Block

select

colA

,(

select count(*)

from tableA b

where b.colA <= a.colA

) as RowNum

from tableA a

|||Yes, the source is SQL Server 2005. OLE DB Source component has Query Builder GUI which doesn't support OVER construct. I entered query manually and it works.

Lookup tables

For a "tblFormObject" table whose every single record links to many different
records in "tblFormInstances" table, I think I want
tblFormObject.frmId as Primary Key
tblFormInstances.frmId as Foreign Key
tblFormInstances.InstId as Primary Key
However, in the tblFormInstances table I also have a lookup column called
tblFormInstances.statId which should link to
tblFormStatuses:
tblStatId PrimaryKey
tblStatDescription
Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
as different form instances can have the same form status! But How could I
set the relationship' I want tblFormInstances.StatId as primary and
tblFormStatuses.tblStatId as foreign' However, when I try to create such an
relationship with Enterprise Manager, ticking all options except cascade
update/delete, I get errror:
The columns in table 'tblFormInstances' do not match an existing
primary key or UNIQUE constraint
How could I resolve this? What keys do I need/want?Hi
I think that:
tblFormInstances.statId has a foreign key to tblFormStatuses:tblStatId
In the same way that
tblFormInstances.frmId has a Foreign Key to tblFormObject.frmId
It seems you are trying to create the foreign key for statId the wrong way
around!
John
"Patrick" wrote:
> For a "tblFormObject" table whose every single record links to many different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
> as different form instances can have the same form status! But How could I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||Patrick,
tblFormInstances needs foreign key linking to both tblFormObject via frmId
and to tblFormStatuses via StatId.
hth
Quentin
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> For a "tblFormObject" table whose every single record links to many
> different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in
> tblFormInstances
> as different form instances can have the same form status! But How could
> I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such
> an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||I thought so too!
tblFormInstances.statId as primary key table
tblFormStatuses.statID as foreign key table
I have under Enterprise Manager for tblFormInstances:
Relationship name: FK_tblFormStatuses_tblFormInstances:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Check existing data on creation: ticked
Enforce relationship for replication: ticked
Enforce relationship for INSERTs and UPDATEs: ticked
Cascades: NOt ticked
but I get error saying "The columns in table 'tblFormInstances' do not match
an existing primary key or UNIQUE constraing"
Try swapping the table the other way round does not help either.
"Quentin Ran" wrote:
> Patrick,
> tblFormInstances needs foreign key linking to both tblFormObject via frmId
> and to tblFormStatuses via StatId.
> hth
> Quentin
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> > For a "tblFormObject" table whose every single record links to many
> > different
> > records in "tblFormInstances" table, I think I want
> > tblFormObject.frmId as Primary Key
> > tblFormInstances.frmId as Foreign Key
> > tblFormInstances.InstId as Primary Key
> >
> > However, in the tblFormInstances table I also have a lookup column called
> > tblFormInstances.statId which should link to
> > tblFormStatuses:
> > tblStatId PrimaryKey
> > tblStatDescription
> >
> > Obviously, tblFormInstances.statId is not a primary key in
> > tblFormInstances
> > as different form instances can have the same form status! But How could
> > I
> > set the relationship' I want tblFormInstances.StatId as primary and
> > tblFormStatuses.tblStatId as foreign' However, when I try to create such
> > an
> > relationship with Enterprise Manager, ticking all options except cascade
> > update/delete, I get errror:
> > The columns in table 'tblFormInstances' do not match an existing
> > primary key or UNIQUE constraint
> >
> > How could I resolve this? What keys do I need/want?
>
>|||Hi
It would be easier to understand if you posted the DDL
http://www.aspfaq.com/etiquett­e.asp?id=5006
once you are used to the T-SQL syntaxes it is often easier to write the code
rather than mess around with the EM gui!
This does not sound correct:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Try making it
Primary Key Table: tblFormStatuses statId
Foreign Key Table: tblFormInstances statId
John
"Patrick" wrote:
> I thought so too!
> tblFormInstances.statId as primary key table
> tblFormStatuses.statID as foreign key table
> I have under Enterprise Manager for tblFormInstances:
> Relationship name: FK_tblFormStatuses_tblFormInstances:
> Primary Key Table: tblFormInstances statId
> Foreign Key Table: tblFormStatuses statId
> Check existing data on creation: ticked
> Enforce relationship for replication: ticked
> Enforce relationship for INSERTs and UPDATEs: ticked
> Cascades: NOt ticked
> but I get error saying "The columns in table 'tblFormInstances' do not match
> an existing primary key or UNIQUE constraing"
> Try swapping the table the other way round does not help either.
> "Quentin Ran" wrote:
> > Patrick,
> >
> > tblFormInstances needs foreign key linking to both tblFormObject via frmId
> > and to tblFormStatuses via StatId.
> >
> > hth
> >
> > Quentin
> >
> >
> > "Patrick" <questions@.newsgroup.nospam> wrote in message
> > news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> > > For a "tblFormObject" table whose every single record links to many
> > > different
> > > records in "tblFormInstances" table, I think I want
> > > tblFormObject.frmId as Primary Key
> > > tblFormInstances.frmId as Foreign Key
> > > tblFormInstances.InstId as Primary Key
> > >
> > > However, in the tblFormInstances table I also have a lookup column called
> > > tblFormInstances.statId which should link to
> > > tblFormStatuses:
> > > tblStatId PrimaryKey
> > > tblStatDescription
> > >
> > > Obviously, tblFormInstances.statId is not a primary key in
> > > tblFormInstances
> > > as different form instances can have the same form status! But How could
> > > I
> > > set the relationship' I want tblFormInstances.StatId as primary and
> > > tblFormStatuses.tblStatId as foreign' However, when I try to create such
> > > an
> > > relationship with Enterprise Manager, ticking all options except cascade
> > > update/delete, I get errror:
> > > The columns in table 'tblFormInstances' do not match an existing
> > > primary key or UNIQUE constraint
> > >
> > > How could I resolve this? What keys do I need/want?
> >
> >
> >

Lookup tables

For a "tblFormObject" table whose every single record links to many different
records in "tblFormInstances" table, I think I want
tblFormObject.frmId as Primary Key
tblFormInstances.frmId as Foreign Key
tblFormInstances.InstId as Primary Key
However, in the tblFormInstances table I also have a lookup column called
tblFormInstances.statId which should link to
tblFormStatuses:
tblStatId PrimaryKey
tblStatDescription
Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
as different form instances can have the same form status! But How could I
set the relationship? I want tblFormInstances.StatId as primary and
tblFormStatuses.tblStatId as foreign? However, when I try to create such an
relationship with Enterprise Manager, ticking all options except cascade
update/delete, I get errror:
The columns in table 'tblFormInstances' do not match an existing
primary key or UNIQUE constraint
How could I resolve this? What keys do I need/want?
Hi
I think that:
tblFormInstances.statId has a foreign key to tblFormStatuses:tblStatId
In the same way that
tblFormInstances.frmId has a Foreign Key to tblFormObject.frmId
It seems you are trying to create the foreign key for statId the wrong way
around!
John
"Patrick" wrote:

> For a "tblFormObject" table whose every single record links to many different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
> as different form instances can have the same form status! But How could I
> set the relationship? I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign? However, when I try to create such an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?
|||Patrick,
tblFormInstances needs foreign key linking to both tblFormObject via frmId
and to tblFormStatuses via StatId.
hth
Quentin
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> For a "tblFormObject" table whose every single record links to many
> different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in
> tblFormInstances
> as different form instances can have the same form status! But How could
> I
> set the relationship? I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign? However, when I try to create such
> an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?
|||I thought so too!
tblFormInstances.statId as primary key table
tblFormStatuses.statID as foreign key table
I have under Enterprise Manager for tblFormInstances:
Relationship name: FK_tblFormStatuses_tblFormInstances:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Check existing data on creation: ticked
Enforce relationship for replication: ticked
Enforce relationship for INSERTs and UPDATEs: ticked
Cascades: NOt ticked
but I get error saying "The columns in table 'tblFormInstances' do not match
an existing primary key or UNIQUE constraing"
Try swapping the table the other way round does not help either.
"Quentin Ran" wrote:

> Patrick,
> tblFormInstances needs foreign key linking to both tblFormObject via frmId
> and to tblFormStatuses via StatId.
> hth
> Quentin
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
>
>
|||Hi
It would be easier to understand if you posted the DDL
http://www.aspfaq.com/etiquettXe.asp?id=5006
once you are used to the T-SQL syntaxes it is often easier to write the code
rather than mess around with the EM gui!
This does not sound correct:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Try making it
Primary Key Table: tblFormStatuses statId
Foreign Key Table: tblFormInstances statId
John
"Patrick" wrote:
[vbcol=seagreen]
> I thought so too!
> tblFormInstances.statId as primary key table
> tblFormStatuses.statID as foreign key table
> I have under Enterprise Manager for tblFormInstances:
> Relationship name: FK_tblFormStatuses_tblFormInstances:
> Primary Key Table: tblFormInstances statId
> Foreign Key Table: tblFormStatuses statId
> Check existing data on creation: ticked
> Enforce relationship for replication: ticked
> Enforce relationship for INSERTs and UPDATEs: ticked
> Cascades: NOt ticked
> but I get error saying "The columns in table 'tblFormInstances' do not match
> an existing primary key or UNIQUE constraing"
> Try swapping the table the other way round does not help either.
> "Quentin Ran" wrote:

Lookup tables

For a "tblFormObject" table whose every single record links to many differen
t
records in "tblFormInstances" table, I think I want
tblFormObject.frmId as Primary Key
tblFormInstances.frmId as Foreign Key
tblFormInstances.InstId as Primary Key
However, in the tblFormInstances table I also have a lookup column called
tblFormInstances.statId which should link to
tblFormStatuses:
tblStatId PrimaryKey
tblStatDescription
Obviously, tblFormInstances.statId is not a primary key in tblFormInstances
as different form instances can have the same form status! But How could I
set the relationship' I want tblFormInstances.StatId as primary and
tblFormStatuses.tblStatId as foreign' However, when I try to create such an
relationship with Enterprise Manager, ticking all options except cascade
update/delete, I get errror:
The columns in table 'tblFormInstances' do not match an existing
primary key or UNIQUE constraint
How could I resolve this? What keys do I need/want?Hi
I think that:
tblFormInstances.statId has a foreign key to tblFormStatuses:tblStatId
In the same way that
tblFormInstances.frmId has a Foreign Key to tblFormObject.frmId
It seems you are trying to create the foreign key for statId the wrong way
around!
John
"Patrick" wrote:

> For a "tblFormObject" table whose every single record links to many differ
ent
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in tblFormInstance
s
> as different form instances can have the same form status! But How could
I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such
an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||Patrick,
tblFormInstances needs foreign key linking to both tblFormObject via frmId
and to tblFormStatuses via StatId.
hth
Quentin
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
> For a "tblFormObject" table whose every single record links to many
> different
> records in "tblFormInstances" table, I think I want
> tblFormObject.frmId as Primary Key
> tblFormInstances.frmId as Foreign Key
> tblFormInstances.InstId as Primary Key
> However, in the tblFormInstances table I also have a lookup column called
> tblFormInstances.statId which should link to
> tblFormStatuses:
> tblStatId PrimaryKey
> tblStatDescription
> Obviously, tblFormInstances.statId is not a primary key in
> tblFormInstances
> as different form instances can have the same form status! But How could
> I
> set the relationship' I want tblFormInstances.StatId as primary and
> tblFormStatuses.tblStatId as foreign' However, when I try to create such
> an
> relationship with Enterprise Manager, ticking all options except cascade
> update/delete, I get errror:
> The columns in table 'tblFormInstances' do not match an existing
> primary key or UNIQUE constraint
> How could I resolve this? What keys do I need/want?|||I thought so too!
tblFormInstances.statId as primary key table
tblFormStatuses.statID as foreign key table
I have under Enterprise Manager for tblFormInstances:
Relationship name: FK_tblFormStatuses_tblFormInstances:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Check existing data on creation: ticked
Enforce relationship for replication: ticked
Enforce relationship for INSERTs and UPDATEs: ticked
Cascades: NOt ticked
but I get error saying "The columns in table 'tblFormInstances' do not match
an existing primary key or UNIQUE constraing"
Try swapping the table the other way round does not help either.
"Quentin Ran" wrote:

> Patrick,
> tblFormInstances needs foreign key linking to both tblFormObject via frmId
> and to tblFormStatuses via StatId.
> hth
> Quentin
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:E155F114-94C9-42B8-9108-F86C3B824BA0@.microsoft.com...
>
>|||Hi
It would be easier to understand if you posted the DDL
http://www.aspfaq.com/etiquett_e.asp?id=5006
once you are used to the T-SQL syntaxes it is often easier to write the code
rather than mess around with the EM gui!
This does not sound correct:
Primary Key Table: tblFormInstances statId
Foreign Key Table: tblFormStatuses statId
Try making it
Primary Key Table: tblFormStatuses statId
Foreign Key Table: tblFormInstances statId
John
"Patrick" wrote:
[vbcol=seagreen]
> I thought so too!
> tblFormInstances.statId as primary key table
> tblFormStatuses.statID as foreign key table
> I have under Enterprise Manager for tblFormInstances:
> Relationship name: FK_tblFormStatuses_tblFormInstances:
> Primary Key Table: tblFormInstances statId
> Foreign Key Table: tblFormStatuses statId
> Check existing data on creation: ticked
> Enforce relationship for replication: ticked
> Enforce relationship for INSERTs and UPDATEs: ticked
> Cascades: NOt ticked
> but I get error saying "The columns in table 'tblFormInstances' do not mat
ch
> an existing primary key or UNIQUE constraing"
> Try swapping the table the other way round does not help either.
> "Quentin Ran" wrote:
>

Lookup Functionality

Hi,

I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think

but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0

very strange..

doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case

i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...

more magically appear when hte same statement is ran in SSIS!!

I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...

any help would be muc happreciated

regards

Chris

You might want to make sure you are converting the values for the Lookup and the value you are matching on to UPPERCASE to avoid any case mismatches.

|||

Hi, thanks for your post.. i have already put it into uppercase, but this has not solved the problem completely..

i still don't understand how the query the first time returns all the values as it should, and when it is re-ran more magically appear but in different cases (there is also an issue with foreign text, but thats something different)

one of my tables is nvarchar, and to accomodate this, the target table for all the entries is nvarchar therefore needing the items entered into it to be converted from varchar

could this be causing the problem perhaps? something to do with unicode / non-unicode?

cheers

Chris

|||

Is the problem only in the Source adapter? If you add a RowCount immediately after the Source, is it returning a variable number of rows?

|||

Hi,

The source returns the same number of rows each time, but for some reason is failing to match them up when doing a lookup and thus insertng duplicates...

one such example is:

select * from Snowflake.DimCity

where city = 'zweibrücken'

zweibrucken 2007-08-01 09:33:00

zweibrücken 2007-08-01 09:34:00

how is this discrepancy being missed first time around?!?

my SELECT statement converts all the city fields to nvarchar on select... however i have to do this becasue some of the source tables are nvarchar and some arent, and SSIS throws errors if i don't convert ;(

help help

|||

You are aware that the lookup caches all of its data at the beginning of the data flow, aren't you? So, by default, it can't match duplicates on rows that you are inserting in the same data flow? You can work around this by using an aggregate transform to eliminate the duplicates, or by disabling caching on the lookup (found on the advanced tab), which forces it to query the database for each row. Unfortunately, disabling caching makes it run slower, and doesn't guarantee you won't get duplicates, because of the way rows are handled in batches.

Also, I am fairly positive that because "u" and "ü" are two different characters, the lookup will not match them. If you looked at the actual bytes making up those two strings, they would be different. You might need to use the Fuzzy Lookup to do your matching in this scenario.

|||Hi,

I have solved this issue now, I converted all the fields in use to nvarchar in the query.. and then the collation of the fileds used in the database to Latin1_General_BIN2

From SQL Server Developer Centre:

"Sorts and compares data in SQL Server tables based on Unicode code points for Unicode data. For non-Unicode data, Binary-code point will use comparisons identical to binary sorts.

The advantage of using a Binary-code point sort order is that no data resorting is required in applications that compare sorted SQL Server data. As a result, a Binary-code point sort order provides simpler application development and possible performance increases."

This is basically what you said in your last post jwelch, comparing the acutal bytes and all seems to be well now!! I no longer have spontaneuously appearing entreis when i run the SSIS package.

Thank you

Chris

Looking for tools to monitor sql and applications

I am looking for the right tools to do application monitoring.

I'm hoping to find one single tool that can do the entire job but if
it does not exist then a few monitoring apps would do as well.

I need the ability to do the standard things like testing for ping,
checking for windows services running and restarting them after some
threshold is met, and wmi.

Some of the more tricky things I need it to do are:

* Parse log files looking for specific error codes, and the ability to
set an alert only if it sees that error X times over some period of
time.

* Run custom slq queries like row counts and max values returned from
a query and if that condition is met X times over some period of
time.

* Run an external app that does its own custom testing and act on its
results, which could be to parse the result file from the app.

Keep in mind cost is not the isssue right now - so this can be
freeware to some type of enterprise solution that our company can use.
Does anyone know of any tools that you can point me towards?

Thanks...(reg@.yahoo.com) writes:
> I am looking for the right tools to do application monitoring.
>
> I'm hoping to find one single tool that can do the entire job but if
> it does not exist then a few monitoring apps would do as well.
>
> I need the ability to do the standard things like testing for ping,
> checking for windows services running and restarting them after some
> threshold is met, and wmi.
> Some of the more tricky things I need it to do are:
>
> * Parse log files looking for specific error codes, and the ability to
> set an alert only if it sees that error X times over some period of
> time.
> * Run custom slq queries like row counts and max values returned from
> a query and if that condition is met X times over some period of
> time.
> * Run an external app that does its own custom testing and act on its
> results, which could be to parse the result file from the app.
>
> Keep in mind cost is not the isssue right now - so this can be
> freeware to some type of enterprise solution that our company can use.
> Does anyone know of any tools that you can point me towards?

Have you looked at Microsoft Operations Manager? I can't tell that it
does all you want, but MOM is the tool Microsoft presents when you
need to monitor many servers. (It is not really clear from your post
that is what you want, though.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Looking for SQL Recruiting agency

I'm sure many of you MVP's are independents, but we are looking for a single
SQL Server specialist for a large project and were looking for a respectable
agency for finding VERY high level candidates.
New system will be multi-million transactions per day. [Clustered VLDB].
Must be up 99.999% with triple redundancy.
We're looking for someone who has done a comparable project and has
references to prove.
Thanks."Chris" <rooster575@.hotmail.com> wrote in message
news:%23h7HHfrVHHA.1180@.TK2MSFTNGP05.phx.gbl...
> I'm sure many of you MVP's are independents, but we are looking for a
> single SQL Server specialist for a large project and were looking for a
> respectable agency for finding VERY high level candidates.
> New system will be multi-million transactions per day. [Clustered VLDB].
Note Multi-million transactions per day doesn't necessarily mean a VLDB
(just making sure we're on the same page.)
I was recently managing a db that would do millions of transactions per day
and the DB in question was under 10 gigabytes I believe.
> Must be up 99.999% with triple redundancy.
Define triple redundancy?
As for 5 9s. I've found very few companies really truly a) need that b) can
afford it.
That's 5 minutes of outage a year. Do you really absolutely need this?
How do you plan on doing simple maintenance like applying patches, etc?
Many companies once they look at the costs involved, etc. decide that they
really don't mean 5 9s.
A rule of thumb that I saw years back that has often worked for me is that
for each 9 you want, figure on doubling costs.
So going from 90% to 99% uptime, doubles your cost. 99.9% doubles it again,
etc.
> We're looking for someone who has done a comparable project and has
> references to prove.
>
I'm not sure what you have is a project or an ongoing need. If it's a
project, what do you plan on doing when the person leaves? Achieving 5 9s
is an ongoing job, not a "project".
(Sorry if I'm sounding negative here, just trying to get a better feel for
what you really need/want.)
> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com