Problem with CDATA section in XQuery

Hi,
i am trying to insert a CDATA section using XQuery in T-SQL. I am following
the example from books online
http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting th
e
error 'XQuery [modify()]: Syntax error near '<!''
The code is:
DECLARE @.myDoc xml
SET @.myDoc =
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features> </Features>
</ProductDescription>
</Root>'
SELECT @.myDoc
SET @.myDoc.modify('
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
into (/Root/ProductDescription/Features)[1] ')
SELECT @.myDoc ;
Does anyone have a solution for this?
Thanks,
Stefan
I think you can achieve the same effect by doing this
SET @.myDoc.modify('
insert text {"<notxml> as text </notxml> or cdata"}
into (/Root/ProductDescription/Features)[1]')|||Hello markc600@.hotmail.com,
Not really. If he really does want to use the CDATA tag here, he can't --
the content doesn't mater. I suspect, but XMLRW isn't parsing it correctly.
This smells like a bug to me.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||In this case I believe you should be able to use the text node constructor,
like
this
SET @.myDoc.modify('insert text{"<notxml> as text </notxml> or cdata"} into
(/Root/ProductDescription/Features)[1] ')
Denis Ruckebusch
SQL Server XML datatype test team
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mindflower" <mindflower@.discussions.microsoft.com> wrote in message
news:9DBECB6E-006B-4537-A039-CEEA784B9FD8@.microsoft.com...
> Hi,
> i am trying to insert a CDATA section using XQuery in T-SQL. I am followin
g
> the example from books online
> http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting
the
> error 'XQuery [modify()]: Syntax error near '<!''
> The code is:
> DECLARE @.myDoc xml
> SET @.myDoc =
> '<Root>
> <ProductDescription ProductID="1" ProductName="Road Bike">
> <Features> </Features>
> </ProductDescription>
> </Root>'
> SELECT @.myDoc
> SET @.myDoc.modify('
> insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
> into (/Root/ProductDescription/Features)[1] ')
> SELECT @.myDoc ;
> Does anyone have a solution for this?
> Thanks,
> Stefan

problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem with Bulk Insert Task and Oracle Database

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

Saurabh Kulkarni wrote:

I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.

I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.

When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).

Does anyone have solution for this problem?

I don't know for sure but I can make an educated guess.

The Bulk Insert task leverages SQL Server's bulk insert functionality. Hence, it is to be used with SQL Server only. The first sentance in the BOL topic about Bulk Insert Task says "The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view"

i.e. Not Oracle.

Did you read the documentation?

-Jamie

|||

Jamie is right; here is the link to the BOL that talks about Bulk insert task:

http://msdn2.microsoft.com/en-us/library/ms141239.aspx

To solve your problem you can use OLE DB Destination instead and choose fast load option...

|||Rafael,

As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?

Thanks.|||

Kaarthik Sivashanmugam wrote:

Rafael,
As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?
Thanks.

I was not confused; I provided the link to support Jamie statement. I have edited my previous post to clarify it.

|||

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

|||

OLE DB destination has several moving parts that will allow you to tune it for large amount of rows. First at all; you should use fast load as the data access mode; at least against SQL server that works as bulk inserts. Then you have 'rows per batch' and 'Maximum Insert commit size' parameter that would help to control how often the transactions will commit. I have seen at least 1 thread in this forum discussing how to use those options. Also and if third party providers are an option for you; I think there is at least 1 vendor offering a high performance connector for Oracle DBs (http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm). I recommend you to run some tests and see by yourself.

|||

Loading data into Oracle could be done by using the "execute process task" and then use sqlldr as command. You have to install the oracle client - which contains the sqlldr - on the maschine, where your package will run. Using the process task gives you all the flexibility for dealing with paramters, config-files and so on, which makes sqlldr fast.

I realized a similar scenario for transfering Sybase data into a MS SQL Server by using standard utilities from Sybase to get data out (or in, whatever the direction is), dependent on a configration table (so fully dynamic).

Norbert

|||

Saurabh Kulkarni wrote:

sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.

Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.

Regards,

Saurabh

The OLE DB Destination is perfectly fine for loadif to Oracle as long as you use it the right way. Read these links:

Persistent Oracle Connector
(http://blogs.conchango.com/jamiethomson/archive/2006/04/07/3575.aspx)

Loading to Oracle
(http://blogs.conchango.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-Loading-to-Oracle.aspx)

-Jamie

Problem with BULK INSERT in SQL Server 2005 Express

Hi, group
I'm having a problem when trying to use 'BULK INSERT' to import data from a
plain file to a SQL Server 2005 Express database; i have simplified both
the table and the data file, but i'm still unable to understand the
error... I'm using a (simplified) format file, specifically this one:
9.0
2
1 SQLINT 0 4 ";" 1 Id
""
2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The (simplified) data file i'm trying to import have now only one line:
1;8015
(his content, as seen in a hex editor, is exactly 313B383031350D0A)
The (simplified) database is being created with this:
create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
However, when i execute this command:
BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
(FORMATFILE='D:\access\articulos.fmt')
i'm getting the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1,
column 2. Verify that the field terminator and row terminator are specified
correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I have done some tests changing the terminators, adding more lines,
changing the size of the fields, but they have failed and, what is worse, i
don't understand where is the problem
Does anyone know what is happening? Any help would be welcomed...
Jose Luis BravoJose,
You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
Try this format file:
9.0
2
1 SQLINT 0 4 ";" 1 Id ""
2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The prefix length column is used for native format imports, not text
file imports.
(This information is harder to find in the 2005 documentation,
unfortunately.)
Steve Kass
Drew University
Jose Luis Bravo wrote:

>Hi, group
>I'm having a problem when trying to use 'BULK INSERT' to import data from a
>plain file to a SQL Server 2005 Express database; i have simplified both
>the table and the data file, but i'm still unable to understand the
>error... I'm using a (simplified) format file, specifically this one:
>9.0
>2
>1 SQLINT 0 4 ";" 1 Id
>""
>2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
>SQL_Latin1_General_CP1_CI_AS
>
>The (simplified) data file i'm trying to import have now only one line:
>1;8015
>(his content, as seen in a hex editor, is exactly 313B383031350D0A)
>The (simplified) database is being created with this:
>create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
>
>However, when i execute this command:
>BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
>(FORMATFILE='D:\access\articulos.fmt')
>i'm getting the following error:
>Msg 4866, Level 16, State 7, Line 1
>The bulk load failed. The column is too long in the data file for row 1,
>column 2. Verify that the field terminator and row terminator are specified
>correctly.
>Msg 7399, Level 16, State 1, Line 1
>The OLE DB provider "BULK" for linked server "(null)" reported an error.
>The provider did not give any information about the error.
>Msg 7330, Level 16, State 2, Line 1
>Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
>I have done some tests changing the terminators, adding more lines,
>changing the size of the fields, but they have failed and, what is worse, i
>don't understand where is the problem
>
>Does anyone know what is happening? Any help would be welcomed...
>
>Jose Luis Bravo
>|||El Wed, 08 Mar 2006 08:54:09 -0500, Steve Kass escribi:
Thanks, Steve! Effectively, i hadn't seen that information in the docs but,
as i had made the original format file with the bcp utility, i incorrectly
supposed it should be OK.
Thanks again.

> Jose,
> You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
> Try this format file:
> 9.0
> 2
> 1 SQLINT 0 4 ";" 1 Id ""
> 2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
> SQL_Latin1_General_CP1_CI_AS
> The prefix length column is used for native format imports, not text
> file imports.
> (This information is harder to find in the 2005 documentation,
> unfortunately.)
> Steve Kass
> Drew University

Problem with BULK INSERT ASCII file into nvarchar column

Hi,

I have a problem with BULK INSERT. I created the following table:

Code Snippet

create table Test
(id char(4), name nvarchar(16), last char(1))

I am trying to bulk insert data from ASCII (not unicode) file with only two rows:

0011First name
0018Second name

Since it is a fixed length file, I am using the following format file:

Code Snippet

8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "\r\n" 3 Last HEBREW_CI_AS

With bcp utility everything works just fine!

Code Snippet

bcp Demo.dbo.test in c:\test -T -f c:\test.fmt

But when I use BULK INSERT in the following form:

Code Snippet

BULK INSERT Test FROM 'c:\Test'
WITH
(
FORMATFILE='c:\Test.fmt',
CODEPAGE='OEM'
);

I am getting error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).

Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.

Can anybody explain what is going on here?

I am using MS SQL 2000 and MSDE

Thanks in advance,

Eugene.

Another thing is that if I set the format file to specify row delimiter for that nvarchar field, it will also work.

Code Snippet

8.0
2
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "\r\n" 2 NAME HEBREW_CI_AS

But then in the real system i can't have multiple fields within the file...

|||

On SQL2005 the problem does not exist! Then it seems like a bug in SQL2000!

sql

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
--
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with Bulk insert

Hello,
I use the statement "Bulk insert" to insert data from a file to one table on
SQL Server.
I do like this:
bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
wiht (FieldSeparator = ',', Rowseparator='\n')
Table Clients(num int, name char(50), comment varchar(max))
But im my file "TableClients.txt", I have one row like this:
7,Peter,'Hello,World"\n
You can see, I want to use "," in my column comment but I have this message
error:
Type mismatch...
It's because I use "," in my comment and for statement "bulk insert", it's a
field terminator and then I have a number of colums different from my table
"Client".
How Can I resolve this problem I want to use "," in column "comment"?
Thanks!I'm losing anything, I dont' see anywhere 'fieldseparator' or 'rowseparator
'
options.
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>|||You can define what is your fieldsepartore in your file txt and what is your
rowseparator.
In my case, fieldseparator=','
rowseparator='\n'
But my question, it's: can we use ',' in a string in one line of filetext'
Consult the first message which I sent for more details
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>

Problem with boolean in DTS AxtiveScript

I just want to insert true value in a bit field
does anybody know why this is not working

Function Main()
DTSDestination("Active") = True
Main = DTSTransformStat_OK
End Function

Also tryed this

Function Main()
DTSDestination("Active") = 1
Main = DTSTransformStat_OK
End Function

the error is "unspecified error"
Please does somebody know what the problem isMay check this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q196/9/59.asp&NoWebContent=1) reference.

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
----
--
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> > Hi Tracy ,
> > your guess is correct. i'm only checking for a single ID in the
> > trigger. i dont know how can i handle the situation where i would have
> > mulitple rows.
> > i thought the trigger will be triggered each time row is getting
> > inserted. i dont know how the trigger behaves in bulk insert/update.
> >
> > by the way here is the code for the trigger. please give it a look and
> > advise me how can i modify it to handle the bulk insert/update.
> >
> > alter TRIGGER trigg_insert_row
> > ON [Table10]
> > FOR INSERT, UPDATE
> > AS
> >
> > declare @.ResourceID uniqueidentifier
> > declare @.msg varchar(255)
> > declare @.flag bit
> > set @.flag=0
> >
> > select @.ResourceID=ResourceID from inserted
> >
> > IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > if(@.flag=0)
> > begin
> > set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> > )
> > RAISERROR ( @.msg,16, 1 )
> > rollback transaction
> > end
> >
> >
> > -----
> > Please let me know if u need some more information on it.
> >
> > thanks,
> >
> > Lucky
> >
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:
> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>> Untested, but I think something like this will work better for you:
> [snip]
>> SELECT TOP 1 @.ResourceID = ResourceID
>> FROM
>> (
>> SELECT ResourceID
>> FROM inserted
>> LEFT JOIN Table1
>> ON inserted.ResourceID = Table1.ID
>> WHERE Table1.ID IS NULL
>> UNION
> [snip]
>> ) AS MissingIDs
>> IF @.ResourceID IS NULL
>> BEGIN
>> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
>> RAISERROR (@.msg, 16, 1)
>> ROLLBACK TRANSACTION
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Lucky
lucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegro ups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky
|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
|||Ed Murphy wrote:
> Tracy McKibben wrote:
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem with a SP. Help!

Why does only the first INSERT statement work?

CREATE PROCEDURE insert_inventory
@.item_name varchar(20),
@.description varchar(100),
@.notes varchar(255),
@.amount varchar(8)
AS
DECLARE @.item_id int
DECLARE @.transaction_date datetime

IF (@.item_name = '') SET @.item_name = NULL
IF (@.description = '') SET @.description = NULL
IF (@.notes = '') SET @.notes = NULL
IF (@.amount = '') SET @.amount = NULL

SET @.item_id = IDENT_CURRENT('inventory')
SET @.transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))

Also, where I have IF(@.blah = '') SET...

I use these to convert blank fields in VB6 to NULL values, is there any easier way?do you get any error on the second insert?

if @.blah='' set... can be removed, but you will have to check for the value somewhere if you care for it: either in your vb6, in the if as you do now, or in the values clause (...values (nullif(@.blah, ''), ...)|||Try to change your SP this way (you have to get id after insert - not before):

SET @.transaction_date = GETDATE()
begin tran
INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)
if @.@.error<>0 begin
rollback
return
end
SET @.item_id = IDENT_CURRENT('inventory')

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))
commit|||i think ident_current(..) returns the _last_ new identity value for the specified table, not the identity value generated by the current scope. in this case if your insert occurred before another insert in another scope you may acquire the value that is not yours. scope_identity() guarantees that identity value belongs to insert from your session.

also, it's better to write the error handler this way:

declare @.error int, @.id int
...insert operation
select @.error = @.@.error, @.id = scope_identity()
if @.error <> 0 begin
raiserror (...)
rollback tran
return (1)
end
commit tran|||Cheers, for those, they were all good recommendations which I am now using, but it still would only execute the first INSERT statement. That is until I used this:

SET NOCOUNT ON

Works beautifully now!!!

Rayden

Problem with 2 tables

So i have these 2 tables:

