Problem with SQL Update when a field is NULL

I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.

i.e. This does not work if something is NULL

UPDATE myTable SET something = @.something
WHERE ID = @.o_ID AND something = @.o_something

Thank You,
Jason

The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown. Try the link below for work around. Hope this helps.

http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp

|||Will there be a huge performance impact if I use Coalesce() 144 times in a statement?|||

No I think you should try SET ANSI_NULLS OFF instead. Try the link below for details from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp

|||It still does not work when I use set ANSI_NULLS OFF|||

I have two option there is a new tool that gives you intelisense in SQL Server Query Tools called SQL Prompt see if it can give you some idea of how to get the info. The other is a link with long code using COALESCE and ISNULL. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Problem with SQL Statement and JOIN

Hello NG,
i have 2 Tables
Table 1 -> PEOPLE_DB
Table 2 -> LINK_DB
Table1 fields: name | age | color | unique_nr|
Table2 fields: link_nr1| link_nr2 |
every row in Table1 have a lot of rows in table2
link_nr1 <=> unique_nr
link_nr2 have the unique_nr - values of other rows of table1
link_nr2 doesnt look at himselfe (link_nr2 can not be link_nr1)
WHAT I WANT TO DO :
SELECT * from People_db where age=20 and color=red and name like % ab %
where in table2 of the person is link_nr2 with value 10 AND link_nr2 with
value 44
Group By People_db.name
Order By age,color Descending
how can i realise this ?
ThanX for Help
--
Contact:
Erich Wanker | www .sec. at | wanker @. sec.at
Using:
Delphi7 | Jedi | Reportbuilder | LmdTools | Zeos | InfoPower | RXLib |
FIREBIRD 1_5 SQL
Links:
http://msdn.microsoft.com/library/default.asp
http://www.swissdelphicenter.ch/torry/
http://www.w3schools.com/sql/
http://www.delphi-fundgrube.de/
http://www.schattenbaum.net/php/Try something like this:
declare @.PEOPLE_DB table (name varchar(20), age int, color varchar(10),
unique_nr int)
declare @.LINK_DB table (link_nr1 int, link_nr2 int)
insert @.PEOPLE_DB values ('baby', 20, 'red', 1)
insert @.PEOPLE_DB values ('abbey', 20, 'red', 2)
insert @.PEOPLE_DB values ('gabby', 30, 'red', 3)
insert @.PEOPLE_DB values ('tabby', 35, 'red', 4)
insert @.LINK_DB values (1, 2)
insert @.LINK_DB values (1, 3)
insert @.LINK_DB values (1, 4)
insert @.LINK_DB values (2, 3)
insert @.LINK_DB values (2, 4)
SELECT *
from @.People_db p
inner join @.LINK_DB l
on p.unique_nr = l.link_nr1
inner join @.People_db p2
on p2.unique_nr = l.link_nr2
where p.age=20 and p.color='red' and p.name like '%ab%'
--add additional filters here
Order By p.age,p.color desc|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Your personal narrative cannot be tested.
You also need to learn that reows are not records, columns are not
fields.
If you had followed minimal netiquette and data modeling rules, would
you have actually posted something like this?
CREATE TABLE People
(person_nbr INTEGER NOT NULL PRIMARY KEY,
person_name CHAR(35) NOT NULL
birth_date DATETIME NOT NULL, -- never store age
skin_color CHAR(10) NOT NULL); -- color of what?
CREATE TABLE Partners
(person_nbr_1 INTEGER NOT NULL,
person_nbr_2 INTEGER NOT NULL,
CHECK (person_nbr_1 <> person_nbr_2)
PRIMARY KEY (person_nbr_1, person_nbr_2));
You should also have REFERENCES clauses on the Partners back to People,
but SQL Server does not allow this without triggers. Also, links are
not References. The rest of your combination narrative and personal
pseudo-code made no sense, but here is a guess.
SELECT P2.person_name
FROM People AS P1,People AS P2, Partners AS L
WHERE L.person_nbr_1 = P1.person_nbr
AND L.person_nbr_2 = P2.person_nbr
AND DATEDIFF (YR, P1.birth_date, CURRENT_TIMESTAMP) = 20
AND P1.sking_color = 'red'
AND P1.person_name LIKE '% ab %'
AND L1.person_nbr_2 IN (10, 44)
GROUP BY P2.person_name
HAVING COUNT(*) = 2;
Now, would someone else like to tell him about cross posting and
netiquette?|||On 10 Mar 2006 12:11:48 -0800, --CELKO-- wrote:
(snip)
>If you had followed minimal netiquette and data modeling rules, would
>you have actually posted something like this?
>CREATE TABLE People
>(person_nbr INTEGER NOT NULL PRIMARY KEY,
>person_name CHAR(35) NOT NULL
>birth_date DATETIME NOT NULL, -- never store age
>skin_color CHAR(10) NOT NULL); -- color of what?
>CREATE TABLE Partners
>(person_nbr_1 INTEGER NOT NULL,
>person_nbr_2 INTEGER NOT NULL,
>CHECK (person_nbr_1 <> person_nbr_2)
>PRIMARY KEY (person_nbr_1, person_nbr_2));
>You should also have REFERENCES clauses on the Partners back to People,
>but SQL Server does not allow this without triggers.
Hi Joe,
Nonsense. Check the product specs (or better: run an actual test) before
posting.
SQL Server will happily allow the foreign key constraints between
Partners and People. What it won't allow (at least not in SQL Server
2000 - I didn't check if this got fixed in SQL Server 2005) is to create
*both* these constraints with a CASCADE option. You can give the CASCADE
option to one, though; the other can only be NO ACTION (or they can both
be NO ACTION, of course).
Hugo Kornelis, SQL Server MVP

