I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).
I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.
Could someone help me with writing the code to export the contents of a varbinary field in my database to make the contents be written to the local harddrive? I can do this in Visual Foxpro but my company wants it in C# and I have no clue about C#.
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
We serialize a custom object into a byte array (byte[1000]) and store it in a SQL Server 2005 table column as varbinary(1000). There are a lot of rows retrieved with each SqlDataReader from C# code: up to 3,456,000 rows at a time (that is, roughly 3.5 million rows).
I am trying to figure out what will be more efficent in terms of CPU usage and processing time. We have come up with quite a few approaches to solve this problem.
In order to try a few of them, I have to know how I can extract certain "pieces" of data from a varbinary value using T-SQL.
For example, out of those 1000 bytes, at any given moment we need only the first 250 bytes and the third 250 bytes. Total: 1000 -> [250-select][250-no-need][250-select][250-no-need]
One approach would be to get everything and parse it in C#: get the 1st and the 3rd chunks of data and discard the unneeded 2nd and 4th. This is WAY TOO BAD.
Another approach would be to delegate the "filtering" job to SQL Server so that SqlDataReader gets only what it needs.
I am going to try a SQL-CLR stored procedure, but when I compared performance of T-SQL vs. SQL-CLR stored procs a few weeks ago, I saw that the same job is done by T-SQL a bit faster AND (more importantly for us) with less CPU consumption than SQL-CLR.
So, my question is: how do I select certain "pieces" of varbinary column data using T-SQL?..
In other words, instead of SELECT MyVarbinary1000 FROM MyTable how do I do this: SELECT <first 250 from MyVarbinary1000>, <third 250 from MyVarbinary1000> FROM MyTable ?
I need to store 256 bit hash (SHA-2 alogrithmn) in one of the table'sprimary key. I would prefer to use numeric data type rather varcharetc.* Decimal datatype range is -10^38 +1 to 10^38 -1. I can split my 256bit hash into two decimal(38, 0) type columns as composite key* I can store the hash as varbinary. I never used it and don't havemuch understanding in terms of query writing complexities and dealingit through ADO (data type etc.)It would be heavy OLTP type of systems with hash based primary keyused in joins for data retrieval as well.Please provide your expert comments on this.RegardsAnil
I need to put .doc data into a varbinary column for full text searching. I have created the db and columns but am unsure as to how to insert the varbinary data. I have found some discussions about inserting images but nothing explicitly on .doc files. Can anyone suggest resources or sample code?
Hey all, I have a profile table for members of a website that allows them to upload an avatar for use throughout the site. I decided to add the option to just remove the avatar if they want to no longer use one at all and I am stuck. I am running an update statement against thier entire profile incase they change more then just removing thier avatar but of course when I try this statement. myCommand.Parameters.AddWithValue("@avatar", "") I get a conversion error, which makes sence. So what instead of "" do I need for code to just insert empty data to overwirte thier current avatar data. Any help would be great, thanks for you time.
Hey all, Another varbinary question. I am trying to move an image stored in a table varbinary(max) directly from one table to another programmatically. The rest of the data is just nvarchar(50) so I just use a T-SQL select statement in the code behind and feed all of the date into an SqlDataReader, I do this becuse there is some user interaction when the data is moved, so there may be some new values updated when transfering from one table to another, so once the old and possibly new data is stored in seperate variables then I use a T-SQL insert statement to move all of the data into the other table. Problem is I am not really sure how to handle the image data(varbinary(max)) to just do a straight up transfer from the one table to another. I get conversion errors when trying to handle the data as a string which makes sense. Not sure what to put for code examples since I really am stumped with this, but here is what is not working. Dim imageX As String SqlDataReader Code - imageX = reader("imageData") Insert code - myCommand.Parameters.AddWithValue("@imageData", imageX) Thanks in advance,
I have a database that I am using as an archive for emails and am storing them in varbinary(max) data types. The database works fine for inserts and retrieval but I cannot delete large sets of records ( > 30K) without it timing out. I assume this is because SQL server isn't simply releasing the handle to the blob but is instead doing a lot of work reclaiming the space. Is there any flag I can set or approach I can use to resolve this issue?
I was considering moving the blobs into a seperate simple table and putting async triggers on the primary to delete the blobs, will this work?
Any ideas are appreciated. Besides the "store files in the file system" idea. - Christopher.
I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?
I have a SQL script file which creates a table and and inserts all data required by the client app into the table.
The table also holds images in a varbinary field. How do I insert an image held on the file system (e.g. "C:ImagesMyImage.gif") into a varbinary field using a script that is run using sqlcmd on the command line?
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG 0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text. 2. Turn into a byte array and see if it is a jpg 3. Turn into a byte array and see if it is a pdf 4. Convert it to hex and then cast as varchar 5. etc....
CREATE TABLE [dbo].[IntegrationMessages] ( [MessageId] [int] IDENTITY(1,1) NOT NULL, [MessagePayload] [varbinary](max) NOT NULL, )
I call the following insertmessage stored proc from a c# class that reads a file.
ALTER PROCEDURE [dbo].[InsertMessage] @MessageId int OUTPUT AS BEGIN
INSERT INTO [dbo].[IntegrationMessages] ( MessagePayload ) VALUES ( 0x0 )
SELECT @MessageId = @@IDENTITY
END
The c# class then opens a filestream, reads bytes into a byte [] and calls UpdateMessage stored proc in a while loop to chunk the data into the MessagePayload column.
ALTER PROCEDURE [dbo].[UpdateMessage] @MessageId int ,@MessagePayload varbinary(max)
AS BEGIN
UPDATE [dbo].[IntegrationMessages] SET MessagePayload.WRITE(@MessagePayload, NULL, 0) WHERE MessageId = @MessageId
END
My problem is that I am always ending up with a 0x0 value prepended in my data. So far I have not found a way to avoid this issue. I have tried making the MessagePayload column NULLABLE but .WRITE does not work with columns that are NULLABLE.
My column contains the following: 0x0043555354317C... but it should really contain 0x43555354317C...
My goal is to be able to store an exact copy of the data I read from the file.
Here is my c# code:
public void TestMethod1() { int bufferSize = 64; byte[] inBuffer = new byte[bufferSize]; int bytesRead = 0;
byte[] outBuffer;
DBMessageLogger logger = new DBMessageLogger();
FileStream streamCopy = new FileStream(@"C:vsProjectsSandboxBTSMessageLoggerInSACustomer3Rows.txt", FileMode.Open);
try { while ((bytesRead = streamCopy.Read(inBuffer, 0, bufferSize)) != 0) { outBuffer = new byte[bytesRead];
//This calls the UpdateMessage stored proc logger.LogMessageContentToDb(outBuffer); } } catch (Exception ex) { // Do not fail the pipeline if we cannot archive // Just log the failure to the event log } }
I have read the forums and fail to be encouraged about using the timestamp to handle concurrency. I have SQL 2005 and am using the DataReader and command objects for data manipulation. The database has about a dozen related tables to each other in some way. Correct me if I am wrong, but using timestamps means I must store each table's original rowversion at read time in several possible combinations of variables (based on the joined tables at the time of read). Is this right? Then at update time, what if some related tables are timestamped with the original value and others have changed since read time? How would each update stored procedure know which other dependent tables were fetched at read time? It's easy to corrupt the data this way. SELECT s.OrderNumber, a.UserName AS OrderedBy, cn.LastName + ', ' + cn.FirstName AS ContactName, s.CarbonCopy, s.Application, s.OrderDate, s.FollowUpDate, s.ProdStartDate, s.InternalNote, s.SampleNote, s.UpdatedBy, s.DateLastUpdated, cn.EMail AS ContactEmail, s.MfgID, co.CompanyName, s.ShipVia, cn2.LastName + ', ' + cn2.FirstName AS ContactName2, co2.CompanyName AS Expr1, cn3.LastName + ', ' + cn3.FirstName AS ContactName3, s.MfgContactID, s.DistributorID, s.DistContactID, s.CustomerContactID, s.VersionStamp, a.VersionStamp, cn.VersionStamp, co.VersionStamp, cn3.VersionStamp <------- Is all this really necessary? FROM Samples AS s LEFT OUTER JOIN Associates AS a ON s.OrderedByID = a.AssociateID LEFT OUTER JOIN Contacts AS cn ON s.CustomerContactID = cn.ContactID INNER JOIN Companies AS co ON s.MfgID = co.CompanyID LEFT OUTER JOIN Contacts AS cn3 ON s.DistContactID = cn3.ContactID LEFT OUTER JOIN Contacts AS cn2 ON s.MfgContactID = cn2.ContactID LEFT OUTER JOIN Companies AS co2 ON s.DistributorID = co2.CompanyID WHERE (s.SampleID = @SampleID) What would be a simple way to update a table that depends on other tables having original version stamps? Then at runtime how would I enforce it without generating violations when the users were updating, say, the Samples table and just viewing the dependent tables' columns, not changing them.
When i try to load tables from DB2 OS390 Via DTS and DB2 ODBC it give me an error with the timestamp field. Its (DTS) says the timestamp field on SQL is marked read only.
I think I am missing some essential idea when working with a timestamp column, maybe someone around here can give me the needed pointers on how-to...
My situation is: I want to copy data periodically from one DB to another. Both are in the same SQL instance, so there are no transaction, networking or similar issues involved. It all really comes down to identifing the new rows in source and moving them over to destination.
I added a TIMESTAMP column to my source, and I can see it count up slowly for every line inserted. On the destination side, I added a table containig just one field, type binary(8) to store the last value to which the last transfer ran. Notice that on source side I got the counter on every row, but on destination side I got only one value, I thought it would be waste to carry over the data for every row when I really only need the latest one.
Now, what my package does is: a) Select the last used binary(8) value into a package variable named TS of type object (works) b) Start a dataflow, where the source is SELECT statement and the WHERE clause is TIMESTAMP > package variable TS (works) c) Multicast the data into two recordsets d) One recordset makes most of the columns flow through some Lookups, Derive Columns into a Destination and are written back. (works) e) The other exit of the Multicast shall go through all lines and catch the highest TIMESTAMP that came by using an Aggregate and write it down, since that is the point I need to pick up later. (and that is the problem)
Problems are: I cannot run Aggregate max on the TIMESTAMP. Only Count is allowed. So I need to convert the BINARY(8) into a number to be able to catch the max value. Converting between BINARY(8) and DT_I8 or similar seems not possible, or the result is wrong due to the byte ordering (MSB/LSB, most/less significant byte first)
Later on, I need to write out my new found highest value again, but here the same problem applies. How do I convert from DT_I8 back to BINARY(8)?
Having my own reference value stored as bigint instead of binary(8) does not work either: You cannot assign bigint from a select to a package variable, bigint are loaded as string (as I read in a blog, and I think they are right since I get exactly the same errors).
So... HOW does one work in an efficient way with a TIMESTAMP column to Aggregate it to max and store out this max value? Or do you all keep the TIMESTAMP from source appended to your rows and stored with each row in destination (Wasting eight precious bytes a row)?
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ? What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
I'm trying to filter out timestamps that land exactly at .000 milliseconds. (e.g. 2007-12-05 16:30:50.000) Do I have to convert the timestamp to a string first and then use the LIKE statement? If so, can somebody show me how. I'm pretty green to SQL but know the basics. Any help would be greatly appreciated!
I have an Access XP ADE application connected to a SQL Server 7.0 SP4database. I have created a timestamp column in the main table.Unfortunately, I am now getting persistent write conflict errors.The order of operations are:1. The application starts and loads the recordset into the form using astored procedure.2. I modify a field and press a save button which uses me.dirty=false toforce a save.3. The field is saved to the database. Using profiler I can observe themodified field being saved. As I would expect, the update statement isusing the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.4. Without navigating off the record, I alter the same field (or adifferent field) and press save again and a write conflict will appear.Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.I thought that the timestamp column value is incremented each time therecord is updated. The ADE application does not appear to be recognizingthe new timestamp value.Any help or advice you could give would be appreciated.ThanksGeorge
I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better.Â
Facundo writes "Hi: How can i add a timestamp to a generated file of a complete database backup, the idea is to archive full database backups using the date to identified it.
How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?
F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak The user database dumps each night at 9 PM.
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions: 1. Why it is taking 1st 2 Charecters? 2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?Â
I'm in the process of converting a rather huge VSAM database into a set of SQL tables. I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME). I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255). And I have to cut and paste the data names from the manframe side to the server side (and the data types to.) So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away... Thanks for any help.
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
Is it possible to easily copy data from one table to another if the data types don't match. I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't. I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at leastthink I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the olddatabase are text datatypes and the new database is nvarchar(50) data types. Thanks for any suggestions.
I have a really dumb question regarding SQL data types. I have a couple columns in a table that are specified as MONEY. These columns are being read from my web app and displayed on the website for reference when filling in other information.
My problem is that when it is displayed on the website it give four decimal places instead of two. For example I want it to report $33,000.29 but what is actually displayed is $33,000.2965.
How can I set up either SQL or my web app so that it only displays the two decimal places? I've looked into changing the datatype already but SMALLMONEY and MONEY have the same type of decimal values.
I am looking for a chart of SQL Server data types and information about them, such as usage, constraints, etc. Could anyone point me in the right direction?
