Thursday, March 22, 2012
Combining 2 records into 1
There are two erros entries for every error reason.
DDL
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS_REASON]
GO
CREATE TABLE [dbo].[INPUT_ERRORS] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemErrorItemType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ErrorMessageMessageLine] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
RECORD',' BACS',540)SET NOCOUNT ON
SET NOCOUNT OFF
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',23
6)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
SET NOCOUNT OFF
i would like to get this sresult returned for easy reading in a report
Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessageLi
ne
'217523' '16781' 'ORIGINAL RECORD' '19069'
'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
INVALID'
'217993' '25194' 'ORIGINAL RECORD' '7'
'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
INVALID'
'217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUNT
DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'Peter Newman wrote:
> I have the follwing tables which contain error records and error reasons.
> There are two erros entries for every error reason.
> DDL
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS_REASON]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemErrorItemType] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ErrorMessageMessageLine] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
> RECORD',' BACS',540)SET NOCOUNT ON
> SET NOCOUNT OFF
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',
236)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
> CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
> SET NOCOUNT OFF
> i would like to get this sresult returned for easy reading in a report
> Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
> ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessage
Line
> '217523' '16781' 'ORIGINAL RECORD' '19069'
> 'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' '7'
> 'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
> 'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUN
T
> DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'
Thanks for including the DDL. Unfortunately neither table has any keys!
Including an IDENTITY column means we can probably guess that that is a
key but an IDENTITY may not be enough to solve your problem. IDENTITY
should not be the only key of a table but you haven't shown us any
other so we can only guess. Here's one possibility:
SELECT licence, reportnumber,
LEFT(err1,50) AS erroritemtype1, RIGHT(err1,20) AS erroritemref1,
LEFT(err2,50) AS erroritemtype2, RIGHT(err2,20) AS erroritemref2
FROM
(SELECT licence, reportnumber,
MIN(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err1,
MAX(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err2
FROM input_errors
GROUP BY licence, reportnumber) AS T ;
David Portas
SQL Server MVP
--
Sunday, March 11, 2012
COM+ Event error in relation to SQL Server
In my Application log, I received this error from
SQLSERVERAGENT:-
Unable to read local eventlog (reason: The data area
passed to a system call is too small).
I then received lots of the following errors, source
EventSystem:-
The COM+ Event System detected a bad return code during
its internal processing. HRESULT was 800706BF from line
42 of .\eventsystemobj.cpp. Please contact Microsoft
Product Support Services to report this error.
The Server needed a reboot.
Has anyone ever seen this?
Thanks
SueThey are related but different. SQL Agent does not use COM+, but both are
complaining they can not read the NT Event Log.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"SueB" <sue.bridges@.aculab.com> wrote in message
news:057801c354f2$0c90a250$a601280a@.phx.gbl...
> Hello,
> In my Application log, I received this error from
> SQLSERVERAGENT:-
> Unable to read local eventlog (reason: The data area
> passed to a system call is too small).
> I then received lots of the following errors, source
> EventSystem:-
> The COM+ Event System detected a bad return code during
> its internal processing. HRESULT was 800706BF from line
> 42 of .\eventsystemobj.cpp. Please contact Microsoft
> Product Support Services to report this error.
> The Server needed a reboot.
> Has anyone ever seen this?
> Thanks
> Sue|||Hi,
Any clue as to how to fix it? Also, received the following error in the System log this morning from DCOM:-
Access denied attempting to launch a DCOM Server using DefaultLaunchPermssion. The server is:
{0002DF01-0000-0000-C000-000000000046}
The user is Unavailable/Unavailable, SID=3DUnavailable.
Also in the past I've seen the following DCOM error:-
The server {1BE1F766-5536-11D1-B726-00C04FB926AF} did not register with DCOM within the required timeout.
Any clues on these?
Thanks
Sue
>--Original Message--
>They are related but different. SQL Agent does not use COM+, but both are
>complaining they can not read the NT Event Log.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"SueB" <sue.bridges@.aculab.com> wrote in message
>news:057801c354f2$0c90a250$a601280a@.phx.gbl...
>> Hello,
>> In my Application log, I received this error from
>> SQLSERVERAGENT:-
>> Unable to read local eventlog (reason: The data area
>> passed to a system call is too small).
>> I then received lots of the following errors, source
>> EventSystem:-
>> The COM+ Event System detected a bad return code during
>> its internal processing. HRESULT was 800706BF from line
>> 42 of .\eventsystemobj.cpp. Please contact Microsoft
>> Product Support Services to report this error.
>> The Server needed a reboot.
>> Has anyone ever seen this?
>> Thanks
>> Sue
>
>.
>|||I only found that a reboot to corrects this.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"SueB" <sue.bridges@.aculab.com> wrote in message
news:113e01c35a6d$43453510$7d02280a@.phx.gbl...
Hi,
Any clue as to how to fix it? Also, received the
following error in the System log this morning from DCOM:-
Access denied attempting to launch a DCOM Server using
DefaultLaunchPermssion. The server is:
{0002DF01-0000-0000-C000-000000000046}
The user is Unavailable/Unavailable, SID=Unavailable.
Also in the past I've seen the following DCOM error:-
The server {1BE1F766-5536-11D1-B726-00C04FB926AF} did not
register with DCOM within the required timeout.
Any clues on these?
Thanks
Sue
>--Original Message--
>They are related but different. SQL Agent does not use
COM+, but both are
>complaining they can not read the NT Event Log.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>You assume all risk for your use.
>Copyright © SQLDev.Net 1991-2003 All rights reserved.
>"SueB" <sue.bridges@.aculab.com> wrote in message
>news:057801c354f2$0c90a250$a601280a@.phx.gbl...
>> Hello,
>> In my Application log, I received this error from
>> SQLSERVERAGENT:-
>> Unable to read local eventlog (reason: The data area
>> passed to a system call is too small).
>> I then received lots of the following errors, source
>> EventSystem:-
>> The COM+ Event System detected a bad return code during
>> its internal processing. HRESULT was 800706BF from line
>> 42 of .\eventsystemobj.cpp. Please contact Microsoft
>> Product Support Services to report this error.
>> The Server needed a reboot.
>> Has anyone ever seen this?
>> Thanks
>> Sue
>
>.
>
COM Interops on MTA's...
I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
application... I'm currently getting an error when I try to use this object
saying:
System.InvalidCastException: Unable to cast COM object of type
'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error:
No such interface supported (Exception from HRESULT: 0x80004002
(E_NOINTERFACE)).
at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
pbstrConnectionString)
at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
62
at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
Now I'm guessing that this COM object does support multiple threads.
So the question is how to get around this. Would wrapping this function in a
singleton, for example, do the trick? Or maybe having a static function in
an object?
What would be the best approach?
thanks for your time.
Daniel."Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OC2INWwwHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Greetings!
> I'm trying to use the SQLXML BulkLoad COM object from a .net multithreaded
> application... I'm currently getting an error when I try to use this
> object saying:
> System.InvalidCastException: Unable to cast COM object of type
> 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type
> 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the
> QueryInterface call on the COM component for the interface with IID
> '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following
> error: No such interface supported (Exception from HRESULT: 0x80004002
> (E_NOINTERFACE)).
> at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ConnectionString(String
> pbstrConnectionString)
> at Olympus.Integrate.Client.SqlServerClient.SendMessage(String message)
> in C:\Projects\prototype\Integrate\SqlServe
rClient\SqlServerClient.vb:line
> 62
> at Olympus.Integrate.Core.Core.MonitorFlow(Object flowObj) in
> C:\Projects\prototype\Integrate\Integrat
e\Core.vb:line 152
>
> Now I'm guessing that this COM object does support multiple threads.
> So the question is how to get around this. Would wrapping this function in
> a singleton, for example, do the trick? Or maybe having a static function
> in an object?
> What would be the best approach?
> thanks for your time.
> Daniel.
Make sure your thread is initilaize to enter a STA before starting (see
SetApartmentState(ApartmentState.STA) in System.Threading).
Willy.
>|||Willy,
Thanks for your reply.
In my application I dynamically create multiple threads according to some
configuration. At this point in the application the knowledge doesn't exist
as to whether the thread that is created will use the COM object... Is it
recommended that I just mark each thread as STA?
For Each myObj As MyObjectType In myObjects
' launch a seperate thread for each configured message flow
Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
t.Name = myObj.myName
t.SetApartmentState(ApartmentState.STA) // <-- ' Is
this okay '
MyThreadHandler.Instance.AddThread(t)
t.Start(myParameter)
Next
Cheers.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Willy,
> Thanks for your reply.
> In my application I dynamically create multiple threads according to some
> configuration. At this point in the application the knowledge doesn't
> exist as to whether the thread that is created will use the COM object...
> Is it recommended that I just mark each thread as STA?
> For Each myObj As MyObjectType In myObjects
> ' launch a seperate thread for each configured message flow
> Dim t As Thread = New Thread(AddressOf MyThreadStartFunction)
> t.Name = myObj.myName
> t.SetApartmentState(ApartmentState.STA) // <-- ' Is
> this okay '
> MyThreadHandler.Instance.AddThread(t)
> t.Start(myParameter)
> Next
> Cheers.
> Dan.
>
Dan, The apartment state is a COM only thing, it doesn't affect .NET. An STA
apartment is valid for single and apartment threaded COM object types, so in
your case it is safe to initialize your threads to enter an STA .
Willy.|||"Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:e1UxaY5wHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan, The apartment state is a COM only thing, it doesn't affect .NET. An
> STA apartment is valid for single and apartment threaded COM object types,
> so in your case it is safe to initialize your threads to enter an STA .
> Willy.
>
Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
like a bogus message. My process spends a lot of time looping around in
other parts of the code while polling which suggests the reason for this
error coming up.
Thanks for your help.
Dan.|||"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:OiEYjv7wHHA.3444@.TK2MSFTNGP05.phx.gbl...
> "Willy Denoyette [MVP]" <willy.denoyette@.telenet.be> wrote in message
> news:uj0Wdq7wHHA.4592@.TK2MSFTNGP05.phx.gbl...
> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> like a bogus message. My process spends a lot of time looping around in
> other parts of the code while polling which suggests the reason for this
> error coming up.
> Thanks for your help.
> Dan.
>
This is not necessarily bogus message, don't take this too lightly. One
possible reason for the MDA is that your thread fails to pump the message
queue, if that's the case you will have problems whenever the finalizer
needs to release the COM object references you are holding in this thread,
this will block the finalizer thread with as a result that finalizable
objects stay in memory, finally leading into OOM and other kind of failures.
Willy.|||>> Thanks. I keep getting a ContextSwtichDeadlock, but reading up it sounds
> This is not necessarily bogus message, don't take this too lightly. One
> possible reason for the MDA is that your thread fails to pump the message
> queue, if that's the case you will have problems whenever the finalizer
> needs to release the COM object references you are holding in this thread,
> this will block the finalizer thread with as a result that finalizable
> objects stay in memory, finally leading into OOM and other kind of
> failures.
> Willy.
>
I've kept the process running, keeping an eye on memory and handles, and it
appears to destory and release the COM object correctly as there's no
increase in the resources used.
How would I check whether I'm "failing to pump the message queue"? This is
not a GUI app, but a backend service. Is there any way in .Net to check the
COM reference count?
I do explicitly disgard the object after creating and using...
' clear up
System.Runtime.InteropServices.Marshal.ReleaseComObject(bulkLoadComObject)
bulkLoadComObject = Nothing
Thanks.
Daniel.
COM DLL containing UDFs works fine with as2k but gives "function does not exist" error
Hi,
I have a legacy COM dll that contains some UDFs that we want to use with our 2005 cubes. Against an as2k cube I can get this working using the Use Library statement, but as this has been deprecated in 2005 I can't use this method. I have added the dll as an assembly in both the server and the database but I cannot access the functions within it. I just recieve a "function does not exist error".
Does anyone have any ideas?
regards
Colin
I remember COM UDF's are turned off by default.
Start SQL Server Surface Area Configuration by going through Start menu ->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.
Click on the Sufrace Area Configuration for Features. Under Analysis Services the last node is User-Defined Functions. Change that option and see if your UDF works. (You might need to restart the server.)
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Edward,
Unfortunately it doesn't appear to have made a difference., it still cannot see the function. I guess I will just need to rewrite in .NET.
regards
Colin
Thursday, March 8, 2012
Columns inster into problem
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.
Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>
|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks
Columns inster into problem
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks
Columns inster into problem
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks
Wednesday, March 7, 2012
Column width
'Cannot specify a column width on data type smalldatetime.'
I'm using SQL Server & I've check the data type in the db & it's same as smalldatetime.
Any suggestions would be highly appreciated.
Thanks
<Error
Column or parameter #8: Cannot specify a column width on data type smalldatetime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.
Source Error:
Line 344: Try
Line 345: myConnection.Open()
Line 346: dgdSearch.DataSource = myCommand.ExecuteReader()
Line 347: dgdSearch.DataBind()
Line 348: Finally
Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb Line: 346
Stack Trace:
[SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader() +41
Demo_Equip_Log.Search_Equipment.BindGrid() in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:346
Demo_Equip_Log.Search_Equipment.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:195
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750Show the code you are using to set up the parameter. I presume the message means EXACTLY what it says. Are you trying to set the length of the smalldatetime parameter? You cannot.|||Hi Doug, thanks for your response & Yep!. I've released that after I posted on the forum.
This is the first time I'm using Stored Procedure.
I've removed it & now I'm not getting that error.
However, I'm getting a different error ... I highly appreciate your suggestion.
Thanks
<Error>
Cannot compute Count for a data source that does not implement ICollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: Cannot compute Count for a data source that does not implement ICollection.
Source Error:
Line 345: myConnection.Open()
Line 346: dgdSearch.DataSource = myCommand.ExecuteReader()
Line 347: dgdSearch.DataBind()
Line 348: Finally
Line 349: myConnection.Close()
Source File: c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb Line: 347
Stack Trace:
[HttpException (0x80004005): Cannot compute Count for a data source that does not implement ICollection.]
System.Web.UI.WebControls.PagedDataSource.get_DataSourceCount() +152
System.Web.UI.WebControls.PagedDataSource.get_Count() +187
System.Web.UI.WebControls.PagedDataSource.GetEnumerator() +268
System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) +712
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49
System.Web.UI.WebControls.BaseDataList.DataBind() +23
Demo_Equip_Log.Search_Equipment.BindGrid() in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:347
Demo_Equip_Log.Search_Equipment.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Demo Equip Log\Search Equipment.aspx.vb:195
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750
Saturday, February 25, 2012
column reference error
Can anyone help me with what I should be looking for with this error?
(I have also tried putting single quotes around the search number in the
query - with the same results)
Thanks!
Rich.
An unhandled exception of type 'System.Exception' occurred in
receivingdb.dll
Additional information: Error number = 547
Error class = 16
Error state = 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_purchaseorders_receivinglog'. The conflict occurred in database
'Receiving', table 'purchaseorders', column 'delivery'.
DELETE FROM [receivinglog] WHERE [delivery] = 125Rich K wrote:
> Hi,
> Can anyone help me with what I should be looking for with this error?
> (I have also tried putting single quotes around the search number in
> the query - with the same results)
> Thanks!
> Rich.
>
> An unhandled exception of type 'System.Exception' occurred in
> receivingdb.dll
> Additional information: Error number = 547
> Error class = 16
> Error state = 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_purchaseorders_receivinglog'. The conflict occurred in database
> 'Receiving', table 'purchaseorders', column 'delivery'.
> DELETE FROM [receivinglog] WHERE [delivery] = 125
You have a foreign key column in another table. You need to delete the rows
in that table that are controlled by the rwo you are deleting in this table.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OgZIYd47FHA.3976@.TK2MSFTNGP15.phx.gbl...
> You have a foreign key column in another table. You need to delete the
> rows in that table that are controlled by the rwo you are deleting in this
> table.
>
As easy as that eh?
Thanks Bob it makes sense now!
Column prefix doesnt match with a table name error
name or alias name with the query with this sql query (part of a c#
script):
"SELECT
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
d.ddate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
d.salesOff_loc_ID = " + 3 + "";
I'm trying to include the tblBankTran table and it's Amount_Revenue,
which the tblBanktran table field MGTag_ID is related to tblCustTag ID
field.
Thanks in advance
.NetSportsHi
This is invalid syntax
FROM tblBankTran as t,tblDeal as d INNER JOIN
Instead , FROM tblBankTran t INNER JOIN tblDeal s ON....
For details please refer to the BOL
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1118099682.466063.5530@.g43g2000cwa.googlegroups.com...
> I keep getting an error of Column prefix " t" doesnt match with a table
> name or alias name with the query with this sql query (part of a c#
> script):
> "SELECT
>
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
> d.ddate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
> FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
> d.salesOff_loc_ID = " + 3 + "";
> I'm trying to include the tblBankTran table and it's Amount_Revenue,
> which the tblBanktran table field MGTag_ID is related to tblCustTag ID
> field.
> Thanks in advance
> .NetSports
>
Thursday, February 16, 2012
Column Conflict error
I am getting this error while I am trying to delete a record from "MediaTypes" table
Media Types table and DVBTestCOutputs table have related through MediaID (cascade on update only not delete)
MediaTypes DVBTestCOutputs
---- -------
MediaID int ....................
MType char(10) ....................
MediaType int
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DVBTestCOutputs_MediaTypes1'. The conflict occurred in database 'Test', table 'DVBTestCOutputs', column 'MediaType'.
can you help me please I do not want the related record deleted also from DVBTestCOutputs table so I didn't choose the cascade on delete checkbox is this the problem??If there is a PK-FK constraint, then you are not allowed to have child records that have no parent record. You will need to update the value of the ParentID in the child table to another ParentID, before deleting the parent record.
Otherwise, you need to remove your constraint.
Cheers
Ken
Tuesday, February 14, 2012
Column ''cb.CurrentBalance'' is invalid in the HAVING clause ...
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM, (SELECT (rb.RMSCHGAMT - rb.RMSRCVPCPL)
+(rb.RMSASSCCST - rb.RMSRCVDCST)
+(rb.RMSACRDINT - rb.RMSRCVDINT)
+(rb.UDCCOSTS1 - rb.UDCRECCS1)
+(rb.UDCCOSTS2 - rb.UDCRECCS2)
+(rb.RMSCOST1 - rb.RMSCOST1R)
+(rb.RMSCOST2 - rb.RMSCOST2R)
+(rb.RMSCOST3 - rb.RMSCOST3R)
+(rb.RMSCOST4 - rb.RMSCOST4R)
+(rb.RMSCOST5 - rb.RMSCOST5R)
+(rb.RMSCOST6 - rb.RMSCOST6R)
+(rb.RMSCOST7 - rb.RMSCOST7R)
+(rb.RMSCOST8 - rb.RMSCOST8R)
+(rb.RMSCOST9 - rb.RMSCOST9R)
+(rb.RMSCOST10 - rb.RMSCOST10R)
- rb.RMSXCSRCVS
FROM RPRDBAL rb) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, rf10.rmstranamt10
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
--WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
Friday, February 10, 2012
Collation problem
I need to correct some data in my SQL database due to a collation error
when I did the DTS from Paradox files to my SQL database I was using the "(Server default)" collation and now the extracted data has some weird chars
I need to use a Portuguese (Brazilian) collation, can i fix the error without loosing my curent data? If yes, how can I do it
Thanks!Marcus,
You can use ALTER TABLE xxx ALTER COLUMN yyy COLLATE collation_name
You should read the COLLATE topic. Whether you get what you want will
depend on whether some characters were remapped during the load into the
(server default) collation. (I don't think so.) You will have to drop
any indexes, statistics, etc. that you created on the columns.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:DD900B9F-5D44-4ACE-9DAC-139EEC85591A@.microsoft.com...
> Hi!
> I need to correct some data in my SQL database due to a collation error!
> when I did the DTS from Paradox files to my SQL database I was using the
"(Server default)" collation and now the extracted data has some weird
chars!
> I need to use a Portuguese (Brazilian) collation, can i fix the error
without loosing my curent data? If yes, how can I do it?
>
> Thanks!