Problem with sql statement

I'm trying to use the following statement:

IF (IDS2_TXT.FIRST_MODIFIER="HQ" THEN
(IDS2_TXT.UNITS/240))

But an error appears whn i try to run it. Basically i want sql to only divide the amount of unit of a record if it finds a value on one of the fields (an leave the rest like they are) can someone please help me on what is wrong with the statement. Thank you.

using the derived column transformation:

FIRST_MODIFIER=="HQ"?(UNITS/240):UNITS

Frank

Problem with SQL Statement

Can anyone see anything obviously wrong with this statement? I'm not really
an SQL kind of guy

SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND CategoryID
= 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title

PlatformID contains a GUID and I thought passing a wildcard would allow all
all records to be selected with the matching CategoryID

Any ideas? I'm assuming I've just formed the SQL incorrectly"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:iju_b.555$1d5.5476581@.news-text.cableinet.net...
> Can anyone see anything obviously wrong with this statement? I'm not
really
> an SQL kind of guy
> SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
CategoryID
> = 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> PlatformID contains a GUID and I thought passing a wildcard would allow
all
> all records to be selected with the matching CategoryID
> Any ideas? I'm assuming I've just formed the SQL incorrectly

The LIKE is the problem - you can look it up in Books Online - but it
doesn't seem to be necessary here anyway, as your description suggests that
the PlatformID is irrelevant:

SELECT
ProductID, Title
FROM
Products
WHERE
CategoryID = 'f5e45b55-95de-47f4-a79b-b24969178b52'
ORDER BY
Title

If this doesn't give the results you expect, then you'll need to post some
more information about the structure of the table and what your data look
like.

Simon|||Managed to Fix it simon.

I need the PlatformID as users are selecting both the platform and category
from a drop down menu.

1 platform can have many categories

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:403a7398$1_1@.news.bluewin.ch...
> "Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
> news:iju_b.555$1d5.5476581@.news-text.cableinet.net...
> > Can anyone see anything obviously wrong with this statement? I'm not
> really
> > an SQL kind of guy
> > SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
> CategoryID
> > = 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> > PlatformID contains a GUID and I thought passing a wildcard would allow
> all
> > all records to be selected with the matching CategoryID
> > Any ideas? I'm assuming I've just formed the SQL incorrectly
> The LIKE is the problem - you can look it up in Books Online - but it
> doesn't seem to be necessary here anyway, as your description suggests
that
> the PlatformID is irrelevant:
> SELECT
> ProductID, Title
> FROM
> Products
> WHERE
> CategoryID = 'f5e45b55-95de-47f4-a79b-b24969178b52'
> ORDER BY
> Title
> If this doesn't give the results you expect, then you'll need to post some
> more information about the structure of the table and what your data look
> like.
> Simon|||Andrew Banks (banksy@.nojunkblueyonder.co.uk) writes:
> Can anyone see anything obviously wrong with this statement? I'm not
> really an SQL kind of guy
> SELECT ProductID, Title FROM Products WHERE PlatformID LIKE % AND
> CategoryID
>= 'f5e45b55-95de-47f4-a79b-b24969178b52' ORDER BY Title
> PlatformID contains a GUID and I thought passing a wildcard would allow
> all all records to be selected with the matching CategoryID

