Unable To Create Stored Procedure

Jun 4, 2004

I tried to create a stored procedure but instead of opening up to a new stored procedure it displays an exist stored procedure. I erased the code and typed in my code now i received this error message.

MS SQL-DMO (ODBC SQLState:42000)
Error 2729: Procedure ‘spUpdate_date_time’ group number 1 already exists
in the database. Choose another procedure name


Does anyone know how I can fix this?

Your help is appreciated

View 2 Replies


ADVERTISEMENT

Unable To Extract From Stored Procedure

Jul 30, 2007

 hi everyone ive written a stored procedure which returns a value depending on the i/p parameterive executed it in the object browser but i cant extract it from the stored proc to the front end  create procedure  checking ( @username varchar(20), @password varchar(20) ,@result int out)asif @username ='admin'set @result = 1else set @result = 0 return @resultGOand this is what i wrote in the code behind SqlCommand check = new SqlCommand("checking",con);//checking is the stored procedure             check.CommandType = CommandType.StoredProcedure;            check.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar)).Value = TxtUsername.Value;            check.Parameters.Add (new SqlParameter("@password", SqlDbType.VarChar)).Value = TxtPassword.Value;            check.Parameters.Add(new SqlParameter("@result", SqlDbType.Int)).Value = 0;int status = ( Convert.toint32 )check.executenonquery();// the line which is prolly causing the error , can u tell me what should be written here?  the status returns -1                        

View 2 Replies View Related

Unable To Get Return Value From Stored Procedure

Jan 2, 2007

Hi everyone

I am trying something that should be so simple but I cant get it to work. I am calling a stored procedure to lock a table and update a counter. I have tried to follow the exact code use in the MSDN examples but it doesnt work and always returns VT_EMPTY. It also returns a closed recordset so attempting to close it fails. The database is updated correctly and there are no exceptions just no return value.

Here is the stored procedure

CREATE PROCEDURE GETMODELID AS
DECLARE @i INT
BEGIN TRANSACTION
 SET @i=(SELECT ModelID from tblModelID WITH (HOLDLOCK,TABLOCKX))+1
 UPDATE tblModelID SET ModelID = @i
COMMIT TRANSACTION
RETURN @i
GO


Here is the ADO written in C++.

TDatabase DB;

try

{

if(DB.Open()==false)

{

AfxMessageBox(DB.m_ErrStr);

return FALSE;

}

_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandPtr spCMD;

CREATEiNSTANCE(spCMD,Command);

spCMD->ActiveConnection = DB.m_Cnn;

spCMD->CommandText = "GETMODELID" ;

spCMD->CommandType = adCmdStoredProc;

spCMD->Parameters->Refresh();

_RecordsetPtr spRS;

_variant_t vRa;

spRS = spCMD->Execute( &vRa, &vtEmpty2, adCmdStoredProc );

_variant_t rtn(DEF_PARAM(spCMD,0L));

DB.Close();

return (int) rtn;

}

View 3 Replies View Related

Unable To Submit More Than 8000 Characters With Stored Procedure

Jul 20, 2005

Hi all,I have a internet page written in asp to submit into authorscurriculum vitae publications (title, author, year, etc.).If the author submit less than 8000 characters it functions OK, but Ifthe author try's to submit more than 8000 characters the asp page doesnot return an error but the text is not saved in the database or,sometimes, it returned a "Typ mismatch" error.Here is the sp:---------------------------------------------------------------------CREATE PROCEDURE sp_CV_publications(@formCommandnvarchar(255)='process',@id numeric=null,@id_personint=null,@typPubID tinyint= NULL,@publicationstext=null)ASif @formCommand='process'beginINSERT INTO CV_publications(idperson,typPubID,publications)VALUES (@id_person@typPubID,@publications);select 1 as status, @@IDENTITY AS insertedID, * FROMCV_publications WHERE id=@@IDENTITYend----------------------------------------------------------------------------The server is running IIS5 and SqlServer 2000Any ideas ???

View 1 Replies View Related

Unable To Retrieve The @@Error Code In Stored Procedure

Aug 25, 2006

Hi All,

In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.

CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
As
Begin
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
Begin
select '1'
End
Else
Begin
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'
End

End

Thanks & Regards

