Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS
"Hongyu Sun" wrote:
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Friday, March 23, 2012
help on insert a record on sql server with identity column as key
Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql
Help on global variable for insert statement
I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.
I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?
My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.
My question is how do I format the following line of code to do what I need?
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
I think it is something like this, but I am Really not sure and some help would be greatly appretiated:
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"
I am really not sure.
Here is all the code:
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing
End Sub
This sentence is confusing: "I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls."
Do you mean that the table fields do not accept NULL values?
If I recall correctly, a textbox cannot contain a NULL value. At the minimum, it contains an 'empty string'.
In your code sniplet, you are inserting the value '1rowst' in the field [Test1], etc. There is no ambiguity about NULL values.
Perhaps I am missing something. Could you please expand upon your request so in order to clear up the confusion?
|||I am sorry, lack of english.
My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.
My question is how do I format the following line of code do do what I need?
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
I think it is something like this, but I am Really not sure and some help would be greatly appretiated:
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"
I am really not sure.
Thanks
|||I'm still confused. Please help me clarify.
You wish to put all the contents of three textboxes into a single field in the table?
"Textbox1, 2, 3 Needs to insert values into the first column"
If that is the situation, why not just have one textbox -not three?
|||No, sorry Arnie,
Textbox 1,4,7 go in column 1
TextBox 2, 5, 8 Go in column 2
TextBox 3, 6, 9 Go in column 3
I need my application to insert the values into the Database. If there is Nulls in the textbox go to the next record.
I think the code should go something like this:
If Textbox2 = '' then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"
Elseif Textbox3 = '' then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox1.Text "', '" Textbox4.Text "', '" Textbox7.Text "')"
And
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text "', '" Textbox5.Text "', '" Textbox8.Text"')"
As you can see I am throwing a rock into the grass, because I am not % 100 sure if this is correct.
Thanks for the patience Arnie
|||If I am understanding you correctly, it appears that you wish to load textbox values into the table depending upon the status of other textbox values.
Your use of the IF...ELSEIF structure may work just fine for your intentions. Something like this might work.
IF Textbox1.Text = '' Then
IF Textbox2.Text = '' Then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox3.Text + "', '" + Textbox6.Text + "', '" + Textbox9.Text + "')"
ELSE
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" Textbox2.Text + "', '" + Textbox5.Text + "', '" + Textbox8.Text + "')"
END IF
ELSE
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" + Textbox1.Text + "', '" + Textbox4.Text + "', '" + Textbox7.Text + "')"
END IF
|||That was exactly what I needed. Thanks Arnie|||Of course you should be careful with this method since this is completely vulnerable to SQL injection attacks.
Just a friendly reminder.|||
Jon,
Thank you very much for that. I was not familiar with that at all. I just want to make sure. This is a big issue with web applications (wich mine is not). Is that correct?
Thanks
|||Yes it is, but it's completely possible from within windows forms applications as well. So it's definitely something to watch out for.Wednesday, March 21, 2012
help on alias...
Dear friends and guru:
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyA) This is the wrong group for this request, it is the Reporting Services
group.
B) Don't use aliases, they don't work on inserts. Since Insert only inserts
into a single table, the tablename prefix is totally unneccesary.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:C3EFCB25-67CF-4FED-BFE4-D9A7EB7989A4@.microsoft.com...
> Dear friends and guru:
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
>
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyA) This is the wrong group for this request, it is the Reporting Services
group.
B) Don't use aliases, they don't work on inserts. Since Insert only inserts
into a single table, the tablename prefix is totally unneccesary.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:C3EFCB25-67CF-4FED-BFE4-D9A7EB7989A4@.microsoft.com...
> Dear friends and guru:
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
>
Monday, March 19, 2012
Help needed with trigger
Hi All,
I have this trigger wich runs good!
CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.
Does someone have an Idea?
Cheers WimLOC_Street = case when insrted.PostalCode is Null or
inserted.DoorNumber is Null then '' else GEO_Postcode.STraatID end
Change your UPDATE Statment to include this. Do something similar with Loc_City.
I have this trigger wich runs good!
CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.
Does someone have an Idea?
Cheers WimLOC_Street = case when insrted.PostalCode is Null or
inserted.DoorNumber is Null then '' else GEO_Postcode.STraatID end
Change your UPDATE Statment to include this. Do something similar with Loc_City.
Help needed with insert Statement
Hi,
I am trying to insert the follows rows to my production database... and this the sample data
RowPlan PART_ID FUND_ID TOT_ACT1 TOT_ACT2 Number Num1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79 2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27 3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00 4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59 5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79 6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48 7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16 8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80 9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94
So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows
1INSERT INTO Statements..ParticipantPlanFundBalances12(3PlanId,4ParticipantId,5PeriodId,6FundId,7 Loans,8--PortfolioId,9Act1,10TotAct1,11Act2,12TotAct2,13Act3,14TotAct3,15Act4,16TotAct4,17Act5,18TotAct5,19Act6,20TotAct6,21Act7,22TotAct7,23Act8,24TotAct8,25Act9,26TotAct9,27Act10,28TotAct10,29Act11,30TotAct11,31Act12,32TotAct12,33Act13,34TotAct13,35Act14,36TotAct14,37Act15,38TotAct15,39Act16,40TotAct16,41Act17,42TotAct17,43Act18,44TotAct18,45Act19,46TotAct19,47Act20,48TotAct20,49OpeningUnits,50OPricePerUnit,51ClosingUnits,52CPricePerUnit,53AllocationPercent54)55SELECT56cp.PlanId,57p.ParticipantId,58@.PeriodId,59CaseWhen a.FUND_ID <>'LOAN'Then f.FundIdELSE 0END,60CASEWhen a.FUND_ID ='LOAN'Then'LOAN'END as Loanfunds,61--planinfo.PortfolioId,62CaseWHEN a.ACT_ID1 ='BB'Then 1END,63a.TOT_ACT1,64CaseWHEN a.ACT_ID2 ='CT'Then 2END,65a.TOT_ACT2,66CASEWhen a.ACT_ID3 ='DV'then 3END,67a.TOT_ACT3,68CASEWhen a.ACT_ID4 ='GL'Then 4End,69a.TOT_ACT4,70CAseWhen a.ACT_ID5 ='TF'THEN 5END,71 a.TOT_ACT5,72CASEWhen a.ACT_ID6 ='WD'THEN 6END,73a.TOT_ACT6,74CASEWHEN a.ACT_ID7 ='OT'THEN 7END,75a.TOT_ACT7,76CASEWhen a.ACT_ID8 ='EB'THEN 8END,77a.TOT_ACT8,78a.ACT_ID9,79a.TOT_ACT9,80a.ACT_ID10,81a.TOT_ACT10,82a.ACT_ID11,83a.TOT_ACT11,84a.ACT_ID12,85a.TOT_ACT12,86a.ACT_ID13,87a.TOT_ACT13,88a.ACT_ID14,89a.TOT_ACT14,90a.ACT_ID15,91a.TOT_ACT15,92a.ACT_ID16,93a.TOT_ACT16,94a.ACT_ID17,95a.TOT_ACT17,96a.ACT_ID18,97a.TOT_ACT18,98a.ACT_ID19,99a.TOT_ACT19,100a.ACT_ID20,101a.TOT_ACT20,102a.UNIT_OP,103a.PRICE_OP,104a.UNIT_CL,105a.PRICE_CL,106IsNull(i.ALLOC_PER1,'0.00')107FROM108ASDBF a109110--Derive the unique Plan Id111INNERJOIN Statements..ClientPlan cp112ONa.PLAN_NUM = cp.ClientPlanId113AND114cp.ClientId = @.ClientId115--Derive the unique ParticipantId from the Participant table116INNERJOIN Statements..Participant p117ONa.PART_ID = p.PartId118-- Derive the unique fund id from the Fund Table119INNERJOIN Statements..Fund f120ONa.FUND_ID = f.Cusip121OR122a.FUND_ID = f.Ticker123OR124a.FUND_ID = f.ClientFundId125LeftOuter JOIN INVSRC i126ONa.FUND_ID = i.INV_ID127AND128a.PLAN_NUM = i.Plan_Number129AND130a.PART_ID = i.PART_ID131--Get the unique portfolio name ffor the PArticipant Funds..132WHERE133--Ignore rows that failed the scrub.134a.Import = 1135AND136--Import only those that are not already in the ParticipantPlanFundBalances table137NOT EXISTS (138SELECT *139FROM140Statements..ParticipantPlanFundBalances1 pfb141WHERE142pfb.PlanId = cp.PlanId143AND144pfb.ParticipantId = p.ParticipantId145AND146pfb.PeriodId = @.PeriodId147AND148pfb.FundId = f.FundId149)
any help is appreciated.
Regards
Karen
Is there any error msg? Also can you explain this:
INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
Thanks for your answer, no i am not getting any error message
INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
and this one means... i am getting the fundId from that table and inserting it to the PlanFundbalances..
for example in the sample data i have provided.. FUND_ID can be a 5 letter word saying DODGX,(Ticker) or some alphanumeric data whose length is and ClientFund(what ever the client wants and not in our database)
So fUND_ID 19765P471 will have a fundId of 15 or whatever..
But the word Loan isnt there in the Statements..Fund f table
Hope this helps.
Regards
Karen
|||The way to debug would be to selectively comment out lines.. comment out the INSERT INTO line and just run the SELECT part. Start with the first ASDBF JOIN with ClientPlan and see if you get results. Then include the join with Participant and see if you get expected results..keep including each of the tables and see which part of the query is throwing you off. Otherwise there's really no way for us to tell what the issue is.. unless we see some sample data from each of the tables in the query and expected data into the final table...
|||This is the first 10 rows of my final table..
1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.009178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0010178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00
Suppose if PartID 18752 had loans i want the information to be displayed like in Line number 9
1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.00917824 18752 7 0 LOAN other columns10178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0011178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00
I will try debugging the sproc and see what i can acheive
Regards,
Karen
|||ndinikar hit it spot on. Are there any records in the ASDBF table that have a FUND_ID ='LOAN'?
If not, that's your problem.
If yes, then one of the joins you have is filtering them out.
|||Yes i do have 18 rows of Data where FUND_ID = 'LOAN'
|||after debugging it...
When i include this Join
JOIN Statements..Fund fON a.FUND_ID= f.Cusip
OR
a.FUND_ID= f.TickerOR
a.FUND_ID= f.ClientFundId
i am getting a problem and i solved it by giving
LeftOuterJOIN Statements..Fund fON a.FUND_ID= f.Cusip
OR
a.FUND_ID= f.TickerOR
a.FUND_ID= f.ClientFundId
Thanks a lot...
Regards
Karen
Help needed with an update trigger
Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Monday, March 12, 2012
Help needed on select and insert query
a bit of a begginer's request here.
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,
> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,
> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B
Friday, March 9, 2012
Help needed for Transaction Support in SQL server 2005
Hi,
and if i remove the Transaction, My SaveOrder method save the Master table entry while some time I am getting error while saving order details.|||
I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.
my data layer methods somewhat looks like
public void Save(order value)
{
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
int orderId = SaveOrderMaster(value);
value.OrderId = orderid;
int childId = SaveOrderDetails(value);
//complete the transaction
transactionScope.Complete();
}
}
here
1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.
2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".
My Problem:
Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.
The problem may occur if your code calls the second procedure before the first procedure is done (or before the row has been added to the parent table). This can happen, because in your code you have not ensured the second procedure to call after the first procedure is completed. So either you can implement this logic in your code, or you can make use of trigger functionality in SQL. For example you can use an INSERT trigger on the parent table to automatically do the same thing as your second procedure did:
CREATE TRIGGER trg_InsOrdDetails ON Orders FOR INSERT
AS
--do your insert here, you can also call the second procedure
go
For more information about SQL trigger, you can refer to this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp
|||JayBut those calls are from Businss Layer Dll which written in .NET. So when SaveOrderMaster(value) call returns it should update the record in database. because SaveOrderMaster(value) calls one stored procedure that returns the id.
so call is flowing in the following order:
UI calls BLL.Save(value) -->
BLL calls DAL.SaveOrderMaster(value) -->
DAL calls Stored Procedure and set the newly created id in value object.
BLL Calls DAL.SaveOrderDetails(values) -->
DAL calls stored procedure with the details and Id (created in SaveOrderMaster call).
and I am getting the foreignKey error while making call to SaveOrderDetails().
But I am not able to find the source of error. as this is not repeatative behaviour, some time it ouccurs after 5000 BLL.Save() calls.
I also cant use triggers here, as order detail data is huge. and i dont want to call my pass so many data to stored procedure in one go. (aka. business and design requirement -:) )
any help in this regard is highly appriciated?|||
When you have the error, what's the orderId in the parent table, and does it look like what you'd expect? Also, are you using identity(), or Scope_identity to get the key? If you're not using scope_identity(), then that could be your problem.
|||I am using the Scope_identity to retrive the value for the Order Table identity column.
and if i remove the Transaction, My SaveOrder method save the Master table entry while some time I am getting error while saving order details.|||
The TransactionScope class performs none atomic transactions which is not legal so pass your code to T-SQL transaction block and your problem will go away. Hope this helps.
http://www.codeproject.com/database/sqlservertransactions.asp
http://msdn2.microsoft.com/en-us/library/ms190295.aspx
Help needed for Sql2005 Transaction Logs
Hi,
I have run a lot of insert/ delete, update queries on a database in sql 2005 for a couple of months
Is there a way to track when and what are sql transactions that are have been executed?
Thanks in advance for your time and help.
whitze
You can monitor the activity in your database by running sql profiler........but this can be performed when you want to identify the bottlenecks in your db or in your server.........but it is not advisable to run it always....if you have the trace which is captured when those DML's were performed you can track it .......else i dont think its possible..........im not too sure about it......|||If you have a complete trail of the Transaction Logs, you could use one of the several third party log tools to accomplish your task.
If you do not have a complete trail of Transaction Logs, then most likely, you will not be able get that information.
Wednesday, March 7, 2012
Help needed
Hi,
I have program to make invoice called "primavera" that uses crystal reports.
I need to insert the logo in the report.
I have downloaded a free trial of Cristal report 11.
When I open the file it gives the message:" The saved data will be discarded since one or more formulas failed to compile"
I insert the logo in the file but the "primavera" doesn't assume the changes.
Can someone help me please?
Thank you.I already encounter this problem. You used a formula in a section expert or anywhere in the report but you delete the formula without delete it first in section expert or anywhere in the report.
I have program to make invoice called "primavera" that uses crystal reports.
I need to insert the logo in the report.
I have downloaded a free trial of Cristal report 11.
When I open the file it gives the message:" The saved data will be discarded since one or more formulas failed to compile"
I insert the logo in the file but the "primavera" doesn't assume the changes.
Can someone help me please?
Thank you.I already encounter this problem. You used a formula in a section expert or anywhere in the report but you delete the formula without delete it first in section expert or anywhere in the report.
Help needed
Hi all,
I am trying to write a stored procedure, which has two insert statements.
the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.
Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?
thanks for your help!
Hello rasesh_dave,
Yes, you can do this with one stored procedure.
The newly inserted primary key value can be retrieved with @.@.Identity.
|||Ofcourse, just put the two insert statements into one proc.
|||yup, but how do i pass a list / array of items to be inserted into the second table?
|||Post the code you currently have so we can see what you are doing..
|||Okay,
I have two stored procs. The first one is
CREATE PROCEDURE [dbo].[sp_EventNew_Insert]
@.EventName VARCHAR(50),
@.StartDate DATETIME,
@.EndDate DATETIME,
@.Organiser VARCHAR(50),
@.Telephone VARCHAR(15),
@.Fax VARCHAR(15),
@.Email VARCHAR(50),
@.EventURL VARCHAR(50),
@.PendingVerification BIT,
@.SubmitersName VARCHAR(50),
@.SubmitersEmail VARCHAR(50),
@.EventVenue VARCHAR(50),
@.EventCity VARCHAR(50),
@.EventState VARCHAR(50),
@.CountryID VARCHAR(50),
@.SubmiterName VARCHAR(50),
@.SubmiterEmail VARCHAR(50)
AS
INSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,
Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)
VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax, @.Email, @.EventURL, @.EventURL, @.PendingVerification,
@.SubmiterName, @.SubmiterEmail)
SELECT @.@.Identity as EventID
GO
which returns the EventID which is received by the DataAdapter class, and
then executes a loop for say 10 SiteID and calls another stored procedure for each siteID which is
CREATE PROCEDURE [dbo].[sp_EventSitesNew_Insert]
@.EventID INT,
@.SiteID INT,
@.LiveOnWeb BIT
AS
INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)
VALUES (@.EventID, @.SiteID, @.LiveOnWeb)
GO
is there a way i can pass an array of siteID into the sp, and execute a loop without generating a loop at the Data Adapter?
Here's how I would modify your proc
(1) DO NOT name your procs with "sp_..". sp_ means system proc and SQL Server looks for the proc under master db thinking its a system proc and if it doesnt find there, it will look under the db you are running queries from. So this is an unnecessary overhead.
(2) Create a function that takes a concatenated string and returns a table of parsed values. Refer:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx
(3) Finally modify the proc as follows:
CREATE PROCEDURE [dbo].[SP_EVENTNEW_INSERT]@.EventNameVARCHAR(50),@.StartDateDATETIME,@.EndDateDATETIME,@.OrganiserVARCHAR(50),@.TelephoneVARCHAR(15),@.FaxVARCHAR(15),@.EmailVARCHAR(50),@.EventURLVARCHAR(50),@.PendingVerificationBIT,@.SubmitersNameVARCHAR(50),@.SubmitersEmailVARCHAR(50),@.EventVenueVARCHAR(50),@.EventCityVARCHAR(50),@.EventStateVARCHAR(50),@.CountryIDVARCHAR(50),@.SubmiterNameVARCHAR(50),@.SubmiterEmailVARCHAR(50),@.SiteIDVarchar(100),@.LiveOnWebBITASBEGINSET NOCOUNT ONDeclare @.EventIdINTINSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax,@.Email, @.EventURL, @.EventURL, @.PendingVerification,@.SubmiterName, @.SubmiterEmail)SELECT @.EventId = SCOPE_IDENTITY()Declare @.SiteIdTableTable (SiteIdINT)INSERT into @.SiteIdTableSELECT *FROM dbo.fnGetIDs (@.SiteID)INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)SELECT @.EventID, SiteID, @.LiveOnWebFROM @.SiteIdTableSET NOCOUNT OFFEND
|||
Thanks for the code, i am trying to implement it but is gives me an error
Server: Msg 208, Level 16, State 1, Procedure sp_EventNew_Insert, Line 35
Invalid object name 'dbo.fnGetIDs'.
I am trying this in query analyser! i dont know if i neet to add any udf as well with that?
|||You need to create the function based on the code I provided in the article link.
|||Thanks Dinakar,
I wonder how much efficient code a developer can write, if they know the system copletely!
|||
rasesh_dave:
Thanks Dinakar,
I wonder how much efficient code a developer can write, if they know the system copletely!
Definetely...
Help needed
Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)
Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>
|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>
|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)
Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>
|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>
|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Help needed
Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Help needed
Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) = C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
> > Is there no one who has had a simular situation or has indepth
> > knowledge of linked servers, I have been struggling with this for
> > days.
> >
> > Thanks
> > Jim Wile
> >
> >
> > "Jim Wile" <jimwile@.mopac.com> wrote in message
> > news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> >> Hello
> >>
> >> I am trying to insert records into a lined server (Oracle RDB
> >> database) from a sql database using Tsql. I cannot get past the
> >> error:
> >> Server: Msg 446, Level 16, State 9, Line 4
> >> Cannot resolve collation conflict for equal to operation.
> >>
> >> The Tsql script follows, any help will be greatly appreciated!
> >>
> >> Thanks
> >> Jim Wile
> >>
> >> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> >>
> >> INSERT INTO CASPR_VENDORS...VENDORS
> >> (VENDOR_NUMBER,
> >> VENDOR_TYPE,
> >> VENDOR_NAME,
> >> ADDRESS_1,
> >> ADDRESS_2,
> >> CITY,
> >> STATE,
> >> ZIP_CODE,
> >> PHONE_NUMBER,
> >> TAGS_ON_BILLS,
> >> ADDRESS_3)
> >> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> >> 'XX',
> >> M.VENDOR_NAME,
> >> M.SYS_ADDRESS_1,
> >> M.SYS_ADDRESS_2,
> >> M.SYS_CITY,
> >> M.SYS_STATE,
> >> M.SYS_POSTAL_CODE,
> >> M.PHONE,
> >> 'N',
> >> SYS_ADDRESS_3
> >> FROM Magpi.dbo.VENDORS AS M
> >> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> >> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => >> C.VENDOR_NUMBER)
>|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> The easy answer would to that you need to change the collation for
>> the fields you are using in your comparison. I don't know if it's
>> possible to find out which collation the ORacle server is using -
>> and also I don't know if that will solve the problem.
>> If not, then you might be able to select the values you want to
>> compare from your Oracle server into a temp table on your SQL server
>> and then use that for mathing the values in your other SQL server
>> database. It's maybe not a very nice solution and I don't know if
>> it's gonna work, but it might be worth giving a thought.
>> Regards
>> Steen
>> Jim Wile wrote:
>> Is there no one who has had a simular situation or has indepth
>> knowledge of linked servers, I have been struggling with this for
>> days.
>> Thanks
>> Jim Wile
>>
>> "Jim Wile" <jimwile@.mopac.com> wrote in message
>> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) = C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
> > Is there no one who has had a simular situation or has indepth
> > knowledge of linked servers, I have been struggling with this for
> > days.
> >
> > Thanks
> > Jim Wile
> >
> >
> > "Jim Wile" <jimwile@.mopac.com> wrote in message
> > news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> >> Hello
> >>
> >> I am trying to insert records into a lined server (Oracle RDB
> >> database) from a sql database using Tsql. I cannot get past the
> >> error:
> >> Server: Msg 446, Level 16, State 9, Line 4
> >> Cannot resolve collation conflict for equal to operation.
> >>
> >> The Tsql script follows, any help will be greatly appreciated!
> >>
> >> Thanks
> >> Jim Wile
> >>
> >> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> >>
> >> INSERT INTO CASPR_VENDORS...VENDORS
> >> (VENDOR_NUMBER,
> >> VENDOR_TYPE,
> >> VENDOR_NAME,
> >> ADDRESS_1,
> >> ADDRESS_2,
> >> CITY,
> >> STATE,
> >> ZIP_CODE,
> >> PHONE_NUMBER,
> >> TAGS_ON_BILLS,
> >> ADDRESS_3)
> >> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> >> 'XX',
> >> M.VENDOR_NAME,
> >> M.SYS_ADDRESS_1,
> >> M.SYS_ADDRESS_2,
> >> M.SYS_CITY,
> >> M.SYS_STATE,
> >> M.SYS_POSTAL_CODE,
> >> M.PHONE,
> >> 'N',
> >> SYS_ADDRESS_3
> >> FROM Magpi.dbo.VENDORS AS M
> >> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> >> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => >> C.VENDOR_NUMBER)
>|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> The easy answer would to that you need to change the collation for
>> the fields you are using in your comparison. I don't know if it's
>> possible to find out which collation the ORacle server is using -
>> and also I don't know if that will solve the problem.
>> If not, then you might be able to select the values you want to
>> compare from your Oracle server into a temp table on your SQL server
>> and then use that for mathing the values in your other SQL server
>> database. It's maybe not a very nice solution and I don't know if
>> it's gonna work, but it might be worth giving a thought.
>> Regards
>> Steen
>> Jim Wile wrote:
>> Is there no one who has had a simular situation or has indepth
>> knowledge of linked servers, I have been struggling with this for
>> days.
>> Thanks
>> Jim Wile
>>
>> "Jim Wile" <jimwile@.mopac.com> wrote in message
>> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)
help needed
Hi
hi,
just go through all the commands available in SQL server books on line, which is available with all MS SQL server installation, and try 2 learn urself best.. any way the answer is
INSERT INTO tab_name Values('MSSQL', 134)
if u r inserting varchar values enclose data between two single quotes like 'MSSQL"
if it is Numeric values write as it is like 14, 78.9 etc etc
I've just started learning SQL today. How do i insert values to a table i've created?
thnx
ice
Quote:
Originally Posted by realredice
Hi
I've just started learning SQL today. How do i insert values to a table i've created?
thnx
ice
hi,
just go through all the commands available in SQL server books on line, which is available with all MS SQL server installation, and try 2 learn urself best.. any way the answer is
INSERT INTO tab_name Values('MSSQL', 134)
if u r inserting varchar values enclose data between two single quotes like 'MSSQL"
if it is Numeric values write as it is like 14, 78.9 etc etc
regards,|||hi
thnx 4 the reply. Another q. Is there much difference between MySQL and SQL* Plus?
regards
ice
Monday, February 27, 2012
help me! read text file in sql
HELLO,
please help me, how to read txt file and then insert it to sql table.
i have file like this name.txe
<...
012301231923123902132003
23423i4u23490342342342343
...>
I have 4 column and want to fractionise this text '012301231923123902132003'
and put in to table.
Best regards
RobertHi
If you want to use DTS see the following tutorial
http://www.sqldts.com/default.aspx?6,107,276,7,1
John
"Robert K" <rkloma@.hotmail.com> wrote in message
news:bhap7a$c3n$1@.news.onet.pl...
> HELLO,
> please help me, how to read txt file and then insert it to sql table.
> i have file like this name.txe
> <...
> 012301231923123902132003
> 23423i4u23490342342342343
> ...>
> I have 4 column and want to fractionise this text
'012301231923123902132003'
> and put in to table.
>
> Best regards
> Robert|||See response in microsoft.public.sqlserver.programming
--
- Anith
( Please reply to newsgroups only )|||Hi,
You can use the xp_cmdshell to read the contents of a text file.
You can look at the sample at
http://www.kodyaz.com/articles/read...p_cmdshell.aspx
Eralper
http://www.kodyaz.com
Friday, February 24, 2012
Help me pls - with database and Insert statement
I Have an error:
Server Error in '/quanlythietbi' Application.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated.
Source Error:
Line 129:mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));Line 130:mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;Line 131:int i = mycommand.ExecuteNonQuery();Line 132:if (i>0)Line 133:{and this is my code:
string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";
myconnection =new SqlConnection(stringconn);
mycommand =new SqlCommand(sqlstring,myconnection);
myconnection.Close();
myconnection.Open();
mycommand =new SqlCommand(insertquery,myconnection);
mycommand.Parameters.Add(new SqlParameter("@.ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));
mycommand.Parameters["@.ngayGiaiQuyetxong1"].Value = Label8.Text;
mycommand.Parameters.Add(new SqlParameter("@.date_yc1",System.Data.SqlDbType.SmallDateTime));
mycommand.Parameters["@.date_yc1"].Value = Label7.Text;
mycommand.Parameters.Add("@.manv_yc1",System.Data.SqlDbType.Char,10);
mycommand.Parameters["@.manv_yc1"].Value = TextBox2.Text;
mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));
mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;
int i = mycommand.ExecuteNonQuery();
if (i>0)
{
lbcheck.Text = "?ã C?p Nh?t Yêu C?u.";
}
---------------------------------------
I don't know what I must do to repair it :(
How does insertquery look like? The error comes from an INSERT command, so you should check the INSERT command to see whether it tries to insert a row which conficts with the FK constraint. Such error will be raised if you try to insert a row with a field value that does not exist in the PRIMARY KEY (in another table) referenced by the FOREIGN KEY. To learn more about FOREIGN KEY constraint, you can refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8ypg.asp
|||thank a lot :) I will try to fix it now
Sunday, February 19, 2012
help me out for a simple query
I am sending a small scenario
i have a table
CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
INSERT INTO VALUES (1, 20)
INSERT INTO VALUES (1, 10)
INSERT INTO VALUES (1, 30)
INSERT INTO VALUES (1, 50)
INSERT INTO VALUES (2, 50)
INSERT INTO VALUES (2, 70)
INSERT INTO VALUES (2, 20)
INSERT INTO VALUES (2, 40)
INSERT INTO VALUES (2, 90)
i need the output like
examid marksstring
----
1 20,10,30,50
2 50,70,20,40,90
----
thx for ur help
*** Sent via Developersdex http://www.examnotes.net ***Here's one way to achieve this using a function:
CREATE FUNCTION dbo.fn_ConcatMarks(@.id INT) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.marks VARCHAR(8000)
SET @.marks = ''
SELECT @.marks = @.marks + CAST(MARKS AS VARCHAR(10)) + ',' FROM
STUDENT_ANSWERS
WHERE EXAMID = @.id
RETURN LEFT(@.marks, LEN(@.marks) - 1)
END
GO
SELECT EXAMID, dbo.fn_ConcatMarks(EXAMID) AS marks
FROM STUDENT_ANSWERS
GROUP BY EXAMID
EXAMID marks
-- --
1 20,10,30,50
2 50,70,20,40,90
You can find several other solutions in previous threads if you look for the
keywords PIVOT, crosstab.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.rediffmail.com> wrote in message
news:OzYlXrmRFHA.1208@.TK2MSFTNGP10.phx.gbl...
>
> I am sending a small scenario
> i have a table
> CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
> INSERT INTO VALUES (1, 20)
> INSERT INTO VALUES (1, 10)
> INSERT INTO VALUES (1, 30)
> INSERT INTO VALUES (1, 50)
> INSERT INTO VALUES (2, 50)
> INSERT INTO VALUES (2, 70)
> INSERT INTO VALUES (2, 20)
> INSERT INTO VALUES (2, 40)
> INSERT INTO VALUES (2, 90)
>
>
> i need the output like
> examid marksstring
> ----
> 1 20,10,30,50
> 2 50,70,20,40,90
> ----
>
> thx for ur help
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
Help me Insert/Update!
Can someone please Help,
I'm trying to write an insert/update query but it doesn't work.
My syntax is similar to the following
INSERT/UPDATE INTO GL_SCORE VALUES (1, 'andrew');
INSERT INTO GL_SCORE VALUES (2, 'gordon');
SELECT * FROM GL_SCORE
But it doesn't work, why?
Please help me insert/update becuase it would be good.RTFM
Look up the syntax for UPDATE & INSERT, I'm sure you can answer your question by yourself.|||Hi,
I found syntax on insert and update but nothing on insert/update, like doing them both at once. Why?|||Why do you think, that SQL Server has a Update/Insert command, like Oracle 9i has it's MERGE? Which SQL Server version you are talking about?|||Dear Tronboy
I have never encountered Insert/Update in SQL Server syntax
I think you will have to choose, either its INSERT or UPDATE
If its a type of functionality or logic which is often used you should consider putting it in a user defined function or sproc.|||Tronboy,
Insert and Update are two different command in SQL Server (although updates actually involve deleting and reinserting rows, this all happens behind the scenes).
You may be getting confused because triggers can be set to execute and update.
blindman|||Originally posted by blindman
You may be getting confused because triggers can be set to execute and update.
blindman
Good point, blindman; this is the way to confuse TronBoy completely. I'm proud of you!
I'm trying to write an insert/update query but it doesn't work.
My syntax is similar to the following
INSERT/UPDATE INTO GL_SCORE VALUES (1, 'andrew');
INSERT INTO GL_SCORE VALUES (2, 'gordon');
SELECT * FROM GL_SCORE
But it doesn't work, why?
Please help me insert/update becuase it would be good.RTFM
Look up the syntax for UPDATE & INSERT, I'm sure you can answer your question by yourself.|||Hi,
I found syntax on insert and update but nothing on insert/update, like doing them both at once. Why?|||Why do you think, that SQL Server has a Update/Insert command, like Oracle 9i has it's MERGE? Which SQL Server version you are talking about?|||Dear Tronboy
I have never encountered Insert/Update in SQL Server syntax
I think you will have to choose, either its INSERT or UPDATE
If its a type of functionality or logic which is often used you should consider putting it in a user defined function or sproc.|||Tronboy,
Insert and Update are two different command in SQL Server (although updates actually involve deleting and reinserting rows, this all happens behind the scenes).
You may be getting confused because triggers can be set to execute and update.
blindman|||Originally posted by blindman
You may be getting confused because triggers can be set to execute and update.
blindman
Good point, blindman; this is the way to confuse TronBoy completely. I'm proud of you!
Subscribe to:
Posts (Atom)