Cars - has a primary key car

Car Accessories - has a foreign key car

When i insert a new car i has no accessories, so i dont have to insert a record into the car accessories table, but when
i want to add a car accessories record for a specific car i bump into a problem. If i want to add it i dont know which statement to use. If i use insert and the accessories record is already present, then ill get an error or if i use update and the accessories record is not present then it will not work again.

any ideas? maybe it is possible to use some kind of relationship which automatically creates the car accessories record when you insert a new car?

In SQL Server I use an Stored Procedure called xxx_AddUpdate, where xxx is some table or other friendly descriptive name

using the keys passed as parameters, I perform process as follows

if(exists(select <your Id> from [Car Accessories] where <your Id> = @.<your id> ))

-- perform update SQL

else

-- perform insert SQL

|||

Hey thanks for the reply, what you said will do the job just fine, i didnt know you can check if a record exists like that, thanks

I have some other question:

I have most of my sql commands stored in a xsd dataset, would there be any advantage of using stored procedures instead?

|||

For security and other reasons it is traditional to use stored procedures, unless you have other overriding factors in your design.

SP's provides a data access "protocol" that can be better managed from a security perspective, both in terms of what can be limited or exposed to a user for a process, but also in terms of limiting access by database and application roles

|||

Aah so u are saying if security is not a priority its not so important to use stored procedures.