First a general piece of advice: when you post a question like this, be
more specific. If there is an error message, please share it. If you
get unexpected results, please say so, and why they are unexpected.

In this particular case, the problem is that you cannot have a mod
operator between LIKE and AND. The percent is presumably meant to be
a wildcard, but in this case you need to quote it:

... LIKE '%' AND ...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Problem with SQL in ASP.NET...

Hello,

I have the following SELECT statement I am using in asp.net page:

SELECT MonthName(Month([TransEntryDate])) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY MonthName(Month([TransEntryDate]));

I get the following error:

Undefined function 'MonthName' in expression.

This used to work with classic ASP, but I'm not sure how to form this to work with ASP.NET. I am using this because my results are showing months numerically and I'd like them to be returned with the actual month name (january, february, etc.)...

Any help would be greatly appreciated!MonthName is not a SQL Server keyword, its only support in Analysis Services. Is you .net project pointing to the same database as your classic asp?|||Yes pointing to same db... I'm not sure how to get the results I'm looking for though...|||Yes...pointing to same db, but not sure how to make this work for the results I need...|||I think that what I'm looking for is an example of how I could use the CASE Expression within my SELECT statement to return the month name (not the numeric value)... PLEASE HELP!!!!|||ah now why didn't you say so ;)

SELECT DATENAME(month, getdate()) AS 'Month Name'

|||I hate to be a pain...I'm new to this... Could you show me how to use this with my SELECT statement in my first post? Thank you!!!|||

SELECT DateName(month,[TransEntryDate]) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY [TransEntryDate]

NB Simply using "Group By" doesn't guarantee a sorted order.

Problem with SELECT...FOR XML in SQL 2000

Hi,
I have a problem with a SELECT..FOR XML Statement. I would like to do
something like:
DECLARE @.DeletedXml VARCHAR(8000)
SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id = @.Id
FOR XML AUTO, ELEMENTS)
But I get this error when I try to run: Incorrect syntax near 'XML. I have
then consulted the manual, and I can see to my big surprise that this is not
supported. Is there any way I can solve this? I want to save the @.DeletedXml
variable in a table.
Thanks
HenrikYou'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
Server 2000, you'll have to save it as native SQL datatypes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem with a SELECT..FOR XML Statement. I would like to do
> something like:
> DECLARE @.DeletedXml VARCHAR(8000)
> SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id =
> @.Id FOR XML AUTO, ELEMENTS)
> But I get this error when I try to run: Incorrect syntax near 'XML. I have
> then consulted the manual, and I can see to my big surprise that this is
> not supported. Is there any way I can solve this? I want to save the
> @.DeletedXml variable in a table.
> Thanks
> Henrik
>|||it's a bit messy, but you can
1. Put the FOR XML command as a stoerd proc
2. In .NET, execute the stored proc w/ the SqlXmlCommand object
3. Store the results of the stored proc in a stream -- Dim strm As
IO.Stream = xmlCommand.ExecuteStream()
4. Write the plain-text contents of the stream to the Database|||Hi Tom,
How can I save it as native SQL types?
Thanks Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> You'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
> Server 2000, you'll have to save it as native SQL datatypes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
>|||Thank you very much for your reply. You are right this is a bit messy :-)
I unfortunately can't do it in a stored procedure because I am using the
deleted and inserted tables.
And I would like if possible to do it all in SQL.
<scottstein@.gmail.com> wrote in message
news:1134406828.872432.69030@.g14g2000cwa.googlegroups.com...
> it's a bit messy, but you can
> 1. Put the FOR XML command as a stoerd proc
> 2. In .NET, execute the stored proc w/ the SqlXmlCommand object
> 3. Store the results of the stored proc in a stream -- Dim strm As
> IO.Stream = xmlCommand.ExecuteStream()
> 4. Write the plain-text contents of the stream to the Database
>|||You'll have to save the columns of the inserted/deleted tables to the
columns of another table or you can concatenate the values into a string and
save that - as long as the total string length is <= 8000 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> How can I save it as native SQL types?
> Thanks Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>|||Hi Tom,
Again thanks.
Ok, that is of course an idea, to skip the FOR XML attribute and generate
the XML myself. I guess that is what you mean? The 8k barrier is no problem.
Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
> You'll have to save the columns of the inserted/deleted tables to the
> columns of another table or you can concatenate the values into a string
> and save that - as long as the total string length is <= 8000 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Yeah. basically, do an INSERT SELECT * FROM deleted. When you go to SQL
2005, you can then store it as XML.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%236J6x$1$FHA.264@.tk2msftngp13.phx.gbl...
> Hi Tom,
> Again thanks.
> Ok, that is of course an idea, to skip the FOR XML attribute and generate
> the XML myself. I guess that is what you mean? The 8k barrier is no
> problem.
> Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
>

Problem with select to another server.

Hello All!

This is the first time I'm working between two SQL servers. Here is a simple SELECT statement.

DECLARE @.Acct nvarchar(50)

SELECT First_Name, Last_Name, Age, DOB, Account_Number

FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

Here is the error

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "MKE01-2NX2461.Pharm Test Local.dbo.Active_Orders" could not be bound.

What am I doing wrong?

Thanks!

Rudy

MKE01-2NX2461 is set up as a Linked Server in MKE01-Demo-XX?

|||

You are attempting to compare a table to a variable.

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

It would be nice, but it isn't going to happen...

But more critically, I don't see any relationship between the two tables from the two databases. How would a row from [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders be connected to the table [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders in such a way that using the variable value against one table 'should' return a row from the other table.

I think that something isn't quite right here... (Perhaps a JOIN is missing.)

|||

Hi guys!

Ah yes. A JOIN would make sense. Mke Demo is the linked serve on MKe 29nx... So let me give that a shot. I'm sure I'll be back here with a question or two.

Thanks!

Rudy

|||

Perhaps something more like this?

Code Snippet

DECLARE @.Acct nvarchar(50)

SET @.Acct = {someValue}

SELECT
x.First_Name,
x.Last_Name,
x.Age,
x.DOB,
x.Account_Number
FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders x
JOIN [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders t
ON x.Account_Number = t.Account_Number
WHERE x.Account_Number = @.Acct

This assumes that the values you wish to return are located in the [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders table, and that both tables have the Account_Number column to link the data.

problem with SELECT statement and WHERE

i am using visual web developer 2005 and SQL server 2005 with VB as the code behind

i am using the following code to retrieve data from the database and display in textboxes and it works fine

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

now i want to display data in the text boxes that match certain criteria.i modified the code like this but it did not work

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;UserInstance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 WHERE name=peter", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

it gave the result as Invalid column name 'peter'.

but if i give as SELECT * FROM Table1 WHERE id=1result is displayed correctly

but id is the primary key whereas name is not a primary key.

please help me how to display the values corresponding to the name field

i would think your "Select * from table1 where name = peter"
should be
"Select * From Table1 where name = 'peter'"

notice the single quotes on around peter as your querying a char field

HTH,
AjaxButter

|||

Try putting single quotes around peter like this 'peter'. The SQLAdapter is interrepting the string peter as a column name and not a value. The reason it works with the id is because id is most likely a numeric type value (IE: int or decimal, etc.) so single quotes are not needed.

Dapanther

|||

AjaxButter,

Beat again by my slow typing LOL

Dapanther

|||

if peter is just a substring and i want all the rows containing peter as a substring in name field what will i do

please help me

|||

If you need peter as a substring then there are the follwoing possiblities.

1. the name is "xyz peter"

For this the querry would be Select * From table1 where name like '%peter'

2. the name is "Peter xyz"

Here the querry would be like this Select * From table1 where name like 'Peter %'

3. The name could be "abc Peter xyz"

The querry would be as follows: Select * From table1 where name like '%peter%'

It all depends upon what you require. The % is a wild string search.

Please mark this as answer if the post help you.

Problem with select statement

Hello,

If I give
select * from test where description like '%test%'
it displays me all the results that have the word test in is such as testing, tests and so on.

If I give
select * from test where description = 'test'
it displays me the result with the rows which have description as test.

I have got a paragraph for description that have got many words and I want to get the results which have test in it, i.e. the description should not only be 'test' but paragraph which have got 'test' word in it and not testing and tests.

How can I do so.

Please reply as soon as possible.

Thanks in advance,
Uday.select * from test where description like '% test %'

This will not catch test if the text is like so:

'This is a test.'
'This is a test, that is not.'

and so to do what you are looking to do, you would really need to do a more complete parsing of the entry. A naive attempt at this would be:

select * from test where description like '% test[. ,;]%'

Even this will miss:

'Test, one two three'|||Hi,

It seems the above one is working but what exactly are we doing by giving [. ,;] and why "Test, one two three" will not work.

Thanks in advance,
Uday.|||[. ,;] is a regular expression meaning that the character after the last t in test must be a space, comma, period or semicolon.

As written, the Like clause expects a space in front of the word Test. Without that space, it would not match. You could do an additional test for LIKE '% Test[. ,;]%'. Again, even that would miss something like '(this is a test)'|||Hi,

Thanks for you help.

Best Regards,
Uday.

Problem with select every TOP 1 records from different group in a

Hi Expert,
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:

> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam

problem with select and int statement

hey the following code doesnt worki it comes up with an error Specified cast is not valid. pointing at the exceutescalar

any ideas?

cheers

string maxquery = "Select Max(activity_order) from roomactivitylk where room_code = 'v1106'";

//int max = 0;
int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteScalar();

++max;
max++;
max = max + 1;
tbtest1.Text = max.ToString();

I'm not shure, but AFAIK aggregate functions (like MAX()) usually requires GROUP BY clause.

Try modify line with error such way.

max = cmd15.ExecuteScalar();

And look what woul be max equal to in debugger.

Looks like your query returns result which simply can't be parsed to integer.

|||

Your query is OK. Run the query in SQL directly, make sure it return integer. If not integer, you need to set it as 0 value.

|||

Be sure to check to see what happens if it returns NULL as well. I don't believe your code would work if that were the case.

Problem with SELECT and INSERT T-sql statement

hello everybody

I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue:

We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems.

I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10:

select * from cweb.DBNAME.dbo.TableNAME

Or

insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c10.DBNAME.dbo.TableNAME

All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements.

So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails

C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this:

insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2

It fails with the following 2 error messages:

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID.

I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid.

So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server

So in summary the problem is this:

1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange)

2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I have been searching thru google and forums but havent found any solutions yet.

Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_?

thanks a lot

Helkyn

This is a Transact-SQL question, not an SSIS question. Moving there...

problem with select * from [remote server] in QA

In Query Analyzer connected to my local server using Windows authenticatio I
tried to run this select statement against my remote server:
select * from [11.22.333.444].remoteDB.dbo.tbl1
I got this error:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
I can open a new connection in QA using an ID and password to this remote
server (which is not the same userID and password for my local server), and
then I can successfully run the select statement above, but I also need to
run statements against my local tables. So If I log into the remote server
and then try to reach my local server like this:
select * from myLocalServer.myLocalDB.dbo.myTable
--now I get the error message that :
"Server 'myLocalServer' is not configured for DATA ACCESS."
I believe the remote server IS configured for data access. So my question
is if it is possible to append the userID and password to the select
statement from the local connection?
select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
or is there some way to supply the credentials along with the select
statement? Any suggestions appreciated.
Thanks,
RichRich,
I don't know exactly what you meant when you said 'remote server', but
you can only exec stored procedures but not query on a remote server,
use linked server instead.

>From BOL:
========================================
==========
If you want to set up a server configuration in order to execute stored
procedures on another server and do not have existing remote server
configurations, use linked servers instead of remote servers. Both
stored procedures and distributed queries are allowed against linked
servers; however, only stored procedures are allowed against remote
servers.
Note Support for remote servers is provided for backward compatibility
only. New applications that must execute stored procedures against
remote instances of SQL Server should use linked servers instead.
========================================
===================
Example,
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
You can define your remote password/login using sp_addlinkedsrvlogin on
the linked server.
Mel|||Rich
Have you created linked server?
exec sp_serveroption 'servername','data access','true'
go
select * from servername.database.dbo.tablename where ...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D3D8127B-DE34-4598-9A11-88AE2FF24F3A@.microsoft.com...
> In Query Analyzer connected to my local server using Windows authenticatio
> I
> tried to run this select statement against my remote server:
> select * from [11.22.333.444].remoteDB.dbo.tbl1
> I got this error:
> "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> I can open a new connection in QA using an ID and password to this remote
> server (which is not the same userID and password for my local server),
> and
> then I can successfully run the select statement above, but I also need to
> run statements against my local tables. So If I log into the remote
> server
> and then try to reach my local server like this:
> select * from myLocalServer.myLocalDB.dbo.myTable
> --now I get the error message that :
> "Server 'myLocalServer' is not configured for DATA ACCESS."
> I believe the remote server IS configured for data access. So my question
> is if it is possible to append the userID and password to the select
> statement from the local connection?
> select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
> or is there some way to supply the credentials along with the select
> statement? Any suggestions appreciated.
> Thanks,
> Rich
>

Problem with SELECT

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).

