Problems inserting records into non dbo schema

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?This turned out to be a conflict between using Nvarchar(max) and Nvarchar(255). Even though the data would fit ssis didnt seem to like it.|||

Of course not. they're two different data types!

If I understand correctly varchar(max) is not the same as an infinitely long "normal" varchar. Or it helps to not think about it that way anyway.

I'm sure you'd get a better answer on the T-SQL forum

-Jamie

sql

Problems inserting into table

I am trying to write an insert statement. I insert into 1 table test with th
e
statements below and what I want is I want to use this table to insert value
s
in
table test1 but I want be able to insert everything from the keyword product
so the new table description is always beginning from Product......
Since the length of month varies before the keyword I cannot use a static
substring or trim functions.
My insert will be something like this in test1.
id desc
-- --
1 Product dispatch to website...(individual number)
2 Product dispatch to website...(individual number)
Can anyone help me .
create table test(
Sno Int,
description varchar(2000),
cyats datetime default getdate() )
create table test1(
id int,
description varchar(2000),
cyats datetime default getdate())
insert into test(sno,description)
values(1,'Integrated Release August 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is :...')
insert into test(sno,description)
values(1,'Integrated Release December 2004. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')
insert into test(sno,description)
values(1,'Integrated Release June 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')First suggestion clean this data before getting into SQL Server using
whatever tool you have (like DTS or whatever.) as it will be far more
natural. However, a possible way is to reformat as a select in whatever
code you are using to build this data:
SELECT 1,substring('Integrated Release August 2005. Product despatched to
website.
Website address for tracking consignment is http://www.track.com,your
number
is :...', charindex('product', 'Integrated Release August 2005. Product
despatched to website.
Website address for tracking consignment is http://www.track.com,your
number
is :...'), 4000)
You could change the string to a variable, but I wouldn't unless you are
hand typing this stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8B17F575-0318-4317-A318-2E7F543D1A31@.microsoft.com...
>I am trying to write an insert statement. I insert into 1 table test with
>the
> statements below and what I want is I want to use this table to insert
> values
> in
> table test1 but I want be able to insert everything from the keyword
> product
> so the new table description is always beginning from Product......
> Since the length of month varies before the keyword I cannot use a static
> substring or trim functions.
> My insert will be something like this in test1.
> id desc
> -- --
> 1 Product dispatch to website...(individual number)
> 2 Product dispatch to website...(individual number)
> Can anyone help me .
> create table test(
> Sno Int,
> description varchar(2000),
> cyats datetime default getdate() )
> create table test1(
> id int,
> description varchar(2000),
> cyats datetime default getdate())
> insert into test(sno,description)
> values(1,'Integrated Release August 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is :...')
> insert into test(sno,description)
> values(1,'Integrated Release December 2004. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')
> insert into test(sno,description)
> values(1,'Integrated Release June 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')

Problems creating Error File when using Bulk Insert or BCP from xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

I had this error too. It looks like a bug as BULK INSERT works fine without the -ERRORFILE option. It seems that the execution of the BULK INSERT first creates the file without closing it which results in the error message afterwards. As Workaround just don't use the option

Nobsay

Problems catching @@error from trigger on insert from openxml

Have kind of an oddball scenario that needs a solution.
I have a stored proc which is passed XML that pulls out sections of the xml
for inserts into 3 different tables.
The first insert has an "instead of" trigger that on rare occasions raises
an error.
After that first insert (using an "insert into ... select * from openxml"
form) I need to know if that error was raised. Testing @.@.ERROR always gives
me 0.
Is this an issue with the openxml insert form?
What I would like to do is rollback a transaction if any of the inserts fail.Just put all the 3 inserts in a transaction with
BEGIN TRANSACTION
INSERT 1
IF @.@.TRANCOUNT > 0
INSERT 2
IF @.@.TRANCOUNT > 0
INSERT 3
IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION
An error (any error) in a trigger will terminate and rollback the
transaction that fired it. The thing you want to avoid to do after that is
so the next inserts. The code above will give you some control over the
exact execution, but if you don't need that, you can use SET XACT_ABORT ON
before you start the transaction, and no code will be executed in the batch
after an error is encountered.
You probably don't see any value for @.@.ERROR because you don't check it
immediately after the statement that raises the error (easy mistake to
make).
For some more background information read these excellent articles by SQL
Server MVP Erland Sommarskog:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
--
Jacco Schalkwijk
SQL Server MVP
"D.Kratt" <DKratt@.discussions.microsoft.com> wrote in message
news:7EFFB116-37E6-4188-8500-2846C5233AEA@.microsoft.com...
> Have kind of an oddball scenario that needs a solution.
> I have a stored proc which is passed XML that pulls out sections of the
> xml
> for inserts into 3 different tables.
> The first insert has an "instead of" trigger that on rare occasions raises
> an error.
> After that first insert (using an "insert into ... select * from openxml"
> form) I need to know if that error was raised. Testing @.@.ERROR always
> gives
> me 0.
> Is this an issue with the openxml insert form?
> What I would like to do is rollback a transaction if any of the inserts
> fail.|||Tried both (thanks for the suggestions, eventually got me to a solution),
but didn't work until I placed an explicit ROLLBACK TRANSACTION right before
my raiseerror in the insert trigger on the first table.
"Jacco Schalkwijk" wrote:
> Just put all the 3 inserts in a transaction with
> BEGIN TRANSACTION
> INSERT 1
> IF @.@.TRANCOUNT > 0
> INSERT 2
> IF @.@.TRANCOUNT > 0
> INSERT 3
> IF @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> An error (any error) in a trigger will terminate and rollback the
> transaction that fired it. The thing you want to avoid to do after that is
> so the next inserts. The code above will give you some control over the
> exact execution, but if you don't need that, you can use SET XACT_ABORT ON
> before you start the transaction, and no code will be executed in the batch
> after an error is encountered.
> You probably don't see any value for @.@.ERROR because you don't check it
> immediately after the statement that raises the error (easy mistake to
> make).
> For some more background information read these excellent articles by SQL
> Server MVP Erland Sommarskog:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D.Kratt" <DKratt@.discussions.microsoft.com> wrote in message
> news:7EFFB116-37E6-4188-8500-2846C5233AEA@.microsoft.com...
> > Have kind of an oddball scenario that needs a solution.
> >
> > I have a stored proc which is passed XML that pulls out sections of the
> > xml
> > for inserts into 3 different tables.
> >
> > The first insert has an "instead of" trigger that on rare occasions raises
> > an error.
> > After that first insert (using an "insert into ... select * from openxml"
> > form) I need to know if that error was raised. Testing @.@.ERROR always
> > gives
> > me 0.
> >
> > Is this an issue with the openxml insert form?
> >
> > What I would like to do is rollback a transaction if any of the inserts
> > fail.
>
>

Problem: must detect db changes

Hello

I′m using MS-SQL Server 2k with a custom application connecting to it.
I need a quick and simple way to detect any change (insert, update, delete)
to my database (not just to a single table). The purpose is to notify
somehow different instances of the application about the data changes,
since they should refresh their local query results.

Is there any function, stored procedure, system database entry, etc. I can
exploit to get the job done? Can you suggest me the most suitable mechanism
to implement this?

T.I.A.

AndreA"AndreA" <andrea.galbusera@.teamware.it> wrote in message
news:c4recb$89p$1@.grillo.cs.interbusiness.it...
> Hello
> Im using MS-SQL Server 2k with a custom application connecting to it.
> I need a quick and simple way to detect any change (insert, update,
delete)
> to my database (not just to a single table). The purpose is to notify
> somehow different instances of the application about the data changes,
> since they should refresh their local query results.
> Is there any function, stored procedure, system database entry, etc. I can
> exploit to get the job done? Can you suggest me the most suitable
mechanism
> to implement this?
> T.I.A.
> AndreA

You can use triggers on the tables you want to audit, or perhaps use a
product which can read the transaction log and generate an audit trail:

http://www.lumigent.com/products/entegra/entegra.htm

One other option is to create a server-side trace, although personally I
would use that only as a temporary solution or to find a specific problem.

Simon

Problem: Can dhcp callout dll insert new row into dsn database?

I current working on this dll, but i found the dll doesn't response to any of the cdatabase execute response? I have try all the cdatabase and sql in c++ console and found that it can be run. The database is open success and the sql statement is correct.
After then i put it into the dhcp callout dll, and compile success.
When i add it into the registry and try the dhcp server, it doesn't have any response database table. And i using sql server for the dsn.
Here is my code
#include "stdafx.h"
#include "callout.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

CDatabase dbCallout;

struct tm *newtime;
char am_pm[] = "AM";
__time64_t long_time;

CString strCmd, strPrimaryKey, strTime;
int nRetCode = 0;

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved )
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
} return TRUE;
}

