I have integer data stored in an image field. Every 2 bytes in the image field is a data point.
Currently this data is extracted from within a VB6 application, converted to an ADO recordset and passed in to Crystal Reports. I'd like to find a way to unpack the data through a stored procedure and report from the store procedure instead (bypassing the VB6 application).
Can some one point me in the right direction please?
Hi, Does anyone know how to deal with image data type within stored procedure when using SQL2000? I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP, change another columns data (but, that the image is not modified) / and save the modified row back to the same table. Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please. Thank you.
I tried to insert a row using a clr stored procedure where a field was a varbinary(max) and the data was a jpg file. The data was truncated to 8000 bytes. A similar T-SQL sp did not truncate the data even though the parameters were identical. With the clr sp I tried varbinary, variant and image for the parameter type. The variant gave an exception. The others worked but the data was truncated.
I used sqlpipe.executeandsend. Someone asked elsewhere if the pipe had an 8000 byte limit but was told it had not.
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.
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.
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
Been pondering the idea of putting 43 - 56K Pdf documents into a SQL DB but everything I've read only goes as far as to explain how the data is stored or manipulated.
I need to establish whether there are real performance issues or gains that will be experienced by doing this.
If indeed, performance would be a big issue, would storing the files in a Folder on another drive outside the Database be all that much faster (Let windows handle the fetch and carry instead of SQL Server).
The plan at the moment is to store all the image data in a separate data file or multiple data files. Using partitioned views is also a consideration.
Number of records per day (pdf's) would be somewhere around a 1000.
Hi When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed. When I open the images table in VWD and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table. Any ideas? Thanks
I am saving large text document in an image type of column in a SQL Server 2000 table. How will I set up searching of words/ phrases for data stored in this column?
Hi, I have a website and i am uploading the gif image to the database. i have used varchar(500) as the datatype and i am saving the file in the webserver so the path to it c:intepub....a.gif
my upload table has the folliwing feilds UploadId Int Identity, Description, FileName, DiskPath varchar(500), weblocation varchar(500). I have a main sproc for the report where i am doing a inner join with other table to get the path of the gif..
So my question is how can i get a picture to show up on the report. . What kinda datatype the gif file should be stored in the database? If it is stored as a varchar how can i access it and what is best way to reference that particular.
I am using the 3-tiered architecture design (presentation, business laws, and data acess layers). I am stuck on how to send the image the user selects in the upload file control to the BLL and then to the DAL because the DAL does all the inserts into the database. I would like to be able to check the file type in the BLL to make sure the file being uploaded is indeed a picture. Is there a way I can send the location of the file to the BLL, check the filetype, then upload the file and have the DAL insert the image into the database? I have seen examples where people use streams to upload the file directly from their presentation layer, but I would like to keep everything seperated in the three classes if possible. I also wasn't sure what variable type the image would be in the function in the BLL that receive the image from the PL. If there are any examples or tips anyone can give me that would be appreciated.
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW
Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt
Can someone tell me why my stored procedure is repeating the Name in the same column? Here's my stored procedure and output: select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name], Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate, Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate from LibraryRequest join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null Output: ID Title Name Request Date Ship Date 29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008 Going crazy ugh...
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure. for example: alter procedure [dbo].[AddDetails] ( @IndustryName nvarchar(50), @CompanyName nvarchar(50), @PlantName nvarchar(50), @Address nvarchar(100), @Createdby int, @CreatedOn datetime ) as begin insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn) insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn) insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp) end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this. Please help me, i need the solution soon.
Hi iam working with two dropdownlists,one gets the data dynamically when the pageload.later after selecting particular item in the dropdownlist1 i must get data to the dropdownlist2 depending on 1. For example: Dropdownlist1 is for industry and 2 is for company. when i select particual industry in ddl1 i must get companies based on this industry in ddl2.Both the Industry name and company name are maintained in two different tables industry and company with common field ID.please help me with a stored procedure to sort out this problem...
Hello Group I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code. CREATE PROCEDURE stpMyAuthentication ( @fldUsername varchar( 50 ), @fldPassword Char( 25 )--, --@fldFirstName char( 30 ) OUTPUT, --@fldLastName char( 30 ) OUTPUT ) As DECLARE @actualPassword Char( 25 ) SELECT @actualPassword = fldPassword FROM tbMembUsers Where fldUsername = @fldUsername IF @actualPassword IS NOT NULL IF @fldPassword = @actualPassword RETURN 1 ELSE RETURN -2 ELSE RETURN -1
SELECT fldFirstName, fldLastName, fldPassword FROM tbMembUsers Where fldUsername = @fldUsername GO ############### login page ################ Sub Login_Click(ByVal s As Object, ByVal e As EventArgs) If IsValid Then If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False) End If End If End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim myReturn As SqlParameter Dim intResult As Integer Dim sqlConn As String Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr") myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn) myCmd.CommandType = CommandType.StoredProcedure
If intResult < 0 Then If intResult = -1 Then lblMessage.Text = "Username Not Registered!<br><br>" Else lblMessage.Text = "Invalid Password!<br><br>" End If End If
Return intResult End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after? Michael
I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better... e.g Sample XML file required...<Person><Name>Raymond</Name><NickName>The Legend</NickName></Person><Person><Name>Peter</Name><NickName>The King</NickName></Person> sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Web Assistant Procedures', 1;GORECONFIGUREGOsp_makewebtask @outputfile = 'C:MyExportFile.xml', @query = 'SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS', @templatefile = 'C:Template.tpl'
I want to capture and process data inside of a stored procedure by executing another stored procedure. If proc1 calls proc2 and proc2 returns 1 or more rows, consisting of 2 or more columns, what is the best way to do this?
Currently, I know I'm returning 1 row of 3 columns, so I concatenate the data and either return it with an OUTPUT parameter or using the RETURN stmt.
I have a table that has data and is indexed by the Visit #. Each month I receive new data from a text file that I have to import into this table. On all occasions the text file will have one of the existing Visit #'s contained in it.
I wrote a simple stored procedure that has the INSERT/SELECT statement but the problem I am having is when I execute the Stored Procedure and I run into a record from the text file that already exist in my table the stored procedure errors and stops at that point.
How do I write a stored procedure that will ignore that text record and continue reading the text file and insert records that do not exist?
Hi i m new in sqlserver databases i need to know how to "store output of data from stored procedure in a text file " suppose i have a stored procedure which has to cuculate some out put from some tables and in the end i want that all out put in comma delimited text file. my databse name is check1 i need help please thanks in advance take care bye
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
Hi AllIm generally a vb programmer and am used to referencing multiple recordsreturned from a query performed on an sql database and im trying to movesome functions of my software into sql stored procedures. So far ive beenable to move the functions relatively easily but im unsure about how tooutput multiple values from an sql stored procedure. By this i mean forexample one of the stored procedures may take your username and return thecontents of a single field in a record of one of the tables, but i wouldlike to be able to return for arguement sake the contents of a single fieldfrom two records if possible. Under VB im used to referencing the recordsetwith a (1) after it to reference the corresponding record from the query. Iwas wondering if there is a way to do something similar to this with storedprocedures if possible ?Thanks for any help