SELECT s_supplier_code,MAX(s_supplier_name) FROM suppliers GROUP BY
s_supplier_code;

My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?

Thanks

Ted>My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

I think I feel a headache coming on!

The publisher is Microsoft Press. It is for SQL Server 2005 and I AM
RUNNING SQL Server 2005! You'd think they'd run sample code from their
own book against their own SQL Server.

Cheers,

Ted

Someone pass me the super strength Motrin! :-(|||* Ted wrote, On 21.08.2006 17:28:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
>
However, it generates the following error.
>
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

What happens if you replace
select top(1) *
with this?
select top(1) some_column_name

Boa
[snip]|||Ted wrote:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
>

Ted,

The example in Listing 7-1 is different from your query, and the
example works (and it makes perfect sense to me). I am not sure I
understand what you were trying to accomplish. Note that:
1. Listing 7-1 uses SELECT TOP(1) OrderId, as opposed to your top(1) *
2. Listing 7-1 uses different columns to filter the result set
(OrderId) and to correlate the subquery (EmployeeId). Using one and the
same solumn s_supplier_code in both cases makes little sense to me.

Good luck!|||boa wrote:

Quote:

Originally Posted by

* Ted wrote, On 21.08.2006 17:28:

Quote:

Originally Posted by

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

>
>
What happens if you replace
select top(1) *
with this?
select top(1) some_column_name
>

Nothing changes. The same error is generated.

Thanks

Ted|||Since this is MS-only

Quote:

Originally Posted by

syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

Joe,

Oracle and DB2 also provide proprietary features for this purpose, and
those features are also widely used. Looks like something is missing in
the standard, right?|||You have incorrectly reproduced the query from Itziks book (just checked
myself)

Check back again, you will see that you have put * (all columns) you should
have used a single column key in Itziks example it was OrderId.

SELECT s_supplier_code, s_supplier_name
FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) OrderId FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Ted" <r.ted.byers@.rogers.comwrote in message
news:1156177703.807874.29310@.b28g2000cwb.googlegro ups.com...

