SQL,tables in a DB: procedure

Showing posts with label procedure. Show all posts

Sunday, March 25, 2012

(newbie) - Stored Procedure Problem

Can anyone tell me what is wrong with this stored procedure?

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
ALTER TABLE tblUserGroups ADD @.Perm VARCHAR(1) NULL
GO

When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @.Perm'

I have checked the syntax for the ALTER command, and it looks correct to me...

This is my first day at using SQL Server in anger, so any help appreciated :)You must use dynamic sql for that, check out :

http://www.sqlteam.com/item.asp?ItemID=4599

The following will work

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
exec ('ALTER TABLE tblUserGroups ADD ' + @.Perm + 'VARCHAR(1) NULL')
GO|||Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me :)

I'm off to check your link now - thanks very much for taking the time to post.

Mark.sql

(local) vs. the actual name of SQL Server when using a named instance

Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,|||Referencing (local) implies that you are creating a new connection. This
connection won't know which instance the command comes from so will connect
to the default instance.
Is this bcp or osql? Can't think of another reason for it.
If you want to connect to the same instance from an sp then use @.@.servername
and put it in [].
"Leo" wrote:
> Hello,
> My question is this: in a named instance installation of a SQL Server, when
> you reference the server as (local) from a Stored Procedure that accesses a
> table for example, will that point to the Named Instance or will it access
> the table on the actual database on the main installation?
>
> Thank you
> leo
>
>|||I am actually running an Instance of SQL server on the same machine as an
installation of SQL server. The reason for that is QA testing. When I make a
change to the database I copy it to the instance os SQL on the same machine.
I am using store procedures to run DTS packages. So I am doing a DTSRUN
command on the package. On my SQL server I was using (local) as my server.
The reason I asked was that I didn't want to go into the Instance of the SQL
and change the name of the server on the stored procedures manually. I
wanted the stored proc to pick up the name of the server it;s running on
without manual changes to the code. I was affraid that if I leave that code
unchanged as (local) that the stored proc will look for the wrong DTS
package, meaning the one on the SQL server, not the one in the instance of
SQL. I am not sure if I made this more confusing or more explained.
in short I am trying to provide the samew functionality within one physical
sever using an installation of a SQL instance, as if it was two separate
physical servers, where you can reference (local) and whenever you more a
stored proc from one server to the other you do not need to change that code
to point to the right SQL server
Thanks
Leo
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:15408704-EE37-4A96-97D2-1BF39A6CE5AE@.microsoft.com...
> Referencing (local) implies that you are creating a new connection. This
> connection won't know which instance the command comes from so will
connect
> to the default instance.
> Is this bcp or osql? Can't think of another reason for it.
> If you want to connect to the same instance from an sp then use
@.@.servername
> and put it in [].
> "Leo" wrote:
> > Hello,
> >
> > My question is this: in a named instance installation of a SQL Server,
when
> > you reference the server as (local) from a Stored Procedure that
accesses a
> > table for example, will that point to the Named Instance or will it
access
> > the table on the actual database on the main installation?
> >
> >
> >
> > Thank you
> >
> > leo
> >
> >
> >

(local) vs. the actual name of SQL Server when using a named instance

Thursday, March 22, 2012

(2000) Unable to connect to debugger

On my development maching, I have three different SQL Server instances:

..\SQL2000
..\SQL2005EXPRESS
..\SQL2005

When i try to debug a stored procedure in Query Analyzer, I get the
following error:

"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disable
for connection 53."

Client side components _are_ installed. What gives? Any ideas?

J. Jespersen
DenmarkJeppe Jespersen (jdj krlledims jdj punktum dk) writes:

Quote:

Originally Posted by

On my development maching, I have three different SQL Server instances:
>
.\SQL2000
.\SQL2005EXPRESS
.\SQL2005
>
When i try to debug a stored procedure in Query Analyzer, I get the
following error:
>
"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side
components, such as SQLLE.DLL, are installed and registered on MYPC.
Ddebugging disable for connection 53."
>
Client side components _are_ installed. What gives? Any ideas?

Which service pack of SQL 2000 are you running, and which OS do you
have?

If you are running Windows XP SP2, you should upgrade to SP4 of SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

(2000) Unable to connect to debugger

