I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried:
select left(s2.name,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on s1.id=s2.id
where type = 'U' and s2.name like 'New%'
group by s2.name
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
TableNameRows
NewARTxn0
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Can some one please help. Lately I am getting an error: LAZY WRITER WARNING: LRU List is Empty
Than the system locks! can anyone help. This is driving us crazy. There is no reference in the KB. We are trying to insert records into a table by a SP.
I created a package scope variable to use as a sql command for a data flow task. But when I click the drop down to select it, nothing appears. Anybody else have this issue and find a solution?
I have an issue with connecting Excel to an analysis Services 2005 database. The computer is running Windows XP Sp2 and Office XP. I installed the oledb driver 9.0 but when I tried to connect with the pivot table and I want to create the connection, the dropdown list of the data provider is empty. I tried on another computer with Excel XP and the system is working fine. Is there somebody that could help?
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables: If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice. The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application. Has anybody suffered this problem and what solution was adopted? Thanks for any information you can provide.
I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.
SELECT 3 row_id, '2003' year, 'Mar' period, (SELECT count(*) FROM news WHERE news.id IN ( SELECT news.id FROM news WHERE news.announced_date >= '2003-03-01' AND ........
So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.
SELECT 3 row_id, '2003' year, 'Mar' period, (SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN ( SELECT news.id FROM news WHERE news.announced_date >= '2003-03-01' AND ............
I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.
SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server. but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
Ali
/*Truncate from all tables*/ select 'Truncate table ' + name from sysobjects where type ='u' order by name
/*Count all table rows from all tables*/ select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u' order by name
/*View all sp*/
select * from sysobjects where type ='p' and name not like 'dt%' order by name
/*View all triggers*/
select * from sysobjects where type ='tr' order by name
/*View all Views*/ use master select * from sysobjects where type ='v' order by name
I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.
I am in need of help to develop a query I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me? Lets see an example tb_exame
id_exame date_exame
1 07/01/2006
2 08/01/2006
3 09/01/2006 tb_exame_lab
id_exame_lab id_exame desc_exame status_exame
1 1 NORMAL 1
2 1
0
3 2 NORMAL 1
4 2 PROBLEMS 1
5 2 OK 1
6 3 OK 0 in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab can anyone help me?
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL, dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') AS V_PROCESS_ITEMS FROM APPLI_SUPPLIER INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24 Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM] (@QUERY AS VARCHAR(3900)=null) RETURNS varchar(8000) AS BEGIN -- Insert statements for procedure here declare @v_List_ITEM as varchar(8000) set @v_List_ITEM='' if @QUERY is not null Begin declare @cur_Lect_ITEM CURSOR; declare @FUNCTION AS NVARCHAR(4000); Declare @ITEM as VARCHAR(255); SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor'
EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output fetch next from @cur_Lect_ITEM into @ITEM while @@fetch_status=0 begin set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM fetch next from @cur_Lect_ITEM into @ITEM end deallocate @cur_Lect_ITEM SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '') set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3) End RETURN @v_List_ITEM END
I've just creates a report with about 20 Tables. To my suprise, it almost runs as intended ;-) But there is one flaw - Is there any way of hiding an empyt table? I want to hide all tables that are not filled with data. But wich one is empty differs from the entered parameter.
I have written a reporting application which has a SQL2005 backend. An import routine into SQL, written by a 3rd party, frequently fails. The main problems are missing rows in certain tables.
I am going to write an SP that will accepts a from and to date. I then want to search for rows of type X between those dates that do not exist so we then know between a date range, we have no data for these XYZ days.
I have this working by returning all rows between the dates into a dataset, sorted by date, and then running through the rows and testing if the next rows date is the next expected date. This works but I think is a very poor solution. This is all done on the client in C#.
I want to learn and implement the most efficent way of doing this. My only solution in a SP was to make a temporary table of all dates between the date range for row type X and then do a right outer join against the data table, returning all rows which are missing.
Something like this:
SELECT twmd.date FROM #temp_table_with_all_dates ttwad RIGHT OUTER JOIN table_with_missing_date twmd ON ttwad.date = twmd.date WHERE twmd.date IS NULL
Would this be a good, efficent solution, or should I just stick to my processing of a dataset in C#?
I would like to empty out my 2k8 database (remove all the data in the tables only) And reloaded it with the data from production (2k5). I know I would have to dis-enable any constraints, triggers and remove all indexes before I can run a Truncate/Delete on all tables correct And then import the data via Wizard or script And lastly enable all the constraints & triggers, rebuild the indexes. Is this the only way to go about this? I don't want to do a backup & restore because the 2k5 doesn't have a 20% of the tables that are in 2k8. The 20% of those tables in 2k8 i'm not going to remove.
I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.
I'm running a master package executing 8 child packages.
Each package contains the same connection managers and each package is stored within the MSDB database. The master package executes the packages stored in the MSDB using the 'execute package task'.
When running the master package from either MSDB or visual studio directly the odd thing happens that some tables will be filled, but after package execution I notice each table to be empty. There's no rollback 'procedure 'specified within the package and each package executes successfully because of error row handling. Anybody any hints how to solve this one?
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
Does any one know what command(s) I can use from a SQL prompt to tell me what tables are cruuently residing in a particular database. I'm looking for something along the lines of "select list_of_all_tables from tempdb", which can be applied to any valid db on the system.
Hi, I got a request from a devloper asking, how can he find out what tables his stored procedure his using, so he can remove unwanted SP which are using old tables in the database.
Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.