Quote:

Originally Posted by

>I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;
However, it generates the following error.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.
This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).
SELECT s_supplier_code,MAX(s_supplier_name) FROM suppliers GROUP BY
s_supplier_code;
My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?
Thanks
Ted
>

|||This is just how God punishes people for using proprietary code :)

Quote:

Originally Posted by

Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>

As ever you are talking utter rubbish.

It takes 2 or 3 or sometimes more releases to deprecate and then discontinue
a feature, actually - thats better than the standard SQL cycle where you
only get 4 year was it celko? And you don't get any warning there either
unless you subscribe and pay the fees....

Quote:

Originally Posted by

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

So, just how would you using standard SQL do the equivelant of this without
using a 'scratch tape' or relying on the application to dig you out of the
featureless ansi sql standard?

-- Get last 3 most recent sales...
select ...
from sales
where orderid in (
select top 3 orderid
from sales
order by orderdate desc
)

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1156180035.849964.264800@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>My code can continue on, but I want to understand why the statement I
>>constructed by following the example in my book was rejected. Did I
>>miss something? Or is there an error in the book? Or is there a bug in
>>SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

|||Thanks Tony and Alexander

Thanks to your posts, I now understand what I missed and I now have it
working.

Thanks

Ted

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

as

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.

Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

Problem with query, involving INNER HASH JOIN

