Showing posts with label due. Show all posts
Showing posts with label due. Show all posts

Monday, March 12, 2012

Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

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

|||Thanks, I had to break the query into pieces to get this working.

Monday, February 20, 2012

Management Studio timeout problem when querying large table

Hi,

I have SQL Server Dev 2005 and I'm pulling my hair out due to timeouts when using the Criteria pane to query a large table. The timeout occurs after approximately 32 seconds.

I have changed all the timeout settings I can find/think of:

Server Query wait time of 59 seconds (has been much higher but made no difference)

Query execution timeout of 0

Designers | Analysis Service Designers | General | Query timeout = 0 Connection timeout = 0

Connection and execution settings on database are 0

I would expect the query to return around 50 rows from over 1,000,000 but I get:

SQL Execution Error

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

If I change the criteria to return significantly more rows, then it works no problem. Which timeout setting have I missed?

Thanks in advance,

Chinnybloke

In Management Studio do the following:

1) Select Tools -> Options

2) Expand the "Query Execution" node

3) Click on "SQL Server"

4) Check the value for "Execution time-out"

Also, you'll want to check the server settings:

1) Connect the Server

2) In Object Explorer right-click on the server and select "Properties"

3) Select the "Advanced" page

4) Check the value for "Query Wait" under "Parallelism" - you can check out Books Online for more information or clicking in the value field for "Query Wait" displays a little bit of contextual help at the bottom of the dialog.

Finally, the other thing to try is running the same query from sqlcmd. This will at least help you isolate if it's a Management Studio setting problem or a server setting problem.

Cheers,
Dan