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
>

execption with store procedure

HI!

how do i make the store procudere only exec when new data inserted in the other table? recently i hav use sp_procoption to make an autoexecution.... but it will insert the new data together with the old data
any suggestion?

CREATE PROCEDURE [P1] AS

INSERT INTO TABLE1

SELECT sa.NoMatriks, sa.SesiSem,
SUM(Nilai * KreditSubjek) / SUM(KreditSubjek) AS CPA,
SUM(KreditSubjek) AS JumKredit,
p.Agama, p.Aliran, p.Negeri, p.Kaum
FROM TblSubjekAmbil sa, TblSubjek s, TblGred g, TblPelajar p
WHERE sa.NoMatriks = p.NoMatriks AND
sa.KodSubjek = s.KodSubjek AND
sa.Gred = g.Gred
GROUP BY sa.NoMatriks, sa.SesiSem,
p.Agama, p.Aliran, p.Negeri,
p.Kaum (SELECT sa.NoMatriks, t1.NoMatriks
FROM TblSubjAmbil sa, TABLE1 t1
WHERE t1.NoMatriks <> sa.NoMatriks)

exec sp_makestartup N'P1'

exec sp_makestartup N'P1'

exec sp_makestartup N'P1'Databases have a great concept, called trigger. Just define an INSERT trigger for your table, and let that trigger execute your code or procedure.sql

Exec Stored Proc

I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnNamenvarchar (50),
@.dtDate1 Date,
@.dtDate2 Date
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ' + ''' + @.dtDate1 + ''' + ' AND ' + ''' + @.dtDate2 + '''')i havent tried it but check if this works:

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName +
 ' BETWEEN ' + '''' + @.dtDate1 + '''' + ' AND ' + '''' + @.dtDate2 + ''''

hth|||Try this:

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ''' + @.dtDate1 + ''' AND ''' + @.dtDate2 + '''')|||How many possible values for @.strColumnName could there be? For performance and code reliability reasons I would write it like below. That way the SQL is precompiled (allowing the db engine to skip a step during execution and also allowing syntax errors to be spotted at the time of sp creation instead of execution.

ALTER PROCEDURE dbo.GetSearchByDateRange
(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as
 if (@.strColumnName = 'Birthdate')
 begin
 SELECT * FROM Customers
 WHERE Birthdate BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 if (@.strColumnName = 'LastPayment')
 begin
 SELECT * FROM Customers
 WHERE 'LastPayment'BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 ETC...

|||Thank you all for the infos.
Corbi I think you had a good point there and I will definatly take it in consideration.

Oh last thing how do I control the injection of a date from my code to the store proc if my db is using small dates? I see that it can cause problem if I don't send the right format to the sp.
Thanks again|||If possible, i would change the stored procedure parameters to "smalldatetime", process the different date formats in your application and convert your userinput to "smalldatetime" there.

Hth,

Moon|||the only things that need to be considered in the Execute(Whatever Valid SQL Query in string format) is to have double single qoutes for each date value in the query, and that those date values are converted to a valid string (this is because you need to concatenate a string, so try this

Declare @.SQLString As VarChar(8000)

Set @.SQLString = 'SELECT * FROM Employees WHERE ' + @.strColumnName + ' BETWEEN ''' + Convert(Char(8), @.dtDate1, 112) + ''' AND ''' + Convert(Char(8), @.dtDate2, 112) + ''''

Exec (@.SQLString)

This will Work fine

Delfino III Salinas Sepúlveda
DSS Hi-Tech, México
diiisalinas@.prodigy.net.mx|||Also use DateTime or smalldatetime types.

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florenceSQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florence
SQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

Exception - CXVariant::CopyDeep

hi, when i execute a store procedure, the sql server return a access eorror,
like:
2003-08-11 15:48:29.04 spid23 Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to C:\MSSQL7\log\SQL00012.dmp
2003-08-11 15:48:31.07 spid23 Error: 0, Severity: 19, State: 0
2003-08-11 15:48:31.07 spid23 SqlDumpExceptionHandler: Process 23
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
.
****************************************************************************
***
*
* BEGIN STACK DUMP:
* 08/11/03 15:48:31 spid 23
*
* Exception Address = 0041395A (CXVariant::CopyDeep + 59)
* Exception Code = c0000005 E
* Access Violation occurred reading address 00000000
* Input Buffer 1090 bytes -
* d e c l a r e @. P 1 v a r c h a r ( 2 5 5 )
* s e t @. P 1 = ' '
* d e c l a r e @. P 2 v a r c h a r ( 2 5 5 )
* s e t @. P 2 = ' '
* d e c l a r e @. P 3 v a r c h a r ( 2 5 5 )
* s e t @. P 3 = ' '
* d e c l a r e @. P 4 i n t
* s e t @. P 4 = 0
* e x e c s p _ ...
does anyone know why and how? thank a lot.
lee.Acess violations are generally caused by bugs in SQL Server code... Ensure
you are up to date on service packs, then open a call to MS PSS...
"lee" <freesearcher18@.hotmail.com> wrote in message
news:#pJwoqHYDHA.1280@.tk2msftngp13.phx.gbl...
> hi, when i execute a store procedure, the sql server return a access
eorror,
> like:
> 2003-08-11 15:48:29.04 spid23 Using 'sqlimage.dll' version '4.0.5'
> Stack Dump being sent to C:\MSSQL7\log\SQL00012.dmp
> 2003-08-11 15:48:31.07 spid23 Error: 0, Severity: 19, State: 0
> 2003-08-11 15:48:31.07 spid23 SqlDumpExceptionHandler: Process 23
> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is
> terminating this process.
> .
>
****************************************************************************
> ***
> *
> * BEGIN STACK DUMP:
> * 08/11/03 15:48:31 spid 23
> *
> * Exception Address = 0041395A (CXVariant::CopyDeep + 59)
> * Exception Code = c0000005 E
> * Access Violation occurred reading address 00000000
> * Input Buffer 1090 bytes -
> * d e c l a r e @. P 1 v a r c h a r ( 2 5 5 )
> * s e t @. P 1 = ' '
> * d e c l a r e @. P 2 v a r c h a r ( 2 5 5 )
> * s e t @. P 2 = ' '
> * d e c l a r e @. P 3 v a r c h a r ( 2 5 5 )
> * s e t @. P 3 = ' '
> * d e c l a r e @. P 4 i n t
> * s e t @. P 4 = 0
> * e x e c s p _ ...
> does anyone know why and how? thank a lot.
> lee.
>
>|||Also search the MS web site... I found this ( perhaps it applies)
http://support.microsoft.com/default.aspx?scid=kb;en-us;174512
"lee" <freesearcher18@.hotmail.com> wrote in message
news:#pJwoqHYDHA.1280@.tk2msftngp13.phx.gbl...
> hi, when i execute a store procedure, the sql server return a access
eorror,
> like:
> 2003-08-11 15:48:29.04 spid23 Using 'sqlimage.dll' version '4.0.5'
> Stack Dump being sent to C:\MSSQL7\log\SQL00012.dmp
> 2003-08-11 15:48:31.07 spid23 Error: 0, Severity: 19, State: 0
> 2003-08-11 15:48:31.07 spid23 SqlDumpExceptionHandler: Process 23
> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is
> terminating this process.
> .
>
****************************************************************************
> ***
> *
> * BEGIN STACK DUMP:
> * 08/11/03 15:48:31 spid 23
> *
> * Exception Address = 0041395A (CXVariant::CopyDeep + 59)
> * Exception Code = c0000005 E
> * Access Violation occurred reading address 00000000
> * Input Buffer 1090 bytes -
> * d e c l a r e @. P 1 v a r c h a r ( 2 5 5 )
> * s e t @. P 1 = ' '
> * d e c l a r e @. P 2 v a r c h a r ( 2 5 5 )
> * s e t @. P 2 = ' '
> * d e c l a r e @. P 3 v a r c h a r ( 2 5 5 )
> * s e t @. P 3 = ' '
> * d e c l a r e @. P 4 i n t
> * s e t @. P 4 = 0
> * e x e c s p _ ...
> does anyone know why and how? thank a lot.
> lee.
>
>

Excel SQL 2000

Is it possible to store excel spreadsheet files in a sql
server database? If so how and what are the implications
of doing so..
Thanks,
AnjelinaIs this easy to do? If the excel file changes and the column is to be used for replication will the changes be replicated?
Anjelina
>--Original Message--
>Try to "dump" it in the database with Textcopy program, or use methods in
>RDO and ADO via GetChunk and AppendChunk commands. (Store it in the column
>type IMAGE)
>Jens S=FC=DFmeyer.
>
>.
>