Bijay

View 4 Replies View Related

Unable To Execute Stored Procedure When New Item Is Selected In Dropdown.

Mar 30, 2006

I'm using a form that has a dropdown control.  This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call.  I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected.  Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback.  Any ideas on resolving this issure?  Your help is much appreciated.

View 1 Replies View Related

Connection.Open () ERROR !!! Unable To Find Stored Procedure Sp_sdidebug.

Feb 15, 2006

Hi all,
When I try to open a connection it gives me error "Stored Procedure sp_sdidebug not found"
Can anyone help me in this respect?
I was running the same application with the SQL Server 2005 only earlier when it was running.But now after I reinstalled VS.NET & SQL Server I am getting this error.What could be the problem?
 
Please help... I am badly stuck up.
 
PAM

View 3 Replies View Related

Unable To Access VM Network Drive In SQL During Backup Generated By Stored Procedure

Mar 12, 2007

I get the following error in a log file created in the osql command:

Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'.
Device error or device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
BACKUP DATABASE is terminating abnormally.


The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is:

for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i

:loop
if exist x: net use x: /del
net use x: \192.168.%SUBNET%.1ackup
if not ERRORLEVEL 1 (
goto continue
) else (
echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log"
sleep 60
goto loop
)


:continue

echo IP ADDRESS OBTAINED

Echo delete old log file if it exists

if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log


Echo Rename log file to old.log

rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log


echo backup SQL Databases on server will start now

osql -E -n -d %1 -i %2kaman_full_sqldb_backup.sql -h-1 -o %3\%Computername%_kaman_full_sqldb_backup.log


In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally.

I notice when I do a

exec master..xp_cmdshell 'x:'

The system cannot find the drive specified.

Yet, when I go to the server there is an x drive.

I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes.

Any help would be appreciated. Thanks, dbmsql

View 6 Replies View Related

Reporting Services :: Unable To Display Time Difference Value From Stored Procedure

Nov 2, 2015

Created a report that displays the Maximum Response time (example of value 00:00:00) which is directly pulled from the Stored proc.When I ran the report, the column displays blank values.I am not sure if I should add any conversion to the Response value in the report.

View 2 Replies View Related

Integration Services :: Unable To Get Return Code Executing SSIS Package From Stored Procedure?

Jun 11, 2015

We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all.  After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.

 SELECT @cmd = 'dtexec /FILE "D:Program FilesMicrosoft SQL Server100DTSPackages.....PopulateReport.dtsx"'          
  SELECT @cmd = @cmd + ' /Decrypt T@!0er '          
  SELECT @cmd = @cmd + ' /set package.variables[vAppID].Value;' + CONVERT(VARCHAR(10),@appId)          
  SELECT @cmd = @cmd + ' /set package.variables[vDBName].Value;' + '"' + @db + '"'          
  SELECT @cmd = @cmd + ' /set package.variables[vBuildMFF].Value;' + CONVERT(VARCHAR(10),@BuildMFF)          
 
[code]....

View 6 Replies View Related

How Can I Create Stored Procedure?

Feb 13, 2008

 forumid    questid   answerid   answer     replyby   replyon    1            1            1            xxxx        aaa        01/01/08(mm/dd/yy)    1            1            2            yyyy        bbb        01/02/08    2            1            1            zzzz        ccc        01/02/08    1            1            3            hhhh         bbb        01/04/08    2            1            2            uuuu         vvv         01/04/08    1            2            1            tttt            ooo        01/05/08suppose i give forumid value=1 i want following answer recent reply and no.of replies  forumid    questid   answerid  no.of.reply  answer     replyby   replyon            1            1            3            3               hhhh        bbb       01/04/08      1            2            1            1               ttt            ooo        01/05/08 

View 14 Replies View Related

Create Stored Procedure

Apr 21, 2004

Hi!

I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.

Please help me!

Mike.

View 5 Replies View Related

How To Create Rdl Using Stored Procedure Which Use

Apr 3, 2008

Hi

I am sql server 2005.

how to create rdl using stored procedure which uses parameter


Thank you.

View 1 Replies View Related

Create Stored Procedure

May 6, 2008

Is there a way to create a stored procedure that accepts a column name and returns the name of the tables that have that column name?

