tsql

Changing data types in SQL Server 2005

Hello everyone, I have several tables in SQL Server 2005 that have columns with datatype money. I would like to change them into datatype numeric(20, 2). I am a lazy guy so would like to update each and every money data types to numeric data types with as little hassle as possible. Can anyone help please. Thank you so very much. ...

What framework to use to connect to a SQL Server instance from an iPhone?

I need to write an iPhone application to connect an instance of SQL Server but I can't figure out which framework I could use to do this. Is there an ODBC framework I could use? Any links would be appreciated. Please do not tell me to use a web service as that won't fit for this situation. EDIT: I'm building a SQL querying tool for the...

Update every column in every table

My database has recently been hacked by SQL injection leaving tags throughout data in all columns in all tables in my database. Is there a quick way of running a REPLACE UPDATE on all tables? Something like: UPDATE [all tables] SET [all columns]=REPLACE([all columns], '<script>....</script>', '') ...

yet another tsql question

i have three tables documents attributes attributevalues documents can have many attributes and these atributes have value in attributevalue table what i want in single query get all documents and assigned atributes of relevant documents in row each row (i assume every documents have same attributes assigned dont need complexity of ...

Insert statement whith unknown number of values extracted from a string

I have a stored procedure in a sql server 2008 db. This stored procedure takes a parameter that is a list of max 5 comma separated integer values. This string is passed through a php sript and formatted like this: 01,02,03,14,15 These values should go in this table: Id | Type id ------------- 1 | 1 1 | 2 1 | 3 1 | 14 1 | 15 ...whe...

t-sql - compute the difference between dates for each row

Can you show how can this be done in t-sql? sample records accountnumber trandate ------------------------- 1000 02-11-2010 1000 02-12-2010 1000 02-13-2010 2000 02-10-2010 2000 02-15-2010 How to compute the # of days between each transactions for each accountnumber? like this accountnumbe...

help in solving a problem in sql server

I have aquestions from you. Does SQL server have any features so we can limit a field to fill with just specific values? For example assume that you have a field named "Name" then we want SQL just let us to fill this field with the following values: "Bella", "Jack", "Rose". is there any featues to do it? please guide me. thanks ...

How to yield recursion in query? What is the use of "With" ? How it works internaly ?

Why this query is completed with error ? ;with tempData as ( select 32 as col1, char(32) as col2 union all select col1+1, char(col1+1) from tempData ) select * from tempData ...

Sybase: create a database from a backup file

Hi there, Using SQL statements (sybase) how can I create or restore a full database given the conpressed backup file (myDatabase.cmp.bak). The reason i'm saying create or restore is that the DB already exists but I can drop it before creating it if that's easier. Do I need to worry about Device files? For example if the backup file us...

Sorting a column against different collations in SQL Server

I have a Companies table with a multilingual CompanyName column (defined as nvarchar(512)). I also have a stored procedure to search through and return a list of companies, it takes 2 nvarchar parameters - one is the search term, and the other is an ISO language code. What I would like to be able to do is return the results of the sear...

Why to use "not null primary key" in TSQL?

I am seeing too frequently "not null primary key" in scripts creating tables in TSQL in blogs, articles, books, forums, here in SO, etc. For example, BING gives 630,000 results ((just in English) if to search against "not null" NEAR "primary key" "sql server": http://www.bing.com/search?q=%22not+null%22+NEAR+%22primary+key%22+%22sql+s...

SQL error stating invalid column name when I have verification if it exists. Why?

There is staging script, which creates new column DOCUMENT_DEFINITION_ID stages it with values of MESSAGE_TYPE_ID + 5 and then removes column MESSAGE_TYPE_ID. First time everything run ok, but when I run script second time I'm getting this error: Invalid column name 'MESSAGE_TYPE_ID'. It makes no sense since, I have verification i...

How cen i get a time between two dates when they are not in the same day?

Hello, i was searching in the past questians and couldn't find what i was looking for, In my web app i need to get all the records in order table where the orders where orders in a certian shift: A shift has an openning date only and a record id in shiftTypes. shiftTypes holds the time of start and ending of a shift(implicitly) Now,...

T-SQL How to run a procedure/function within a SELECT query ?

Hi, I can't check it right now (don't have a compiler right now), but will that query executes ? select myTable.id_customer, [my_procedure_or_function](myTable.id_customer) from myTable group by myTable.id_customer that procedure/function returns a NUMERIC(18,0) or NULL to sum up, I need to select distinct id_customer from that tab...

SELECT with multiple subqueries to same table

I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information and the last book they checked out, if one exists: SELECT TStudents.*, BookName = (SELECT TOP 1 BookName ...

TimeStamp in SQL Server 2000

I have a table named Table1 which contains an ID and TimeStamp. Table structure ID TimeStamp 1 0x0000000000047509 But when I compare the combination of these fields, it always shows false. What is the reason for this? My Query is : DECLARE @ID int DECLARE @TimeStamp timestamp SET @ID = 1 SET @TimeStamp = 0x000000000004750...

Intellisense and context help for sys stored procedures - bugs or by design ?

SQL Server 2008 R2 Dev Execution in SSMS of: 1) use AdventureWorksDW; GO; sp_cdc_enable_table 'dbo', 'FactInternetSales', @role_name=NULL, @supports_net_changes=0 succeeds. Why does execution of 2) sp_cdc_enable_table 'dbo', 'FactInternetSales' --, @role_name=NULL, @suppo...

SQL GOTO statement in a script with multiple GO

I need to exit a SQL script without an error if a certain condition holds true. I've read that 1 solution would be to raiseerror with error code 20+ and with log parameter. But the limitation for that is that i can execute that only as an admin and the connection to the db will be aborted. Also, I tried using GOTO and jump to the end-o...

What is the best way to loop over a table's rows?

Hi, What is the best way to loop over a database table's rows in SQL Server 2008 R2? I am looking for something which is fairly similar to writing foreach ( ) and quite performant. Thanks ...

How to write a cross tab query with median

Here's my table ST NUM 1 1 1 2 1 2 2 1 2 2 2 2 3 2 3 8 I want to return a query where it returns the median of NUM for each ST ST NUM 1 2 2 2 3 5 I already have a median function SELECT CONVERT(DECIMAL(10,2), ( (CONVERT (DECIMAL(10,2), (SELECT MAX(num) FROM (SELECT TOP 50 PERCENT num FROM dbo.t ORDER BY num ASC) AS H1) + (SELECT M...