Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts

Friday, March 23, 2012

how many times a DML was executed ?

how can I find out , how many times a SQL statment was executed ?
can it be done using enterprise Manager , T-sql scripts ?
(same as V$sql.EXECUTIONS in oracle)
10x in advanceLook in Books-On-Line at system table syscacheobjects and column usecounts. I believe that once the object is swapped out of cache and brought back in the count will be initiated to zero.

Example script
SELECT SUBSTRING(master..syscacheobjects.sql,1,35) AS SQL,
master..syscacheobjects.cacheobjtype AS CacheObjectType,
master..syscacheobjects.objtype AS ObjectType,
SUBSTRING(master..sysdatabases.name,1,25) AS DatabaseName,
master..syscacheobjects.pagesused,
master..syscacheobjects.usecounts AS [Number of times used since inception],
master..syscacheobjects.sqlbytes AS [Length of name or batch submitted (in bytes)],
SUBSTRING(master..sysusers.name,1,15) AS Creator
From master..syscacheobjects
INNER JOIN
master..sysdatabases
ON master..syscacheobjects.dbid = master..sysdatabases.dbid
INNER JOIN
master..sysusers
ON master..syscacheobjects.uid = master..sysusers.uid
ORDER BY
master..sysdatabases.name

Wednesday, March 7, 2012

How insert more then 4000 characters?

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:

nvarchar(MAX)

This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).

varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image

|||

Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:

To modify a XML document this way is too slow, do you know anther way?

|||

You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.

Best regards

Michael

How insert more then 4000 characters?

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:

nvarchar(MAX)

This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).

varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image

|||

Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:

To modify a XML document this way is too slow, do you know anther way?

|||

You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.

Best regards

Michael

Friday, February 24, 2012

How I post a message on MSMQ?

Hi,
I want to execute a T-SQL for adding a message on a message queue, I know
that I can create a DTS task but I want to include it on a trigger. I found
extended procedures like xp_createprivatequeue and so on but no procedure to
push a message, Also I know that I can create my own xp to do it but I was
wondering if there is a simpler way,
thanks!
Salva
Salvador Alvarez Patuel
salvador.alvarez@.exony.com
Exony Ltd - London, UKHi
That is an undocumented XP and you should not use it.
SQL Server has no built-in methods to acces MSMQ. That belongs in a
middle-tier or your application.
Regards
Mike
"Salvador" wrote:

> Hi,
> I want to execute a T-SQL for adding a message on a message queue, I know
> that I can create a DTS task but I want to include it on a trigger. I foun
d
> extended procedures like xp_createprivatequeue and so on but no procedure
to
> push a message, Also I know that I can create my own xp to do it but I was
> wondering if there is a simpler way,
> thanks!
> Salva
> --
> Salvador Alvarez Patuel
> salvador.alvarez@.exony.com
> Exony Ltd - London, UK