How To Save A Stored Procedure With Management Studio?
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 Complete Forum Thread with Replies
Related Forum Messages:
How Do I Create A Stored Procedure In SQL Server Management Studio Express?
I have wrriten many stored procedures in the past without issue, but this is my first time using SQL Server Management Studio Express. I am having trouble creating a new stored procedure. Here is what's happening: I am opening my database, right clicking on "Stored Procedures" and selecting "New Stored Procedure". A new window opens with a template for creating a stored procedure. The window is called: "SQLEXPRESS.DBName - sqlquery1.sql". I then type up my stored procedure without an issue. However, when I go to save the stored procedure it wants to save it as a separate file in a projects folder. It does not become part of the DB (as far as I can tell). When I used to use Enterprise Manager (not an option anymore) this never happened. I'm sure I'm doing something dumb. Can someone enlighten me. Thanks,Chris
View Replies !
How To Test Stored Procedure With Output Parameters In Management Studio
I have this SP: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetSessionInformation] @CustomerID int, @Success bit OUTPUT, @Email VarChar(55) OUTPUT, @FirstName VarChar(55) OUTPUT, @LastName VarChar(50) OUTPUT, @PhoneNumber VarChar(50) OUTPUT, @CompanyName VarChar(50) OUTPUT AS SET NOCOUNT ON DECLARE @UserKey AS int SELECT @CustomerID = CustomerID FROM Customers WHERE CustomerID = @CustomerID IF @CustomerID IS NULL BEGIN SET @Success = 0 END ELSE BEGIN SET @Success = 1 END BEGIN SELECT customerID, Email, FirstName, LastName, PhoneNumber, CompanyName FROM Customers WHERE CustomerID = @UserKey How do I test it in management studio? When I run a EXECUTE GetSessionInformation 56 I get this error: Procedure 'GetSessionInformation' expects parameter '@Success', which was not supplied. Thanks for any help!
View Replies !
Attaching A Stored Procedure To A Database In MS SQL Server Management Studio
Hello all! Quick question... I've created my DB in MSSQLSMS, then attempted to created a stored procedure for it. The code itself is fine, I just need to know how to actually attach it so that it appears in the "Stored Procedures" section of my Database. I have Right Clicked on Stored Procedures > New Stored Procedure... > Edited as required > Save When I attempt to save it, it prompts me for a file. That's fine, did that - but I can't see ANY way to actually attach this to the DB. Any help is appreciated! Daniel Davies
View Replies !
How To Empty A Stored Procedure In Ms Sql Server Management Studio Express
hi everyone, I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it? Thanks in advance, best regards bg
View Replies !
Management Studio - Configure Save On Exit
Greetings, I might have blown right by how to configure this...so here it goes... In 2000 Query Analyzer I could configure whether I wanted to save any querys on exiting QA. In SQL 2005 Management Studio where is the magical configuration bullet that will do the same thing for the query tabs???? Thanks... bEH
View Replies !
Stop Management Studio Prompting To Save Files?
Hey all, I know this is a very minor gripe, but I cannot find any way to get SQL Server Management Studio to stop prompting me to save queries. I've poked through all the options without success, but it doesn't seem possible that they would have left this out. Am I out of luck?
View Replies !
Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
View Replies !
Save Custom Settings In SQL Server Management Studio Express
I use SQL Server Management Studio Express to work in several databases. What gets annoying, is that the databases I work in have 30+ tables in them, only 5-10 of which I actually work with. Then there's the stored procedures I have to sort through to find the ones that I need and so on. I set filters so that I see only what is applicable to me, but I hate having to set those filters every time I start Management Studio back up. Is there a way to save my settings so that it opens the same way every time? I've looked around and can't find an answer to this. Thanks!
View Replies !
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)
Hi Jonathan Kehayias, Thanks for your valuable response. I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread. Here is my response to the last code/instruction you gave me: I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me. I got the following debug error message: 1) A Box appeared and said: String or binary data would be truncated. The statement has been terminated. |OK| 2) After I clicked on the |OK| button, the following message appeared: This "SqlException was unhandled String or binary data would be truncated. The statement has been terminated." is pointing to the "Throw" code statement in the middle of ....................................... Catch ex As Exception MessageBox.Show(ex.Message) Throw Finally .......... Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC. Thanks, Scott Chang The code of my Form1.vb is listed below: Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Public Class Form1 Public Sub InsertNewFriend() Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;" Dim connection As SqlConnection = New SqlConnection(connectionString) Try connection.Open() Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7 command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig" command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley" command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave" command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City" command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD" command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045" command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com" Dim resulting As String = command.ExecuteNonQuery MessageBox.Show("Row inserted: " + resulting) Catch ex As Exception MessageBox.Show(ex.Message) Throw Finally connection.Close() End Try End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load InsertNewFriend() End Sub End Class
View Replies !
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?
Hi all, In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully: --insertNewRocord.sql-- USE shcDB GO CREATE PROC sp_insertNewRecord @procPersonID int, @procFirstName nvarchar(20), @procLastName nvarchar(20), @procAddress nvarchar(50), @procCity nvarchar(20), @procState nvarchar(20), @procZipCode nvarchar(20), @procEmail nvarchar(50) AS INSERT INTO MyFriends VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress, @procCity, @procState, @procZipCode, @procEmail) GO EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit', 'Michigon', '67899', 'PeterWang@yahoo.com' GO ======================================================================= Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet": --Form1.vb-- Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Public Class Form1 Public Sub InsertNewFriend() Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _ "Initial Catalog=shcDB;Data Source=.SQLEXPRESS" Dim connection As SqlConnection = New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection) command.CommandType = CommandType.StoredProcedure EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave', 'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' Console.WriteLine("Row inserted: " + _ command.ExecuteNonQuery().ToString) Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub End Class =========================================================== I ran the above project in VB 2005 Express and I got the following 5 errors: 1. Name 'EXEC' is not declared (in Line 16 of Form1.vb) 2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb) 3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb) 4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb) 5. Expression expected (in Line 16 of Form1.vb) ============================================================ I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave', 'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error. But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it. Please help and advise me how to correct these problems. Thanks in advance, Scott Chang
View Replies !
SQL Management Studio Express - Save &&"Solution / Project&&"
In SQL Management Studio Express, is there any wany to save the "solution" consisting of the set of queries that I have open at a point in time? I generally have 10 views I switch back and forth between to monitor a bunch of data load processes, and it would be helpful to be able to return to the same setup next time I login. TIA, -- VPDJ
View Replies !
How To Save Stored Procedure To NON System Stored Procedures - Or My Database
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 Replies !
How Does One Save A Stored Procedure
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 Replies !
Different Results When Running Procedure From Management Studio Vs Application Code
I'm updating a process that recreates a large table every night. The table is the result of a bunch of nightly batch processes and holds a couple million records. In the past, each night at the end of the batch jobs the table would be dropped and re-created with the new data. This process was embodied in dynamic sql statements from an MFC C++ program, and my task is to move it to a SQL Server 2000 stored procedure that will be called from a .Net app. Here's the relevant code from my procedure: sql Code: Original - sql Code -- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Begin RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT End Else Begin print 'Old BatchTable dropped.' End print 'Creating new BatchTable...' SELECT TOP 0 *, cast('' as char(3)) as Client, cast('' as char(12)) as ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create If Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Begin RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT End Else Begin print 'New BatchTable Created.' End -- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'Old BatchTable dropped.' END print 'Creating new BatchTable...' SELECT TOP 0 *, CAST('' AS CHAR(3)) AS Client, CAST('' AS CHAR(12)) AS ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'New BatchTable Created.' END The print statements are there because the .net app will read them in and then write them to a log file. Some of the other mechanics are there to mimic the old process. The idea is to duplicate the old process first and then work on other improvements. This works in Management studio. The .Net App reports that the old table was dropped, but when it tries to create the new table it complains that "There is already an object named 'BatchTable' in the database." I have verified that the old table is removed. Any ideas on how to fix this?
View Replies !
Bug In Management Studio : Cannot Add Procedure Article For Heterogeneous Transactional Replication
Hi, I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties Copy extended properties : false Destination object name : pGS_RefuseRequest Destination object ownere : dbo Action if name is in use : keep existing object unchanged Replicate : Execution of the stored procedure Create schemas at Subscriber : false When I save the article and then the publication I got following error message: Can not add artice 'pGS_RefuseRequest'. Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo) That's realy strange because the wizard offered the procedure pGS_RefuseRequest in the list of possible articles. Fortunatly I can create the article with following TSQL statement : exec sp_addarticle @publication = N'RIGHTS_EDV_4T_pub' , @article = N'pGS_RefuseRequest' , @source_owner = N'dbo' , @source_object = N'pGS_RefuseRequest' , @type = N'proc exec' , @description = N'' , @creation_script = N'' , @pre_creation_cmd = N'none' , @schema_option = 0x00 , @destination_table = N'pGS_RefuseRequest' , @destination_owner = N'dbo' , @status = 0 go Did anybody seen this bug before ? It seems to be specific for heterogeneuous replication. In a pure MS environement the Wizard works fine! Wolfgang Kunk
View Replies !
Can't Save My Stored Procedure Using VS2005
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 Replies !
Stored Procedure Folder To Save
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 Replies !
Stored Procedures Not Showing In Server Management Studio
i'm trying to create my first stored procedure. i believe i've done everything correctly, the file shows up in the projects directory with the .sql extension, but the proc doesnt appear in object explorer under my database name/programmability/stored procedures. when i execute the query, i get "Could not find stored procedure 'IsEmployeeInTable'.". i'm running on my laptop using asp.net development server. thanks. matt
View Replies !
MS SQL Server Management Studio - Permissions And Stored Procedures
Hi My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables. I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table! Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :( many thanks
View Replies !
Trying To Save A SQL Request As A Stored Procedure Or A View
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 Replies !
Stored Procedure Is Hard To Create/Save
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 Replies !
What Is The Security Setting To Allow Editing Of Stored Procedures From Management Studio?
Greetings Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all. Is there a seperate security setting specifically for management studio? The user has: The dbCreator Server Role Is mapped to the development database as dbo with datareader/datawriter/db owner/public role Is mapped to master reader/writer/public Is mapped to model reader/writer public Is mapped to msdb reader/writer public Is mapped to tempdb reader/writer publuc This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
View Replies !
Save Upload Image To Db Using Stored Procedure Problem
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 Replies !
Viewing Large Numbers Of Stored Procs In SQL Server Management Studio
I work with a large and complex reporting system with several hundred reports: the Programmability; Stored Procedures node of the object explorer has become very difficult to navigate. Is there any metadata that can be embedded in the stored procs that would create subfolders like the existing System Stored Procedures node in this node of the object explorer? I suspect that the correct answers are: Rename all your queries with a rational naming convention; Cull the deadwood; Assign them to categories then export them to separate databases. Unfortunately, one of these has already been done, and the other two will break several hundred dependent processes - the recoding and retesting is neither economical nor desirable. Still, all advice is welcome. Pitch your answers at a banking geek who does intermediate to advanced stored procedures and triggers, but isn't allowed to play with sharp things (like sys objects) - but I can probably get help from a grown-up on the sysadmin team: I have discovered that the rumours about human sacrifice are baseless, and they will perform favours in return for beer. This is also a good time to ask: just how many stored procs and functions are you allowed in SQL Server 2005? Nile.
View Replies !
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Hi all, I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server. Whenever I tried to right click stored procedure and select step into store procedure> i get following error "User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information" I am not sure what needs to be done on sql server side We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master. Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean? Please advise.. Thank You
View Replies !
Need Help With Stored Procedure Management
Hi again, Background: I have a stored procedure that I use to pull records to be diplayed in a gridview. There is a requirement that the users should be able to search using some criteria (supplied from textboxes) and also opt to search exact match (without LIKE clause) or wildcard match (with LIKE clause). To accomplish this, I created an identical copy of the existing stored procedure and removed all LIKE clauses. I then just check if the user wanted an EXACT or LIKE match and then call the appropriate procedure. Now there is another requirement to filter out records that have toll free numbers in them (based on user choice. NOTE: Toll Free Numbers = numbers starting with 800,866,877,888). Problem: Should I create additional stored procedures to accomplish this? If I go this way, then I am worried if more requirements like this arise, tt will be very difficult to manage so many stored procedures. Or, should I use if() statements in stored procedures which get executed according to the flag parameters sent from the code? I have read that if I have if() blocks in stored procedures, they are recompiled every time they are accessed possibly affecting performance. Any suggestions. Thanks
View Replies !
Stored Procedure Management Software
Can anyone recommend some software for the management of stored procedures? These are some of the features I would like: - Call the source code of the proc up in an "intelligent" SQL editor (code coding, auto indent, etc.) - "Compile" the proc (run the SQL script that defines the proc), allowing for compilation to multiple databases - Execute the proc, prompting for parameters if necessary - Display results of proc execution in grid-like view with ability to export results to various formats - Maintain versions of the proc source - Automatically include code to drop the procedure before compiling - Automatically include GRANT statements for setting the permissions of the proc, based on the way permissions are defined in the database Any ideas? Mickey Langley American Cancer Society
View Replies !
SQL Server Management Express Studio Management Tools
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
View Replies !
Visual Studio Database File And SQL Server Management Studio Express Question
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
View Replies !
Visual Studio 2005 Standard And SQL Server Management Studio?
I am new to visual studio and I am still not sure of all its components and features. I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio? I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional? I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool. How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase. Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology. My goodness, it seems I still have so much to learn. Thanks
View Replies !
Debug Stored Procedure Using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully?
View Replies !
Visual Studio Step Into Stored Procedure
Hi, I have been developing a web application on Visual Studio 2003.NET and MSSQL2005 Developer Edition for some time without problem. Then suddenly today (and for no apparent reason that I am aware of), I lost the ability to step into stored procedures. I get a message saying I do not have permission to run master.sp_sdidebug. I cannot even find this stored procedure - doesn anyone know where it might live or how to reinstall it? Also, does anyone know what user Visual Studio uses when it is talking to MSSQL? Any help would be most appreciated. Best wishes, Patrick Skelton
View Replies !
Debugging Stored Procedure From Visual Studio 2005
Hi ,I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspxI have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am using windows authentication to login to sql server Any help?
View Replies !
Problem Debbuging A Stored Procedure In Visual Studio
Hello,I want to debug a Stored Procedure in the VIsual Studio. Actually I managed to do that, but only from Step into SP and Execute. I want to put a breakpoint in the procedure and when it is hit to stop, but if I Run(With Debug) my Site it doesn't stop at the breakpoint in the SP. I put a mark in the project options to debug SQL. What can be wrong?
View Replies !
Debugging Stored Procedure From Visual Studio 2005
Hi , I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspx I have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am a sysadmin in SQL server. I am using windows authentication to login to sql server Any help?
View Replies !
Stored Procedure Only Worked After I Rebuild Web Site In Visual Studio
Hi,today I was modifying a number of Stored Procedures and upon altering my code to reflect the changes in the Stored Procedures my my database (SQL Express) was returning no values where it should be, and was before.I recompiled the whole website and one of the stored procedures worked. This made no sense, so I built the site again, and guess what, the next stored procedure worked. This worked for the final procedure aswell.So to summarise, I had to rebuild for the stored procedures to work again. Also of note is that when they were not working, I was getting no errors, just simply no data coming back from the procedure. And to make them work again, I had to rebuild the site 3 times in Visual Studio!!!Im using Visual Studio 2005, Sql Express 2005, and Microsoft Enterprise Library for accessing the database. Has anyone had this experience, or can anyone explain it?Conor
View Replies !
SQL Server 2005 Managment Studio Newbie: Can't See Stored Procedure
I just created a stored procedure in SQL Server 2005 management studio. I went to my db and expanded the programmability folder then right clicked the stored procedures folder and created a stored procedure. I saved the procedure as sp_getDistance in the default folder SQL managment studio picked. Now when I went back to that stored procedure folder I did not see my stored procedure sp_getDistance, all that was there was the system stored procedure folder. Did I save the stored procedure in the wrong place, or what did I do wrong. I can't find the procedure anywhere, its just sitting in my My DocumentsSQL Server Management StudioProjectssp_getDistance.sql folder. Thanks,Kyle Spitzer
View Replies !
Testing A Stored Procedure With Output Params In SQL Server Managment Studio
I have an SP like this (edited for brevity): set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[TESTING_SP] @Username MediumText, @Password MediumText, @UserKey int OUTPUT, @RoleKey int OUTPUT, @UserGroupKey int OUTPUT, AS BEGIN SELECT @UserKey = UserKey FROM UserProfile WHERE Username = @UserName AND [Password] = @Password END I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error: Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0 Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied. How do I set up the output parameters and then select the values in MS for testing purposes? Thanks a ton for helping a noob.
View Replies !
Urgent: How Save SP Thru SQL Server Mgmt Studio
I created a bunch of stored procedures on my dev machine using Visual Studio 2008 interface. Now I am using MS SQL Server Mgmt Studio 2005 and looking at the PRODUCTION database online. I tried to create the new stored procedure. But how do I SAVE it to the database? It keeps asking me to save the sql file locally, but I don't know how to do it to the db itself. Please help. Thanks!
View Replies !
Server Express, Management Studio Express, Visual Studio Standard, And An ASP.NET Page....
I believe I'm missing something as far as my configuration goes...I'm new to working with SQL server and the databinding I'm trying to do. I have an ASP.NET page that has a radio button list box which I have databound to a database served up by SQL Express I'm having a lot of problems with connections... I was going to enumerate the behavior under each configuration (local only, remote tcp/ip only, remote pipes only, remote both), but I've realized that it is just flaky. Sometimes I can connect to the db with SQL Studio express, sometimes not. Sometimes I can add new tables and edit tables, sometimes not. Sometimes I can create a new connection when trying to databind from within Visual Studio, and sometimes not (sometimes I can't even connect, other times, I can connect initially but when I build a query through the designer it then has problems). Lastly, sometimes the ASP.NET page works correctly and sometimes not. This all seems to be mostly independent of how I configure SQL Server as far as what kinds of connections are allowed. I thought there was a pattern, but as I tested it more rigorously I found there wasn't. What I would like to do is be connected to the same DB with Visual Studio and SQL Studio, edit the DB as I wish, databind to it, and test my ASP.NET page, all without needing to jump through a bunch of hoops (although right now I wouldn't even know what those hoops are...it's just a shot in the dark as to what will work). It's all quite maddening and any help would be appreciated.
View Replies !
SQL Server 2005 Mgmt Studio: Save Table Vew Positions
I'm in stage of manually populating a database that requires me to have about a dozen tables open simultaneously. It would be great to be able to save the tables, locations and sizing so that these can be recalled and opened automatically. Currently I do a screen capture, which is pretty lame. Any suggestions would be welcome. Config: Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
View Replies !
Management Studio
dont know y, but i have prob. installing MS Management studio....After a hard drive failure I was forced to reinstall everything... :( The problem is that after installing vs2005 proffesional edition I installed the sql server 2005 trial edition and everything was fine during the installation.When I looked for the Management studio it wasnt there for me :( I uninstalled and reinstalled trying the full installation but results was the same, no Management studio!!!After uninstalling the SQL server trial edition I installed the Management studio express and it works. except that from some reason I cannot browse my XP user folder which has no password protection.. :( Any idea for installing the SQL trial edition including the Management studio ???Why can't I browse my XP folder with Management studio express ???Where can I download Management studio (non express version)?? regards
View Replies !
Management Studio
Hi I am moving towards managing sqlserver databases and i basically come from a command line background. Can you please tell me some of the tasks that needs to be done using command line only in sqlserver and wherein management studio falls short. regards db2hrishy
View Replies !
|
TRACKER
