Stored Procedure Is Hard To Create/Save
Jun 19, 2008
Hi,
Recently, I started to move more databases from SQL Server 2000 to 2005, well the Stored Procedures in 2005 is hard to handle.
First of all, I created the new SP in Object Explorer, the "New Stored Procedure" query just created a new name under the Stored Procedures folder, then I have to re-open it to put in TSQL code.
After I put in the code in the new SP, if I click "Save", the File
Manager comes out to let me to save in a physical location (C: or D: or Network place...). But after I close and open the new SP, the code I put in there is not really in the new SP.
I know that I have to "Execute" the SP in order to save the change. But the problem is I don't want to run the code at this step, the SP is to get a lot of data transactions, I'll setup a "Job" to do this.
So, the question is: Is this the way to create and save a new SP? Is there any other way to save the code changes in the SP WITHOUT "Execute" it?
Please give me any advise, article or links to read.
Thanks.
View 6 Replies
ADVERTISEMENT
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Oct 18, 2007
Hi all,
I have one table with a column of type 'image'. There are manytypes of files saved in that column (i.e. .Doc,Xls,Pdf,jpg,gif etc.). What I want is, read that files from database and save it in temp folder on d drive of server. Can anyone help me in my problem?
Thanx in advance
View 1 Replies
View Related
Aug 9, 2006
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
View 10 Replies
View Related
Dec 20, 2005
In Microsoft SQL Server Management Studio Express you can right click on "Stored Procedures" under a database in the object explorer and the resulting Context Menu offers a selection called "New Stored Procedure".
If you select "New Stored Procedure", a Stored Procedure Template Document is added for editing. It has the suffix ".sql"; I can save this document to the file system after I edit it, but I cannot figure out how to add it to the database as a Stored Procedure.
Can someone please tell me how to do this.
Thank you.
View 7 Replies
View Related
Nov 17, 2006
Hi!
I'm using VS2005 and trying to save my very simple stored procedure.The error message "Invalid object name 'dbo.xxxxx' just pop's up.
The SP is written through the Server Explorer > Stored Procedures > Add new.... etc.Using SQL server 2000.
Don't think this has to do with the SP-code but here it is:
ALTER PROCEDURE dbo.KontrollUnikPersNr@PersNr nvarchar(50) = null OUTPUTASSELECT @PersNr = PersNr FROM User WHERE PersNr = @PersNrRETURN @@ROWCOUNT
Thanx i advance for any help!
View 4 Replies
View Related
Feb 8, 2007
I am running Sql Server 2005, When I create a new stored procedure and try to save it, I'm prompted with the 'save file as dialog'. Is'nt the stored procedure suppose to be saved within the database?
View 2 Replies
View Related
Mar 12, 2008
Hi, I have created a store procedure and want to save them. this is wha ti did.
I clicked on the Northwind--> Programm--> Stored Procedure-- > right clicked --> new stored procedure
after writing my stored procedure I saved it. But when I am saving I want to save it under the Stored Procedure folder
of the NorthWind database. I guess this is what it should do. When I use to use the sql 2000, i could always send my stored procedure here , i guess it was by default. But even if i save my sql stored procedure at any other location than at the run time, how the applicaiton will find my store procedure.
Well so thinking of that I have to save my stored procedure under the database for which table it is created however, i can not save it there. I tried to figure out the directly but I couldnt.
So any suggestion????
View 1 Replies
View Related
Jul 30, 2007
Hi,
i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio.
But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp.
Thanks
tartuffe
View 3 Replies
View Related
Nov 14, 2006
I am null in Transact-SQL
Hello , I ve made a SQM request I would provide it result via stored stored procedure how can I do this ?
this is the request
SELECT DISTINCT Account.Name AS exp, Campaign.New_FormationIdName
FROM Email INNER JOIN
CampaignActivity ON Email.RegardingObjectId = CampaignActivity.ActivityId INNER JOIN
Account ON Email.ToRecipients = Account.EMailAddress1 INNER JOIN
Campaign ON CampaignActivity.RegardingObjectId = Campaign.CampaignId CROSS JOIN
New_Formation CROSS JOIN
New_formationparticipation MINUS
SELECT New_exportateurIdName, New_formationIdName
FROM new_formationparticipation
-An other ponit: How can I add a parameter to this stored procedure?
View 1 Replies
View Related
Jul 15, 2004
I am trying to save an uploaded image and its associated info to sql server database using a stored procedure but keep getting trouble. When trying to save, the RowAffected always return -1. but when i debug it, I dont' see problem both from stored procedure server
explore and codebehind. it looks to me every input param contains correct value(such as the uploaded image file name, contentType and etc). well, for the imgbin its input param value returns something like "byte[] imgbin={Length=516}". Below is my code, could anyone help to point out what did I do wrong?
Thank you.
================================================
CREATE PROCEDURE [dbo].[sp_SaveInfo]
(
@UserID varchar(12),
@Image_FileName nvarchar(50),
@Image_ContentType nvarchar(50),
@Image_ImageData image,
@Create_DateTime datetime)
AS
set nocount on
insert ExpertImage(UserID, Image_FileName, Image_ContentType, Image_ImageData, Image_ReceiveDateTime)
values(@UserID, @Image_FileName, @Image_ContentType, @Image_ImageData, @Create_DateTime)
GO
private void Submit1_ServerClick(object sender, System.EventArgs e)
{
if(Page.IsValid)
{
Stream imgStream = File1.PostedFile.InputStream;
int imgLen=File1.PostedFile.ContentLength;
string imgContentType = File1.PostedFile.ContentType;
string imgName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") + 1);
byte[] imgBinaryData = new byte[imgLen];
int n=imgStream.Read(imgBinaryData, 0, imgLen);
int RowsAffected = SaveInfo(imgName,imgBinaryData, imgContentType);
if(RowsAffected > 0)
{
..
}
else
{
..
}
}
}
public int SaveInfo(string imgName, byte[] imgbin, string imgcontenttype)
{
SqlConnection objConn = new DSConnection().DbConn;
SqlCommand objCMD = new SqlCommand("sp_SaveInfo", objConn);
objCMD.CommandType = CommandType.StoredProcedure;
objCMD.Parameters.Add("@UserID", SqlDbType.VarChar, 12);
objCMD.Parameters["@UserID"].Value = txtMemberID.Text.ToString();
objCMD.Parameters["@UserID"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Create_DateTime", SqlDbType.DateTime);
objCMD.Parameters["@Create_DateTime"].Value = DateTime.Now.ToLongTimeString();
objCMD.Parameters["@Create_DateTime"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_FileName", SqlDbType.NVarChar, 50);
objCMD.Parameters["@Image_FileName"].Value = imgName;
objCMD.Parameters["@Image_FileName"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_ContentType", SqlDbType.NVarChar, 50);
objCMD.Parameters["@Image_ContentType"].Value = imgcontenttype;
objCMD.Parameters["@Image_ContentType"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_ImageData", SqlDbType.Image);
objCMD.Parameters["@Image_ImageData"].Value = imgbin;
objCMD.Parameters["@Image_ImageData"].Direction = ParameterDirection.Input;
int numRowsAffected = objCMD.ExecuteNonQuery();
return numRowsAffected;
}
View 1 Replies
View Related
Aug 17, 2015
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
View 6 Replies
View Related
Jul 27, 2006
Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,
restore DB and after create SP from file.
Thanks.
View 3 Replies
View Related
Apr 9, 2007
I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
