can varchar(max) store images in sql ?

If you serialize the image into a string ? If you want to save a byte array to a string, do you have to use a base64 encoded string ?

sql server can store binary objects directly, thats probably a much better solution, unless you have very specific requirements?sql

Can this be Done?

Hi, just wondering if something like this can be done

procedure [dbo].[UpdateStringColumn]
@.KeyValue int,
@.Column varchar(50),
@.value float,
@.TableName varchar(30),
@.KeyName varchar(30)
as
begin
exec('UPDATE '+@.TableName+'
Set '+@.column+ ' = '+@.value+'
where '+@.TableName+'.'+@.KeyName+' = '+@.KeyValue+'' )
end

If I can get this to work I only need a few stored procedures to update any table column combination, one for each @.Value datatype

Well, it can be done. But it is a bad idea. Using dynamic SQL like this has lot of implications - maintainability, security risks, performance problems, manageability etc. See below link for some discussion:

http://www.sommarskog.se/dynamic_sql.html

Other things to ponder: Why do you want to have single SP to update data? And what happens if you want to update multiple columns in a table? What happens if the data type of each column in different? What happens if there is some function in the client that need to only update one column and another function that updates entire row? What happens if you need to perform additional validations for updates on some tables? What happens if there are some users who can only update some tables? How will you manage the permissions since with dynamic SQL you need to grant all the required permissions to users directly?

Can this be changed to a CASE construct?

-- First, some DDL:
CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
/*
What I want to achieve is:
If there are no rows in #TEMP
OR
If any of the expiration dates are NULL
OR
If any of the expiration dates are <= TODAY
return the string 'NOT CLEARED'
ELSE return the string 'CLEARED'
The following works fine:
*/
DECLARE @.ClinicClearanceStatus varchar(12)
SET @.ClinicClearanceStatus = 'CLEARED'
IF
((SELECT COUNT(*) FROM #TEMP) = 0)
OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
OR
((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
BEGIN
SET @.ClinicClearanceStatus = 'NOT CLEARED'
END
SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
/*
I'm just curious if the preceding logic could be changed to a CASE
construct; something like
SELECT CASE WHEN < throw the IF statement in here somehow >
THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
As always, thanks in advance for all your help.
Carl
*/CREATE TABLE #TEMP (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ1', '2006-01-23')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ2', '2006-05-29')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ3', '2007-05-01')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ4', '2006-05-25')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ5', '2006-05-26')
INSERT INTO #TEMP (Requirement, ExpirationDate)
VALUES ('REQ6', '2005-01-01')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ7')
INSERT INTO #TEMP (Requirement)
VALUES ('REQ8')
SELECT
CASE
WHEN
(
((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
)
THEN
'NOT CLEARED'
ELSE
'CLEARED' END as status|||Thanks for your reply, Johnny. I checked it out, and it looks like it's
not testing for NULLs correctly; i.e., when I change all of the
expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
NULL dates intact, it returns CLEARED. I suspect it's a very minor
change but I don't know where to make it . . .
Thanks --
Carl
Johnny D wrote:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
>
> SELECT
> CASE
> WHEN
> (
> ((SELECT COUNT(1) FROM #TEMP) IS NULL) OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < getdate()) OR
> ((SELECT COUNT(1) FROM #TEMP WHERE ExpirationDate IS NULL) IS NULL )
> )
> THEN
> 'NOT CLEARED'
> ELSE
> 'CLEARED' END as status
>|||You can do it, as JohnyD pointed out, but IMO the if then logic is easier to
follow than a case statement in a select.
Of course it depends on where and how you plan on using this return value.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:uMy7B9CgGHA.1792@.TK2MSFTNGP03.phx.gbl...
> -- First, some DDL:
> CREATE TABLE #TEMP (
> Requirement varchar (20) NOT NULL,
> ExpirationDate datetime NULL
> ) ON [PRIMARY]
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ1', '2006-01-23')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ2', '2006-05-29')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ3', '2007-05-01')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ4', '2006-05-25')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ5', '2006-05-26')
> INSERT INTO #TEMP (Requirement, ExpirationDate)
> VALUES ('REQ6', '2005-01-01')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ7')
> INSERT INTO #TEMP (Requirement)
> VALUES ('REQ8')
> /*
> What I want to achieve is:
> If there are no rows in #TEMP
> OR
> If any of the expiration dates are NULL
> OR
> If any of the expiration dates are <= TODAY
> return the string 'NOT CLEARED'
> ELSE return the string 'CLEARED'
> The following works fine:
> */
> DECLARE @.ClinicClearanceStatus varchar(12)
> SET @.ClinicClearanceStatus = 'CLEARED'
> IF
> ((SELECT COUNT(*) FROM #TEMP) = 0)
> OR
> ((SELECT MIN(ExpirationDate) FROM #TEMP) < GETDATE())
> OR
> ((SELECT COUNT(*) FROM #TEMP WHERE ExpirationDate IS NULL) > 0)
> BEGIN
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> END
> SELECT @.ClinicClearanceStatus AS ClinicClearanceStatus
> /*
> I'm just curious if the preceding logic could be changed to a CASE
> construct; something like
> SELECT CASE WHEN < throw the IF statement in here somehow >
> THEN 'NOT CLEARED' ELSE 'CLEARED' END AS ClinicClearanceStatus
> As always, thanks in advance for all your help.
> Carl
> */|||I think it is just a typo.
Change the last "IS NULL" in the case function to " > 0"
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply, Johnny. I checked it out, and it looks like it's
> not testing for NULLs correctly; i.e., when I change all of the
> expiration dates in REQ1 through REQ6 to '2007-01-01' and leave the two
> NULL dates intact, it returns CLEARED. I suspect it's a very minor
> change but I don't know where to make it . . .
> Thanks --
> Carl
> Johnny D wrote:|||Thanks for the clarification Jim -- that did it.
I appreciate your and Johnny's time.
Carl
Jim Underwood wrote:
> I think it is just a typo.
> Change the last "IS NULL" in the case function to " > 0"
> "Carl Imthurn" <nospam@.all.thanks> wrote in message
> news:e5TMdPDgGHA.2208@.TK2MSFTNGP05.phx.gbl...
>
>
>|||On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
(snip)
>What I want to achieve is:
>If there are no rows in #TEMP
>OR
>If any of the expiration dates are NULL
>OR
>If any of the expiration dates are <= TODAY
>return the string 'NOT CLEARED'
>ELSE return the string 'CLEARED'
Hi Carl,
This can be greatly simplified:
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
Or, if you really want a CASE:
SET @.ClinicClearanceStatus =
(SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
FROM #TEMP)
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Hugo --
First, thanks for posting back to me.
I tested your statement with COALESCE and it worked for conditions 2 and
3 ( NULL expiration dates and expiration dates < = today ) but failed on
condition 1 (empty table). In other words, if I have an empty table, the
SELECT statement returns CLEARED where it should return NOT CLEARED.
Here's some DDL if you have a chance (and desire) to pursue this further:
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.TEST')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.TEST
CREATE TABLE TEST (
Requirement varchar (20) NOT NULL,
ExpirationDate datetime NULL
) ON [PRIMARY]
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ1', '2006-05-31')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ2', '2006-05-29')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ3', '2007-05-01')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ4', '2006-05-25')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ5', '2006-05-26')
-- INSERT INTO TEST (Requirement, ExpirationDate)
-- VALUES ('REQ6', '2005-01-01')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ7')
-- INSERT INTO TEST (Requirement)
-- VALUES ('REQ8')
DECLARE @.ClinicClearanceStatus varchar(20)
IF (SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST) <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
Thanks for your help Hugo - I appreciate it.
Carl
Hugo Kornelis wrote:
> On Thu, 25 May 2006 12:15:22 -0700, Carl Imthurn wrote:
> (snip)
>
>
> Hi Carl,
> This can be greatly simplified:
> IF (SELECT MIN(COALESCE(ExpirationDate, '19000101') FROM #TEMP) <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> Or, if you really want a CASE:
> SET @.ClinicClearanceStatus =
> (SELECT CASE WHEN MIN(COALESCE(ExpirationDate, '19000101') <
> CURRENT_TIMESTAMP THEN 'NOT CLEARED' ELSE 'CLEARED' END
> FROM #TEMP)
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>|||Carl,
You could change the last section to this...
DECLARE @.ClinicClearanceStatus varchar(20)
IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
'') <
CURRENT_TIMESTAMP
SET @.ClinicClearanceStatus = 'NOT CLEARED'
ELSE
SET @.ClinicClearanceStatus = 'CLEARED'
SELECT @.ClinicClearanceStatus
HTH
Barry|||Barry --
That did it! Thanks much -- I appreciate your time.
Have a great day.
Carl
Barry wrote:
> Carl,
> You could change the last section to this...
>
> DECLARE @.ClinicClearanceStatus varchar(20)
> IF IsNull((SELECT MIN(COALESCE(ExpirationDate, '19000101')) FROM TEST),
> '') <
> CURRENT_TIMESTAMP
> SET @.ClinicClearanceStatus = 'NOT CLEARED'
> ELSE
> SET @.ClinicClearanceStatus = 'CLEARED'
> SELECT @.ClinicClearanceStatus
>
> HTH
> Barry
>

Can the Output parameter length be more than 8000 characters?

Hi!
I am running one SP - which needs to return strings, seperated by
delimitter. I am using output parameter of type Varchar (8000). I learned
that this is maximum length allowed.
Now what problem I am facing is, for a particular field, the delimitted text
is getting higher than 8000 characters and that is why the rest of the value
is getting truncated.
Can you guys let me know any better way of achieving this?
I will be extremely thankful to you.
Regards,
SachinYou'll have to select the data instead of using an output param... Or
upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted
> text
> is getting higher than 8000 characters and that is why the rest of the
> value
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Instead of Varchar(8000) ... how about using TEXT or NText as your datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Sachin Vaishnav" wrote:

> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted te
xt
> is getting higher than 8000 characters and that is why the rest of the val
ue
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Thanks. Using 2005 is not possible for me now. I will have to manage fromw
what I have already :)
Anyways, as per your other suggestion, the problem in that is, I am already
having one select returned out of the SP. So, there is no point in that also
.
Can some cursor type of output or XML type of output is useful to me?
I need to send it back to the API and the API is used by UI.
Help me,
Sachin
"Adam Machanic" wrote:

> You'll have to select the data instead of using an output param... Or
> upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
>
>|||Stored procedures can return multiple rowsets... Why not use two?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
> Thanks. Using 2005 is not possible for me now. I will have to manage fromw
> what I have already :)
> Anyways, as per your other suggestion, the problem in that is, I am
> already
> having one select returned out of the SP. So, there is no point in that
> also.
> Can some cursor type of output or XML type of output is useful to me?
> I need to send it back to the API and the API is used by UI.
> Help me,
> Sachin
> "Adam Machanic" wrote:
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Ogxg9Nr7FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Stored procedures can return multiple rowsets... Why not use two?
...or use multiple Output parameters.
When one reaches the 8000 character limit, insert the rest in the 2nd.
But I'd prefer Adam's solution, 2 recordsets.|||Hi!
Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to get
once. Can I have some example of the same?
Thanks
Sachin
"Adam Machanic" wrote:

> Stored procedures can return multiple rowsets... Why not use two?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
>
>|||Sure...
CREATE PROCEDURE TWO_RESULT_SETS
AS
BEGIN
SELECT 1
SELECT 2
END
GO
EXEC TWO_RESULT_SETS
GO
DROP PROCEDURE TWO_RESULT_SETS
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
> Hi!
> Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to
> get
> once. Can I have some example of the same?
> Thanks
> Sachin
> "Adam Machanic" wrote:
>|||Thanks a lotl!
However, I know this. But i guess, the problem is perhaps, when I write 2
selects in the SP, if I am using ADODB.Recordset to retrieve the data, I
won't get the result of both the record set. Right?
So, can you suggest me how do I tackle that one? :)
Thanks again!
Regards,
Sachin
"Adam Machanic" wrote:

> Sure...
> --
> CREATE PROCEDURE TWO_RESULT_SETS
> AS
> BEGIN
> SELECT 1
> SELECT 2
> END
> GO
> EXEC TWO_RESULT_SETS
> GO
> DROP PROCEDURE TWO_RESULT_SETS
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
>
>|||Set rsSecond = rsFirst.NextRecordset()
cheers,
</wqw>

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KM
I realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The t
ext, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KMI realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]
The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can someone tell me whats wrong with this code?

Hi All

I keep getting back VINET and not Lilas...can someone point me in the
right direction?

Thanks a lot

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT',2)
WITH (

CustomerID varchar(100) )I'm not an expert on SQL Server and XML, but it seems to me that
OPENXML is very picky about the XML form that it will accept; can you
modify your XML statement to read like this?

SET @.doc ='
<ROOT>
<Customer>
<CustomerID>VINET </CustomerID>
</Customer>
<Customer>
<CustomerID>Lilas</CustomerID>
</Customer>
</ROOT>'

and then your OPENXML to statement to read like this:

SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (
CustomerID varchar(100) )

Don't know why it works, but it does.

Stu|||Try this...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/CustomerID',2)
WITH (

CustomerID varchar(100) '.')

EXEC sp_xml_removedocument @.iDoc|||Thank you Mark that works great!!!!
Can you explain to my why '.' needed to be included? I can't seem to
locate it in Books online...

Thanks again|||(reezaali@.gmail.com) writes:
> Thank you Mark that works great!!!!
> Can you explain to my why '.' needed to be included? I can't seem to
> locate it in Books online...

Books Online says about col-pattern:

Is an optional, general XPath pattern that describes how the XML nodes
should be mapped to the columns. If the ColPattern is not specified, the
default mapping (attribute-centric or element-centric mapping as
specified by flags) takes place.

'.' maps to the current node, which is /Root/CustomerID.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you...