Showing posts with label save. Show all posts
Showing posts with label save. 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

Monday, March 19, 2012

How to get status of sp_executesql

Hello

The basic requirement is to import data from excel sheet and save it to a table. The excel filename and sheet name is passed in as a parameter to a stored procedure. I am using sp_executesql to execute the statement.

First I delete all the records from the table and then copy the data from excel into the table. I want to do this as a transaction so that if there is any error in the import process, I want to rollback the transaction otherwise commit it. sp_executesql returns 0 for success or 1 for failure.

In my case, even if there is any syntax error, I get the status of 0. Also the @.@.ROWCOUNT contains some positive number greater than 0 even if some error occurs.

BEGIN TRANSACTION T1

GO

DELETE FROM SORTGROUP

GO

DECLARE @.Statement as NVARCHAR(1024)

DECLARE @.TableName as VARCHAR(32)

DECLARE @.ExcelFile AS VARCHAR(512)

DECLARE @.ParamNameList as VARCHAR(1024)

DECLARE @.SheetName as VARCHAR(64)

DECLARE @.Status as INT

DECLARE @.ErrorCode as INT

DECLARE @.RowCount AS INT

SET @.TableName = 'SORTGROUP'

SET @.ExcelFile = 'E:\PROJECTS\RALEIGH\TEST.XLS'

SET @.ParamNameList = '*'

SET @.SheetName = 'SORTGROUPDATA'

EXEC master..xp_sprintf @.Statement OUTPUT, 'INSERT %s SELECT %s FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE=%s'', ''SELECT %s FROM [%s$]'')', @.TableName, @.ParamNameList, @.ExcelFile, @.ParamNameList, @.SheetName

SET @.Status = -1

exec @.Status=sp_executesql @.Statement

SELECT @.ErrorCode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT

IF @.Status = -1

ROLLBACK TRANSACTION T1

ELSE COMMIT TRANSACTION T1

PRINT @.ErrorCode

PRINT @.RowCount

PRINT @.Status

Is there any reason why you're NOT using DTS to do this? The work flow management will handle all of that for you

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish