I want to load a file into a blob column using a stored proc. The file will be on a UNC path on the network. Is there a system stored proc or any utility that can be fired off from the sp, using xp_cmdshell that will take a file path from the filesystem and load it into a blob column in a database table.
I have worked with blob fields in the past but in those instances I loaded the blobs into the database from front end ADO.net code. This time around I need to pull this off stricktly from a stored proc.
Hi, Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.
Now, with SQL2005, I see it wants me to modify the SP, then run it as a script.
What is the best way for me to take stored proc changes from my SQL2005 server in the office, and get them to the clients SQL2005 server?
BEGIN SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode) AND tbl_SurgerySlot.ExtractDate = @ExtractDate AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo AND tbl_SurgerySlotDescription.NormalBookable = 1 AND tbl_SurgerySlot.SurgerySlotKey NOT IN( SELECT tbl_Appointment.SurgerySlotKey FROM tbl_Appointment WHERE tbl_Appointment.ExtractDate = @ExtractDate AND tbl_Appointment.Deleted = 0 AND tbl_Appointment.Cancelled = 0 ) END ELSE
BEGIN IF @96hrPlusFlag = 1
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode) AND tbl_SurgerySlot.ExtractDate = @ExtractDate AND tbl_SurgerySlot.StartTime >@DateTo AND tbl_SurgerySlotDescription.NormalBookable = 1 AND tbl_SurgerySlot.SurgerySlotKey NOT IN( SELECT tbl_Appointment.SurgerySlotKey FROM tbl_Appointment WHERE tbl_Appointment.ExtractDate = @ExtractDate AND tbl_Appointment.Deleted = 0 AND tbl_Appointment.Cancelled = 0 )
Hi experts, I have a textbox and a upload file function in my asp.net page.User can either copy/paste their resume in text or upload their resume file and submit the application.The uploaded file will be saved into a BLOB column, but do you know if text in textbox can be saved into BLOB column? I received error message on the code:'save Applicant resume to a BLOB-image datatype column objComd.Parameters.Add(New SqlParameter("@AppResume", SqlDbType.NText)) error:Exception has been thrown by the target of an invocation.Operand type clash:ntext is incompatible with image
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
We are currently working on a method to store Visio drawings in a SQL Server database. One approach we looked at was to take and store the coordinates of each individual object in the drawing in a table, and then pull those coordinates back out when we need to render the drawing.
The second approach we are examining is the use of BLOB's; specifically, store the entire Visio drawing as a BLOB in SQL Server 7. We have only been able to find examples utilizing ADO. Is anyone aware of any method to save BLOB's using stored procedures? We'd like to use this method fore simplicity and maintenance sake.
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool.
I work with Microsoft SQL Server 2005 on windows XP professional. I'd like to create stored procdure to add image to my database (jpg file). I managed to do it using VARCHAR variable in stored procedure and then using EXEC, but it don't work directly.
My Table definiton: CREATE TABLE [dbo].[Users]( [UserID] [int] IDENTITY(1,1) NOT NULL, [Login] [char](10), [Password] [char](20), [Avatar] [image] NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
My working solution using stored procedure: ALTER PROCEDURE [dbo].[AddUser] @Login AS VARCHAR(255), @Password AS VARCHAR(255), @AvatarFileLocation AS VARCHAR(255), @UserId AS INT OUTPUT AS BEGIN SET @Query = 'INSERT INTO USERS ' + CHAR(13) + 'SELECT '''+ @Login + ''' AS Login, ' + CHAR(13) + '''' + @Password + ''' AS Password,' + CHAR(13) + '(SELECT * FROM OPENROWSET(BULK ''' + @AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)' EXECUTE (@Query) SET @UserID = @@IDENTITY END
I'd like to use statement in the stored procdure: ALTER PROCEDURE [dbo].[AddUser] @Login AS VARCHAR(255), @Password AS VARCHAR(255), @AvatarFileLocation AS VARCHAR(255), @UserId AS INT OUTPUT AS BEGIN DECLARE @Query AS VARCHAR(MAX)
SET @AvatarFileLocation = 'C:hitman1.jpg' INSERT INTO USERS SELECT @Login AS Login, @Password AS Password, (SELECT * FROM OPENROWSET(BULK @AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)
SET @UserID = @@IDENTITY
END
It generates error: Incorrect syntax near '@AvatarFileLocation'.
My question is: Why it does not work and how to write the stored procedure code to run this code without errors.
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?
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
We are debating what is industry “best practice� for serving huge numbers of images in an industrial scale website. More directly, which approach produces the best performance and the best scalability? For example, how do sites like ebay, Amazon, and other large sites handle the millions or billions of images they must deal with?
Store as BLOB in sql server?
Store in /images folder and store url text into sql server?
We always assumed that the second approach is what most sites must do. But do they?
One developer on our team maintains that storing one million or more image files in a directory will most certainly result in poor performance, because the server must scan the directory, searching for the correct file, each time a web request is made. The directory is not indexed (?) so performance must eventually suffer.
Other developer counters that storing millions of images as BLOBs into sql server will result in poor performance and HUGE database. An additional layer of access (webserver to sql server, back to webserver, then to client) causes a delay and performance hit. Who is right? What do the gurus as the world class sites do?
I was trying to save the Stored Procedures; however, SQL Server would not save it and gave me the "Error 259: (SQL Server) Ad-hoc updates to system catalogs not enabled. System Administrator must reconfigure system to allow this."
Does anyone know what to do with this error? I went search the error in the Microsoft TechNet Website and did not get any resolution.
In SQL Server 2000 when we use to create a new procedure it was saved in Management Consule, but when I create a new procedure and wants to save it SQL Server 2005 (Management Console) ... it saved like a query on my local drive ... I am confused what to do ... if i save it on local drive then how can i call it when required.
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.
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!
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?
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.
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
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.
I have visual web developer and sqlexpress 2005 installed on my windows XP pro.
I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.
When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.