View 2 Replies View Related

Create A Stored Procedure

May 7, 2008

I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:

create proc spTheNameOfTheProcedure
as
select table_name from information_schema.columns
where column_name = XXXXXX

no sure what to put where I have XXXXX

View 2 Replies View Related

Create Stored Procedure

Dec 22, 2007

Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007

If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)


results would be 10_12_2007,10_13_2007.

So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3

View 1 Replies View Related

How To Create A PDF From A Stored Procedure

Sep 18, 2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please


Thanks in advance for your help

View 5 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

Stuck Trying To Create A Stored Procedure

May 3, 2005

Hi

Let me start by saying that SQL is
not my strong suit so please bear with me.

Here's my situation
I'm building a property portfolio system with db structure as follows

tblProperty (The master table - linked 1 to many via propertyID)
tblValuations (columns valuationID, propertyID, currentValue, valuationDate)

For each property in tblProperty the valuations table can hold multiple valuation entries.

I need to have a summary that shows the number of properties a user has
listed and crucially I need to be able to find the latest
valuation for each property & add them together to retrieve the
total value of a users portfolio

I'm totally stumped with this so any help/advice would be gratefully received

Lee

 

View 1 Replies View Related

How To Create An Array In A Stored Procedure

Aug 8, 2000

I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.

View 1 Replies View Related

Grant &#34;create Stored Procedure&#34;

Feb 24, 2000

Hi!
How do I grant rights to an user for creating stored procedures (using T-SQL)?
Thanks,
Fabio

View 1 Replies View Related

VB5 Create Temporary Stored Procedure

Sep 3, 1998

When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?

The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240

View 4 Replies View Related

Create Stored Procedure Without Admin Right

Mar 12, 2001

I have no right to connect to my hosting company using MS SQL2000 client.
And I have to use stored procedure, can any body tell me is it possible to
create stored procedure ( I need is complex one with loops of if loops).
If possible please list the syntax.

View 2 Replies View Related

How Do You Create A Database Within A Stored Procedure

Apr 20, 2004

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

View 3 Replies View Related

T-SQL (SS2K8) :: How To Create Stored Procedure

Oct 16, 2014

yesterday i was trying to create Stored procedure but it fails i don't know why

CREATE proc GetBooksbyBorrowerID @Borrower_id INT
AS
BEGIN
SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b
WHERE a.borrower_id=@Borrower_id
AND a.ISBN = b.ISBN
END
GO
EXEC SP_Task1 10001

View 9 Replies View Related

Permissions To Create A Stored Procedure

Apr 30, 2008

A simple one (not for me)

MS SQL Server 2005

Which permissions do I need to have (as a User) to create a Stored Procedure
Which other entity's properties do I need to change in order to create a Stored Procedure

Thanks

View 2 Replies View Related

Need Help To Create A View/Stored Procedure

Jun 24, 2008

Experts

I am trying to create a view or Stored Procedure between different table

Table1 consist of the follwing Fields:

Ref_No: String hold the reference number, Unique
Details: String

Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique

What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers

Any advice?

View 4 Replies View Related

Create View Within A Stored Procedure

May 21, 2007

hi,

Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go

go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?

thank you

View 20 Replies View Related

How To Create A Dbf File From A Stored Procedure?

Jan 28, 2008

Hi there,

Does anyone know any possible way of creating a dbf file from within a stored procedure?

Any guidence, articles, examples, topics to revice would be a great help.

Thanks guys
Butterfly82

View 6 Replies View Related

How Do I Create A Stored Procedure In SQL Server05

Feb 8, 2008

Yes it looks like a stupid question
but when i right click stored procedures
and click new stored procedure, it gives me a
QRY analyzer style window and all i can do is save
the qry as a regular .qry file ?

View 5 Replies View Related

Stored Procedure To Create New Database

Jul 20, 2005

Is there a stored procedure installed by sql server 2000 that I cancall and just pass in the name of a new database and have it createthe database for me? If not, how do I do it in sql? Thanks.

View 3 Replies View Related

How To Create Rdl Using Stored Procedure Which Uses Parameter

Apr 3, 2008

Hi
I am sql server 2005.
how to create rdl using stored procedure which uses parameter

Thank you.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved