tsql

SQL Server: enable remote connections without SSMS

I've got a SQL Server Express 2008 install on my web server, which by default does not allow remote connections (probably a good thing.) I opted not to install SQL Server Management Studio Express along with it for disk space and other reasons. I need to enable remote connections but any instructions I can find involve using SSMS to ch...

How to conditionally filter on a column in a WHERE clause?

OK, the umpteenth conditional column question: I'm writing a stored proc that takes an input parameter that's mapped to one of several flag columns. What's the best way to filter on the requested column? I'm currently on SQL2000, but about to move to SQL2008, so I'll take a contemporary solution if one's available. The table queried in...

To get date from datetime in sql

I have datecreated field in a table. It contains value as "2009-12-30 11:47:20:297" I have a query like this: select * from table where DateCreated = getdate() Although one row exists with today's date, I am not getting that row while executing above query. Can anybody help? ...

How to retreive the last row of a table?

How to retreive the last row of a table which doesn't has any unique id like select * from sample where id=(select max(id) from sample) ...

Is LINQ Query Syntax Related to T-SQL Order of Execution?

Does the LINQ syntax match the T-SQL order of execution? I suspect it does and thus the reason for the change in query order between SQL and LINQ but wanted some proof. ...

T-Sql: check if IP matches netmask

My SQL-Server db stores IP netmasks as binary. I need a way to match these with a given IP for example: is 192.168.21.5 part of a netmask which is stored in the db? the binary representation of 192.168.21.5: 11000000.10101000.00010101.00000101 (without the dots) Netmask stored in db is: binary(4) and a tinyint field 11000000.1010100...

Timeout in SQL Procedure

I am using the below sql to import some data from a file from the intranet. However every once a while, there will be a timeout error and the proc would fail, which is why I am using a transaction. If the transaction fails, I want the ImportedTable to get cleared. However this does not seem to happen. Is there anything I am missing here?...

Join two tables where all child records of first table match all child records of second table.

I have four tables: Customer, CustomerCategory, Limit, and LimitCategory. A customer can be in multiple categories and a limit can also have multiple categories. I need to write a query that will return the customer name and limit amount where ALL the customers categories match ALL the limit categories. I'm guessing it would be simila...

Help with PIVOT

I have a table as follows Name | Words A words for A1 here B words for B1 here C words for C1 here A words for A2 here B words for B2 here C words for C2 here I want to pivot the above table to get the following result A ...

T-SQL - Called called the current proc

Is there way to tell what proc called the currently executing stored procedure. ...

Finding approximately duplicate database records using T-SQL?

Hey all. I have a MSSQL 2008 database with a fair number of rows. As of now, before new rows are inserted into the table, the stored procedure checks to see if that record already exists in the database (by checking a column labeled Title). This check is exact, and if the to-be-inserted record is slightly different, it will insert it ins...

TSQL/VB.NET - write stream to a table

Dear Folk's i'm using the following code in order to send the bytes of a picture into the stream table: Dim FirstColumnNames As String = imTable(0) & "_Code, " & imTable(0) & "_Price, " & imTable(0) & "_Title, " & imTable(0) & "_Type, " & imTable(0) & "_Height, " & imTable(0) & "_Width, " & imTable(0) & "_Comments " Dim FirstFieldsValue...

SQL: Does disabling triggers count as schema change? ("Could not complete cursor operation because the table schema changed")

Does disabling triggers count as schema change in SQL Server? I am getting error: "Could not complete cursor operation because the table schema changed" I have a stored procedure p_DeleteA that deletes a row from table A and all of its child records from table B; however, as a row in the table B gets deleted, grandchild records in table...

Do I need a second table for this database logic?

Hi folks, Firstly, this DB question could be a bit DB agnostic, but I am using Sql Server 2008 if that has a specialised solution for this problem, but please keep reading this if you're not an MS Sql Server person .. please :) Ok, I read in a log file that contains data for a game. Eg. when a player connects, disconnects, does stuff...

Setting version column in append only table

We have a table that will store versions of records. The columns are: Id (Guid) VersionNumber (int) Title (nvarchar) Description (nvarchar) etc... Saving an item will insert a new row into the table with the same Id and an incremented VersionNumber. I am not sure how is best to generate the sequential VersionNumber values. My initia...

In tsql is an Insert with a Select statement safe in terms of concurrency?

In my answer to this SO question I suggest using a single insert statement, with a select that increments a value, as shown below. Insert Into VersionTable (Id, VersionNumber, Title, Description, ...) Select @ObjectId, max(VersionNumber) + 1, @Title, @Description From VersionTable Where Id = @ObjectId I suggested this because I b...

TSQL in SQL 2005: Query

I have 2 columns in my table, called TaskSet and SkillsSelected. The sample data as follow: TaskSet | SkillsSelected -------------------------------------------------- SK000001, SK000004, SK000002 | SK000001, SK000002, SK000003 SK000002 | SK000002, SK000003, SK000004 As you can see it's ...

sql compare datetime today

Hi I hope anyone can translate my abstract query. I want to select * from TABLE where ( [MYDATETIMEROW] < (TODAY - 3 Days)). Does I have to Convert, cast or use datepart or anything else?.. im confused. Are there simple rules? I would'nt have problems to do that with linq but simple sql I learned just hardly. Thank you and best rega...

TSQL - Cast string to integer or return default value

Hi! Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails? Best Regards Oliver Hanappi ...

When to begin T-SQL query with USE?

I'm a T-SQl and database newbie, and a little confused. In the T-SQL book I'm reading it says that a USE statement is written to set the database context of the session. Does that mean that there can be more than one database in an instance of SQL-server and the USE statement tells SQL server which database the query will be on? ...