How do i convert a hexadecimal VARCHAR to a VARBINARY in SQL Server
I have a hexadecimal string in a VARCHAR field and I need to convert it to VARBINARY. How does one do this? ...
sql-server-2005
table relationships, SQL 2005
Ok I have a question and it is probably very easy but I can not find the solution. I have 3 tables plus one main tbl. tbl_1 - tbl_1Name_id tbl_2- tbl_2Name_id tbl_3 - tbl_3Name_id I want to connect the Name_id fields to the main tbl fields below. main_tbl ___________ tbl_1Name_id tbl_2Name_id tbl_3Name_id Main tbl has a Uniq...
SSIS Execute a Stored Procedure with the parameters from .CSV file SQL Server 2005
I'm learning SSIS and this seems like an easy task but I'm stuck. I have a CSV file Orders.csv with this data: ProductId,Quantity,CustomerId 1,1,104 2,1,105 3,2,106 I also have a stored procedure ssis_createorder that takes as input parameters: @productid int @quantity int @customerid int What I want to do is create an SSIS package...
Copy one column to another for over a billion rows in SQL Server database
Database : SQL Server 2005 Problem : Copy values from one column to another column in the same table with a billion+ rows. test_table (int id, bigint bigid) Things tried 1: update query update test_table set bigid = id fills up the transaction log and rolls back due to lack of transaction log space. Tried 2 - a procedure on fol...
What parameters are applicable to Microsoft.Ace.Oledb.12.0
Hi All, Where can I find a list/explanation of Extended Properties for Microsoft.ACE.Oledb.12.0 Thanks ...
SQL Server 2005 Views
I have a table that contains multiple records per employee by effective data and effective sequence: EMPLID, EFFDT, EFFSEQ. I am trying to come up with a view in which I could specify an As Of Date other than the current date and get the most current record for each employee as of that date. The query that I usually use is: SELECT C.E...
SQL Select a row and store in a SQL variable
So, I'm writing this Stored Proc and I really suck at SQL. My Question to you guys is: Can I select an entire row and store it in a variable? I know I can do something like: declare @someInteger int select @someInteger = (select someintfield from sometable where somecondition) But can I select the entire row from sometable and stor...
SSIS 2005 - Ignore row insert failures
Hi, I would like to ignore the errors that may occur when a batch is commited. In my case, unique columns. The OLE DB Destination Error Output is set to "Ignore failure" but it is still failling. The Data Flow "stop on failure properties" are set to false and the MaximumErrorCount to 0. I don't want to do row redirection to be able to...
Convert string time like '18:15' to DateTime like 23/09/2010 18:15:00
How to convert VARCHAR time like '18:15' to DateTime like 23/09/2010 18:15:00. There can be any date format I just want to have date and time. The reason is I have a column in db time of VARCHAR(5) type. I have input, time in string, from user. Which I want to compare like this WHERE MyTable.Time < @userProvidedTime ...
join two tables with different number of rows(in sql server)
Hello, I have two tables: A(col1,col2,col3,col4) B(col1,col2,col3,col4) Table A has 4 records(rows) and B has 6 rows.I want to join them like this,for example join them in C table C(B.col1,B.col2,A.col3,A.col4,B.col3,B.col4) (tables have different values in records just col1 and col2 contains the same values) when i join them on A.c...
Convert Binary Id Field to Text
I need the text (representation) of a id field in SQL Server 2005. Is there a way, we can generate the textual representation of the id field? For instance, if the id field reads as 0x00000000000002F0, I need the text value of 0x00000000000002F0 so that I can run SUBSTR operations on the same. Constraints I am not allowed to create ...
How to list all columns of a given sql query
Hello, is there an easy way to get a list of all columns of a SQL query? They are listed in the header of the results window of SSMS but I can't copy them. Thanks in advance. EDIT: sorry, I found it myself after a little googling: http://vidmar.net/weblog/archive/2008/06/05/save-sql-query-results-with-column-names-in-msssms.aspx ...
Is there any difference between IS NULL and =NULL
I am surprised to see that IS NULL and =NULL are yielding different results in a select query. What is difference between them? When to use what. I would be glad if you can explain me in detail. ...
How to pull out the name of the primary key column(s) of a MS SQL Server table?
I have a MS SQL Server Database with about 75 tables on it, and I am trying to insert records into tables if a record with the same primary key doesn't exist, or update if they do. I could hard code the primary keys for every table into my vb.net code, but I'd rather not as more tables are to be added at a later date and my code needs to...
How to use the database that is updating by transactions log shipping service
Hi. Not so far ago I was faced with the trouble of dynamically transferring data from one database to another. The only reason to do that for me is when first database server go down, the system can use second server. For this purpose i used Transaction Log Shipping service. As far as i can see, it working fine now and copying logs from...
How to count the occurrences of a string inside TEXT datatype in sql server 2005
There is a column of datatype TEXT in my table. Now, I need to find the number of occurrences of a string in that TEXT field. I have already created a Full-text index on that table. But I don't know how to proceed further. I already found ways to count string occurrences for VARCHAR. But they cannot be applied AS IS to TEXT field. Any s...
How to recover Accidently deleted records
Dear Friends, How to recover Accidently deleted records from SQL Server 2005? Please Friends help me Thanx in advance ...
is it valid smalldatetime?
SQL Server 2005 How to check if given date is valid smalldatetime? Before converting datetime to smalldatetime, I already know that the value is valid datetime. But while converting datetime to smalldatetime, it is raising overflow error. So I wanted to check if the value is valid smalldatetime and if it is then convert to smalldatetim...
SQL Server Entity-Relationship Model generation
What's the best tool to reverse engineer an SQL Server 2005 to an entity-relationship Model diagram? Could it be done usin SSMS? ...
SQL Cross Apply Count
Hi, I'm trying to use CROSS APPLY in SQL, but only want to use the results of the call if the returned row count is greater than 1. I have the following SQL: INSERT INTO @dest (val1, val2, val3) SELECT t2.crossVal, t2.crossVal2, t1.tempVal FROM @tempTable t1 CROSS APPLY dbo.TableValuedFunction(t1.IDColumn) t2 Th...