On my development maching, I have three different SQL Server instances:
.\SQL2000
.\SQL2005EXPRESS
.\SQL2005
When i try to debug a stored procedure in Query Analyzer, I get the
following error:
"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect
to debugger
on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disable
for connection 53."
Client side components _are_ installed. What gives? Any ideas?
J. Jespersen
DenmarkThe files might be corrupted or you don't have permission for the program. T
o
check the permission check the sp_sdidebug.
"Jeppe Jespersen" wrote:

> On my development maching, I have three different SQL Server instances:
> ..\SQL2000
> ..\SQL2005EXPRESS
> ..\SQL2005
> When i try to debug a stored procedure in Query Analyzer, I get the
> following error:
> "Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to conne
ct to debugger
> on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
> such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disabl
e
> for connection 53."
> Client side components _are_ installed. What gives? Any ideas?
> J. Jespersen
> Denmark
>
>
>

Monday, March 19, 2012

"Sometimes"- Error message

I've a SSIS Package, what executes a stored procedure, who calculates a lot of stuff with a recursive algorithm (TSQL). Sometimes the package aborts with this error message:

"Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The first few german words mean: "Syntax error, Security error or general error"...

If i restart the packages, the error usually does'nt appear again. All releated data are'nt changed in the meantime. I can not reproduce the the error and a think, it depends on the connection (but SSIS and SQL Server are running on the same virtual maschine). By the way, the executed stored procedure does not have any resultset what is given back...

Every idea is very welcome...

Torsten

Since you say there is a lot of complexity in the stored procedure and that it only happens sometimes, I wonder if it isn't something in the procedure causing the problem (a concurrency-induced deadlock, a random number causing a divide by zero, the recursion limit being reached, etc.)

I suggest you try running a profiler trace and see if any errors are getting thrown by the procedure which aren't being reported by SSIS.
|||

Thanks for your message!

Running the profiler reduces the performance in a way, that it would take a fews days...

When talking about recusion limit, what is ment by it? In my recursion i've an hirarchie with not more then 10 levels. the max-recursion option i've set to 500 and increased to 1000. Doesn't matter - i get the error sometimes like before... A division by zero is not possible, i'm only summerizing. A deadlock doesn't make sense, i'm not changing the data while running these process (and no other does).

But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?

"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "

Where is the place to increase the system limit of 64 threads? Sp_configure says following:

name min max config run_value

Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 3 3
affinity mask -2147483648 2147483647 3 3
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 1
allow updates 0 1 0 0
awe enabled 0 1 1 0
blocked process threshold 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1031 1031
default language 0 9999 1 1
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 4000 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 128 32767 128 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 16
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Web Assistant Procedures 0 1 0 0
xp_cmdshell 0 1 0 0

|||

Torsten_Katthoefer wrote:

But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?

"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "

Good grief! You must have a huge Data Flow. You need to break that up into multiple smaller data flows. Increasing the thread limit is not a good solution (even if it is possible). A raw file destination can be placed in a data flow to persist the pipeline to the disk, and then another data flow started with a raw file source to pick up where the other left off. I think you'll find your performance will be much better.
|||

Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.

It would like to increase the thread limit, only to observe these process. Is it possoble to do this?

|||

Torsten_Katthoefer wrote:

Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.

It would like to increase the thread limit, only to observe these process. Is it possoble to do this?

Well then something must be really messed up for SSIS to think it needs 107 threads. I don't know for sure, but I doubt you can raise that limit. If your package so simple, I would delete it and start over. Something in there is not right.

Tuesday, March 6, 2012

"FOR XML EXPLICIT" query Works on SQL 2000 but same does not...

We are using a stored procedure which uses FOR XML EXPLICIT and it works fine with SQL Server 2000 but doesnt work with SQL Server 2005. Can anyone help me out in understanding the reason behind such a behavior and any possible solution. Please find the details of the problem below:

The procedure runs fine in SQL 2000 the input xml and gives us the correct XML:

SELECT

1 AS TAG,
NULL AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
NULL AS [user!2!title!element],
NULL AS [user!2!firstname!xml],
NULL AS [user!2!lastname!xml],
NULL AS [user!2!login!element],
tUser.id AS [user!2!userID!element]

FROM TbUser AS tUser

INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id

UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
tUser.title_lookup_id AS [user!2!title!element],
tUser.firstname AS [user!2!firstname!xml],
tUser.lastname AS [user!2!lastname!xml],
tUser.login_name AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
order by [mailbox!1!mailbox-name!element], [user!2!userID!element]
FOR XML EXPLICIT

