who still uses the old dblib can help me?
thanks in advance:
1.create table & procedure in db:
test_table(uniqueidentifier a,varbinary50 b)
CREATE PROCEDURE insert_table
@b_in varbinary
AS
insert into test_table(b) values (@b_in)
GO
2.write program use dblib:
wchar_t str[120]=L"ABC";
dbrpcparam(dbproc[i], "@b_in", (BYTE)NULL, SQLVARBINARY,
-1, 6, &str)
3.I can find the program executed successfully,but only 1 Byte is inserted:
a b
-----------------------------------------------
199D71BE-327A-4BC1-AEC8-ACB0C96076CA 0x41
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer form that logged in users have access to. it has 2 questions including a text box for Q1 and dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am having problems getting this to work and keep getting exceptions. Be very helpful if somebody could check the code and advise where the problem is??
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) { SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1"); MembershipUser myUser = Membership.GetUser(this.User.Identity.Name); Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue; DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString()); DataSource.Insert();
I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error:
Server: Msg 4866, Level 17, State 66, Line 1 Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
How can I resolve this problem?
EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.
It seems to me that files created on Unix machines with line terminator , or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all with , there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?
We currently have a C++ application thats uses DBLib to insert data from a socket conection into a SQL 2005 Database.
The data from the socket is Real time Market Quotes (Ticks) and it the application takes the quote messages, formats them and inserts them in to the Database via DBlib Bulk API, and batching the insert at 1000 messages / sec.
As any in the financial industry knows, the message rates have expoloded over the past year and they keep inceasing, DBLIB has always been the Highest Performing way to get these Quotes ticks into the Database fast.
I house testing has show that DBLIB does top out (for us) at arround 15,000 messages per second which under a market swing can excede 20,000 / sec. This then leads us ot a queueing situation (Application Queue) till the traffic slows and the Applicaiton can flush all the data to the Database. We get arround this DBLIb limitation by splitting the insert duty to more them on process where each process's DBLIB connection has that 15,000 message celiing.
This application does only inserts to a table, no updates or deletes, every tick is a row.
The Question... With DBlib being Depricated through 2000 and 2005 we now would like to redue the application with a more supported interface to SQL Server. I am researching the SQL 2005 Native client API's and I am looking for which method the SQL Native Client (ODBC) or the SQL Native Client (OLE DB) are the close cousin to the old DBlib to do this type of Real time Bulk operation?
I have an application that was converted from using DBLIB bcp to using ODBC bcp. All other field/data types bind are updated correctly, but dates put in the date
1753-01-01 00:00:00.000 when it should be NULL. The variable is a char [24] and the first item is set to ( dbLoanBankruptcy.MotionforReliefReqDate[0] = NullChar; ). Is there any example or way to put the date in as null when the variable is null and an actual date as needed?
In the end of the post, there is my vba functions and query to create datatable. I got a run-time error message on the red line in the function. It says: [sql native client] string data right truncation. Did I do sometime wrong? Thanks for your time!
-L
Sub test() Dim BinaryStream Set BinaryStream = CreateObject("ADODB.Stream") Dim mConn As ADODB.Connection 'read the file data to bytes 'Specify stream type - we want To get binary data. BinaryStream.Type = adTypeBinary 'Open the stream BinaryStream.Open 'Load the file data from disk To stream object BinaryStream.LoadFromFile file Dim mCmd As ADODB.Command Set mCmd = New ADODB.Command With mCmd ' define query .CommandText = "INSERT INTO TEST (FDATA) " & _ "VALUES (?)" .CommandType = adCmdText ' add parameter .Parameters.Append .CreateParameter("@P1", adVarBinary, _ adParamInput, BinaryStream.Size, BinaryStream.Read) End With ' open db connection mConn.ConnectionString = _ "Driver={SQL Native Client};Server=.SQLEXPRESS;Database=XXXX;Trusted_Connection=yes;" mCmd.ActiveConnection = mConn 'run-time error: [sql native client] string data right truncation mCmd.Execute mConn.Close End Sub
GO /****** Object: Table [dbo].[TEST] Script Date: 10/04/2007 11:06:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TEST]( [ID] [int] IDENTITY(1,1) NOT NULL, [FDATA] [varbinary](max) NOT NULL ) ON [PRIMARY]
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
Create and populate table: create table maxtable ( tablename varchar (18) not null, [...] )
Try to insert into test3: insert into test3 (name, tbname) select i.name, o.name from dbo.sysindexes i, sysobjects o, maxtable m where i.indid > 0 and i.indid < 255 and i.id = o.id and i.indid = 1 and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
Hello, I have a table which uses binary data to store passwords. How do I view the contents of the "binary data" column, ie. the passwords? It just shows it as <binary data>?
We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.
Here is the SQL String that has been executed successfully in Query Analyzer:
When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."
How does one go about getting a graphic image into SQL Server 6.5. For example, let`s say I have a company logo that I want to include in a company profile table to be used on some reports. The graphic is now a .BMP or .GIF or .JPG file.
Hi there, Am working on an archiving system that stores files/images in a column of type Binary. we want to change the front end of this archiving system it was done using asp.net we dont have access to the source code what is the way to retrieve the files from the binary columns? how they store or retrieve the files? Thanx best site for sql,,,
Hello Dears, I was Maked Pictures table in sql server 2005 with Two Col pic_Id (int) and picture(binary) i need now ro insert image into this table by Asp.net under VB.net Code i have in my form FileUpload and button control and this is my CodeProtected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click Dim content() As Byte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType) StoreBlob2DataBase(content) End SubFunction ReadBitmap2ByteArray(ByVal FileName As String) As Byte() Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName)Dim stream As IO.MemoryStream = New IO.MemoryStream() image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)Return stream.ToArray End Function Sub StoreBlob2DataBase(ByVal content As Byte()) con.Open()Dim cm As New SqlCommand("Insert into Pictures(Pic_Id,picture) values(@id,@pic)", con) cm.Parameters.AddWithValue("@ID", 1)Dim p1 As New SqlParameter p1.ParameterName = "@pic" p1.SqlDbType = Data.SqlDbType.Binary p1.Value = content p1.Size = content.Length cm.Parameters.Add(p1) cm.ExecuteNonQuery() End Sub
when i make run to my website it give me an Exeption in this statement Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName) it tell me that Parameter is not valid i need help about this please with my Best regard khalil
Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated. Thanks Jerry oSQLConn.Open()Dim myreader As SqlDataReader myreader = myCommand.ExecuteReader Response.Expires = 0 Response.Buffer = True Response.Clear() Do While (myreader.Read()) Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content")) Loop
Hello All, I am using a stored procedure. In that stored procedure I am passing the name of a table as an argument. and after executing that procedure, it gives me the 'Insert' query of each and every row. So if I have 10 records, then the procedure will give me 10 'Insert' queries with its table values. There is a image datatype in last column of my table. and i am not able to fetch that binary data of that table. Can anybody tell me what is the problem with my stored proce. given below :: ALTER PROCEDURE [dbo].[SP_Generate_Insert_Statements_Testing] @strTableName varchar(128), -- used to specify the table to generate data for @RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements AS
--create a cursor that loops through the fields in the table --and retrieves the column names and determines the delimiter type that the field needs DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME, 'IsChar' = CASEWHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0 WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2 WHEN DATA_TYPE in ('text', 'ntext') THEN 3WHEN DATA_TYPE in ('sql_variant') THEN 4 WHEN DATA_TYPE in ('image') THEN 5ELSE 6 END FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK) INNER JOIN syscolumns sc WITH (NOLOCK) ON c.COLUMN_NAME = sc.name INNER JOIN sysobjects so WITH (NOLOCK)ON sc.id = so.id AND so.name = c.TABLE_NAMEWHERE table_name = @strTableName AND DATA_TYPE <> 'timestamp'AND sc.IsComputed = 0 ORDER BY ORDINAL_POSITIONFOR READ ONLYOPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1) BEGIN IF @@fetch_status <> -1 BEGINIF @SelList = 'SELECT' BEGIN SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 2 THEN @SelList + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' --WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) ''' WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' WHEN 5 THEN @SelList + '''MyImageData''''' ELSE @SelList + ' ISNULL(CONVERT(VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END END ELSE BEGIN SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + ' WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' --WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) ''' WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + ' WHEN 5 THEN @SelList + ''',''' + '''MyImageData''''' ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT(VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END ENDIF @IsChar = 5 SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar END
SET NOCOUNT ON --now we need to create and load the temp table that will hold the data --that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(MAX)) INSERT INTO #TheData (TableData) EXEC (@SelList) IF @strImageSQL <> '' BEGIN CREATE TABLE #ImageData (TableData image) INSERT INTO #ImageData (TableData) EXEC (@strImageSQL) END --Cursor through the data to generate the INSERT statement / VALUESDECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR READ ONLY OPEN CR_Data FETCH NEXT FROM CR_Data INTO @tableDataIF @strImageSQL <> '' BEGIN DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FORREAD ONLY OPEN CR_ImageDataFETCH NEXT FROM CR_ImageData INTO @ImageData END
WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2)BEGIN IF @strImageSQL <> '' BEGINPRINT @InsertStmt PRINT 'VALUES ' PRINT '(' PRINT SUBSTRING(@tableData,1,CHARINDEX('MyImageData',@tableData)-2)PRINT @ImageData PRINT SUBSTRING(@tableData,CHARINDEX('MyImageData',@tableData) + 12,LEN(@tableData)) PRINT ')' + CHAR(13) END ELSE PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)
IF @RequiredGo = 1 PRINT 'GO'
ENDFETCH NEXT FROM CR_Data INTO @tableData IF @ImageData <> '' FETCH NEXT FROM CR_ImageData INTO @ImageData
-- IF @TextData <> '' -- FETCH NEXT FROM CR_TextData INTO @TextData END CLOSE CR_Data DEALLOCATE CR_DataIF @ImageData <> '' BEGINCLOSE CR_ImageData DEALLOCATE CR_ImageDataEND
IF @bitIdentity = 1 BEGIN PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF ' END
PRINT '---- ** End of Inserts ** ----' RETURN (0)
=========== End of Procedure ======================= Any help will be appreciated.
I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512. I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters. How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently? The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated. I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used. How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large. Thanks!
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
Hi I have C++ Client application writes data using sp in SQL Server. After inserting more than 150000 records, i am unable to insert data , simply it hangs and no more connection are not allowing.If I close the application, it is working fine. What could be the reason?
INSERT INTO ticket_dump_datawarehouse SELECT Ticket_ID, ID_Entry_Type, Region, Related_Case, Create_Date, ID_Service_Center, ID_Type, ID_Priority FROM dump_view will fill fail >
cannot insert the value NULL into column 'Ticket_ID', table 'capacity_base_tracking.dbo.ticket_dump_datawareho use'; column does not allow nulls. INSERT fails. The statement has been terminated.
But dump_view does not contain any Ticket_ID columns that have NULL value..
Hi Folks, After I inserted a row in my Database (row 27) I started getting this error when I try to insert, update or delete the record in the database. I've searched about the error on google and it says that I should have a field that crossed the limit of characters. I have only the autoincrement field, two varchar fields and a text field, neither one of the varchar fields crossed the limit, they arent even close. I found in google that one solution would be turn the field that is having problems in a text field, but the only field that actually can be causing the problem already is of the text type. The exact error I get on VS 2005 when trying to change something in the row 27 is: "No row was updated. The data in row 27 was not committed.Error Source: .Net SqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated. Corret the errors and retry or press ESC to cancel the change(s)." I need some help guyz, see ya, hugs.
Hi Guys, I'm trying to save the data into 2 table when i click the button. But it pops out this error: String or binary data would be truncated. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.Source Error:
Line 116:Line 117: Sqlinsert.Connection.Open()Line 118: Sqlinsert.ExecuteNonQuery()Line 119:Line 120: Sqlinsert.Connection.Close() I Don't know what it means so i paste my codes regarding the button & the redline Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click 'add the data into shopper
Dim strsqlcmd As String
Dim strName, straddress As String Dim strContact, strEmail, strPw As String Dim strIC As String
' go the add item Response.Redirect("NewPrescItem.aspx") why my sqlinsert.excutenonquery will have this error? and what is this error really means? Thanks in advance
Hi, I am facing problem inserting binary data into sql data type varbinary. I want to save an object data type in var binary data type into sql.It gives a followin error.
The name 'DocumentData' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. I do not use ASP Upload file. Any type of help .
I am currently developing a simple program that will upload ms-word documents to a database so users can view them within the department. The program worked prefectly while using sql 2005 with the these fields: FormId intFileName nvarchar(50)FileBytes varbinary(Max) This was done in unit testing, now in system testing there is a difference. A slight downgrade in the database and server software! We are using sql 2000 on a 2000 server and the FileBytes datatype had to change to the following: FormId intFileName nvarchar 50 FileBytes varbinary 8000 There is no 'Max' option in sql 2000 for the datatype varbinary. So when the insert is perform, we get this error message: String or binary data would be truncated.The statement has been terminated. I am guessing it has something to do with FileBytes, because this is the only thing that changed. I just don't know how to solve the problem.P.S.The application is in ASP.NET 2.0
