Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts

Friday, March 30, 2012

How to get the whole DDL command from EVENT_INSTANCE

Hello,
I try to save the current DDL in a table using the trigger on database ddl
events.
As usual,
DECLARE @.data XML
SET @.data = EVENTDATA()
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
How can I extract more then 2000 chars? Should I use a system table or
function to retrieve all the DDL command? I have SPs whith tons of chars...
Thanks,
CatalinHow about, for instance:
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
> Hello,
> I try to save the current DDL in a table using the trigger on database ddl
> events.
> As usual,
> DECLARE @.data XML
> SET @.data = EVENTDATA()
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
> How can I extract more then 2000 chars? Should I use a system table or
> function to retrieve all the DDL command? I have SPs whith tons of chars..
.
> Thanks,
> Catalin|||Thanks, but i have SPs with probably 40k chars or more... Neither varchar
(8000) is enough...
"Tibor Karaszi" wrote:

> How about, for instance:
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in messag
e
> news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
>|||Did you try nvarchar(max)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:989EED5F-D2F0-44EB-A43E-A2FB23BB9B5B@.microsoft.com...
> Thanks, but i have SPs with probably 40k chars or more... Neither varchar
> (8000) is enough...
> "Tibor Karaszi" wrote:
>|||Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'
t
tell me that it was also available on SQL 2000...)
I am so deceived about me... After 8 years of SQL I will have to start again
from ABC... Sometimes I am so busy to find complex solutions and I am not
able to see the simplest one.
Thanks again|||> Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'ted">
> tell me that it was also available on SQL 2000...)
The max datatypes are indeed new to 2005. Consider them as replacements for
the less than user
friendly text, ntext and image datatypes.

> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
This happens to all of us. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:01DD8998-1C93-43E2-AA9A-F82694049866@.microsoft.com...
> Thank you, I had no ideea about (max) implementation on 2K5... (Please, do
n't
> tell me that it was also available on SQL 2000...)
> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
> Thanks again

Friday, February 24, 2012

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:uem$6elfGHA.5088@.TK2MSFTN
GP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> glsD:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Image (and text/ntext) data types cannot be declared as local variables.
>> These are mostly intended to be transferred to/from application code
>> directly.
>> What do you plan to do with the image value in the proc? You can use
>> SUBSTRING to assign 'chunks' of the image value to a local varbinary
>> variable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "angi" <angi@.news.microsoft.com> wrote in message
>> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>>
>