Hi all,
I've got 2 problems with the following statement, which leads me to
believe there is a bug somewhere.
This weekend we upgraded from SQL7 to SQL2000 (SP3) and it all went
smoothly except for this statement below 'losing' a couple of records.
It is contained in a stored procedure (believe me you don't want to
see it) and gives me 2 problems.
1. It seems depending on the parameters passed which affect the volume
of records being processed all the records either come through or not.
Now I KNOW the record is in ALWAYS in #TEMP1 (don't question it is as
I'm sure) as I debug it just before the insert. When I change the temp
table to a permanent table and run the raw SQL through QA using this
permanent table it always gets inserted and never loses the record,
hence I know it's not the join leading to the record not coming
through
2. Now the strange thing is if I add 'INNER HASH JOIN' to the 2 joins
then the record is lost but 8 records do come through, but if I just
make it a 'JOIN' then absolutely nothing comes through. Why is this?
How come removing the 'INNER HASH' reduce the number of records?
I know this sounds a bit of a ramble but I've spent several hours
investigating this and still have no idea. What I was wondering is has
anyone had any problems when upgrading with joins/temp tables/stored
procs somehow not selecting all the records expected?
SQL:
INSERT INTO #TEMP2(Day, Year, DataSource, StartDate,
MaturityDate, BusinessDayDate, DealID, TradeNum, Flowsign, Currency,
CurrencyUnit, ReportingCurrency,InstrumentType, ChangeType, FXRate,
SetNumber)
SELECT DISTINCT
@.StartDate,
DATEPART(Year,@.BusinessDayDate),
TRB.DataSource,
@.StartDate,
TP.MaturityDate,
TRB.BusinessDate,
TRB.DealNo,
TRB.TradeNo,
TRB.Flowsign,
TP.CurrencyCode,
TRB.CurrencyUnit,
@.RepCurrency,
TRB.Instrument,
TP.ChangeType,
ER.Rate,
1
FROM TCH_gt_TradeProfile TP
-- remove 'INNER HASH' join from here
JOIN #TEMP1 TRB ON TRB.DealNo = TP.DealID
AND TRB.TradeNo = TP.TradeNum
AND TRB.Currency = TP.CurrencyCode
-- remove 'INNER HASH' join from here
JOIN TCH_t_ExchangeRate ER ON TRB.Currency = ER.CcyCode
AND ER.ValidFrom <= @.BusinessDayDate
AND ER.ValidTo >= @.BusinessDayDate
WHERE @.BusinessDayDate > TP.BusinessDayValidFrom
AND @.BusinessDayDate <= TP.BusinessDayValidTo
AND (TP.ChangeType = @.ChangeType OR @.ChangeType = 'All')Please post DDL and data that demonstrates the problem. If you have a
parallel plan, try adding the MAXDOP 1 optimizer hint to see if that
fixes the problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Rob Jones" <rob.jones@.barclays.co.uk> wrote in message
news:97dbfc5b.0308180057.2bbe0bea@.posting.google.com...
> Hi all,
> I've got 2 problems with the following statement, which leads me to
> believe there is a bug somewhere.
> This weekend we upgraded from SQL7 to SQL2000 (SP3) and it all went
> smoothly except for this statement below 'losing' a couple of records.
> It is contained in a stored procedure (believe me you don't want to
> see it) and gives me 2 problems.
> 1. It seems depending on the parameters passed which affect the volume
> of records being processed all the records either come through or not.
> Now I KNOW the record is in ALWAYS in #TEMP1 (don't question it is as
> I'm sure) as I debug it just before the insert. When I change the temp
> table to a permanent table and run the raw SQL through QA using this
> permanent table it always gets inserted and never loses the record,
> hence I know it's not the join leading to the record not coming
> through
> 2. Now the strange thing is if I add 'INNER HASH JOIN' to the 2 joins
> then the record is lost but 8 records do come through, but if I just
> make it a 'JOIN' then absolutely nothing comes through. Why is this?
> How come removing the 'INNER HASH' reduce the number of records?
> I know this sounds a bit of a ramble but I've spent several hours
> investigating this and still have no idea. What I was wondering is has
> anyone had any problems when upgrading with joins/temp tables/stored
> procs somehow not selecting all the records expected?
>
> SQL:
> INSERT INTO #TEMP2(Day, Year, DataSource, StartDate,
> MaturityDate, BusinessDayDate, DealID, TradeNum, Flowsign, Currency,
> CurrencyUnit, ReportingCurrency,InstrumentType, ChangeType, FXRate,
> SetNumber)
> SELECT DISTINCT
> @.StartDate,
> DATEPART(Year,@.BusinessDayDate),
> TRB.DataSource,
> @.StartDate,
> TP.MaturityDate,
> TRB.BusinessDate,
> TRB.DealNo,
> TRB.TradeNo,
> TRB.Flowsign,
> TP.CurrencyCode,
> TRB.CurrencyUnit,
> @.RepCurrency,
> TRB.Instrument,
> TP.ChangeType,
> ER.Rate,
> 1
> FROM TCH_gt_TradeProfile TP
> -- remove 'INNER HASH' join from here
> JOIN #TEMP1 TRB ON TRB.DealNo = TP.DealID
> AND TRB.TradeNo = TP.TradeNum
> AND TRB.Currency = TP.CurrencyCode
> -- remove 'INNER HASH' join from here
> JOIN TCH_t_ExchangeRate ER ON TRB.Currency = ER.CcyCode
> AND ER.ValidFrom <=> @.BusinessDayDate
> AND ER.ValidTo >=> @.BusinessDayDate
> WHERE @.BusinessDayDate > TP.BusinessDayValidFrom
> AND @.BusinessDayDate <= TP.BusinessDayValidTo
> AND (TP.ChangeType = @.ChangeType OR @.ChangeType => 'All')