DWORD CALLBACK DhcpControlHook(DWORD dwControlCode,LPVOID lpReserved)
{
switch (dwControlCode)
{
case DHCP_CONTROL_START: {
_time64( &long_time ); /* Get time as long integer. */
newtime = _localtime64(&long_time); /* Convert to local time. */

if( newtime->tm_hour > 12 ) /* Set up extension. */
strcpy( am_pm, "PM" );
if( newtime->tm_hour > 12 ) /* Convert from 24-hour */
newtime->tm_hour -= 12; /* to 12-hour clock. */
if( newtime->tm_hour == 0 ) /*Set hour to 12 if midnight. */
newtime->tm_hour = 12;

strPrimaryKey.Format("C%.2d%.2d%.2d%.2d%.2d%.2d", newtime->tm_year - 100, newtime->tm_mon, newtime->tm_mday, newtime->tm_hour, newtime->tm_min, newtime->tm_sec);
strTime = asctime(newtime);

strCmd = "INSERT INTO Callout_Control (control_id, control_desc, control_date) VALUES ('" + strPrimaryKey + "', 'DHCP server have started!', '" + strTime + "')";

dbCallout.ExecuteSQL(strCmd);
dbCallout.Close();

break;
}
case DHCP_CONTROL_STOP:
{

break;
}
case DHCP_CONTROL_PAUSE:
{

break;
}
case DHCP_CONTROL_CONTINUE:
{
break;
}
}
return ERROR_SUCCESS;
}

DWORD CALLBACK DhcpServerCalloutEntry(LPWSTR ChainDlls,DWORD CalloutVersion,LPDHCP_CALLOUT_TABLE CalloutTbl)
{

CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpControlHook=DhcpControlHook;
CalloutTbl->DhcpDeleteClientHook=DhcpDeleteClientHook;
CalloutTbl->DhcpPktDropHook=DhcpPktDropHook;
CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpNewPktHook=DhcpNewPktHook;
CalloutTbl->DhcpPktSendHook=DhcpPktSendHook;

dbCallout.Open(_T("CALLOUT"), FALSE, FALSE, _T("ODBC;"));

return ERROR_SUCCESS;
}
What wrong in this code? Can dll file insert new row into dsn? Some expert please help me!!!

Hi

I think your first mitake is that :

1- In DhcpServerCalloutEntry you should set value of CalloutTbl to null if you dont impliment a function

regards

Problem: Can dhcp callout dll insert new row into dsn database?

I current working on this dll, but i found the dll doesn't response to any of the cdatabase execute response? I have try all the cdatabase and sql in c++ console and found that it can be run. The database is open success and the sql statement is correct.
After then i put it into the dhcp callout dll, and compile success.
When i add it into the registry and try the dhcp server, it doesn't have any response database table. And i using sql server for the dsn.
Here is my code
#include "stdafx.h"
#include "callout.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

CDatabase dbCallout;

struct tm *newtime;
char am_pm[] = "AM";
__time64_t long_time;

CString strCmd, strPrimaryKey, strTime;
int nRetCode = 0;

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved )
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
} return TRUE;
}

DWORD CALLBACK DhcpControlHook(DWORD dwControlCode,LPVOID lpReserved)
{
switch (dwControlCode)
{
case DHCP_CONTROL_START: {
_time64( &long_time ); /* Get time as long integer. */
newtime = _localtime64(&long_time); /* Convert to local time. */

if( newtime->tm_hour > 12 ) /* Set up extension. */
strcpy( am_pm, "PM" );
if( newtime->tm_hour > 12 ) /* Convert from 24-hour */
newtime->tm_hour -= 12; /* to 12-hour clock. */
if( newtime->tm_hour == 0 ) /*Set hour to 12 if midnight. */
newtime->tm_hour = 12;

strPrimaryKey.Format("C%.2d%.2d%.2d%.2d%.2d%.2d", newtime->tm_year - 100, newtime->tm_mon, newtime->tm_mday, newtime->tm_hour, newtime->tm_min, newtime->tm_sec);
strTime = asctime(newtime);

strCmd = "INSERT INTO Callout_Control (control_id, control_desc, control_date) VALUES ('" + strPrimaryKey + "', 'DHCP server have started!', '" + strTime + "')";

dbCallout.ExecuteSQL(strCmd);
dbCallout.Close();

break;
}
case DHCP_CONTROL_STOP:
{

break;
}
case DHCP_CONTROL_PAUSE:
{

break;
}
case DHCP_CONTROL_CONTINUE:
{
break;
}
}
return ERROR_SUCCESS;
}

DWORD CALLBACK DhcpServerCalloutEntry(LPWSTR ChainDlls,DWORD CalloutVersion,LPDHCP_CALLOUT_TABLE CalloutTbl)
{

CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpControlHook=DhcpControlHook;
CalloutTbl->DhcpDeleteClientHook=DhcpDeleteClientHook;
CalloutTbl->DhcpPktDropHook=DhcpPktDropHook;
CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpNewPktHook=DhcpNewPktHook;
CalloutTbl->DhcpPktSendHook=DhcpPktSendHook;

dbCallout.Open(_T("CALLOUT"), FALSE, FALSE, _T("ODBC;"));

return ERROR_SUCCESS;
}
What wrong in this code? Can dll file insert new row into dsn? Some expert please help me!!!

Hi

I think your first mitake is that :

1- In DhcpServerCalloutEntry you should set value of CalloutTbl to null if you dont impliment a function

regards

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

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: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

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: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

problem with xml parsing in enqueue

We are trying to setup simple queue where we are trying to insert(enqueue) record ,it's inserted successfully .

But while retrive records from queues it shows the following err

XML parsing: line 0, character 0, unrecognized input signature

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: XML parsing: line 0, character 0, unrecognized input signature

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

when we use ths following query we r getting records properly

(select * from sys.transmission_queue )

For solving this we have used following commands

USE master ;

GO

ALTER DATABASE database name SET ENABLE_BROKER ;

alter ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
GO
--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname'

use dbname
select * from sys.transmission_queue order by enqueue_time desc

create master key encryption by password = 'pass@.word1';

alter AUTHORIZATION ON DATABASE::[dbname] TO [sa];

but unable to solve it.

In a sample example we can dequeue the element which can be removed but we can't enqueue the element, we can send you sample if you need.

Regards,

Ali

The error you show is a client side error (ADO.Net error), not a server error. It seems that you're trying to read a column that is not XML type through an SqlXml datatype. In adition it seems that you're using a Web Service (HTTP endpoint?) to access the server. Is this true?

BTW, messages should be dequeue using RECEIVE from the target service's queue, not select from sys.transmission_queue

|||

Thanx for giving right solution.

Ofcourse its problem with column size.

i resolved it.

Thanx..

Ali

Problem with using 'OutPut Clause' in SQL SEERVER 2005

Hi everyBody,

I Tried to use "OutPut" clause in insert statment as fallows:

insert into test

OUTPUT Inserted.Name,Inserted.LastName

values ('John','kransky')

The Error:

Line 2: Incorrect syntax near 'OUTPUT'.

but I face with this error,I checked MSDN and the other sources, it seems everything is ok. But when I run it ,I face with that error message.I would be appriciate if someone help me on it.

OUTPUT values MUST be 'captured' in some fashion. Typically, the OUTPUT values are captured in a @.Temp table.

For Example, this might work for you:

Code Snippet

DECLARE @.NewRows table

( [Name] varchar(20),

LastName varchar(20)

);

INSERT INTO Test

OUTPUT Inserted.Name, Inserted.LastName

INTO @.NewRows

VALUES ( 'John', 'kransky' );

Then you can use the data in the @.NewRows table however you wish.

Problem with using ''OutPut Clause'' in SQL SEERVER 2005

Hi everyBody,

I Tried to use "OutPut" clause in insert statment as fallows:

insert into test

OUTPUT Inserted.Name,Inserted.LastName

values ('John','kransky')

The Error:

Line 2: Incorrect syntax near 'OUTPUT'.

but I face with this error,I checked MSDN and the other sources, it seems everything is ok. But when I run it ,I face with that error message.I would be appriciate if someone help me on it.

OUTPUT values MUST be 'captured' in some fashion. Typically, the OUTPUT values are captured in a @.Temp table.

For Example, this might work for you:

Code Snippet

DECLARE @.NewRows table

( [Name] varchar(20),

LastName varchar(20)

);

INSERT INTO Test

OUTPUT Inserted.Name, Inserted.LastName

INTO @.NewRows

VALUES ( 'John', 'kransky' );

Then you can use the data in the @.NewRows table however you wish.

problem with updating identity in transactional replication

Hey. I've a few transactional publications. It fails with this error. When I
looked at the command, it's trying to insert a null in identity column. Why
does it do that? As of now, I've commented the update for the identity column
in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
identity column, what would happen? The reason I need the identity property
on subscriber side is because these replicated tables will be horizontally
partitioned and replicated to another server. And I'm hoping to use
Transactional Replication for that. Please let me know what should be done
and what's the ideal. thank you.
Cannot update identity column 'DeviceBeaconID'.
(Source: XIAN\XIAN2 (Data source); Error number: 8102)
{CALL sp_MSupd_logDeviceBeacon
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
Transaction sequence number and command ID of last execution batch are
0x002BA62A00000E7E000100000000 and 1.
This looks like you update proc as opposed to your insert proc.
Take the proc and open it up in a text editor. In the bottom half of the
proc comment out the part where it updates the identity column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:728B16DD-98F6-41F1-925F-53C6684FD831@.microsoft.com...
> Hey. I've a few transactional publications. It fails with this error. When
> I
> looked at the command, it's trying to insert a null in identity column.
> Why
> does it do that? As of now, I've commented the update for the identity
> column
> in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
> identity column, what would happen? The reason I need the identity
> property
> on subscriber side is because these replicated tables will be horizontally
> partitioned and replicated to another server. And I'm hoping to use
> Transactional Replication for that. Please let me know what should be done
> and what's the ideal. thank you.
>
> Cannot update identity column 'DeviceBeaconID'.
> (Source: XIAN\XIAN2 (Data source); Error number: 8102)
>
> {CALL sp_MSupd_logDeviceBeacon
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
> 15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.
|||Yes, I'm sorry. I used the wrong word. It's trying to update the Ident column
with a 'NULL' value. Can you explain what this statement does?
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
I think this is the line you have asked me to comment out. In my case, I
checked the @.bitmap variable and I found it to be '0x8009' when it does a
substring, it'll be case 0, right? so, it'll go to the else statement,
right?
Now, the question is why does the sp try to insert a null? Is it because no
modifications were made to it?
Thank you for your help.
Tejas
|||It should not be trying to do a null update. I am really confused here
however, you persist in talking about inserts, for the life of me it should
be an update. Or perhaps you are that pesky Paul Ibison in disguise trying
to push me over the edge?
The @.bitmap dictates which columns are to be updated. It looks like for your
bitmask the else will be used which means that the identity value will be
updated to the same value. A Null is passed due to the call type you are
using MCALL IIRC. As the identity value is not updated on the publisher no
value is passed - i.e. a NULL is passed. If the identity column was updated
(if this is possible) a value would be passed here instead of the null.
I think your update proc portion should look like this
update "datObjects" set
-- "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
-- "ObjectTypeID" end
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:2D828E4F-AC1A-4875-83CB-85B58FC80A73@.microsoft.com...
> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
> with a 'NULL' value. Can you explain what this statement does?
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> I think this is the line you have asked me to comment out. In my case, I
> checked the @.bitmap variable and I found it to be '0x8009' when it does a
> substring, it'll be case 0, right? so, it'll go to the else statement,
> right?
> Now, the question is why does the sp try to insert a null? Is it because
> no
> modifications were made to it?
> Thank you for your help.
> Tejas
|||lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm sorry
again for using the word insert in my first post.Thank you very much for your
help. This was what I was actually looking for. Thank you.
|||Ok, thanks Paul.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:50263252-D63C-448A-8A89-470CABA983EA@.microsoft.com...
> lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm
> sorry
> again for using the word insert in my first post.Thank you very much for
> your
> help. This was what I was actually looking for. Thank you.
|||Hey Hilary. I could not do what you had asked. I'm pasting the whole
sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
Below, objectId is the identity column. But it doesn't exist in the else part.
I've pasted the sp as is. Made no modifications to it. So, if i comment the
objectid in the if part it works fine... Is it normal to be not there in the
else part? because that's where u asked me to comment it out.
-----
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else "ObjectID"
end
,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
|||Ok Paul, I think this is it
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
--on the safe side I will do this too
--"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"ObjectID"
--end
--,
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
--"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
--"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
>
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5FA239EC-5A40-4D2F-AB2F-570BF4B6FAF9@.microsoft.com...
> Hey Hilary. I could not do what you had asked. I'm pasting the whole
> sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
> Below, objectId is the identity column. But it doesn't exist in the else
> part.
> I've pasted the sp as is. Made no modifications to it. So, if i comment
> the
> objectid in the if part it works fine... Is it normal to be not there in
> the
> else part? because that's where u asked me to comment it out.
> -----
> CREATE procedure "sp_MSupd_datObjects"
> @.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
> bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
> bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
> ,@.bitmap binary(3)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "datObjects" set
> "ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "ObjectID"
> end
> ,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> ,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end,
> "ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> GO
>
|||Just wanted to know if you noticed that the commented lines in the sp are two
different fields...
In the if, it's objectid
in the else, it's objectTypeID.
These are two different columns with no correlation...
Is the commenting still correct?
Just want to be sure.
Thank you,
TEJAS
(not Paul)
|||Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:561E41D4-279E-4FF3-B976-8F75A2AA7689@.microsoft.com...
> Just wanted to know if you noticed that the commented lines in the sp are
> two
> different fields...
> In the if, it's objectid
> in the else, it's objectTypeID.
> These are two different columns with no correlation...
> Is the commenting still correct?
> Just want to be sure.
> Thank you,
> TEJAS
> (not Paul)

Problem With Update Unicode Field

Hello.
I have a problem with update Unicode field in MS SQL Server 2000.
When I insert new row into the table and fill some Unicode field with some
data it's working fine, but when I try to update existing record with some
new values in Unicode fields the data is displaying as "?" symbols.
Can anybody tell me how can I solve this problem?A table definition and the update statement would be helpful.
A quick example that may be helpful to you:
CREATE TABLE Frog (
UnicodeColumn nvarchar(50),
ANSIColumn varchar(50)
)
GO
INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
GO
SELECT * FROM Frog
GO
UPDATE Frog
SET UnicodeColumn = N'Other Unicode Data'
GO
SELECT * FROM Frog
GO
-- Notice the N in front of the Unicode data segment. This tells SQL Server
that "National" character data follows.
HTH
Rick Sawtell
"David" <david_dvali@.hotmail.com> wrote in message
news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello.
> I have a problem with update Unicode field in MS SQL Server 2000.
> When I insert new row into the table and fill some Unicode field with some
> data it's working fine, but when I try to update existing record with some
> new values in Unicode fields the data is displaying as "?" symbols.
> Can anybody tell me how can I solve this problem?
>|||How can I use 'N' in parametrized SQL?
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
> A table definition and the update statement would be helpful.
> A quick example that may be helpful to you:
> CREATE TABLE Frog (
> UnicodeColumn nvarchar(50),
> ANSIColumn varchar(50)
> )
> GO
>
> INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
> GO
> SELECT * FROM Frog
> GO
> UPDATE Frog
> SET UnicodeColumn = N'Other Unicode Data'
> GO
> SELECT * FROM Frog
> GO
>
> -- Notice the N in front of the Unicode data segment. This tells SQL
Server
> that "National" character data follows.
>
> HTH
> Rick Sawtell
>
> "David" <david_dvali@.hotmail.com> wrote in message
> news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
some[vbcol=seagreen]
some[vbcol=seagreen]
>|||You don't need to use the N if you are passing your data in RPC parameters.
What API are you using? When you define the parameter are you setting it
up as a Unicode param? A bit of sample code showing how you pass your data
in might help.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Reply-To: "David" <david_dvali@.hotmail.com>
| From: "David" <david_dvali@.hotmail.com>
| References: <OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl>
<e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl>
| Subject: Re: Problem With Update Unicode Field
| Date: Thu, 22 Jul 2004 13:41:26 +0400
| Lines: 59
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eX60aB9bEHA.4032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ge-tbs-ats77-tc-ppp12.wanex.net 213.131.38.173
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352505
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| How can I use 'N' in parametrized SQL?
|
| "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
| news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
| > A table definition and the update statement would be helpful.
| >
| > A quick example that may be helpful to you:
| >
| > CREATE TABLE Frog (
| > UnicodeColumn nvarchar(50),
| > ANSIColumn varchar(50)
| > )
| > GO
| >
| >
| > INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| > UPDATE Frog
| > SET UnicodeColumn = N'Other Unicode Data'
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| >
| > -- Notice the N in front of the Unicode data segment. This tells SQL
| Server
| > that "National" character data follows.
| >
| >
| > HTH
| >
| > Rick Sawtell
| >
| >
| >
| > "David" <david_dvali@.hotmail.com> wrote in message
| > news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
| > > Hello.
| > >
| > > I have a problem with update Unicode field in MS SQL Server 2000.
| > >
| > > When I insert new row into the table and fill some Unicode field with
| some
| > > data it's working fine, but when I try to update existing record with
| some
| > > new values in Unicode fields the data is displaying as "?" symbols.
| > >
| > > Can anybody tell me how can I solve this problem?
| > >
| > >
| >
| >
|
|
|

Problem With Update Unicode Field

Hello.
I have a problem with update Unicode field in MS SQL Server 2000.
When I insert new row into the table and fill some Unicode field with some
data it's working fine, but when I try to update existing record with some
new values in Unicode fields the data is displaying as "?" symbols.
Can anybody tell me how can I solve this problem?A table definition and the update statement would be helpful.
A quick example that may be helpful to you:
CREATE TABLE Frog (
UnicodeColumn nvarchar(50),
ANSIColumn varchar(50)
)
GO
INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
GO
SELECT * FROM Frog
GO
UPDATE Frog
SET UnicodeColumn = N'Other Unicode Data'
GO
SELECT * FROM Frog
GO
-- Notice the N in front of the Unicode data segment. This tells SQL Server
that "National" character data follows.
HTH
Rick Sawtell
"David" <david_dvali@.hotmail.com> wrote in message
news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello.
> I have a problem with update Unicode field in MS SQL Server 2000.
> When I insert new row into the table and fill some Unicode field with some
> data it's working fine, but when I try to update existing record with some
> new values in Unicode fields the data is displaying as "?" symbols.
> Can anybody tell me how can I solve this problem?
>|||How can I use 'N' in parametrized SQL?
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
> A table definition and the update statement would be helpful.
> A quick example that may be helpful to you:
> CREATE TABLE Frog (
> UnicodeColumn nvarchar(50),
> ANSIColumn varchar(50)
> )
> GO
>
> INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
> GO
> SELECT * FROM Frog
> GO
> UPDATE Frog
> SET UnicodeColumn = N'Other Unicode Data'
> GO
> SELECT * FROM Frog
> GO
>
> -- Notice the N in front of the Unicode data segment. This tells SQL
Server
> that "National" character data follows.
>
> HTH
> Rick Sawtell
>
> "David" <david_dvali@.hotmail.com> wrote in message
> news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
> > Hello.
> >
> > I have a problem with update Unicode field in MS SQL Server 2000.
> >
> > When I insert new row into the table and fill some Unicode field with
some
> > data it's working fine, but when I try to update existing record with
some
> > new values in Unicode fields the data is displaying as "?" symbols.
> >
> > Can anybody tell me how can I solve this problem?
> >
> >
>|||You don't need to use the N if you are passing your data in RPC parameters.
What API are you using? When you define the parameter are you setting it
up as a Unicode param? A bit of sample code showing how you pass your data
in might help.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Reply-To: "David" <david_dvali@.hotmail.com>
| From: "David" <david_dvali@.hotmail.com>
| References: <OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl>
<e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl>
| Subject: Re: Problem With Update Unicode Field
| Date: Thu, 22 Jul 2004 13:41:26 +0400
| Lines: 59
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eX60aB9bEHA.4032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ge-tbs-ats77-tc-ppp12.wanex.net 213.131.38.173
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352505
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| How can I use 'N' in parametrized SQL?
|
| "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
| news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
| > A table definition and the update statement would be helpful.
| >
| > A quick example that may be helpful to you:
| >
| > CREATE TABLE Frog (
| > UnicodeColumn nvarchar(50),
| > ANSIColumn varchar(50)
| > )
| > GO
| >
| >
| > INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| > UPDATE Frog
| > SET UnicodeColumn = N'Other Unicode Data'
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| >
| > -- Notice the N in front of the Unicode data segment. This tells SQL
| Server
| > that "National" character data follows.
| >
| >
| > HTH
| >
| > Rick Sawtell
| >
| >
| >
| > "David" <david_dvali@.hotmail.com> wrote in message
| > news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
| > > Hello.
| > >
| > > I have a problem with update Unicode field in MS SQL Server 2000.
| > >
| > > When I insert new row into the table and fill some Unicode field with
| some
| > > data it's working fine, but when I try to update existing record with
| some
| > > new values in Unicode fields the data is displaying as "?" symbols.
| > >
| > > Can anybody tell me how can I solve this problem?
| > >
| > >
| >
| >
|
|
|

Problem With Update Unicode Field

Hello.
I have a problem with update Unicode field in MS SQL Server 2000.
When I insert new row into the table and fill some Unicode field with some
data it's working fine, but when I try to update existing record with some
new values in Unicode fields the data is displaying as "?" symbols.
Can anybody tell me how can I solve this problem?
A table definition and the update statement would be helpful.
A quick example that may be helpful to you:
CREATE TABLE Frog (
UnicodeColumn nvarchar(50),
ANSIColumn varchar(50)
)
GO
INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
GO
SELECT * FROM Frog
GO
UPDATE Frog
SET UnicodeColumn = N'Other Unicode Data'
GO
SELECT * FROM Frog
GO
-- Notice the N in front of the Unicode data segment. This tells SQL Server
that "National" character data follows.
HTH
Rick Sawtell
"David" <david_dvali@.hotmail.com> wrote in message
news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello.
> I have a problem with update Unicode field in MS SQL Server 2000.
> When I insert new row into the table and fill some Unicode field with some
> data it's working fine, but when I try to update existing record with some
> new values in Unicode fields the data is displaying as "?" symbols.
> Can anybody tell me how can I solve this problem?
>
|||How can I use 'N' in parametrized SQL?
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
> A table definition and the update statement would be helpful.
> A quick example that may be helpful to you:
> CREATE TABLE Frog (
> UnicodeColumn nvarchar(50),
> ANSIColumn varchar(50)
> )
> GO
>
> INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
> GO
> SELECT * FROM Frog
> GO
> UPDATE Frog
> SET UnicodeColumn = N'Other Unicode Data'
> GO
> SELECT * FROM Frog
> GO
>
> -- Notice the N in front of the Unicode data segment. This tells SQL
Server[vbcol=seagreen]
> that "National" character data follows.
>
> HTH
> Rick Sawtell
>
> "David" <david_dvali@.hotmail.com> wrote in message
> news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
some[vbcol=seagreen]
some
>
|||You don't need to use the N if you are passing your data in RPC parameters.
What API are you using? When you define the parameter are you setting it
up as a Unicode param? A bit of sample code showing how you pass your data
in might help.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Reply-To: "David" <david_dvali@.hotmail.com>
| From: "David" <david_dvali@.hotmail.com>
| References: <OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl>
<e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl>
| Subject: Re: Problem With Update Unicode Field
| Date: Thu, 22 Jul 2004 13:41:26 +0400
| Lines: 59
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eX60aB9bEHA.4032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ge-tbs-ats77-tc-ppp12.wanex.net 213.131.38.173
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFT NGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:352505
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| How can I use 'N' in parametrized SQL?
|
| "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
| news:e9gN9P3bEHA.4092@.TK2MSFTNGP11.phx.gbl...
| > A table definition and the update statement would be helpful.
| >
| > A quick example that may be helpful to you:
| >
| > CREATE TABLE Frog (
| > UnicodeColumn nvarchar(50),
| > ANSIColumn varchar(50)
| > )
| > GO
| >
| >
| > INSERT FROG VALUES (N'Some Unicode Data goes here', 'ANSI data here')
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| > UPDATE Frog
| > SET UnicodeColumn = N'Other Unicode Data'
| > GO
| >
| > SELECT * FROM Frog
| > GO
| >
| >
| > -- Notice the N in front of the Unicode data segment. This tells SQL
| Server
| > that "National" character data follows.
| >
| >
| > HTH
| >
| > Rick Sawtell
| >
| >
| >
| > "David" <david_dvali@.hotmail.com> wrote in message
| > news:OwBu3y1bEHA.1132@.TK2MSFTNGP10.phx.gbl...
| > > Hello.
| > >
| > > I have a problem with update Unicode field in MS SQL Server 2000.
| > >
| > > When I insert new row into the table and fill some Unicode field with
| some
| > > data it's working fine, but when I try to update existing record with
| some
| > > new values in Unicode fields the data is displaying as "?" symbols.
| > >
| > > Can anybody tell me how can I solve this problem?
| > >
| > >
| >
| >
|
|
|