Default data/log path

Hi,

Is there a SQL stored procedure or command to retrieve the server's default path for data and transaction log files?

Thanks.

Part of the Profiler trace created by opening the New Database dialog:

declare @.RegPathParams sysname

declare @.Arg sysname

declare @.Param sysname

declare @.MasterPath nvarchar(512)

declare @.LogPath nvarchar(512)

declare @.ErrorLogPath nvarchar(512)

declare @.n int

select @.n=0

select @.RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'

select @.Param='dummy'

while(not @.Param is null)

begin

select @.Param=null

select @.Arg='SqlArg'+convert(nvarchar,@.n)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @.RegPathParams, @.Arg, @.Param OUTPUT

if(@.Param like '-d%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.MasterPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-l%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.LogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-e%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.ErrorLogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

select @.n=@.n+1

end

SELECT

@.MasterPath AS [MasterDBPath],

@.LogPath AS [MasterDBLogPath]

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
Vitaliy
I'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan
|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last
>
|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>
|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first p
lace, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl..
.
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
..
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
..
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last[vbcol=seagreen]
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the cu
rrent name of the
constraint. You can then use dynamic SQL to drop the constraint. And then ad
d it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations whe
n
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>sql

Decoding Decimal Form of HRESULT from ErrorCode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to another OLE-DB Command transformation that logs the error on that row to a "failed rows" table. In this table I log the ErrorCode and ErrorColumn values that come with the error path from the first OLE-DB Command.

OK, that's all working great. However, here's the kicker: there's no error description value. The ErrorCode value, naturally, is the decimal form of an HRESULT--for example, -1071607696. Without some further information, however, this code is not useful for troubleshooting.

Has anyone figured out a trick here? I'm not even certain that this is an SSIS HRESULT, since it could for all I know be from the OLE-DB layer, the database layer, or somewhere else.

Thanks,
Dan

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209070.html

The DescribeRedirectedErrorCode method may also be of some use here. Not sure what you actually expect to do, but normally I would log the full error via another means, such as the built in SSIS logging. Use that for the text description. The code would alllow you to automate handling of different error scenarios.

|||Hi Darren,

Thanks for the link to that error info (I had actually found that subsequent to my original post with some additional searching), and for the pointer to DescribeRedirectedErrorCode. I did not know about the existence of this method. It's also interesting to find out that this is an SSIS HRESULT even though the error pertains to a foreign key constraint violation in the database layer--is SSIS re-interpreting the original SQL Server exception? I wonder whether an ErrorCode will always be a native SSIS error code...?

You refer to logging the full error via another means. I get the feeling that I'm missing an opportunity here to be logging a row-level error in a data flow in a different way than I am now. I'd obviously prefer to log the full error info instead of just ErrorCode and ErrorColumn. However, I don't see how this would work.

Do you redirect the row first through a script component so that you can programatically use the ErrorCode to call DescribeRedirectedErrorCode for additional info for the subsequent logging?

Or are you catching the error in the control of flow? How does that work exactly? Does a row-level exception in a data flow fire an error event at the control of flow level? I guess I was specifically trying to prevent that by using Redirect Row from my OLE-DB Command transform--I just want to log the problem with that row and keep moving through the rest of the rows...

Thanks,
Dan

|||

You may also be interested in taking a look at "Enhancing an Error Output with the Script Component," which was new in the December drop of BOL.

Also, be aware of the "Integration Services Error and Message Reference" list which includes the HRESULT in hex. As for converting (in code), although I don't have the code that I used to create the list in front of me right now, I believe there are format specifiers that you can use with .ToString() to convert quite simply between decimal and hexadecimal representations.

-Doug

|||That was exactly what I needed, Douglas, thank you. I am going to use that trick on future error pathways. Hopefully a future release of SSIS will make this unnecessary by adding an intrinsic ErrorDescription column to go along with ErrorCode and ErrorColumn.

Here is the link for those who'd like to read the article:

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

Has anyone else noticed that Google's URLs pointing to MSDN articles have a "(d=robot)" in them, so that when you click from Google to MSDN the article shows up with no styling or sidebar navigation. Example:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms345163.aspx

I've noticed it doing this the last couple days.

Thanks again,
Dan

|||

Doug,

What is the difference between GetErrorDescription and DescribeRedirectedErrorCode, they both seem remarkably similar, apart from the hosting class. Context maybe?

How does using GetErrorDescription like this know about the upstream component that raised the error? Surely it needs to know, since if as a component author I generate my own error codes, I would then override DescribeRedirectedErrorCode to give you the description, but how do you call my implementation?

|||

Darren,

A complete answer will need to come from the dev team. The methods seem to do the same thing, as you observed - get a description from an error code. I suspect that this works only with Integration Services errors and messages, and that it is made possible (or easier) by the fact that all of these are consolidated in the managed Microsoft.SqlServer.Dts.Runtime.HResults class. I'll see what I can find out.

-Doug

|||

You can also use my enhanced error component to add the column name of the column that failed to the error output.

I guess I should add the error description as well

Declaring Large Variables In SProc

Hello
I am trying to use the OPENXML command within a sproc to parse an xml
document and save the data to a table.
The xml document is saved in a different table in a field with data type
TEXT.
To parse the document, I first have to prepare it by calling ...
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
... where @.doc is the variable holding the xml document
I am trying to load the xml document into the local @.doc variable by ....
SELECT @.doc = xml_data FROM XMLTABLE
My problem is the xml document is 12-13k long so I can't declare the @.doc
variable as a varchar because a varchar can only be declared to a maximum of
8000 bytes and I cant declare it as a text datatype because local variables
can't be declare as text.
So.....what do I do?
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Peter,
Take a look at the following in BOL
TEXTPTR, WRITETEXT and UPDATETEXT.
You will first need to get a pointer to the Text field and then use the
other two functions to manipulate the content like so
declare @.ptr binary(16)
SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
kevin
"Peter" wrote:

> Hello
> I am trying to use the OPENXML command within a sproc to parse an xml
> document and save the data to a table.
> The xml document is saved in a different table in a field with data type
> TEXT.
> To parse the document, I first have to prepare it by calling ...
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> ... where @.doc is the variable holding the xml document
> I am trying to load the xml document into the local @.doc variable by ....
> SELECT @.doc = xml_data FROM XMLTABLE
> My problem is the xml document is 12-13k long so I can't declare the @.doc
> variable as a varchar because a varchar can only be declared to a maximum
of
> 8000 bytes and I cant declare it as a text datatype because local variable
s
> can't be declare as text.
> So.....what do I do?
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||Thanks Kevin.
I have looked up what you suggested but still can't work out how it helps me
(it is 6.30am and I havn't been to bed yet).
I have retieved the pointer as you outlined but then what do I do with it?
How do I pass the contents of that pointer to the sp_xml_preparedocument
procedure?
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
> Peter,
> Take a look at the following in BOL
> TEXTPTR, WRITETEXT and UPDATETEXT.
> You will first need to get a pointer to the Text field and then use the
> other two functions to manipulate the content like so
> declare @.ptr binary(16)
> SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
> kevin
> "Peter" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
Excuse me if I double posted.
You can pass the pointer into the proc and then use the UPDATETEXT function
to manipulate the text in the xml. I would personally pull that text back t
o
code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you hav
e
some plan for that. the following is an example
******************************
--the table and some data
CREATE TABLE dbo.kevtest (
[int] bigint IDENTITY (1, 1) NOT NULL ,
myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
--the sp
ALTER proc dbo.usp_testptr
@.p binary(16)
as
updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
--the TSQL
declare @.ptr binary(16)
select * from dbo.kevtest
select @.ptr = TEXTPTR(mytext) from dbo.kevtest
EXEC dbo.usp_testptr @.ptr
select * from dbo.kevtest
******************************
kevin
"Peter" wrote:

> Thanks Kevin.
> I have looked up what you suggested but still can't work out how it helps
me
> (it is 6.30am and I havn't been to bed yet).
> I have retieved the pointer as you outlined but then what do I do with it?
> How do I pass the contents of that pointer to the sp_xml_preparedocument
> procedure?
> Peter
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||That should be
INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
"kevin" wrote:
> Peter,
> Excuse me if I double posted.
> You can pass the pointer into the proc and then use the UPDATETEXT functio
n
> to manipulate the text in the xml. I would personally pull that text back
to
> code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you h
ave
> some plan for that. the following is an example
> ******************************
> --the table and some data
> CREATE TABLE dbo.kevtest (
> [int] bigint IDENTITY (1, 1) NOT NULL ,
> myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
> --the sp
> ALTER proc dbo.usp_testptr
> @.p binary(16)
> as
> updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
> --the TSQL
> declare @.ptr binary(16)
> select * from dbo.kevtest
> select @.ptr = TEXTPTR(mytext) from dbo.kevtest
>
> EXEC dbo.usp_testptr @.ptr
>
> select * from dbo.kevtest
> ******************************
> kevin
>
> "Peter" wrote:
>|||Thanks Kevin
I appreciate your time and effort here but the code you have supplied works
because the sproc you have created (usp_testptr) expects a pointer (or at
least a binary(16) value).
However, the sproc I need to use (sp_xml_preparedocument) expects a char,
varchar or text field so I cannot pass the pointer to it.
I may be totally and not seeing the forest for the trees here but I
still cant get you code to work in my circumstance.
Kind Regards
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
> That should be
> INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
>
> "kevin" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
[forum gurus please correct me if I am wrong!]
My suggestion is an alternative. Unless you can be certain that the xml
string will be less than 8000 characters you can't pass it to that sproc.
What I am suggesting is a way you can tell the sproc how to find the text
containing the xml so that it can manipulate it.
There are system stored procedures that you can use to read from a file, but
even then the sproc will still be limited to holding the xml in a
varchar(8000) as you can't create text variable in a sproc... as you already
know.
Why can't you deal with the xml in code?
Has this sproc every worked as you expect it to, or is it in development?
Kevin
"Peter" wrote:

> Thanks Kevin
> I appreciate your time and effort here but the code you have supplied work
s
> because the sproc you have created (usp_testptr) expects a pointer (or at
> least a binary(16) value).
> However, the sproc I need to use (sp_xml_preparedocument) expects a char,
> varchar or text field so I cannot pass the pointer to it.
> I may be totally and not seeing the forest for the trees here but
I
> still cant get you code to work in my circumstance.
> Kind Regards
> Peter
>
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>

Debugging SQL

Hi All
win 2k(pro0 sql server 2k (dev ed) asp-vbscript
I'm still learning how to code in SQL... I've found the "Debug"
command in QA for debuging stored procs but I cant seem to debug other
stuff... functions, triggers etc. I cant even debug code in the QA
that I write to test before I put the final version in a store proc.
Is there anyway of debugging at least the batch sql code I write in
the QA before I put it in the store proc... any 3rd party software if
cant be done in QA? or do I have to write it and put it in a stored
proc then debug it? how do you pros do it?
thanks for any info.
AlYou can debug triggers is you execute the DML statement (Insert, Update or
Delete) that fires the trigger inside a SP - then you can debug the SP. The
same is true for a UDF - call if from a SP. VS.NET IDE has debugger as well.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Harag" <harag@.softhome.net> wrote in message
news:v703mvgc4h59u8hcn0d32k8fk8p777kle0@.4ax.com...
> Hi All
> win 2k(pro0 sql server 2k (dev ed) asp-vbscript
> I'm still learning how to code in SQL... I've found the "Debug"
> command in QA for debuging stored procs but I cant seem to debug other
> stuff... functions, triggers etc. I cant even debug code in the QA
> that I write to test before I put the final version in a store proc.
> Is there anyway of debugging at least the batch sql code I write in
> the QA before I put it in the store proc... any 3rd party software if
> cant be done in QA? or do I have to write it and put it in a stored
> proc then debug it? how do you pros do it?
> thanks for any info.
> Al

Debugging in SSIS - Immediate and Command windows

Hi everyone,
I am having a difficult time debugging a package that I'm working on. I read in BOL that the immediate window should be an option during debugging but I can't find it anywhere(nor can I enable it), and I was planning on using it to access an oledb source property that is using an expression. Is the command window the same as immediate? I didn't orignally think so but I'm not sure. What is the syntax to use for this once I find it?
Thanks,
Adrian

You can't access runtime values of an oledb source property but you can see what data going downstream using visualizers.

Command window isn't supported.

Can you give me a ref where in BOL you found about immediate window?

|||Nick, thanks for responding, I didn't see that anyone had responed until now.
I found it here:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/54a458cc-9f4f-4b48-8cf2-db2e0fa7756c.htm

Adrian