I have a stored proc that calls several store procs, each of which insert dummy data into a single table each. It works fine except that for each loop in the cursor a single row of results is dispayed - just showing ClubcardId = 2, ClubcardId = 3 etc. I have used the SET NOCOUNT ON but this doesn't seem to help. I'm looking for this sto...
How can I Select all columns from all tables from the DB, like: Select * From * in SQL Server 2008??? The table list it´s very very big, and have so many columns, is it possible to do it without writing the column names? Or maybe make a select that returns the name of the tables. ...
Sorry if this is covered elsewhere in another question, but I can't find any examples which exactly match what I need and I'm getting confused. I have data in the table like this :- Name | Value --------------- John | Dog John | Cat John | Fish Bob | Python Bob | Camel And I would like the data like this.......
I have a data access layer which returns DataSets/DataTables by executing Stored Procedure. Everything was working fine from many months. But suddenly we have started getting the following error. System.ArgumentException; Column < ColumnName > does not belong to table < TableName > I wrote come extra logging code to troubleshoot this i...
Using SQL Server 2000 I want to compare the table2.date between table1.from, table1.todate, if exist then value should be 0 (zero) Table1 ID FromDate ToDate 001 20090801 20090815 002 20090817 20090820 …, Table2 Id Date Value 001 20090730 100 001 20090731 200 001 20090801 300 001 20090802 400 … 001 20090815 0 001 20090816 250 … ...
I heard that its not advised to use % in the beginning of LIKE clause in SQL Server due to performance reasons.Why is this is so? Some more details on this will help me in understanding the impact of this issue. ...
So I have a base table that looks something like this. SELECT [BILL_MONTH] ,[BILL_YEAR] ,[BILLED] FROM bill_Detail Everything is varchar. I created a view based on that table that converts the bill year and bill month to a datetime field. Of course, the bill_month and bill_year fields have some crap data that doesn't co...
I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work: create table #tempTable (TempId int primary key, Column1 varchar(100), Column2 varchar(100), DateCreated datetime) insert into #tempTable (TempId, Column1, Column2, Dat...
I'm working on a procedure that will update a large number of items on a remote server, using records from a local database. Here's the pseudocode. CREATE PROCEDURE UpdateRemoteServer pre-processing get cursor with ID's of records to be updated while on cursor process the item No matter how much we optimize it, th...
I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all. I've tried it the following two ways: First way: SELECT field1, field2...etc FROM my_view WHERE parm1 = CASE WHEN @PARM...
Does anyone have a query which searches through SQL2005/2008's plan cache identifying queries or stored procedures which have table/index scans within their execution plans? ...
Hi, I have an Audit database(created by someone else). Something is polulating it, with table sizes data (which makes sense as it is Audit database). The SQL server has too many jobs. I want to know what is populating the audit tables. Is there anything like sys.comments etc? which can tell me what is populating tables or do I have ...
How do you force a rename??? Rename failed for Table 'dbo.x. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+%28%28Katmai%5FPCU%5FMain%29.090329-1045+%29&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&Evt...
I have an UDF that selects top 6 objects from a table (with a union - code below) and inserts it into another table. (btw SQL 2005) So I paste the UDF below and what the code does is: selects objects for a specific city and add a level to those (from table Europe) union that selection with a selection from the same table for objects t...
Looking at the related questions, I don't think this specific question has been asked, so here goes. I had a situation where I joined on a table three times to get different data based on dates. This took too long, so in an effort to optimize, I rewrote it using a group by as defined here: http://weblogs.sqlteam.com/jeffs/jeffs/archive...
how do I make a composite key with SQL Server Management Studio? I want two INT columns to form the identity (unique) for a table ...
Does anyone have a good method for searching an entire database for a given value? I have a specific string I'm looking for, it's in TableA, and it's also a FK to some other table, TableB, except I don't know which table/column that is. Assuming there's a jillion tables and I don't want to look through them all, and maybe will have to d...
I should be able to reference tables in the join clauses from a subquery, shouldn't I? But the following query is giving me errors saying they can't be bound: select * from call c JOIN call_task ct ON c.call_no=ct.call_no AND ct.calltask_no = (select min(ict.calltask_no) FROM call_task ict WHERE ict.call_no=c.call_no) JOIN business b O...
This is an off-shoot of a previous question I had: http://stackoverflow.com/questions/1544314/a-little-fuzzy-on-getting-distinct-on-one-column This query makes a little more sense, given the data: SELECT Receipts.ReceiptID, FolderLink.ReceiptFolderID FROM dbo.tbl_ReceiptFolderLnk AS FolderLink INNER JOIN dbo.tb...
I have a varchar column in a table that is used to store xml data. Yeah I know there is an xml data type that I should be using, but I think this was set up before the xml data type was available so a varchar is what I have to use for now. :) The data stored looks similar to the following: <xml filename="100100_456_484351864768.zip" ...