execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.com
Hi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor
|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com
|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
---
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example belo
w.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

Exec/Query across servers (unlinked)

Can I exec/query across unlinked servers that are on the network but
physically different server instances? If so, how, please?
TIA!
RobertTake a look at OPENROWSET and OPENDATASOURCE in Books On line
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You can use OPENROWSET
SELECT *
FROM OPENROWSET('SQLOLEDB','otherinstance';'u
ser';'pwd',
'SELECT * FROM somedb.dbo.mytable')|||My bad...
I wasn't using fully qualified naming. I was using something like
ABC123.dbo.TableName instead of DEV.ABC123.dbo.TableName.
Thanks!
Robert
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:OKtDvGkXGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Can I exec/query across unlinked servers that are on the network but
> physically different server instances? If so, how, please?
> TIA!
> Robert
>

exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

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

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

EXEC(@SQL) And Unicode Result Bug

I've got a dynamic SQL query that is generated inside a SP, and is run using
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
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|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T

exec xp_sendmail error on SQL Server 2000

I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.

Here is the trigger creation script and error message

use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO

CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)

set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'

exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Appreciate any prompt reply.

JDbefore we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.

Thanks very much for your reminders
I really didn't think about these.

Only one record is inserted at a time.
There is no much transaction, just something like error notification.

mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?

How to fix the permission issue?

Thanks for your help!!

JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).

Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.

That said,

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.

Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?

Regards,

hmscott|||hi hmscott,

Thanks for helping me away such dangerous condition.

It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.

Can you provide any reference for SMTP usage?

But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.

I did exec master.dbo.xp_startmail.

master.xp_startmail was in the error message when firing the trigger.

Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...

As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.

Also, did you check for the existence of the master.dbo.sp_startmail proc?

Regards,

hmscott

Exec time for a query to run in QA?

Hi All
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

GO

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

GO

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

go

AMB

sql

Exec sproc in a update function

Folks

Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity ....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.

update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID

FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.

ThanxUse (create) function instead of sp in this case.|||Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx|||Originally posted by kir441
Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx

What about this draft?

drop table test
drop table test2
create table test(id int)
create table test2(id int, code varchar(10))
go
insert test values(1)
insert test values(2)
insert test values(3)
insert test2 values(1,'a')
insert test2 values(2,'b')
insert test2 values(3,'c')
go
CREATE FUNCTION getit(@.id int)
RETURNS varchar
AS
BEGIN
declare @.ret varchar(10)
select @.ret=code from test2 where id=@.id
RETURN @.ret
END
GO
select *,dbo.getit(id)
from testsql

EXEC sp_attach_db have a error

I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'DS2004',
@.filename1 = N'd:\ds2004_data.mdf',
@.filename2 = N'd:\ds2004_log.Ldf'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:\ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanksI believe the message is trying to tell you that the PageAudit property of the database file header property in ds2004_data.mdf is bad. I think your command is okay, but you have a corrupted file.|||Maybe you're trying to detach a SQL 2k db and restore to SQL 7.0? It doesn't work. Attach or restore a sql 2000 database to version 7.0 is not supported.

Exec replacement

I have a large query that needs to run .
This query has a lot of dinamic parameters ( i.e. wich are send from
the aplicattion ie (1,2,3)).
There are NO select rights granted on the table for the application
role so i need to run a stored proc.
If I put exec in the stored proc i get a error message saying I do not
have the rights. How should I go around and replace exec. I need a
efficient solution.
Thank you,Perhasps you need to create a UDF . Or grant SELECT/UPDATE/INSERT/DELETE
permissions on underlying tables
<petcu.bogdan@.gmail.com> wrote in message
news:1127908009.777329.306010@.g14g2000cwa.googlegroups.com...
>I have a large query that needs to run .
> This query has a lot of dinamic parameters ( i.e. wich are send from
> the aplicattion ie (1,2,3)).
> There are NO select rights granted on the table for the application
> role so i need to run a stored proc.
> If I put exec in the stored proc i get a error message saying I do not
> have the rights. How should I go around and replace exec. I need a
> efficient solution.
> Thank you,
>|||On 28 Sep 2005 04:46:49 -0700, petcu.bogdan@.gmail.com wrote:

>I have a large query that needs to run .
>This query has a lot of dinamic parameters ( i.e. wich are send from
>the aplicattion ie (1,2,3)).
>There are NO select rights granted on the table for the application
>role so i need to run a stored proc.
>If I put exec in the stored proc i get a error message saying I do not
>have the rights. How should I go around and replace exec. I need a
>efficient solution.
>Thank you,
Hi petcu,
Check out the site below. It explains why you can't use dynamic SQL with
your security setup, and also gives alternatives for all common uses for
dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, withi
n that stored procedure a SQL statement is built and then executed using the
EXEC command. It looks like that when the generated statement exceeds a ce
rtain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement
start time but no end time. I have also logged every step in the stored pr
ocedure and it just bails on me. Any ideas?Funny you mention that Peter, I run the stored procedure in QA and it runs f
ine. The funny thing is that when the stored proc fails, the rest of the pro
cess completes. I have not been able to get access to the .Net code to view
how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that
stored procedure a SQL statement is built and then executed using the EXEC command.
It looks like that when the generated statement exceeds a certain time threshold,
the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start
time but no end time. I have also logged every step in the stored procedure and it
just bails on me. Any ideas?|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||Andrew, that is what I am thinking too. I have finally gotten a hold of the
code the developer uses to call this. Can any of you see anything wrong wi
th this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initi
al Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in th
e
> stored procedure and it just bails on me. Any ideas?
>
>|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]sql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the stored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?How are you executing the original SP, and have you tried
it in QA ?
>--Original Message--
>I am running into a situation where a program runs a
stored procedure, within that stored procedure a SQL
statement is built and then executed using the EXEC
command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure
exits without any warning.
>I know there is no warning because I have run profiler
and I get a statement start time but no end time. I have
also logged every step in the stored procedure and it just
bails on me. Any ideas?
>.
>|||Your connection timeout setting is probably too low. Try adjusting it.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
--
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
> > Your connection timeout setting is probably too low. Try adjusting it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <Dan@.discussions.microsoft.com> wrote in message
> > news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> > > I am running into a situation where a program runs a stored procedure,
> > within that stored procedure a SQL statement is built and then executed
> > using the EXEC command. It looks like that when the generated statement
> > exceeds a certain time threshold, the stored procedure exits without any
> > warning.
> > >
> > > I know there is no warning because I have run profiler and I get a
> > statement start time but no end time. I have also logged every step in
the
> > stored procedure and it just bails on me. Any ideas?
> >
> >
> >

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
Funny you mention that Peter, I run the stored procedure in QA and it runs fine. The funny thing is that when the stored proc fails, the rest of the process completes. I have not been able to get access to the .Net code to view how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?
|||Andrew, that is what I am thinking too. I have finally gotten a hold of the code the developer uses to call this. Can any of you see anything wrong with this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initial Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in the
> stored procedure and it just bails on me. Any ideas?
>
>
|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]

exec in user-defined function

Hi,

How can I do dynamical exec to query in user-defined function? At the end I need to return the result.

Thank's
Alexei

You are not allowed. That's by design. Refer to BOL -> CREATE FUNCTION for more details.

exec in a function

I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks muchI don't think you can do Dynamic SQL in a user defined fuction.

Tim S

exec an SP to return rows to another SP

Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??

Something like...

CREATE procedure spThisSP(@.param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @.param)

CheersYou could re-write the sub proc as a function|||Thanks for the reply pkr but I was hoping that I could re-use stored procedures somehow?
Duplicating the code one way or another seems "wrong"...

Cheers|||You could use a temp table...
select into #temp
exec proc1

select * from #temp

But I HATE temp tables.|||i didnt understand what you are trying to do but i know you can call a stored proc from another stored proc..

hth|||pkr,

But I HATE temp tables.

May I ask why? I'm not being critical, just trying to understand various people's like and dislike of temp tables.

Thanks,
Don|||My main hate is a really irrating bug/feature I've hit in the past where the db is convinced the temp table already exists when you try to create it but says it doesn't exist when you try to use it!!

Besides that, there are too many locking issues with tempDb that make it very difficult to effectively police developer code - IMO. I just try to avoid them wherever possible.|||Cool, thanks.

Don|||Thanks for all the replies. From your discussion I found thisarticle which provides some more helpful pointers in using temp tables.

exe to export reports to pdf

Hi,
We have a need to create something like a win app that would be scheduled to
run on the web/reporting server, query the db, pass some parameters to
reports and auto-generate them in pdf format (to be stored in db or on the
same box) to make them available for the website visitors.
Have anybody done something like this before or seen an example of? We tried
to do something similar when reporting services when it just came out but
were unable to implement. Would really appreciate any advice on how to
tackle this!Thanks! Are you using data-driven subscription feature for this? Is it only
available with Enterprise SQL edition?
"Wickherm" <Wickherm@.discussions.microsoft.com> wrote in message
news:3CF580B4-0CD1-4C37-8099-2B65FBF846B2@.microsoft.com...
> We use the subscriptons in RS to schedule reports to save to a file share.
> The parameters in the reports call outside dll's to get the values.
> "ilona" wrote:
>> Hi,
>> We have a need to create something like a win app that would be scheduled
>> to
>> run on the web/reporting server, query the db, pass some parameters to
>> reports and auto-generate them in pdf format (to be stored in db or on
>> the
>> same box) to make them available for the website visitors.
>> Have anybody done something like this before or seen an example of? We
>> tried
>> to do something similar when reporting services when it just came out but
>> were unable to implement. Would really appreciate any advice on how to
>> tackle this!
>>

excuting xp_sendmail

When excuting the xp_sendmail in the Query analyzer, my mail gets send. When doing this in my code it doesn't work eventhough I explicitly use startmailn then xp_sendmail and then sp_stopmail? I think this is a problem of user rights? I gave my user as parameter but even then it didn't work. Any suggestions? I'm pretty sure, the used code is correct.

greetings,
Geoffhumm.

Make sure you configure both the server and the SQL Server Agent to send mail. You do this by specifying the mail profile and running a quick test.