This runs perfectly fine in 2000 but in the 2005 the XML is not correctly formed .. i am not able to figure out why this issue is occuring. In 2005 tt gives me all the tags with the expected values but the sequence is not correct.

Thanks,

Gaurav

Can you give me a more specific case to reproduce your problem so I might help you to find the reason/solution?|||You'll need to give some sample data, but have you tried just adding ORDER BY to the queries. The data will be returned in no particular order if you don't specify ORDER BY, so if it is "correct" on 2000 but "incorrect" on 2005 that is purely by chance.|||

You asked the same question in sql server central. I answered your question there a week ago and don't know solved your problem or not. The answer is to change "tUser.id AS [user!2!userID!element]" to "NULL AS [user!2!userID!element]" in the first SELECT statement in the UNION.

Sunday, February 19, 2012

<Unsupported Data Type> ?

I have a problem in a store procedure....
the field isn't updated or touched...it's only readed with a nested select...

In the grid pane is showed :<Unsupported Data Type> as this..
http://www.base2.it/img/sql3.JPG

If I force a cast like this..
CAST(derivedtbl_1.Descrizione AS varchar(255)) the value is showed...

tha field in the original table source is definded as varchar(255).
http://www.base2.it/img/sql4.JPG

I don't have any idea... what's the problem ?

Could you share your stored procedure with us? It might help to see what it looks like.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||THIS FIRST RETURN CORRECT DATA...
1)
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)

2) SELECT IDMessaggio, Descrizione, IDLingua
FROM (SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)) AS MYTABLE

The only difference as You can see is an extern select 'cause I need to refer my query result with others table...

|||

Since this is really a generic T-SQL question I'm going to move this to the T-SQL forum.

- Mike

|||This is a tools issue (I am moving thread to the Tools forum). It has nothing to do with the query. Both queries are identical and they will produce same results. You have to provide a repro that demonstrates the problem.|||

Hello Bkl,

My team owns the tool and I would like to help. Is it possible for you to send me scripts that generates the base table and the stored procedure? Looks like the SQL statement you added does not really match the screen-shot. I would like to be able to reproduce your issue and see if it's a potential bug on the tool.

Thanks!

Thursday, February 16, 2012

>> Help with Stored Procedure

Hi, I'm so new to programming in MS SQL that the paint is still wet behind m
y
ears...
Using sql 2000 and vb.net (I normally develop in MS Access)
I have a search form set up so that users can enter any combination of the
following search criteria:
User types all or part business name,
User types all or part account name,
User selects from a ComboBox a business type
User types all or part contact name,
User selects from a ComboBox a contact type
I want to have a stored procedure that can handle any combinations of data
entry. The following is my guess for structure and it does not compile...
CREATE PROCEDURE GetSearchResult
@.Account nvarchar(10),
@.Business nvarchar(100),
@.BusinessTypeID Int,
@.ContactID Int,
@.Contact nvarchar(100),
@.ContactTypeID Int
DECLARE @.SearchOptions Int
DECLARE @.ErrorStatus Int OUTPUT
AS
IF LEN(@.Business)>0
SET @.SearchOptions = 1
IF LEN(@.Account) > 0
SET @.SearchOptions = @.SearchOptions + 2
IF @.BusinessTypeID > 0
SET @.SearchOptions = @.SearchOptions + 4
IF LEN(@.Contact) > 0
SET @.SearchOptions = 10
IF @.ContactTypeID > 0
SET @.SearchOptions = @.SearchOptions + 11
CASE @.SearchOptions
WHEN 1 THEN
-- Only search for Business name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
END
WHEN 2 THEN
-- Only search for Account name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @.Account + '%'
END
WHEN 3 THEN
-- Search for Business name and Account
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bAccount LIKE '%' + @.Account + '%'
END
WHEN 4 THEN
-- Only search for Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 5 THEN
-- Search for Business name and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 6 THEN
-- Search for Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @.Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 7 THEN
-- Search for Business name, Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bAccount LIKE '%' + @.Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 10 THEN
-- Only Search for Contact name
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @.Contact + '%'
END
WHEN 11 THEN
-- Only search for Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @.ContactTypeID)
END
WHEN 21 THEN
-- Search for Contact name name Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @.Contact + '%'
AND cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @.ContactTypeID)
END
END
GO
GRANT EXECUTE ON GetSearchResult TO PUBLIC
GO
Can you please give me advice regarding my approach to facilitating a search
form and corrections for more stored procedure
Many thanks
Jonathan ParminterIf the user doen't select an entry what gets passed in?
if null then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + coalesce(@.Account, '') + '%'
and Business like '%' + coalesce(@.Business, '') + '%'
and Contact like '%' + coalesce(@.Contact, '') + '%'
and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
and ContactID = coalesce(@.ContactID, ContactID)
and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
if empty string and 0 from the IDs then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + @.Account + '%'
and Business like '%' + @.Business + '%'
and Contact like '%' + @.Contact + '%'
and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
and (ContactID = @.ContactID or @.ContactID = 0)
and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)|||Nigel, excellant
--
Many thanks
Jonathan Parminter
"Nigel Rivett" wrote:

> If the user doen't select an entry what gets passed in?
> if null then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + coalesce(@.Account, '') + '%'
> and Business like '%' + coalesce(@.Business, '') + '%'
> and Contact like '%' + coalesce(@.Contact, '') + '%'
> and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
> and ContactID = coalesce(@.ContactID, ContactID)
> and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
> if empty string and 0 from the IDs then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + @.Account + '%'
> and Business like '%' + @.Business + '%'
> and Contact like '%' + @.Contact + '%'
> and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
> and (ContactID = @.ContactID or @.ContactID = 0)
> and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)
>|||Nigel,
Just curious as to the use of coalesce on the @.account, @.business, and
@.contact (given it wasn't the first thing that sprang to mind for me.
Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ?
Glenn|||Jonathan (Jonathan@.discussions.microsoft.com) writes:
> IF @.ContactTypeID > 0
> SET @.SearchOptions = @.SearchOptions + 11
> CASE @.SearchOptions
> WHEN 1 THEN
There is no CASE statement in Transact-SQL. There is a CASE *expression*
which can be used in WHERE clauses, SELECT lists or anywhere else you
can use an expression.
To achieve the same logic, you would have to use bunch of IF ELSE.
For a longer discussion on this particular problem - dynamic search
conditions - I have an article on my web site that discusses a number
of alternatives: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ? <<
COALESCE () is Standard SQL and takes a list of parameters. It is a
member fo the CASE expression family.
COALESCE correctly promotes its arguments to the highest data type in
the expression:
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

Saturday, February 11, 2012

#Temp Tables

Why cant I use the same temptable name i a stored procedure after i have droped it?

I use the Pubs database for the test case.

CREATE PROCEDURE spFulltUttrekk AS

SELECT *
INTO #temp
FROM Jobs

SELECT *
FROM #temp

DROP TABLE #temp

SELECT *
INTO #temp
FROM Employee

SELECT *
FROM #temp[posted and mailed, vnligen svara i nys]

Per (per-eivind-greva.sivertsen@.cgey.com) writes:
> Why cant I use the same temptable name i a stored procedure after i have
> droped it?
> I use the Pubs database for the test case.
> CREATE PROCEDURE spFulltUttrekk AS
> SELECT *
> INTO #temp
> FROM Jobs
> SELECT *
> FROM #temp
> DROP TABLE #temp
> SELECT *
> INTO #temp
> FROM Employee
> SELECT *
> FROM #temp

When SQL Server builds the query plan for a procedure, it builds the
plan for the entire procedure in one go, with one exception. If a
statement refers to a non-existing table, that statement is deferred
until run-time.

So when you create the procedure, SQL Server defers the plan for the
two SELECT statements. When execution hits the deferred statement, SQL
Server recompiles the procedure. And the entire procedure. So when it
finds a SELECT * INTO #temp, it thinks that's bad, because #temp does
already exist. At this point, the DROP TABLE statement has not been
executed, so SQL Server does not know that the table will go away.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

#temp table vs. @temp table variable in stored proc?

I have a complex stored procedure (it's the strip-packing one, Dan) that
uses several #temp tables along the way. There are typically 1000 to 50000
rows inserted into them during use. At the end of the procedure the final
results end up inserted into permanent tables. Concurrency is not a factor
here because the procedure is typically run only once a month.
My question is, could any performance improvement be gained by changing
them from #temp tables to @.temp table variables?http://www.aspfaq.com/2475
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jy8ydt3440yb$.dlg@.rpresser.invalid...
> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to
50000
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a
factor
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?|||Ross,
It is hard to tell you without knowing what kind of operations are you doing
with the temporary tables. SQL Server does not create statistics for table
variables, so for small number of rows this is a good option. The best way o
f
knowing this, is giving it a try and comparing performance results.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"Ross Presser" wrote:

> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to 5000
0
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a facto
r
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?
>|||Thank you both (Aaron, Alejandro) for the links. Given the large amount of
data, I think I'll stick with my #temp tables in this instance.|||yes, My experience is that with Small rowsets, the Table Variables are the
way to go.
TempTables, you can add indexes to, etc.
For large Rowsets, Temp Tables performed better in our situations.
Greg Jackson
Portland, OR

#temp query

I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.
I've tried something like the following with no success:
Select * from tempdb..#tempTableu can try these options

select * from #tempTable

select * from tempdb.#tempTable|||From BOL :

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

* A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

* All other local temporary tables are dropped automatically at the end of the current session.

* Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

---------

Hence try using ## (global temp tables), if you need to query outside the stored procedure.|||Can you give me an example in a select statement. The temporary table shows up in tempdb sysobjects as:

#Dup______________________________________________ __________________________________________________ ________________00000000001A|||example

use pubs
go
select * into ##temp from authors
select * from ##temp
--or
select * from tempdb.##temp
--or
select * from tempdb..##temp|||I get "invalid object name" trying any of these. I even tried it straight from the tempdb.|||Give your stored procedure which creates the temp table and explain how you have queried the same outside the procedure.

#Table not found error using Stored Procedure for DataSet

Hi all

I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.

Now when I am using this procedure as dataset to my report, it throws up the following error:

Invalid object Name "#TEMP2".

The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1

Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?

I am not sure should I posting it here or in T-SQL Forum.

Hi,

#Table is not #TEMP2.

Cannot help. Please be more specific. Post your code snippets.

Your data set should at least contains a first set of columns were the name is constant. Then if you need another suite of variable columns, you use another technique. Try to work this out one piece at the time.

Philippe

|||Generally due to limited scope Reporting Services rejects local temp table try changing to global temp table, if you are still getting the error you need to rewrite your stored proc. Hope this helps.|||

Ok here is my code snippet..

The first select statement gives me Customer names across the rows and Circles as columns and the number of associates in it mapped as a pivot table.

(there are actually some 17 odd circles...i am putting Circle 1 , Circle 2 for security reasons...)

Then after obtaining the results I am unpivoting it to meet my requriement i.e. to get only those columns that have the top two highest total and everything else clubbed as others.

I am using this procedure as my datasource for the report.

That is when it is throwing the error Invalid object name "#TEMP".

Caddre, could you tell me how i can change the local temp table to global temp table now that you know what my procedure is looking like..

I know it is a roundabout procedure of pivoting and then unpivoting it which i will try to post shortly on how exactly my db is structed but with the present one could you suggest me the solution..

Code Snippet

ALTER PROCEDURE [dbo].[MnE_usp_ViewSummary_Main]

Select CustomerName,ISNULL([Circle 1],0) AS [Circle 1],ISNULL([Circle 2],0) AS [Circle 2]

INTO #TEMP

FROM

(

SELECT 'CustomerName'= CASE

WHEN Lkp_CustomerGroup.CustomerGroup ='NA' THEN 'Pool'

ELSE ISNULL(Lkp_CustomerGroup.CustomerGroup,Lkp_CustomerGroup.CustomerGroup) END,Lkp_Circle.Abbreviation AS[Circle],ISNULL(COUNT(Tbl_User.ID),0) AS[Total]

FROM Tbl_Customer LEFT OUTER JOIN

Tbl_Project ON Tbl_Project.CustomerID=Tbl_Customer.ID LEFT OUTER JOIN

Tbl_Associate_Project ON Tbl_Project.ID =Tbl_Associate_Project.ProjectID LEFT OUTER JOIN

Tbl_User ON Tbl_User.ID=Tbl_Associate_Project.AssociateID LEFT OUTER JOIN

Tbl_UserDetails ON Tbl_User.ID=Tbl_UserDetails.AssociateID LEFT OUTER JOIN

Lkp_Circle ON Lkp_Circle.ID=Tbl_UserDetails.CircleID LEFT OUTER JOIN

Lkp_CustomerGroup ON Tbl_Customer.CustomerGroupID=Lkp_CustomerGroup.ID

WHERE Tbl_User.ID<>0 AND Tbl_UserDetails.AssociateID<>0 AND Tbl_User.IsActive='True'

GROUP BY Lkp_CustomerGroup.CustomerGroup,Lkp_Circle.Abbreviation,Tbl_Customer.Name

)SUB

PIVOT

(

SUM(Total) FOR

Circle IN ([Circle 1],[Circle 2])

)PivotTable

;

With UnPivoted

(

Select

Sum(Data) Over (Partition By Circle) Sumed

from

#TEMP

Unpivot

(

Data for Circle In ([Circle 1],[Circle 2])

) Pvt

)

, Ranked

(

Select *, DENSE_RANK() Over(Order By Sumed Desc,Circle) Rank From UnPivoted

)

select * INTO #TEMP2 from Ranked;

Declare @.Col1 as Varchar(100);

Declare @.Col2 as Varchar(100);

Select @.Col1 = Circle from #TEMP2 Where Rank=1;

Select @.Col2 = Circle from #TEMP2 Where Rank=2;

;

EXEC('Select

Isnull(CustomerName,''Total'') CustomerName,

''sortorder''=

CASE

WHEN CustomerName =''Pool'' THEN ''YYYYYYY''

WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''

ELSE CustomerName

END,

Sum(Case When Rank=1 Then Data End) as ['+ @.Col1+'] ,

Sum(Case When Rank=2 Then Data End) as ['+ @.Col2+'] ,

Sum(Case When Rank>2 Then Data End) Others,

''Total''=Sum(Data)

From

#TEMP2

Group By

CustomerName

With Cube

Order By

sortorder')

;DROP TABLE #TEMP;

Drop table #TEMP2;

|||A local temp table is # while global temp table is ## but you have two temp tables that is not valid you can only use one temp table in a stored procedure however you can ALTER the temp table many times. So change to global temp table and add one or two indexes and remember to ALTER your existing temp table instead of creating a new one.|||

Cddre, thanks for the suggestion but I haven't worked with indexes before. My procedure does need two temp tables as you have seen. Could you please alter the code and let me know. I tried dropping the #TEMP Table before the statement "select * INTO #TEMP2 from Ranked;" and renaming all the #TEMP2 instances to #TEMP so that there can only be one # table but it is throwing me the error " Incorrect syntax near the keyword "DROP" '

here is what i did with other remaining code above remaining the same..

Code Snippet

DROP TABLE #TEMP;

select * INTO #TEMP from Ranked;

Declare @.Col1 as Varchar(100);

Declare @.Col2 as Varchar(100);

Select @.Col1 = Circle from #TEMP Where Rank=1;

Select @.Col2 = Circle from #TEMP Where Rank=2;

;

EXEC('Select

Isnull(CustomerName,''Total'') CustomerName,

''sortorder''=

CASE

WHEN CustomerName =''Pool'' THEN ''YYYYYYY''

WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''

ELSE CustomerName

END,

Sum(Case When Rank=1 Then Data End) as ['+ @.Col1+'] ,

Sum(Case When Rank=2 Then Data End) as ['+ @.Col2+'] ,

Sum(Case When Rank>3 Then Data End) Others,

''Total''=Sum(Data)

From

#TEMP

Group By

CustomerName

With Cube

Order By

sortorder')

;

DROP TABLE #TEMP;

|||I did not tell you to drop the table just ALTER it, so print out the article from the link below and clean up your code. If that did not work then you have to use SELECT INTO to create a permanent table first then pass the results to a global ## temp table. Hope this helps.

http://www.informit.com/articles/printerfriendly.aspx?p=25288

|||

The point that has already been made by Caddre, and which I also believe is the source of your problem, is that the EXEC statement can't see your temp table because it's in the wrong scope. It would need to have two #'s (##TEMP or ##TEMP2) for it to be "global". When you do the EXEC you are outside the scope of the procedure that is creating your temporary table.

Look at it this way:

Suppose your EXEC statement read something like this :

Code Snippet

EXEC('Select ISNULL(Something, @.MyVal)')

... instead of :

Code Snippet

EXEC('Select ISNULL(Something,'+ @.MyVal+') ')

... now your procedure would complain that it could not find @.MyVal, and for exactly the same reasons. Do you understand this, and does it help you understand why your temp table is not found?

There are many ways to fix this -- and using a global temp table is only one, probably not the best -- but I want to make sure you see the scoping problem first.

OK, if you're "there", now let's think about how to fix it.

You might be able to get rid of the EXEC very easily, by adding a couple of columns indicating the column names,

like this (this is a hack and I'm not being very careful, I just want you to see what I mean:

Code Snippet

Select

Isnull(CustomerName,''Total'') CustomerName,

''sortorder''=

CASE

WHEN CustomerName =''Pool'' THEN ''YYYYYYY''

WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''

ELSE CustomerName

END,

Sum(Case When Rank=1 Then Data End) as Col1,

MAX(@.Col1) AS Col1Name,
Sum(Case When Rank=2 Then Data End) as Col2,

MAX(@.Col2) AS Col2Name,

Sum(Case When Rank>2 Then Data End) Others,

'Total'=Sum(Data)

From

#TEMP2

Group By

CustomerName

With Cube

Order By
sortorder

... now your report or your additional code looks at Col1Name and Col2Name to get whatever significant stuff you need to glean from the column names...

>L<

|||

Thanks Lisa for the reply but getting rid of the EXEC and using your method still is issuing the same error.. "Invalid Object #Temp" but my procedure as stand alone works fine when executed or when called from .Net applications.

Caddre,I have gone through the article and I understand that two # tables can't be used. The problem still remains when I use permanent table. Apart from that I don't know my column names before hand for altering my temp table that too in my case there are three fixed columns i.e. CustomerName,Total, Others and the rest only comes after executing the query. I am still running into the error. I don't know how else to clean up my code without knowing how it is that I am going to alter the temporary table #TEMP. Please help..

|||

Clearly, we can't properly see what your problem is <s>.

I do get that you don't know the column names in advance. I also get that you took this code more or less verbatim from another thread, where they are solving something similar to what you need to do. What I don't really know (sorry!) is:

* -- what parts of that solution are really significant to the original problem you were trying to solve and what parts may be artifacts of the original solution that don't really pertain to your problem-to-solve

* -- what is the significance of needing to know the column names for that original problem.

The need to get the top-ranked 2 columns and then a group of all other columns -- if this was your original goal -- should probably *not* require a pivot followed by an unpivot. If that is your actual problem to solve, can we start from the top please?

I have a real feeling that if, instead of having us come in the the middle (and I know you are invested, at this point, in something you've worked hard on and is "almost working" <s>), it would be better if you said something like this:

I have this schema with these tables (... insert CREATE TABLE statements or whatever description here...) and I need to pull out this information (... insert description of the table structure you're looking for in the report here...). What's the best way to do that?

Again, I'm sorry that we're not immediately coming up with a solution that will work for you but we are willing to try to think it out with you.

>L<

|||

I am sorry I pushed you all into middle of a mess and as you rightly said "something I have worked hard on and is 'almost working' " I didn't want to mess up. But for that I thought I would have to open seperate thread again to get them working. I guess I will define now what my requirement really is :

The tables that are involved with the necessary details

Tbl_Customer

ID Name

CUS1 ABC

CUS2 DEF

Tbl_Project

CustomerID ID NAME

CUS1 PRJ1 Project A

CUS2 PRJ2 Project B

CUS2 PRJ3 Project C

Tbl_UserDetails

PROJECTID ID NAME CircleID

PRJ1 USR1 User 1 0

PRJ1 USR2 User 2 1

PRJ2 USR3 User 3 2

Lkp_Circle ( there are 17 circles in total and the list is pretty dynamic)

ID Circle

0 Circle1

1 Circle2

2 Circle3

Now the output I want is as follows : (the numbers indicate the number of users for each customer account)

[Main Table]

CustomerName Circle1 Circle2 Circle3 .... Others Total

ABC 1 1 0 0 2

DEF 0 0 1 0 1

The link as you can see is like this Customer - Project - User - Circle.

The thing is since there are some 17 odd circles I want the list to be pretty dynamic and display only top 5 circles with their totals and the rest to be clubbed as others and a column called total to see the list as a whole. Well for that I initially pivoted to get the [Main Table] with all the circles corresponding to each customer. From there using unpivot I was trying to achieve the max condition. For clearer understanding you can just see this link where I gave a similar anolagy replacing Class in place of Circle.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1

Please let me know for any further information. Also could it be possible to extract the Circle names dynamically rather than hard-coding it.

|||

Hi again,

Thanks for your patience.

I don't have anything at hand to mock this up properly (and have to get to bed!!) so I will just try to describe what I would do to do this briefly and sketch it out with the first table at hand:

1. get your top ranked items, possibly using partition, although I won't do that here

2. union the above with another one that basically does WHERE NOT IN against the partition and aggregates all those rows

3. save the totalling for the matrix.

It should not matter how dynamic the circle list is, this should work...

I'm pretty tired and may screw this up, but you've made an effort, and you can see the idea with pretty much any data, so I'll give it a quick shot.

So, example. I have a notice queue table, I can find out the 5 dates that had the highest number of messages go through like this:

Code Snippet

SELECT TOP 5 COUNT(*) AS NumberOfNotices, Notice_Requested FROM S2S_Notices

GROUP BY Notice_Requested ORDER BY COUNT(*) DESC

... therefore I can show these five dates unioned with an an aggregate (clubbed) column showing all the other ones in one set like this:

Code Snippet

SELECT NumberOfNotices, NoticeRequested FROM

(SELECT TOP 5 COUNT(*) AS NumberOfNotices,

CAST(Notice_Requested AS VARCHAR) AS NoticeRequested

FROM S2S_Notices

GROUP BY Notice_Requested ORDER BY COUNT(*) DESC) ZZ

UNION ALL

SELECT NumberOfNotices,'All Others' As NoticeRequested FROM

(SELECT COUNT(*) AS NumberOfNotices FROM S2S_Notices WHERE Notice_Requested
NOT IN

(SELECT TOP 5 Notice_Requested FROM

(SELECT COUNT(*) AS NumberOfNotices, Notice_Requested FROM S2S_Notices

GROUP BY Notice_Requested) XX

ORDER BY NumberOfNotices Desc)) YY

My result for this sample looks like this:

Code Snippet

NumberOfNotices NoticeRequested 35386 Jan 10 20078:17AM 30666 Jan 30 20074:19AM 29768 Jan2 2007 11:13AM 20402 May 30 20065:18PM 17125 Aug 10 20061:31PM 362122 All Others

... do you see what I'm driving at? I realize that your case is more complex but basically what's going to happen is the matrix is going to take care of those column names the way it should, if we get the selects done right. And the matrix should do the total in its normal way, too.

Does this make any sense at all?

>L<

|||

Hi Lisa,

I get the drift, thank you. My problem is my end result is not linear as was in the case you described. I will just illustrate this. For getting the top 5 in the following format

Customer Name Circle Total

would be for given customer and given circle i would be getting the total say if there are 10 customers then 170 rows. You see in my final grouping that I get cumulatively I see highest total of all associates in circles across all the the customers then get my result ..something like this

CustomerName Circle1 Circle2 Circle3

abc 5 4 6

def 0 3 10

total 5 7 16

From the above supposing I need only the top 2 then I filter that out and query to get the appropriate result. Hope I am clear on this. It's okay , please take your time and don't spoil your sleep for replying .

Many thanks again

|||

Hi again,

At this point, it isn't so much sleep (I've given up on that <g>) as time -- don't have much time to think about this stuff during my work week <s>.

From my point of view, it shouldn't matter that my data or requirements are different/less complex. The point was to figure out something atomistic -- basically a placeholder for whatever you were filling your temp tables with.

From there, I wanted to illustrate how you would start from that inner most/determining SELECT, whatever it was, and build stuff "outward" to accomplish the whole thing, without a temp table, even if some operations had to be repeated in the nested process.

Right now, although I hear you saying that your data is "not linear", I'm not seeing the issue exactly with doing this with your data set. It can't just be the bottom line totals, because that shouldn't be a problem at all. So I have to CREATE TABLE, add rows, and figure out what wouldn't work.

I am not, unfortunately, the kind of person who easily "sees data" without working it out physically. My spouse/better half/work partner might be able to just see it from the data set, but I have to work it out <s>.

Maybe the problem is that I think you are talking about "top 2 circles" but you mean "top two customers"? That can't be it...

Maybe somebody else here will see what the critical point here and speak up. I will try this out later in the week or in the weekend if you haven't already grokked the answer.

>L<

|||Hi Lisa,

Have you figured out a solution for this or should I be posting it in the T-SQL Forums? Please let me know as there are some pending things that are left to do once I am ready with the dataset.