Problem with Precedence Expression

Hello,

I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@.[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @.[User::RowCountStatus] = True

The other evaluates this way; @.[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

cdun2 wrote:

Hello,

I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@.[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @.[User::RowCountStatus] = True

The other evaluates this way; @.[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

You should use the watch window to test if the variable truly contains the value that you think it does.

This should help:

Using variables to store expressions

(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/SSIS_3A00_-Using-variables-to-store-expressions.aspx)

-Jamie

|||

Thank you!

cdun2

|||Don't know for certain, but shouldn't it be @.[User::RowCountStatus] == False (NOTE: double equal sign for comparison, single equal for assignment). Seems like it might hit the first test {can rowcountstatus = true, yes} and stops there. I very easily could be wrong though.|||

EWisdahl wrote:

Don't know for certain, but shouldn't it be @.[User::RowCountStatus] == False (NOTE: double equal sign for comparison, single equal for assignment).

That was it! Thanks!

Problem with parameterized SELECT statement

I'm trying to use a parameterized SELECT statement, but I must not have it right - the code below gives this compile error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@.UserID".

string

strUserID = (string)Session["UserID"];string strSelectRatings ="SELECT [CommentID], [GameID], [UserID], [Rating], LEFT(Comment,40) as Comment FROM [Comments] WHERE [UserID] = @.UserID";SqlConnection myConnection =newSqlConnection("...");SqlCommand myCommand =newSqlCommand(strSelectRatings, myConnection);

myCommand.Parameters.Add(

"@.UserID", strUserID);

MySqlDataSource.SelectCommand = strSelectRatings;

GridView1.DataBind();

Add the parameters to your SqlDataSource object

MySqlDataSource.SelectParameters.Add("@.UserID", strUserID);