Hi All, I have a scenario where I need to round and then remove the extra zeros from numbers. So if I have a number that I have rounded (12.456400000) I want the zeros to be removed. Is there a function I can use to remove those numbers? The round function appears to leave the zeros in place? As always greatly appreciate the input. ...
Hi Experts, I'm tryin to join two tables. The problem i'm having is that one of the columns i'm trying to join on is a list. So is it possible to join two tables using "IN" rather than "=". Along the lines of SELECT ID FROM tableA INNER JOIN tableB ON tableB.misc IN tableA.misc WHERE tableB.miscTitle = 'help me please' table...
I have a stored procedure that dynamically produces pivot results, passing sql for row defs, column to pivot, aggregate(field) to sum, and table name of aggregate. This works great, but i need to produce a table from these results to use in further calculations. How can I dynamically save the results to a table within the stored proced...
I'm curious on how some other people have handled this. Imagine a system that simply has Products, Purchase Orders, and Purchase Order Lines. Purchase Orders are the parent in a parent-child relationship to Purchase Order Lines. Purchase Order Lines, reference a single Product. This works happily until you delete a Product that is refe...
I am trying to create a calculated field in one of my database tables, but I keep getting the error Error Validating the formula for column FullName I am not trying to incorporate other calculated fields so this should work. I'm using SSMS 2008 R2 with a SS2005 back end. So in the formula field I've tried: Trim([dbo].[Contact...
I have a script that needs to insert 50+ rows into a table, is there a way to loop though each row I want to insert, rather than coding this below statement 50 + times in TSQL? IFEXISTS ( SELECT 1 FROM table where column 1 = ) UPDATE table Column1 = value, Column2 = value, Column3 = value, Column4 = value WHERE c...
I have a hexadecimal string in a VARCHAR field and I need to convert it to VARBINARY. How does one do this? ...
I know that within a trigger - at least for SQL Server - one should never assume that the inserted table has just one row, which means SQL like this in a trigger is usually bad: select @UserID = ID from inserted But out of curiosity, can a set of INSERT statements ever result in an inserted table of more than one row? I know it's easy...
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...
I have a winform app that uses LinqToSql as it's DAL. There is a Central SQL DB and each laptop has a local SQLExpress DB. A seperate module, using Merge Replication, keeps the two in sync. When connection is lost to the central DB it 'fails over' to the local. This works great. However, when I regain conection to the central db, if...
I'm trying to move data from an Oracle (10g) database to SQL-Server (2008). I also want the indexes to be re-created on the SQL-Server side. However, in Oracle, there is a primary key defined on the first two fields, and it has data like this: VALUE3 FOO4 VALUE4 FOO8 Value4 Foo8 When I get that data to SQL Server, it won't mak...
Trying to execute an update of a single field where the current db wants to pull values from last night's backup. Should be something close to: update myTable set status = (select status from .lastBackup.dbo.myTable as t where t.idField = idField) Help? ...
I'm building a query that has a bunch of optional parameters, some of which are Table-Valued Parameters. The problem that I'm facing is how to most efficiently use the TVPs in this query? Each TVP has the type: TABLE( [variable] nvarchar(30)) I know that I could typically: INNER JOIN @TVP to filter out anything that is not in the ...
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...
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...
Since so many other websites have been hit I have to assume it is a bot! It has injected a script with: Yesterday: http://google-stats50.info/ur.php Today: http://google-stats49.info/ur.php It injected it into multiple tables. First, how did it identify the tables and columns? Second, what should I search for in the logs to identify...
I'm not clear on how the parameter to this statement is used. I've read a couple different descriptions about this and I get the impression that the parameter is the interval in which a blocking check is performed. But I've set up blocking situations to test this, and sometimes it picks up the block and sometimes it doesn't. It seems to ...
In the application development there is a concept of defensive programming. How to implement defensive programming techniques and writing robust code using Transact-SQL? ...
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...
I want to write a query in T-SQL to perform a search on two concatenated columns. The two columns are fname and lname. Here is what I have so far: SELECT fname, lname, ... FROM users JOIN othertable ON foo=bar WHERE fname+' '+lname LIKE '%query%' SQL server doesn't like that syntax, though. How do I structure t...