In this case i am gonna stick the strongly typed (xds) dataset and the commands inside it, even though it has many flaws in my opinion, like for example having to make an instance of a dataset table adapter to use its commands, i find it just unnecessary.

|||

It is still worth implementing the suggested workflow in some of your projects as this gets you used to industry standard practice

Problem with @@identity return in stored procedure insert.

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
SELECT @.@.identity AS finalNum
magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure
 ' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSequence").Value = "41833"
' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSubject").Value = "1064"
' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theFirstName", SqlDbType.NVarChar, 50))
 magicCommand.SelectCommand.Parameters("@.theFirstName").Value = orderFirstName
' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@.theLastName").Value = orderLastName
DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If

I need finalMagicNum2I usually just have the stored proc return the @.@.identity like so:

CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
Return @.@.Identity

Your stored proc is not returning finalnum and your code has no output param set up in it.

Sam|||Try this code in .NET

DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables(0).Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables(0).Rows(0).item("finalnum").toString
'OR
'finalMagicNum2 = DSMagic.Tables(0).Rows(0).item(0).toString
End If

Hope this help

problem with @@IDENTITY

In MSSQL I have auto incrementing PK for the row I'm inserting into. The the
code I use below is to insert into the table. What I need is as soon as I
insert the record, I also need to return back what the PK was of my newly
inserted row. I will need to use this value for another section of the FORM
for use as a FK into another table in which I going insert some other data
based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help is
greatly appreciated. I pretty new at this stuff and just learning. Thank
You.
You forgot to include your code. Are you using a stored procedure or an
insert statement to insert the data?
Whatever option you are using you need to SELECT @.@.identity immediately
after the statement that performs the insert. If you are running SQL Server
2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
create proc foo
@.param type.....
as
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
go
Keith
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> In MSSQL I have auto incrementing PK for the row I'm inserting into. The
the
> code I use below is to insert into the table. What I need is as soon as I
> insert the record, I also need to return back what the PK was of my newly
> inserted row. I will need to use this value for another section of the
FORM
> for use as a FK into another table in which I going insert some other data
> based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help
is
> greatly appreciated. I pretty new at this stuff and just learning. Thank
> You.
>
|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OcORPnHsEHA.3876@.TK2MSFTNGP15.phx.gbl...
All relevant stuff.
In addition, if the table you are inserting into has an INSERT trigger which
in turn performs a 'cascaded' INSERT into another table that also has an
identity the value of @.@.IDENTITY or scope_identity() will be that of the
the other table. Beware. THere is a workaround though:
You MUST contrive to cache @.@.IDENTITY coming into your trigger
(i.e. set @.myid = @.@.IDENTITY)and reset it before leaving. If you don't do
this, Access will not be able to correctly track the row inserted and you
will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).
Here is an SQL 2000 idiom to reset @.@.IDENTITY to @.myid (should be done as
the last thing before the trigger exits):
EXECUTE (N'SELECT Identity (Int, ' + Cast(@.myid As Varchar(10)) + ',1) AS id
INTO #Tmp'
G'luck
Malcolm Cook - mec@.stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

> You forgot to include your code. Are you using a stored procedure or an
> insert statement to insert the data?
> Whatever option you are using you need to SELECT @.@.identity immediately
> after the statement that performs the insert. If you are running SQL
Server[vbcol=seagreen]
> 2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
>
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> create proc foo
> @.param type.....
> as
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> go
> --
> Keith
>
> "news.microsoftnews" <sapk81@.yahoo.com> wrote in message
> news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> the
I[vbcol=seagreen]
newly[vbcol=seagreen]
> FORM
data
> is
>
sql

problem with >>insert into varchar field shows "?"

i would like to insert thai characters into varchar field in Database. and show that to a label.

now the problem is when i insert into database it becomes "?"

and when i display to Lable also "?".

is there anyway to fix this?

input comes from Textbox(vb.net)

output to Label.

thanks.

thompson.

You need to specifically 'tell' SQL Server that the incoming data is UNICODE. The common method to do that is to preface the data with the letter [ N ].

It would be done something like this:

INSERT INTO MyTable ( MyColumn ) VALUES ( N'MyUnicodeValue' )

|||

Including Arnie Rowland's suggestion, check the datatype of your table column. It shoule be NVarchar. Varchar only supports ASCII chars, if you want to insert Unicode values then it should be NVarchar/NChar/NText datatype.

Note: Varchar is not equal to NVarchar

|||thanks to everyone answer this post. the problem has been solved.

problem with >>insert into varchar field shows "?"

i would like to insert thai characters into varchar field in Database. and show that to a label.

now the problem is when i insert into database it becomes "?"

and when i display to Lable also "?".

is there anyway to fix this?

input comes from Textbox(vb.net)

output to Label.

thanks.

thompson.

You need to specifically 'tell' SQL Server that the incoming data is UNICODE. The common method to do that is to preface the data with the letter [ N ].

It would be done something like this:

INSERT INTO MyTable ( MyColumn ) VALUES ( N'MyUnicodeValue' )

|||

Including Arnie Rowland's suggestion, check the datatype of your table column. It shoule be NVarchar. Varchar only supports ASCII chars, if you want to insert Unicode values then it should be NVarchar/NChar/NText datatype.

Note: Varchar is not equal to NVarchar

|||thanks to everyone answer this post. the problem has been solved.

problem with convert 2000 server 2000

we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :

(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:

(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))

if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...

convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))

PROBLEM WIHT OPENROWSET FUNCTION

HI FRIENDS THIS IS AMIT. THE PROBLEM IS I M TRYING TO EXPORT DATA FROM SQL SERVER TABLE TO EXCEL FILE USING FOLLOWING CODE SNIPPET,

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 7.0;Database=c:\testing.xls;',
'SELECT * FROM [Sheet1$]') select * from TABLE1

BUT IT IS SHOWING SOME ERROR WHICH IS AS FOLLOWS,

Invalid object name 'OPENROWSET'.

I FOUND OPENROWSET FUNCTION IN T-SQL REFERENCE BUT STILL THE ABOVE MESSAGE IS COMING PLS HELP IN THIS MATTER ASAP.

REGARDS,

AMIT.

What version of SQL Server are you using (show the output of SELECT @.@.VERSION)?

Steve Kass
Drew University
http://www.stevekass.com|||

Make sure you have "Ad Hoc Remote Queries" enabled for your instance.

|||Kindly provide the version of the SQL Server you are using, it should ideally work without problem in SQL Server 2000 and above. You can however try the same using DTS (Data Transformation Services)|||Excel 7.0??

Try Excel 5.0 or Excel 8.0