I have a stored procedure that returns multiple resultsets, it looks something like this BEGIN SET NOCOUNT ON; SELECT c1, c2, c3 FROM t1 WHERE id = @id IF (@@ROWCOUNT = 0) BEGIN SELECT c1, c2, c3 FROM t2 WHERE id = @id END END I use this stored procedure in my front-end ASP.NET website. If...
Ok I have a full text search index created on my JobsToDo table, but what I'm concerned about is if this is rendering my other indexes on the table useless. I have a normal nonclustered index on the CreatedDate field in this table. So when I run my full text search it returns results, I then filter my full text search by CreatedDate >= G...
Consider the following sequence of events: A view v_Foo is defined A user-defined function GetFoo() is defined that includes all columns from v_Foo (using 'Select * ...') The definition of v_Foo changes and now includes more columns I now want GetFoo() to include the new columns in v_Foo, but it still references the old definition I ...
I am using the Database explorer/designer of Visual C# 2008 Express Edition with SQL Server Express 2008. I created a table and want to add an index. But the index would exceed 900 bytes. One workaround is to use included columns in an index. Unfortunately, the "Included Columns" property is disabled in the Index designer and I haven't...
TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primary key of CustomerID. The table Order contains a primary key of OrderID and a foreign key of CustomerID. ...
What datatype is the most efficient to store a number such as 11.111. The numbers will have up 2 digits before the point and up to three after. Currently the column is a 'bigint', I am guessing that this will need to be changed to a decimal or a float. Any help much appreciated. thanks ...
In SQL Server 2008, I have my parent table in one database, and the child table in another database, with FK relationship maintained by triggers. I cannot change it, cannot move both tables into one DB and have a regular FK constraint. When I restored both databases from full backups, I had orphans in my child table, because the full bac...
I'm planning a data warehouse migration to SQL Server 2008, and trying to think of ways to replicate the LAG, LEAD, FIRST_VALUE and LAST_VALUE analytic functions from Oracle in SQL Server 2008. They are not included in SQL Server 2008, although the basic machinery for windowed analytic functions is (e.g. ROW_NUMBER, RANK and DENSE_RANK a...
Is there a way to store numbers in a db so that 7.1 is less than 7.10, ie keep the 2 points? Currently the numbers are stored as floats - do I need to change to small money, decimal or something? Any help hints appreciated. Currently the .Net object uses a Double and sql server column is a float. ...
If I have two tables A and B: A(AId, a1, a2) B(BId, AId, b1, b2, b3) On first thought , I wanted to write a sp (stored procedure) incorporates the two insert statements. But, on a second I thought I felt it would be nicer if I had a separated sp to do an insert for the table B and another to do an insert for table A while calling the ...
I have a 200 line long stored procedure, which gets a parameter 'prmtr', What I want to do is add an "sql part" to my stored procedure, according to my parameter. example: SELECT A.* FROM ( SELECT * FROM table1 ) A IF (my parameter) = a LEFT JOIN ( SELECT * FROM table2 ) B ON A.ID= B.ID ...
How to connect to the MSSQL database by using Hibernate in Netbeans 6.7.1? Please advice. The dialect is disabled for me. ...
At the company I work for date and time values have always been stored separately in integer fields, so for example 8:30 this morning would be stored like this: date of 20091116 and time of 83000 (no leading zeros as it is an integer field) Whereas the time as I type this the time would be stored like this date 20091116 time 133...
Hello all, I have a PHP script that queries SQL server using SQLCMD - I am using SQL Server Express 2008. Most of my queries go through to SQL Server fine but sometimes I get this: sqlcmd return output is: Array ( [0] => Msg 4060, Level 11, State 1, Server ABS-PC\SQLEXPRESS, Line 1 [1] => Cannot open database "test_v02" reques...
I have an script to update a database. That script create some columns in several tables. Some of that columns and so the message "Column names in each table must be unique." is shown. How can I disable this kind of message when running the script? Thanks in advance. Rui ...
How to put row number for sql query in sql 2000 where rownumber() is not supporting? ...
Hi, I have following trigger written for my application which gives me this error "Msg 4104, Level 16, State 1, Procedure AddItemToCurrentItems, Line 11 The multi-part identifier "INSERTED.IsActive" could not be bound." My intension with this trigger is to copy the inserted data in table "Item" to table "CurrentItems" but only if the b...
I've got a distributed deployment of ±20 clients using the same stored procedure to create a record and then attach detail records. We've noticed gaps in the identity fields of the master record. We are not using a transaction in this instance so the only reason we can come up with for the gaps is that one of the developers is swallowing...
I'm using ssrs with the winforms ReportViewer control to display reports to my users. Sometimes they want to select some of the text that the report outputs. But the ReportViewer control does not give us that ability, it just renders the text. Is there some property that I can change in the .rdl report or some property in the ReportViewe...
I have a stored proc that takes one integer parameter. I am calling this in a loop (I would rather use an In but Stored proc is outwith my control). So I have one SqlParameter object who's value is updated each time round a loop. I have a vague memory of there being a side effect to this? Does this ring any bells? (.NET 2.0 / SQL Ser...