Count 4 tables

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - SQL Parameter and wildcards, how to make it work? Grrr... Well, ive fixed it. and I must say that if the search function on this website worked the first time I was here, I never would have posted this thread to begin with. All I had to do to get this working was to set the DECLARE type to nvarchar instead of nchar. Why this makes it work, or why it didnt work with nchar to begin with, I may never know. So, here is the critical change in my case: DECLARE @Dv_id nvarchar ( 15 ) Tag: Count 4 tables
  - 2
    - SSIS Logs You can try to execute your package outside of BIDS by using dtexec.exe. It should help you to reduce amount of logging. Regards, Yitzhak Tag: Count 4 tables
  - 3
    - Received message constantly processed. This sounds like there are messages constantly comming and causing the WAITOFR(RECEIVE...) to get another message while is waiting. Is this the case Tag: Count 4 tables
  - 4
    - How to Send a SQL Server notification to Messenger You'll need to create a custom delivery protocol to send to a IM service. I believe that there are companies out there that offer gateway services to IM, SMS, etc, but I don't know of one off-hand. HTH... Joe Tag: Count 4 tables
  - 5
    - Error with SMO.TransferData() (Exception from HRESULT: 0xC0011008) Try to set the connection up mannually. So instead of using the connection string: Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection( My .Settings.LM_Master_SQLConStr) Use the server.(Parameters) such as userid, password, ect. I ran into the same issue a while back with the transfer, once I set my connection parameters to my server manually this corrected the problem. Here is an example in c# that I wrote maybe it will help. The vb conversion should be straightforward: Code Snippet //Two Server SMO objects to hold source and destination Server server; Server Source; try { //Cannot use the SqlConnection class as this locks out DTO from accessing the login method. //It appears DTO needs to be able to set the login properties which the SqlConnection locks it //out of doing. Below commented methods will not work in this instance. //SqlConnection conn = new SqlConnection("Data Source=.\sqlserver;integrated security = true"); //Server server = new Server(new ServerConnection(conn)); //Destination DataBase connected in an SMO manner using serverconnection ServerConnection connec = new ServerConnection(".\SqlServer"); //Set secure login connec.LoginSecure = true; server = new Server(connec); //Create target database. Transfer method does contain a create command but have not figured //out how to get this to work. Easier to use the SMO method at this time. Database dbo =new Database(server,"AdventureWorksClone"); dbo.Create(); //Source Database connected in an SMO manner using serverconnection ServerConnection conn = new ServerConnection(".\SQLServer"); //Set secure login conn.LoginSecure = true; Source = new Server(conn); //Set source to existing database that is to be copied Database db = Source.Databases["AdventureWorks"]; //Establish transfer passing in source database Transfer t = new Transfer(db); t.CopyAllObjects = true; t.DropDestinationObjectsFirst = true; t.CopySchema = true; t.CopyData = true; //Don't know if this is still needed as it has been set in an SMO manner t.DestinationLoginSecure = true; //Destination Server created above t.DestinationServer = server.Name; //Could not get this to work yet //t.CreateTargetDatabase = true; //Newly created database on destination - Clone not quite right word as in ADO uses clone //to indicate copying of schema only - not the case here t.DestinationDatabase = "AdventureWorksClone"; t.Options.IncludeIfNotExists = true; //This will copy all of the data and relations //Alternatively could use t.scripttransfer() to copy just the schema //SqlBulkCopy might be faster but might not make a copy of entire database t.TransferData(); //Release server objects server = null; Source = null; //Give verification that code is complete Console.WriteLine("Executed"); Console.ReadLine(); } catch (Exception ex) { Console.Out.WriteLine(ex); Console.ReadLine(); server = null; Source = null; } } } Hope this helps a little, sorry the code is in c#. Aaron Tag: Count 4 tables
  - 6
    - Report Builder Report Designer is the tool for building complex reports. In Report Builder you can only build simple reports such as a single table, matrix or graph. The value in report builder is in creating fast ad-hoc reports without having to specify a query first. Report Builder is designed to work with SQL databases, but you have to create a model of your database first. You can do this automatically in Report Manager and then customize your model using Model Designer Tag: Count 4 tables
  - 7
    - 64-Bit SQL Server 2005 (Enterprise), SQL Agent Notifications and Database Mail SQL Database mail which relies on using your SMTP server instead of a mail client to send out email, so check whether that service is started as a first point of check. Tag: Count 4 tables
  - 8
    - importing from excel into SQL Server 2005 changing values to NULL Searching for Excel & NULL in this forum will return plenty of posts on this issue. Set IMEX=1. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1279639&SiteID=1 Tag: Count 4 tables
  - 9
    - Could not load package because of error 0xC001000A Are you running this on the same server where SQL Server is located (I don't see a /Server option, so I assume so) It is saying that it cannot find the package. Have you logged into Integration Services with the user "sa" and verified that you can find the package Tag: Count 4 tables
  - 10
    - Disabling extended stored procedures for security There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks. Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users. Tag: Count 4 tables
  - 11
    - The SSIS package does not run using SQL Server Agent job Could post the error Tag: Count 4 tables
  - 12
    - smalldatettime use the set dateformat dmy before your batch.. Code Snippet set dateformat dmy declare @ValueDate smalldatetime set @ValueDate = '21/05/2007' select @ValueDate Tag: Count 4 tables
  - 13
    - missing borders when SSRS report is rendered in Sharepoint Hi , I am trying to host an SSRS report as a web part in Microsoft Sharepoint 2007. All the elements and data come out well except that a few borders are missing in the rendered report. What could be the possible cause and solution for this. Please suggest. Tag: Count 4 tables
  - 14
    - Reporting services permission denied on microsoft access linked I have a Microsoft access 2003 database with tables linked to a number of "SharePoint lists" I do this by going to File -> Get External Data -> Link Tables. A new window pop-ups in which I select Files of Type to be Windows SharePoint Services.. Then I select the correct site.. Then select the correct Lists.. Then access correctly links the tables to the SharePoint lists. When I Open reporting services and connect to this access database. "Using the Test Connection" (Jet 4.0) its successful" When i when create a new report and select the new datasource i have just created and in reporting services I write a select all query on one of the tables in access I get this error: An Error occurred while executing query. You do not have the necessary permissions to use the “object. Have your system administrator or the person who created this object establish the appropriate permissions for you. Catastrophic Failure. I have tried this on the super admin machine and still get the same error Any suggestions or help Tag: Count 4 tables
  - 15
    - IDENTITY_INSERT Issue You should search Books On-Line for that command you are trying to run. Default permissions for that command (SET IDENTITY_INSERT) are set to the sysadmin role. Below is for SQL 2000, but I imagine the same is said for 2005. (BOL doesn't seem to be working too well for me at the moment.) http://msdn2.microsoft.com/en-us/library/aa259221(SQL.80).aspx Tag: Count 4 tables
  - 16
    - SQLIS Checksum Install Problems In case anyone else has this problem... here's the solution. I didn't have the file: C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.ExceptionMessageBox.dll Grabbing from another computer and copying it to the proper location, I did the following: gacutil.exe /i Microsoft.ExceptionMessageBox.dll restart BIDS No problems now. Tag: Count 4 tables
- Next
  - 1
    - Duplicates This sounds like a transact-sql question, not an SSIS question. But, what is the primary key of the table Dim_date Tag: Count 4 tables
  - 2
    - Adding Columns and rows in pivot table to Excel 2007 Hi, I am developing Excel pivot table reports pointing to Analysis services Cube (SSAS 2005). I tried to add Columns and rows inside the pivot table but it doesnt allow me to do it. Is there a way to achieve this kind of scenario in Excel 2007 apart from converting pivot table into a normal table and manipulating it. I once read that pivot table is a single object and we cannot manipulate(i.e. add new rows and columns) inside the pivot table. Thanks Kiran Tag: Count 4 tables
  - 3
    - Error: Incorrect syntax near the keyword 'DECLARE' What do you want return from you function If you want return results of: SELECT DISTINCT sl.JOB_ID, o.ID FROM @OUTCOMES o INNER JOIN (SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID FROM SCENE_LINK WHERE PROPERTY_ID IN (97, 151)) AS sl ON o.ID = sl.START_SCENE_ID Try following T-SQL: CREATE FUNCTION fn_FunctionName ( @ElementID int ) RETURNS TABLE AS RETURN SELECT DISTINCT sl . JOB_ID , o . ID FROM fn_GetDescendantsByClass ( @ElementID , 302 ) o INNER JOIN ( SELECT START_SCENE_ID , DEST_SCENE_ID AS JOB_ID FROM SCENE_LINK WHERE PROPERTY_ID IN ( 97 , 151 )) AS sl ON o . ID = sl . START_SCENE_ID GO Tag: Count 4 tables
  - 4
    - SQL Server Reporting Services and Http Compression We have a wierd situation in our project. We have the following setup - 1. a Production web server cluster with 4 web servers 2. a Production sql reporting server that has reports called from web apps hosted on the production cluster We had an app on Production that was serving Pdf reports (not saved on the server) using he report server. Last week we setup Http Compression on the web servers. From around the same time, the Pdf reports no longer come up. Opening the report just produces a blank IE window. When I hit the same reporting server for the same report using a test web server (thus bypassing the production cluster and the Http Compression), the report comes up fine. Is the Http Compression interfering with the Pdf data served The report is called using an aspx page and .aspx files are included for compression. Appreciate all your help. Tag: Count 4 tables
  - 5
    - SQL Express compatibility mode I believe that if you 'Upgrade' an MSDE installation, and/or if you attach a SQL 2000 database to SQL Express, the compatibility is automatically set at [80]. If you need a 'tool' to manage your SQL Express installation -rather than be limited to command lines only, you may wish to check out the SQL Server Management Studio -Express version. SQL Server 2005 Express Edition (Advanced/SSMS) http://tinyurl.com/yelwr9 SQL Server 2005 SSMS Express Tutorial http://tinyurl.com/nqsca Tag: Count 4 tables
  - 6
    - SSRS Report Parameters You will have to chop it up manually yourself. This will be very easy to do in a custom function in the code section of the report. The &[ characters are constant across all members of the heirarchy, as is the TO prefixing the time section. So you can either Split() the string based on those, or SubStr() it, whichever you prefer. Then perform a Format(CDate(yourDateString), "your format code") . Easy as pie Tag: Count 4 tables
  - 7
    - MSSQL2005 Analysis Service Distinct Count Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure. You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure. It it possible the view you defined your partitions on, brings data sorted differently Any new data becomes avaliable during processing of the partition The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database. See if you might need to define collation correctly for your sort. Edward. -- This posting is provided "AS IS" with no warranties, and confers no rights. Tag: Count 4 tables
  - 8
    - Maximum Number of Processes Reached Maybe engine forum can answer question about max_worker_threads. Tag: Count 4 tables
  - 9
    - Bar Charts - possible to set max widths of columns ? i have the same issue. i would like to be able to set the width of the bars in the bar chart so that i dont end up with 2 fat bars or 20 razor thin bars. is there a way to do this Tag: Count 4 tables
  - 10
    - Report Builder Query Make sure your calculated and non-calculated measures are housed in the same measure group folder. To move the calcualted measure to a measure group's folder, go to the Calculations tab in the Cube Designer. There is a Properties button in the toolbar which will allow you to choose the calculated member and which measure group to associate it with. (Don't set the display folder.) Deploy your cube and rebuild your Report Model off the DSV. B. Tag: Count 4 tables
  - 11
    - Trigger / Constraint / ? One way would be to add the StartDate and EndDate to the Resource table. (This also allows a finer control on resources, some Events 'may not' require a Resource for the entire time of the Event. For example, a all day event in a conference may only require a live video feed for 2 hours.) Then, the Resource list is filtered to ONLY display those resources available during the event (or Requested Resource time). Something like this: Code Snippet SELECT r.ResourceID, r.ResourceName, r.ResourceDescription, r.etc FROM Resource r JOIN Resource r2 ON ( r.ResourceID <> r2.ResourceID AND ( r2.StartDate <= @ResourceRequestEnd AND r2.EndDate > @ResourceRequestStart ) ) Using a scheme similar to the above, there would be no need to email anyone 'for resources that have the flag set' since it would be impossible to 'double book'. And 'sick leave' is a Resource Tag: Count 4 tables
  - 12
    - tsql for database , size , free space or used space in database Use stored procedure: sp_spaceused Results: database_name database_size unallocated space ------------------------- --------------------- -------------- AdventureWorks 1143.00 MB 128.34 MB reserved data index_size unused ------------------ ------------------ ------------------ ------------------ 184992 KB 88264 KB 89456 KB 7272 KB Tag: Count 4 tables
  - 13
    - SQL DB Cleanup How do you define/determine "needed" Tag: Count 4 tables
  - 14
    - Retriving Deleted record from database if you have the backup which was taken before the datas were deleted you can make use of it and restore it any other server and retrieve your records................ Tag: Count 4 tables
  - 15
    - Date part of DateTime try this... SELECT * FROM tblRecords WHERE datediff(day,inputdate,'1/30/2007') = 0 Tag: Count 4 tables
  - 16
    - Can SSIS save the context of errors at the package level? Yes, it certainly is. Event handlers are what you need. Check this out: Custom Logging Using Event Handlers ( http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx ) -Jamie Tag: Count 4 tables

CoryInIraq

I am trying to get a count of rows in 4 tables at once.

Output:

Table1 Table2 Table3 Table4

12345 143 892626 28428

Or maybe even

Table Count

Table1 12345

Table2 143

Table3 892626

Table4 28428

Thanks,

Cory

Re: Count 4 tables

Manivannan.D.Sekaran

here it is...

Select

(Select Count(*) From Table1) [Table1],

(Select Count(*) From Table2) [Table2],

(Select Count(*) From Table3) [Table3],

(Select Count(*) From Table4) [Table4]

--Or

Select 'Table1' Table, Count(*) Count From Table1

Union ALL

Select 'Table2' Table, Count(*) Count From Table2

Union ALL

Select 'Table3' Table, Count(*) Count From Table3

Union ALL

Select 'Table4' Table, Count(*) Count From Table4

Re: Count 4 tables

hangover

Would this do the trick

SELECT 'Table1' AS table, COUNT (*) AS count FROM table1 UNION

SELECT 'Table2', COUNT (*) FROM table2 UNION

SELECT 'Table3, COUNT (*) FROM table3 UNION

SELECT 'Table4', COUNT (*) FROM table4

Re: Count 4 tables

Manivannan.D.Sekaran

At always better option to use UNION ALL rather than UNION.