Collation Conflict (DHL)

Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.

Here is the SQL code:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT

FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated

LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID

WHERE vINID.BLIV_ID = '071600007594'

Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql

Code Sample for SSIS Script Component

Greetings,

I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.

First, are there any books you recommend for learning ? (I'm not even sure what I need to learn: .NET? ADO.NET?)

And as a follow-up, any good websites that provide good reference documentation?

And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.

Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.

Any help you can provide would be much appreciated.

Rob

1.
Which kind of book do you need ? T-SQL or SQL-CLR integration ?
For T-SQL I suggest you to buy a good basic book from amazon (that ha a good rating) just to learn the basics and then the buy the following two monuments by Itzik Ben-gan (MS Press)
INSIDE SQL SERVER 2005: T-SQL QUERYING
INSIDE SQL SERVER 2005: T-SQL PROGRAMMING
They are really hard... I don't know anything better.
For the second I suggest PRO SQL SERVER 2005 ASSEMBLY by Dewson (Apress)

2.
http://www.sql-server-performance.com
http://www.sqlservercentral.com
Google...

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

__

? www.carlop.com × carlop-dev.blogspot.com

|||

carlop wrote:

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

carlop - thank you for the book suggestions. I'll check them out.

Here is a sample of data. I am after capturing each row when the STATUS changes (highlighted in yellow). A total of six rows: one each of Not Ready, Hold and Complete and three of Active. All of the other rows will be discarded.

I'm thinking this is best done with a CLR procedure, however, I'm open to suggestions.

Rob

AUDIT_ID OBJECTID MODIFIED_DATE STATUS 2753029 124248079 4/26/2007 11:30 Active 2753035 124248079 4/26/2007 11:30 Active 2753076 124248079 4/26/2007 11:33 Active 2754823 124248079 4/26/2007 14:31 Not Ready 2794102 124248079 4/27/2007 7:29 Not Ready 2817836 124248079 4/27/2007 17:06 Active 2817859 124248079 4/27/2007 17:09 Active 2818286 124248079 4/27/2007 18:00 Hold 2818473 124248079 4/27/2007 18:38 Active 2818483 124248079 4/27/2007 18:44 Active 2989403 124248079 5/1/2007 7:14 Active 3022521 124248079 5/1/2007 7:19 Active 3042121 124248079 5/1/2007 10:45 Complete 3082880 124248079 5/2/2007 7:07 Complete

Code Question - Making a field Null

Hi,
I need to change some code and make a field always show the value NULL
moving forward. I need to keep the field there becuase at the end
result the value will be imported another way.
Currently, the field is coded like this: ISNULL(email_addr,'')
How can I change this to always just be NULL?
Thank you,
RayYou can update the column value to be NULL:
UPDATE MyTable
SET email_addr = NULL;
Then you simply select the column without using ISNULL:
SELECT email_addr
FROM MyTable;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Clustering/Failover

We're looking at moving to the newest version of SQL when it's released. In
order to take advantage of the clustering capabilities of SQL, do we also
have to cluster at the OS level, or can you have a SQL cluster running on tw
o
stand-alone Windows Servers?
Thanks!Molnir,
Might take a look at:
http://www.databasejournal.com/feat...cle.php/3444181
and
High Availability and Failover Clustering at:
[url]http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx[/ur
l]
HTH
Jerry
"Molnir" <Molnir@.discussions.microsoft.com> wrote in message
news:B171CF4B-BC63-46D9-88DF-D8F855581A8B@.microsoft.com...
> We're looking at moving to the newest version of SQL when it's released.
> In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on
> two
> stand-alone Windows Servers?
> Thanks!|||You might save yourself some frustration and also look at Neverfail
(www.neverfialgroup.com)
Jo
Molnir wrote:
> We're looking at moving to the newest version of SQL when it's released. I
n
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on
two
> stand-alone Windows Servers?
> Thanks!

Clustering/Failover

We're looking at moving to the newest version of SQL when it's released. In
order to take advantage of the clustering capabilities of SQL, do we also
have to cluster at the OS level, or can you have a SQL cluster running on two
stand-alone Windows Servers?
Thanks!Molnir,
Might take a look at:
http://www.databasejournal.com/features/mssql/article.php/3444181
and
High Availability and Failover Clustering at:
http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx
HTH
Jerry
"Molnir" <Molnir@.discussions.microsoft.com> wrote in message
news:B171CF4B-BC63-46D9-88DF-D8F855581A8B@.microsoft.com...
> We're looking at moving to the newest version of SQL when it's released.
> In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on
> two
> stand-alone Windows Servers?
> Thanks!|||You might save yourself some frustration and also look at Neverfail
(www.neverfialgroup.com)
Jo
Molnir wrote:
> We're looking at moving to the newest version of SQL when it's released. In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on two
> stand-alone Windows Servers?
> Thanks!

Clustering/Failover

We're looking at moving to the newest version of SQL when it's released. In
order to take advantage of the clustering capabilities of SQL, do we also
have to cluster at the OS level, or can you have a SQL cluster running on two
stand-alone Windows Servers?
Thanks!
Molnir,
Might take a look at:
http://www.databasejournal.com/featu...le.php/3444181
and
High Availability and Failover Clustering at:
http://www.microsoft.com/technet/pro...ilability.mspx
HTH
Jerry
"Molnir" <Molnir@.discussions.microsoft.com> wrote in message
news:B171CF4B-BC63-46D9-88DF-D8F855581A8B@.microsoft.com...
> We're looking at moving to the newest version of SQL when it's released.
> In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on
> two
> stand-alone Windows Servers?
> Thanks!
|||You might save yourself some frustration and also look at Neverfail
(www.neverfialgroup.com)
Jo
Molnir wrote:
> We're looking at moving to the newest version of SQL when it's released. In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on two
> stand-alone Windows Servers?
> Thanks!