InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:11:33 GMT20http://www.sqlservercentral.com/Forums/Topic812869-149-1.aspxHello SqlServerCentrals UsersI have this question/request.Id | Name | StartDate. | Enddate | Type ---------------------------------------------------------------------------------2 | Marie | 11/2/2009 | 11/2/2009 | A13 | Hans | [u]11/3/2009[/u] | [u] 11/6/2009 [/u]| A44 | Marie | 11/4/2009 | 11/4/2009 | A15 | Marie | 11/5/2009 | 11/5/2009 | A46 | Marie | 11/6/2009 | 11/6/2009 | A3And I would like to display if a startdate and the enddate not are the same like this.Name | Monday | Thusday | Wednesday | Thursday | Friday---------------------------------------------------------------------------------Hans | | A4 | A4 | A4 | A4Marie | A1 | | A1 | A4 | A3And the final request is how can I exspand the procedure so i can view 5 or 6 weeke at one time?So far I this query but it only works with startdate:SELECT Name, [Monday], [Tuesday], [Wednesday], [Thursday], [Friday] FROM (SELECT Name, type, Datename(Weekday, Startdate) Myweekname FROM wp_Vacation) Src PIVOT (max(type) FOR Myweekname IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt I was helped by some people on asp.net. I'm pretty new to the sql syntax.So if somebody also can explain the syntax I would be glad.Tue, 03 Nov 2009 05:44:38 GMTaslowmohttp://www.sqlservercentral.com/Forums/Topic814834-149-1.aspxwhat is the fragmentationFri, 06 Nov 2009 04:54:37 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814829-149-1.aspxwhat is the DeadLock?How we can resolve the deadlock?Fri, 06 Nov 2009 04:50:54 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814855-149-1.aspxwhat is the reporting services?how we can use reporting servies?Fri, 06 Nov 2009 05:16:50 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814849-149-1.aspxwhat is sql profile? and how we can use the Sql ProfileFri, 06 Nov 2009 05:13:37 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814831-149-1.aspxwhat is means by recovery model? Types of recovery modelsFri, 06 Nov 2009 04:51:36 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814828-149-1.aspxwhat is the logshipping process,how to configure logshipping processFri, 06 Nov 2009 04:50:10 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814888-149-1.aspxHi Folks,I did an install of service pack 3 for sql server 2005. It came back as successful but when I check in management studio it shows as the sp2 version. I also checked patchlevel in registry editor and it shows the sp2 version (3042).What the heck am I doing wrong? My account is part of the administrators group. If anyone knows anything I would appreciate as this is driving me crazy.ThanksTroyFri, 06 Nov 2009 06:28:22 GMTtroypearcehttp://www.sqlservercentral.com/Forums/Topic814847-149-1.aspxHow to configure sql server Agent?Fri, 06 Nov 2009 05:12:24 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814835-149-1.aspxwhat is the De-Normalization process?Fri, 06 Nov 2009 04:55:28 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814851-149-1.aspxwhat is sql scheduler?how e can use sql scheduler?Fri, 06 Nov 2009 05:15:20 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic815240-149-1.aspxHello All,I have a query that uses 6 joins to produce a results set. It is in the form of:Select A.field1, B.field2, C.field3, etc....From Table AInner Join Table BOn A.field1 + A.field2 = B.field1 + B.field2Inner Join Table COn A.field1 + A.field2 = C.field1 + C.field2Inner Join Table DOn A.field1 = D.field1 --this is needed to get D.field99Inner Join Table EOn [b]A.field1 + D.field99[/b] = E.field1 + E.field2Inner Join Table DOn [b]A.field1 + D.field99[/b] = F.field1 + F.field2This query runs as expected and the results have been verified. But in looking back at it I confused as to how the SQL Server engine could process (reliably) two different tables on the same side of the join statement. It seems as though this would not work because the order of processing the joins is left up to SQL Server.Should this work? Would this work in other SQL databases? Is there a better way?....CTE’s etc….Thank you,LonnieFri, 06 Nov 2009 16:06:10 GMTlmeinkehttp://www.sqlservercentral.com/Forums/Topic812906-149-1.aspxI just took a job as a SQL DBA at a very large enterprise. Things are NOT done here like I am used to! The way they have me deploy changes to live production SQL servers is to develop and test my changes on live production SQL servers. Yes, that's really how its done.And I know what will happen soon: I will deploy something that will break or destroy something (it is inevitable!), and then I will get blamed for not knowing what I'm doing. It feels like I am playing Russian Roulette on a daily basis. Not good for the 'ol stress level! So here is my fix: I have now gotten them to give me FIVE clunker old computers, good enough to install Windows Server 2003 and SQL Server 2000 or 2005. So I can set up my own little test lab in my cubicle. This way I can at least build a reasonable facsimile of the production environments (yes, some are even clusters, etc), backup and restore databases to my test environment, and do *some* development and testing on them in my lab, before deploying to a production server.So I have downloaded the 6-month trial versions of Win Server and SQL Server. I will NOT (and cannot) connect these computers to the network, and therefore not to the internet either. I have to download on one computer, burn files to disk, then move that disk to the Lab computers (sneakernet!). This will be a fully self-contained lab, the computers will only talk to each other via a hub. That's it.So my question is: What licensing, if any, do I need to be fully, totally, legitimately legal and in compliance with Microsoft? (Yes, you would [i]think[/i] I could just ask my Microsoft Rep that question. But I have no idea who that person might be or how I would get in touch, or anything.)Perhaps I could just install my 6-month demo versions of everything? Then I think I will have to activate over the phone, right? But then will I have to activate all TEN licenses separately? (5 Win Server 2003 and 5 SQL 2000 or 2005??) Or does only Windows Server require activation and not SQL? (I'm used to working on real versions!) Do my TWO downloads allow 5 activations each?? Or do I have to do 10 downloads?? If I install today, would I need to re-install 6 months from now? Or is that even permitted -- do I just get one 6-month period then I have to have them pay for some sort of "Lab license"?? Or is there a license that actually allows this Test Lab type scenario, and does not require re-installs, or new demo licenses every 6 months or whatever??It seems like the possibilities are endless. Does anyone know?Thank you very much for your help!Tue, 03 Nov 2009 07:12:07 GMTsubs99http://www.sqlservercentral.com/Forums/Topic684266-149-1.aspxHi, I have seen quite a few requests for help on the subject of importing data from Access and Excel and I have followed all the advice given but I am still pulling out what little hair I have left as sod’s law says it will work on all servers apart from the one I want it to.As a test I am running the following, which as I say works on every other server apart from this one which is also running SQL 2005 Version 9.00.3042. SP2 Standard Edition.select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;database=d:\DataExtracts\test.xls', [Sheet1$]); which produces the following error message.OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".I have :-1) Set the Distributed Transaction Coordinator.2) Tested that SQL can write to the SQL services logon TEMP folder.3) Tested that SQL can write to the D:\DataExtracts folder4) Ensured that the test.xls is the same one used on all other servers for testing.The excel being used is 2003 and I have also tried Excel 5.0 in my openrowset statement.In my investigations I also read quite a bit about creating a linked server so I thought I would try that and the commandEXEC sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'd:\DataExtracts\test.xls', NULL, 'Excel 8.0'Return Command(s) completed successfully.However the command SELECT * FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')Returned the same error as before.Then I wondered if it was the RPC properties of the linked server and I attempted to edit them from False to True but that resulted in the error messageCannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData".OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)So now I am completely at a loss. I am going to check the MDAC but the installation came from the same set as the other servers so I suspect that will not be the issue.Any help or fresh ideas would be most appreciated. Thanks.:crazy:Thu, 26 Mar 2009 09:27:40 GMTRonald Cartmalehttp://www.sqlservercentral.com/Forums/Topic652343-149-1.aspxI'm trying to create a query that can read data from an EXCEL 2007 worksheet. I've installed the appropriate drivers downloaded from[url]http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en[/url] but I can't get it to work. The following query timesout[code]Select * from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=d:\dasdgigs.xls;', 'SELECT * FROM [dasdgigs$]')[/code]Using the old 2003 driver works fine using the following query I can successfully reads the data in the dasdgigs.xls sheet.[code]Select * from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)}; DBQ=d:\dasdgigs.xls;', 'SELECT * FROM [dasdgigs$]')[/code]I've tried the obvious things, like uninstalling and reinstalling the drivers, rebooting etc. but I just can't make any progress. Using SQL 2005 Developers Edition.Does anyone know what I may be doing wrong?Sat, 07 Feb 2009 21:14:26 GMTadrian-504794http://www.sqlservercentral.com/Forums/Topic813102-149-1.aspxHello I am trying to recreate a process that I had written in Access SQL into TSQL to speed up the process. What I am doing is taking a field that looks as such: Mid-Penn CDC 21 South 12th Street Philadelphia, PA 19108Note that it is one field with character return and line feeds separating the different parts of the address. So far I have been able to pull the name out of the address using this LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1) AS def and it works great. Next I have to be able to pull the second line and then only the ZIP code out. I have a table of zip codes that I am going to run against that field to pull the correct city and state because much of the time there is only a zip code in that field. I believe I can just use the RIGHT function for the zip code and have it pull the last 5 characters of the field but I have no idea how to pull the mailing address. I have a tried a few things combining LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1) with another CHARINDEX statement but that is not how it works it would appear. Any help would be much appreciated.Cheers,GeorgeTue, 03 Nov 2009 10:49:24 GMTgeorge.greinerhttp://www.sqlservercentral.com/Forums/Topic814806-149-1.aspxCREATE TABLE T1(idno varchar(30) NOT NULL, seqno varchar(30) NOT NULL, Last_Name varchar(30), First_Name varchar(30) constraint t1c PRIMARY KEY (idno,seqno) );CREATE TABLE T2(idno varchar(30) NOT NULL , seqno varchar(30) NOT NULL , FLAG varchar(1), constraint t2c PRIMARY KEY (idno,seqno));T1 idno seqno L_name F_name1 A aaa aaaaa1 B bbb bbbbb2 A aba ababa2 B bab babab3 C ccc ccccc4 D ddd ddddd5 E eee eeeeeT2 idno seqno Flag1 A 01 B 12 B 14 D 0Result idno seqno L_name F_name2 A aba ababa3 C ccc ccccc5 E eee eeeeeHow to get the above result, help us out?Fri, 06 Nov 2009 03:57:42 GMTragunaathanhttp://www.sqlservercentral.com/Forums/Topic814833-149-1.aspxwhat is the normalization of the databaseFri, 06 Nov 2009 04:53:48 GMTRKGoriparthihttp://www.sqlservercentral.com/Forums/Topic814765-149-1.aspxOur data warehousing systems are currently running in SQL Server 2005 technologies. could you please share your ideas/experiences/expertise on the following queries regarding upgrading to SQL 2008.* what are the benefits on moving to SQL 2008? * How easy the migration is from SQL 2005? * We have packages written in SSIS for ETL purpose, do they have to be recompiled? * We have SSAS cubes written in SSAS 2005, will they work without any changes? Thanks in advance.Fri, 06 Nov 2009 02:29:56 GMTesnkumarhttp://www.sqlservercentral.com/Forums/Topic814124-149-1.aspxI need some pointers to the following error that I'm seeing in the error log at random(occurs roughly a dozen times a day). Apologies in advance for the length of the post - I just wanted to provide as much info as possible.[b][i]SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [t:\mssql\data\temp0.ndf] in database [tempdb].[/i][size="1"][/size][/b]This error is repeated for each of the 8 tempdb files that exist on the drive. The drive is a RAID 1+0 array (6 disks) and is dedicated for the tempdb database. The value of x varies between different files and times (sometimes it's 1 and sometimes it's over 1000).Now, the error would clearly suggest a problem with the I/O subsystem. However, looking at the disk performance counters when these errors occurred indicate that disk performance isn't an issue (unless I'm missing something ;-)) At the times of the errors the disk reads/sec and the disk writes/sec is often 0 and likewise the disk queue length is 0. Sometimes there is a bit of activity on the disk but no more than 200 reads/sec.I should point out that there are times when the disk is under more load and handles over 800 I/O's per sec - which you'd expect from a 6-disk RAID 10 configuration. Also, at times of heavier load the errors never occur.I've checked with the person responsible for the SAN and looked at their performance history and nothing would suggest that things are overloaded on the SAN (in fact, things looked pretty quiet as far as they're concerned).Previously, the tempdb was on a single file on a RAID 1 array and was moved to the RAID 10 array and split into 8 files to match the 8 cores on the box. The reason tempdb was moved was because the RAID 1 array couldn't cope - curiously though none of these errors occurred even though the RAID 1 array was clearly under too much stress.Is there any known relationship between number of tempdb files and this error? I know that a potential CPU drift can cause this error to appear erroneously but I don't think we've got a CPU drift issue - but I can't be certain.Allowing for the fact that disk performance issues are ruled out is there anything else I should be looking at for potential causes? Of course, if there are potential disk issues that I have overlooked then feel free to let me know.I should mention that there are no noticeable performance issues (i.e. reports of slow running queries or processes) when these errors appear - but it's just annoying me that they're in the error log.Thanks.Thu, 05 Nov 2009 04:42:04 GMTSQLZhttp://www.sqlservercentral.com/Forums/Topic814263-149-1.aspxHi,Does anyone know if there is any difference in performance when you put your criteria with your INNER JOIN rather than in your WHERE clause?For example:SELECT t1.IdFROM dbo.Table1 AS t1 JOIN dbo.Table2 AS t2 ON t1.Id = t2.Id AND t2.Active = 1Verses:SELECT t1.IdFROM dbo.Table1 AS t1 JOIN dbo.Table2 AS t2 ON t1.Id = t2.IdWHERE t2.Active = 1Thu, 05 Nov 2009 08:00:29 GMTVicki Petershttp://www.sqlservercentral.com/Forums/Topic814605-149-1.aspxProfiler version 2008 running against a 2005 SQL Server box.I'm not sure on how best to word this so apolagies if this seems verbose. Currently in Profiler whgen capturing amy SELECT/INSERT/UPDATE/DELETE command I get the sam T-SQL command at least 4 times. Stmnt:Starting/Stmnt:Completed and then the Batch equiivelants. I can narrow this down by dropping the Batch items and while I could further reduce this by keeping just the STmnt:Completed event, I like to include the Stmnt:Starting event because if there is some long running execution, I can see the actual T-SQL ebing executed where as with Stmnt:Completed you see the T-SQl code only after it's completion.My thinking is there shoudl be a way so that when tracing the STmnt event, you get just 1 line that shows as soon as the command starts and when the command is done, instead of getting another event line, some value in the event line you alrady have simply changes to indicate the status has changed. Make sense>So if I am tracing the activity and a long running SELECT starts I see that SELECT in my trace and it shows with a status of Stmn:Starting and then once it's done the status is chnaged to Stmnt:Completed and no additional copy or event for that same T-SQL is shown. It juist takes a long time dig thru what often feels like a lot of excess capturing.Make ssense? Anyone have any tiops on how to do this or at least to minimize the logging while still maintining the status of the event?ThanksThu, 05 Nov 2009 15:41:41 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic813344-149-1.aspxWe are creating some new update scripts that will be run on a live database. The scripts will be running on the main table for the database, so concurrency is very important.My manager has decided to run a script that uses a cursor to absorb the contents of a select statement and then does an update row by row. I asked him about it, and he said the team had used it before because it was the best way to prevent table locks.We aren't too concerned with speed on these queries, but is there a better way?Tue, 03 Nov 2009 17:19:10 GMThuston.dunlaphttp://www.sqlservercentral.com/Forums/Topic814642-149-1.aspxHi,I am going to have roughly 40 to 50 excel workbooks across the business where users will enter data into the sheets and then press a button and the workbook will call a SQL 2005 stored proc to insert records into the database. The VBA code will be essentially identical to VB 6. I can't use SSIS and create and ETL package. It just isn't feasible as the inserts almost need to be realtime.Essentialy the fields in the destination fact table are Program, Project, Employee Type, Month, Year, Hours, Cost, WindowsAuthenticationID and TimeStamp.There are a total of 252 total records that can be loaded per Project, so once the user presses the load button, I want to delete the 252 records out of the destination table (if they exists) and repopulate the destination table with the new 252 records.The problem I am having with this idea is that each insert has to be done record by record and I want to make sure all records made it in before I delete records. So I think I need to create a temp table, insert the records into a temp table, count to see if the record count = 252 AND perform a sum on Hours and Cost to make sure the sum from the temp table matches the sum in the excel file (separate insert into a another temp table to compare).So here is the entire process:I am wondering if it would be considered good practice to execute two stored procedures before the insert procedure (252 records) where the first stored proc will create a temp table with the name of the windowsAuthID and random number (something like that so there are never two duplicate temp tables) and run the second stored procedure to create a second temp table to insert the totals from the excel spreadsheet and then run the third stored procedure to insert the 252 records into the first temp table. Sum and count the first temp table and compare against the 2nd totals temp table and if the values are equal, then go ahead and delete the 252 records out of the fact table and insert the new 252 records from the 1st temp table. If the sum and counts do not balance, then I would need to raise an error and notifiy the user (this part I would need help on doing).Is this overkill? Is there a better way to do this only using excel 2003 and sql 2005?Should I be interfacing with the fact table or should I be using the View to do the inserts?Any help and guidance would be much appreciated.Thank You.Thu, 05 Nov 2009 18:38:27 GMTease2002ahttp://www.sqlservercentral.com/Forums/Topic814461-149-1.aspxI am using SQL 2005.I am trying to compare values between 2 tables.I have two tables like example Table1 and Table2create table Table1( col1 varchar(10), col2 varchar(10))create table Table2( c1 varchar(10), c2 varchar(10))insert into Table1 values('ABC','USA')insert into Table1 values('XYZ','USA')insert into Table2 values('ABC','USA')Now I need a output query which will give the records which are present in Table1 but are not present in Table2in this case it would be 'XYZ','USA' since 'ABC','USA' is also present in Table2.Please note while comparing values I am looking at the combination of the two columns in each table.Thanks.Thu, 05 Nov 2009 11:41:11 GMTznkinhttp://www.sqlservercentral.com/Forums/Topic813423-149-1.aspxHi,I have one query,suppose I create a store procedure in sql 2005 using below statement :create procedure dbo.<procname>as( some stmts)endPlease help me in understanding the significance of dbo used in the create statement.Also what will be the difference in access rights with or without using dbo.Thanks in advance...Tue, 03 Nov 2009 22:59:47 GMTruchi-761283http://www.sqlservercentral.com/Forums/Topic814397-149-1.aspxI was wondering what some of the options are to set this up?I could execute a .bat file and have that touch files over the network, but batch files don't really do well with network stuff.I was thinking about PowerShell, but that doesn't do remote work well, BUT, it could still execute on the local machine and just reach out to the network share and delete the files that way.So a scheduled task on SQL server kicks off a sproc and the sproc then does "whatever" to get the job done.The sproc is in the database that consumes these log files that are being deleted and because of that I want the delete sproc to be in the database as well so if the database moves around, etc... folks don't forget about it entirely.Thoughts, suggestions?Thu, 05 Nov 2009 10:06:17 GMTMaxerhttp://www.sqlservercentral.com/Forums/Topic437888-149-1.aspxAnyone know if there's a DMV that tracks page splits? I need some kind of indication as to number of page splits per object and index over a period of time.I can get this info out of the tran log, but that's messy.Wed, 02 Jan 2008 03:39:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic798164-149-1.aspxI'm hpoing this isn't just me. Is anyone else having a problem with SSMS locking up /freezing repeatidly throughout the day even when you aren't running a query? I'm using Vista and I would think it might be because of Vista and its UAC but I did not have this same problem on my prior computer which also used Vista. Thats not to say it's not Vista but that somehow it just never happened on my older Vista box which had half the processing power of my new system.I also am using Red Gatres SQL Prompt whcih i used on my old machine too. I also am running both SSMS 2005 & SSMS 2008 but I was doing the same on my old box. Since I have the same items installed & running I can't figure out why I now am having so much trouble.The thing will stay frozen for up to 30 seconds before retruning to normal. ANy ideas?Mon, 05 Oct 2009 13:48:50 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic813499-149-1.aspxI have written a DDL trigger to log the changes in the database in a Logtable on a separate databae. Things are running very smoothly.Now the issue is what if someone deletes my DDL trigger. Delete DDL Trigger operation is not recorded in my Logtable.I need to have an alert or atleast recorded somewhere if someone drops it. I have checked "ALTER ANY DATABASE DDL TRIGGER" but it locks complete database and user are not allowed to Alter or Create any thing.I know I am missing something. Thanks in advance.Wed, 04 Nov 2009 03:00:34 GMTAtif Sheikhhttp://www.sqlservercentral.com/Forums/Topic813657-149-1.aspxUsing a technique I found on the web in various forums:SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')I get the following errors:OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".Msg 2, Level 16, State 1, Line 0Named Pipes Provider: Could not open a connection to SQL Server [2]. How do I rectify this?Wed, 04 Nov 2009 09:09:26 GMTgdudleyhttp://www.sqlservercentral.com/Forums/Topic813874-149-1.aspxHello,I am trying to generate test data (small set) for our databases. What is an easy way to do so? It should also take care of RI. I tried data generetor tool in VSTS. But it looks I have to manually go thru the tables to generate the data (I have about ~100 tables in my db). Is there any script which can inject data into the database taking care of RI issues? Please suggest any good methods I need to follow. ThanksWed, 04 Nov 2009 14:23:17 GMTkrithik_ehttp://www.sqlservercentral.com/Forums/Topic813781-149-1.aspxHi All,I ran SQL Server Profiler on SQL Server Management Studio while a client was running another program that accessed the same database server through t-SQL, and when I ran SQL Server Profiler he was unable to run the program. When I stopped profiler the program was able to run. I didn't think Profiler locked any resources and am a bit puzzled by this. Can anybody shed any light on such behavior.Thanks in advance,UmayalWed, 04 Nov 2009 12:05:20 GMTumayal5http://www.sqlservercentral.com/Forums/Topic709487-149-1.aspx..for 2008 and or 2005 (I have both although I prefer 2008).I have found the BOL Page on the basic command line arguments for SSMS.exe however I can't believe the application is limnited to just those listed in BOL. I'm thinking there are more arguments tha are 'undocumented'. Does anyone know of any undocumented arguments for SSMS version 2005 or 2008 (is they differ)? Also any tricks on laucnhing SSMS to do various things would be neat to know such as how to setup a conmfiguration file (if such a thing exists or is possible with SSMS).I have a Logitech keyboard with the cutomizable buttons for launching apps and I was setting up one to launch SSMS 2008 and connect to a specific DB (the one I use most often) so I could convert many steps into the push of a button. While I can get SSMS 2008 to load (via NT AUthentication) and connect to a specific DB there are a few things missing. For one the object explorer on the left side of SSMS is blank when I luanhc from the command line.[size="3"][u][b]FROM BOL 2008:[/b][/u][/size] [scriptfile] [projectfile] [solutionfile] [-S servername] [-d databasename] [-U username] [-P password] [-E] [-nosplash] [-?][size="2"][b]Arguments[/b][/size][i]scriptfile[/i] Specifies one or more script files to open. The parameter must contain the full path to the files.[i]projectfile [/i] Specifies a script project to open. The parameter must contain the full path to the script project file.[i]solutionfile [/i] Specifies a solution to open. The parameter must contain the full path to the solution file.[-[b]S[/b] [i]servername[/i]]Server name[-[b]d[/b] [i]databasename[/i]]Database name[-[b]U[/b] [i]username[/i]]User name when connecting with SQL Server Authentication[-[b]P[/b] [i]password[/i]]Password when connecting with SQL Server Authentication[-[b]E[/b]]Connect using Windows authentication-[b]nosplash [/b]Prevents SQL Server Management Studio from displaying the splash screen graphic while opening. Use this option when connecting to the computer running SQL Server Management Studio by means of Terminal Services over a connection with a limited bandwidth. This argument is not case-sensitive and may appear before or after other arguments[-[b]?[/b]]Displays command line helpMon, 04 May 2009 10:15:35 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic813159-149-1.aspx:-D:-D I've tried the DTW (Database Tuning Wizard) and it has no recommendations for tuning this query and query plans show low percetages on cost. It's a pretty ugly query. Any suggestions or help would be greatly appreciated. Thanks in advance and beware below :-D:/****** Object: StoredProcedure [dbo].[Report_SpendPerHeadDetail] Script Date: 11/03/2009 13:23:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate proc [dbo].[Report_SpendPerHeadDetail]@MerchantID int,@StockOwnerID int = 87, -- Need to be modified later@Start datetime,@End datetime,@EPOSManual varchar(10),@Unit varchar(max) = '-1'as---- For Debugging Begin ----declare @MerchantID int--declare @StockOwnerID int--declare @Start datetime--declare @End datetime--declare @EPOSManual varchar(10)--declare @Unit varchar(max)----set @MerchantID = 89--set @StockOwnerID = 87--set @Start = '05/01/2009'--set @End = '05/10/2009'--set @EPOSManual = 'EPOS'--set @Unit = '-1'---- For Debugging End --create table #temp (Country varchar(50), CountryID int, AirportCode varchar(10), AirportName varchar(50), Number decimal(18,2), DataType nvarchar(max), I int, i1 int identity)create table #temp1 (i int identity, Airport varchar(10), AirportName varchar(50))create table #Unit (AirportID int)Create table #tempCategory (AirportID int, AirportCode varchar(10), AirportName varchar(50), CountryID int, Country varchar(50), NetSales decimal(18,2), Pax int, CategoryID int, CategoryName varchar(50), BrandName varchar(50),ID int)Create table #tempCat(AirportID int,AirportCode varchar(10),AirportName varchar(50),CountryID int,Country varchar(50),CategoryID int,CategoryName varchar(50),NetSales decimal(18,2),Pax int,SpendPerHead decimal(18,8))--Recursive tablecreate table #tempRecursive (CategoryID int,CategoryName varchar(50), ID int, [Level] int)--Tilting tablecreate table #tempC (Country varchar(50), CountryID int,AirportCode varchar(10),AirportName varchar(50),CategoryName varchar(50),BrandName varchar(50),Number decimal(18,8),DataType nvarchar(max),I int,i1 int identity)declare @sql nvarchar(max), @sql1 nvarchar(max)declare @i int, @j intset @i = 1--This is to find out if any Warehouse has been selected.if @Unit = '-1' begin insert into #Unit select AirportID from Base end--This is to strip the Unit'selse begin while charindex(',', @unit) > 0 begin insert into #Unit values (convert(int, substring(@Unit, 1, charindex(',', @unit)-1))) set @Unit = substring(@Unit, charindex(',', @unit)+1, len(@Unit) - charindex(',', @unit)) end insert into #Unit select(convert(int, @Unit)) end--This is the 1st Partselect A.*into #temp2from (select distinct fr.originatingairportid as AirportID, a.AirportCode, a.AirportName, c.CountryId, c.Name as COuntry, convert(decimal(18,2), GS.GrossSales - D.Discount) as [Net Sales], (GS.EPOSSales - D.EPOSDiscount) as EPOSNetSales, convert(decimal(18,2), UnitsSold) as [Units Sold], convert(decimal(18,2), NoOfEPOSTransactions) as [No. Of EPOS Transactions], convert(decimal(18,2), pax) as PAX, case when Pax = 0 then 0 else convert(decimal(18,2),(GS.GrossSales - D.Discount)/pax) end as [Actual SPH], convert(decimal(18,2), 0) as [Forecast SPH], convert(decimal(18,2), 0) as [Crew Target SPH], convert(decimal(18,2), 0) as [Variance (Act Vs Crew)], convert(decimal(18,2), 0) as [Variance (Act Vs ForeCast)], case when NoOfEPOSTransactions = 0 then 0 else convert(decimal(18,2), (GS.EPOSSales - D.EPOSDiscount)/NoOfEPOSTransactions) end as [Average Transaction Value EPOS], case when NoOfEPOSTransactions = 0 then 0 else convert(decimal(18,2), convert(decimal(18,2), EPOSUnitsSold)/convert(decimal(18,2), NoOfEPOSTransactions)) end as [Average Number Of Items Per Transaction EPOS], EPOSUnitsSold from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID join Airport a with (nolock) on a.AirportID=fr.OriginatingAirportID join Country c with (nolock) on c.CountryID=a.AirportCountry join StockOwnerAirline soa with (nolock) on soa.AirlineID=fr.AirlineID --Gross Sales and Units Sold join (select distinct fr.OriginatingAirportID, case when EPOSSales is null then 0 else EPOSSales end + case when ManualSales is null then 0 else ManualSales end + case when VirtualProductSales is null then 0 else VirtualProductSales end as GrossSales, case when EPOSUnitsSold is null then 0 else EPOSUnitsSold end + case when ManualUnitsSold is null then 0 else ManualUnitsSold end + case when VirtualUnitsSold is null then 0 else VirtualUnitsSold end as UnitsSold, NoOfEPOSTransactions, case when EPOSSales is null then 0 else EPOSSales end as EPOSSales, case when EPOSUnitsSold is null then 0 else EPOSUnitsSold end as EPOSUnitsSold from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID --Manual Sales-- left outer join (select sum(case when soa.Virtual=0 then (wc.StartCount-wc.EndCount)*aap.BasePrice else 0 end) as ManualSales, sum(case when soa.Virtual=0 then (wc.StartCount-wc.EndCount) else 0 end)as ManualunitsSold,fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=wc.AirlineArticleID join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID and aap.StartDate <= bi.FlightDate and aap.EndDate >= bi.FlightDate where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All') and bi.IsManual = 1 group by fr.OriginatingAirportID ) A on fr.OriginatingAirportID=A.OriginatingAirportID --Manual Sales End-- --Virtual Product Sales-- left outer join (select sum(mvp.qty * aap.BasePrice) as VirtualProductSales, sum(mvp.qty)as VirtualUnitsSold,fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID join ManualVirtualProduct mvp with (nolock) on cb.CashBagID=mvp.CashBagID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=mvp.AirlineArticleID join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID and aap.StartDate<=bi.FlightDate and aap.EndDate>=bi.FlightDate where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All') group by fr.OriginatingAirportID ) C on fr.OriginatingAirportID=C.OriginatingAirportID --Virtual Product Sales-- --Auto Sales-- left outer join (select sum(TotalAmount) as EPOSSales, sum(Qty) as EPOSUnitsSold, fr.OriginatingAirportID, count(distinct t.transactionid) as NoOfEPOSTransactions from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID join [Transaction] t with (nolock) on fb.FlightInstanceID=t.FlightInstanceID join TransactionType tt with (nolock) on tt.TypeID=t.TypeID join TransactionArticle ta with (nolock) on t.TransactionID=ta.TransactionID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('EPOS','All') and tt.TypeName='SALE' and t.Voided=0 and bi.IsManual=0 group by fr.OriginatingAirportID ) E on fr.OriginatingAirportID=E.OriginatingAirportID --Auto Sales-- where bi.FlightDate >= @Start and bi.FlightDate < @End+1 ) GS on fr.OriginatingAirportID=GS.OriginatingAirportID --Gross Sales End-- --Discount -- join (select distinct fr.OriginatingAirportID, case when A.EPOSDiscount is null then 0 else A.EPOSDiscount end + case when B.ManualDiscount is null then 0 else B.ManualDiscount end as Discount, case when A.EPOSDiscount is null then 0 else A.EPOSDiscount end as EPOSDiscount from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID --Auto Discount -- left outer join (select sum(case when pt.PromotionTypeName='General Promotion' then p.Discount end) as EPOSDiscount, fr.OriginatingAirportID from [Transaction] t with (nolock) join TransactionType tt with (nolock) on tt.TypeID=t.TypeID join TransactionPromotion tc with (nolock) on t.TransactionID=tc.TransactionID join Promotion p with (nolock) on p.PromotionID=tc.PromotionID join PromotionType pt with (nolock) on pt.PromotionTypeID=p.PromotionTypeID join flightbarset fb with (nolock) on fb.flightinstanceid = t.flightinstanceid join barsetinstance bi with (nolock) on bi.barsetinstanceid = fb.barsetinstanceid join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('EPOS','All') and p.AirlineID = @MerchantID and tt.TypeName='SALE' and t.Voided=0 and bi.IsManual=0 group by fr.OriginatingAirportID ) A on fr.OriginatingAirportID=A.OriginatingAirportID --Auto Discount End-- --Manual Discount -- left outer join (select distinct sum(case when pt.PromotionTypeName='General Promotion' then p.Discount*mp.Qty end) as ManualDiscount, fr.OriginatingAirportID from barsetinstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID and bi.FlightDate >= @Start and bi.FlightDate < @End+1 join ManualPromotion mp with (nolock) on cb.CashBagID = mp.CashBagID join Promotion p with (nolock) on p.PromotionID=mp.PromotionID join PromotionType pt with (nolock) on pt.PromotionTypeID=p.PromotionTypeID and p.StartDate<=bi.FlightDate and p.EndDate>=bi.FlightDate where bi.IsManual=1 and @EPOSManual in ('Manual','All') group by fr.OriginatingAirportID ) B on fr.OriginatingAirportID=B.OriginatingAirportID --Manual Discount End-- where bi.FlightDate >= @Start and bi.FlightDate < @End+1 ) D on fr.OriginatingAirportID=D.OriginatingAirportID --Discount End-- --Pax Count-- join (select distinct fr.OriginatingAirportID, case when EPOSPax is null then 0 else EPOSPax end + case when ManualPax is null then 0 else ManualPax end as Pax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID --Auto Pax Count-- left outer join (select fr.OriginatingAirportID, sum(fi.PassengerCount) as EPOSPax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID join FlightInstance fi with (nolock) on fi.FlightInstanceID=fb.FlightInstanceID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('EPOS','All') and bi.IsManual=0 group by fr.OriginatingAirportID ) A on fr.OriginatingAirportID=A.OriginatingAirportID --Auto Pax Count End-- --Manual Pax Count-- left outer join (select fr.OriginatingAirportID, sum(mfi.PassengerCount) as ManualPax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID join ManualFlightInstance mfi with (nolock) on cb.CashBagID=mfi.CashBagID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All') and bi.IsManual=1 group by fr.OriginatingAirportID ) B on fr.OriginatingAirportID=B.OriginatingAirportID --Manual Pax Count End-- where bi.FlightDate >= @Start and bi.FlightDate < @End+1 )P on fr.OriginatingAirportID=P.OriginatingAirportID --Pax Count End-- where soa.AirlineID = @MerchantID and soa.StockOwnerID = @StockOwnerID and bi.FlightDate >= @Start and bi.FlightDate < @End+1 ) A--EUinsert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry, [Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH], [Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS]) select 0,'EU','','','Europe',sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX), case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end, 0,0,0,0, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end from #temp2 where Country not in ('United Kingdom','Switzerland')--UKinsert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry, [Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH], [Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS]) select 0,'UK','','',Country,sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX), case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end, 0,0,0,0, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end from #temp2 where Country in ('United Kingdom') group by COuntry--Swissinsert into #temp2 (AirportID,AirportCode,AirportName,CountryId,COuntry, [Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH], [Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS]) select 0,'SWISS','','',Country,sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX), case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end, 0,0,0,0, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end from #temp2 where Country in ('Switzerland') group by COuntry--Totalinsert into #temp2 (AirportID,AirportCode,AirportName,CountryId,Country, [Net Sales], [Units Sold],[No. Of EPOS Transactions], PAX,[Actual SPH], [Forecast SPH],[Crew Target SPH], [Variance (Act Vs Crew)], [Variance (Act Vs ForeCast)], [Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS]) select 0,'Total','','','',sum([Net Sales]),sum([Units Sold]),sum([No. Of EPOS Transactions]), sum(PAX), case when sum([PAX]) = 0 then 0 else sum([Net Sales])/sum([PAX]) end, 0,0,0,0, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum([EPOSNetSales])/sum([No. Of EPOS Transactions]) end, case when sum([No. Of EPOS Transactions]) = 0 then 0 else sum(EPOSUnitsSold)/sum([No. Of EPOS Transactions]) end from #temp2 where AirportCode not in ('EU','UK','SWISS')--Updating the null valuesupdate #temp2 set [Net Sales]=0 where [Net Sales] is nullupdate #temp2 set [Units Sold]=0 where [Units Sold] is nullupdate #temp2 set [No. Of EPOS Transactions]=0 where [No. Of EPOS Transactions] is nullupdate #temp2 set PAX=0 where PAX is nullupdate #temp2 set [Actual SPH]=0 where [Actual SPH] is nullupdate #temp2 set [Forecast SPH]=0 where [Forecast SPH] is nullupdate #temp2 set [Crew Target SPH]=0 where [Crew Target SPH] is nullupdate #temp2 set [Variance (Act Vs Crew)]=0 where [Variance (Act Vs Crew)] is nullupdate #temp2 set [Variance (Act Vs ForeCast)]=0 where [Variance (Act Vs ForeCast)] is nullupdate #temp2 set [Average Transaction Value EPOS]=0 where [Average Transaction Value EPOS] is nullupdate #temp2 set [Average Number Of Items Per Transaction EPOS]=0 where [Average Number Of Items Per Transaction EPOS] is nullinsert into #temp(Country,CountryID,AirportCode,AirportName,Number,DataType,i) SELECT Country,CountryID,AirportCode,AirportName,Number,DataType, case when DataType='Net Sales' then 1 when DataType='Units Sold' then 2 when DataType='No. Of EPOS Transactions' then 3 when DataType='PAX' then 4 when DataType='Actual SPH' then 5 when DataType='Forecast SPH' then 6 when DataType='Crew Target SPH' then 7 when DataType='Variance (Act Vs Crew)' then 8 when DataType='Variance (Act Vs ForeCast)' then 9 when DataType='Average Transaction Value EPOS' then 10 when DataType='Average Number Of Items Per Transaction EPOS' then 11 end as I from #temp2 UNPIVOT (Number FOR DataType IN ([Net Sales], [Units Sold] , [No. Of EPOS Transactions],[PAX] , [Actual SPH],[Forecast SPH],[Crew Target SPH],[Variance (Act Vs Crew)],[Variance (Act Vs ForeCast)], [Average Transaction Value EPOS], [Average Number Of Items Per Transaction EPOS] )) u order by countryid desc--This is the 2nd part--insert into #tempCategory (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,ID ) select distinct fr.OriginatingAirportID,a.AirportCode,a.AirportName, c.CountryId, c.Name as Country, GS.grossSales - D.Discount, p.Pax, GS.CategoryID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join StockOwnerAirline soa with (nolock) on soa.AirlineID=fr.AirlineID join --GrossSales-- (select distinctcase when @EPOSManual = 'EPOS' then isnull(A.Sales, 0) when @EPOSManual = 'Manual' then isnull(B.ManualSales, 0) + isnull(VirtualProductSales, 0) when @EPOSManual = 'All' then isnull(A.Sales, 0) +isnull(B.ManualSales, 0) + isnull(VirtualProductSales, 0) end as GrossSales, A.Sales as ESales, fr.OriginatingAirportID,B.CategoryID, isnull(B.ManualSales, 0) + isnull(VirtualProductSales, 0) as MSales from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID --Manual Gross Sales-- join (select sum(case when bi.ismanual=1 then (case when soa.Virtual=0 then (isnull(wc.StartCount, 0)-isnull(wc.EndCount, 0))* isnull(aap.BasePrice, 0) end) end) as ManualSales , soa.CategoryID, fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID join AirlineArticle aa on aa.AirlineArticleID = wc.AirlineArticleID join AirlineArticlePrice aap with (nolock) on wc.AirlineArticleID=aap.AirlineArticleID join StockOwnerArticle soa on aa.StockOwnerArticleID = soa.StockOwnerArticleID and aap.StartDate<=bi.FlightDate and aap.EndDate>=bi.FlightDate where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All','EPOS') group by soa.CategoryID, fr.OriginatingAirportID ) B on fr.OriginatingAirportID=B.OriginatingAirportID-- EPOS Gross Sales-- left outer join (select sum(TotalAmount) as Sales, soa.CategoryID, fr.OriginatingAirportID from transactionarticle ta with (nolock) join [transaction] tr with (nolock) on tr.transactionid = ta.transactionid join flightbarset fb with (nolock) on fb.flightinstanceid = tr.flightinstanceid join barsetinstance bi with (nolock) on bi.barsetinstanceid = fb.barsetinstanceid join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID join TransactionType tt with (nolock) on tt.TypeID=tr.TypeID and tr.Voided=0 and tt.TypeName='SALE' join StockOwnerArticle soa with (nolock) on aa.StockOwnerArticleID = soa.StockOwnerArticleID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and bi.IsManual=0 and @EPOSManual in ('EPOS','All') group by soa.CategoryID,fr.OriginatingAirportID ) A on fr.OriginatingAirportID=A.OriginatingAirportID and B.CategoryID=A.CategoryID --Manual Virtual product Gross Sales-- left outer join (select sum(mvp.qty*aap.BasePrice)as VirtualProductSales, soa.categoryID, fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID join ManualVirtualProduct mvp with (nolock) on cb.CashBagID=mvp.CashBagID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=mvp.AirlineArticleID join AirlineArticlePrice aap with (nolock) on aa.AirlineArticleID=aap.AirlineArticleID and aap.StartDate<=bi.FlightDate and aap.EndDate>=bi.FlightDate join StockOwnerArticle soa on aa.StockOwnerArticleID = soa.StockOwnerArticleID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All') group by soa.CategoryID, fr.OriginatingAirportID ) VP on fr.OriginatingAirportID=VP.OriginatingAirportID and B.CategoryID=VP.CategoryID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 ) GS on fr.OriginatingAirportID=GS.OriginatingAirportID -- Gross Sales End-- --Discount-- join (select sum((case when (bi.ISManual = 1 and @EPOSManual in ('Manual','All')) then isnull(MD.ManualDiscount,0) else 0 end) + (case when (bi.ISManual = 0 and @EPOSManual in ('EPOS','All')) then isnull(ED.EPOSDiscount ,0) else 0 end) ) as Discount, MD.CategoryID, fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID join --Manual Discount-- (select sum(case when bi.IsManual=1 then ManualTotalDiscountAmountApplied end) as ManualDiscount, soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join WarehouseCount wc with (nolock) on bi.BarsetInstanceID=wc.BarsetInstanceID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=wc.AirlineArticleID join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All','EPOS') group by soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID ) MD on bi.BarsetInstanceID=MD.BarsetInstanceID --Manual Discount End-- left outer join --EPOS Discount-- (select sum(TotalDiscountAmountApplied) as EPOSDiscount, soa.CategoryID,bi.barsetinstanceid,fr.OriginatingAirportID from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID join FlightInstance fi with (nolock) on fi.FlightInstanceID=fb.FlightInstanceID join [Transaction] t with (nolock) on fb.FlightInstanceID=t.FlightInstanceID join TransactionType tt with (nolock) on tt.TypeID=t.TypeID join TransactionArticle ta with (nolock) on t.TransactionID=ta.TransactionID join AirlineArticle aa with (nolock) on aa.AirlineArticleID=ta.AirlineArticleID join StockOwnerArticle soa with (nolock) on soa.StockOwnerArticleID=aa.StockOwnerArticleID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and tt.TypeName='SALE' and t.Voided=0 and bi.Ismanual=0 and @EPOSManual in ('EPOS','All') group by soa.CategoryID,bi.barsetinstanceid, fr.OriginatingAirportID ) ED on bi.BarsetInstanceID=ED.BarsetInstanceID and MD.CategoryID=ED.CategoryID -- EPOS DiscountEnd-- group by MD.CategoryID, fr.OriginatingAirportID ) D on fr.OriginatingAirportID=D.OriginatingAirportID and GS.CategoryID=D.CategoryID --Discount End-- join --Pax Count-- (select distinct fr.OriginatingAirportID, case when EPOSPax is null then 0 else EPOSPax end + case when ManualPax is null then 0 else ManualPax end as Pax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join #Unit u with (nolock) on fr.OriginatingAirportID=U.AirportID --Auto Pax Count-- left outer join (select fr.OriginatingAirportID,sum(fi.PassengerCount) as EPOSPax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join FlightBarset fb with (nolock) on bi.BarsetInstanceID=fb.BarsetInstanceID join FlightInstance fi with (nolock) on fi.FlightInstanceID=fb.FlightInstanceID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('EPOS','All') and bi.IsManual=0 group by fr.OriginatingAirportID ) A on fr.OriginatingAirportID=A.OriginatingAirportID --Auto Pax Count End-- --Manual Pax Count-- left outer join (select fr.OriginatingAirportID, sum(mfi.PassengerCount) as ManualPax from BarsetInstance bi with (nolock) join BarsetInstanceFlightSchedule bifs with (nolock) on bi.BarsetInstanceID=bifs.BarsetInstanceID join FlightSchedule fs with (nolock) on fs.FlightScheduleID=bifs.FlightScheduleID join FlightRoute fr with (nolock) on fr.FlightRouteID=fs.FlightRouteID join CashBag cb with (nolock) on bi.BarsetInstanceID=cb.BarsetInstanceID join ManualFlightInstance mfi with (nolock) on cb.CashBagID=mfi.CashBagID where bi.FlightDate >= @Start and bi.FlightDate < @End+1 and @EPOSManual in ('Manual','All') and bi.IsManual=1 group by fr.OriginatingAirportID ) B on fr.OriginatingAirportID=B.OriginatingAirportID --Manual Pax Count End-- where bi.FlightDate >= @Start and bi.FlightDate < @End+1 ) P on fr.OriginatingAirportID=P.OriginatingAirportID --Pax End-- join Airport a with (nolock) on a.AirportID=fr.OriginatingAirportID join Country c with (nolock) on c.CountryID=a.AirportCountry where soa.AirlineID = @MerchantID and soa.StockOwnerID = @StockOwnerID and bi.FlightDate >= @Start and bi.FlightDate < @End+1--Recursive function to get BrandName and CategoryName--declare @ID intdeclare @ID1 int, @CATID intdeclare @CategoryName varchar(max)declare c cursor forselect distinct ID from #tempCategoryopen cfetch next from c into @IDwhile @@fetch_status=0 begin WITH CategoryChart (CategoryID,CategoryName,[Level],SortOrder) AS ( -- Create the anchor query. This establishes the starting point -- SELECT a.CategoryID,a.Name,[Level],a.SortOrder FROM dbo.Category a join CategoryType ct on ct.TypeID=a.TypeID where a.CategoryID=@ID UNION ALL -- Create the recursive query. This query will be executed until it returns no more rows-- SELECT c.CategoryID,c.Name,ct.[Level],c.SortOrder FROM dbo.Category a join Category c on a.ParentCategoryID=c.CategoryID join CategoryType ct on ct.TypeID=c.TypeID INNER JOIN CategoryChart b ON a.CategoryID = b.CategoryID where a.CategoryID<>a.ParentCategoryID ) insert into #tempRecursive select CategoryID, CategoryName, @ID ,[Level] from categorychart fetch next from c into @ID endclose cdeallocate c--Getting the CategoryNameUpdate #tempCategorySet CategoryName=tr.CategoryName ,CategoryID=tr.CategoryID from #temprecursive trWhere #tempCategory.ID=tr.IDand [level]=1--Getting the Brand NameUpdate #tempCategorySet BrandName= tr.CategoryName from #temprecursive trWhere #tempCategory.ID=tr.IDand [level]=2--Grouping the data by Categoryinsert into #tempCat(AirportID,AirportCode,AirportName,CountryID,Country,CategoryID,CategoryName,NetSales,Pax,SpendPerHead) select AirportID, AirportCode, AirportName, CountryID, Country, CategoryID, CategoryName, sum(NetSales), Pax, case when Pax = 0 then 0 else sum(netsales)/Pax end from #tempCategory group by AirportID,AirportCode,AirportName,CountryID,Country,CategoryName,Pax,CategoryID--EUinsert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryName,CategoryID) select 0,'EU','','','Europe',sum([NetSales]),t2.Pax, case when t2.Pax = 0 then 0 else sum([NetSales])/t2.Pax end, CategoryName,CategoryID from #tempcat tc join #temp2 t2on 'EU'=t2.AirportCode where tc.Country not in ('United Kingdom','Switzerland') group by CategoryName,CategoryID,t2.Pax--If any Category is missing for EU make it 0insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName) select distinct 0,'EU','','','Europe',0,0,0,tr.CategoryID,tc1.CategoryName from #tempcat tc right outer join #temprecursive tr on tc.CategoryID=tr.CategoryID and AirportCode in ('EU') join #tempcat tc1 on tc1.CategoryID=tr.CategoryID where tc.CategoryID is null --UKinsert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName) select 0,'UK','','',tc.Country,sum([NetSales]),t2.pax , case when t2.pax = 0 then 0 else sum([NetSales])/t2.pax end, CategoryID,CategoryName from #tempcat tc join #temp2 t2on 'UK'=t2.AirportCode where tc.Country in ('United Kingdom') group by tc.Country,categoryid,CategoryName,t2.pax----If any Category is missing for UK make it 0insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName) select distinct 0,'UK','','','United Kingdom',0,0,0,tr.CategoryID,tc1.CategoryName from #tempcat tc right outer join #temprecursive tr on tc.CategoryID=tr.CategoryID and AirportCode in ('UK') join #tempcat tc1 on tc1.CategoryID=tr.CategoryID where tc.CategoryID is null --------Swissinsert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName) select 0,'SWISS','','',tc.Country,sum([NetSales]),t2.pax , case when t2.pax = 0 then 0 else sum([NetSales])/t2.pax end, CategoryID,CategoryName from #tempcat tc join #temp2 t2on 'SWISS'=t2.AirportCode where tc.Country in ('Switzerland') group by tc.Country,categoryid,CategoryName,t2.pax ----If any Category is missing for SWISS make it 0insert into #tempCat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName) select distinct 0,'SWISS','','','Switzerland',0,0,0,tr.CategoryID,tc1.CategoryName from #tempcat tc right outer join #temprecursive tr on tc.CategoryID=tr.CategoryID and AirportCode in ('SWISS') join #tempcat tc1 on tc1.CategoryID=tr.CategoryID where tc.CategoryID is null --Totalinsert into #tempcat (AirportID ,AirportCode ,AirportName ,CountryID ,Country , NetSales ,Pax ,SpendPerHead,CategoryID,CategoryName)select 0,'Total','','','',sum([NetSales]),t2.pax, case when t2.Pax = 0 then 0 else sum([NetSales])/t2.pax end, CategoryID,CategoryName from #tempcat tc join #temp2 t2on 'Total'=t2.AirportCode where tc.AirportCode not in ('EU','UK','SWISS') group by categoryid,CategoryName,t2.Pax--Converting row into column and vice versainsert into #tempC(Country,CountryID,AirportCode,AirportName,CategoryName,Number,DataType,i) SELECT Country,CountryID,AirportCode,AirportName,CategoryName,Number,DataType, case when DataType='SpendPerHead' then 1 end as I from #tempCat UNPIVOT (Number FOR DataType IN ([SpendPerHead] )) u order by countryid desc-- Getting the Data in the order (Bases,UK,Europe,Total)insert into #temp1 (Airport,AirportName) select t.AirportCode,t.AirportName from #temp t join (select AirportCode,max(i1) as i1 from #temp group by AirportCode ) A on t.i1=A.i1--Get the count select @j=count(Airport) from #temp1set @sql='select DataType'set @sql1='select CategoryName'while (@i<=@j) begin select @sql=@sql+',isnull(max(case when AirportCode='''+Airport+''' then Number end),0) as ['+Airport+' '+AirportName+']' from #temp1 where i=@i select @sql1=@sql1+',isnull(max(case when AirportCode='''+Airport+''' then Number end),0) as ['+Airport+' '+AirportName+']' from #temp1 where i=@i set @i=@i+1 endset @sql=@sql+' from #temp group by I,DataType order by I'set @sql1=@sql1+' from #tempC group by I,CategoryName order by I'EXEC sp_executesql @sqlEXEC sp_executesql @sql1----drop table #tempdrop table #temp1drop table #Unitdrop table #tempCategorydrop table #tempRecursivedrop table #tempCdrop table #temp2drop table #tempcatTue, 03 Nov 2009 12:44:33 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic813698-149-1.aspxWe had an issue recently where a query caused a lock on a database.Looking at the query, its a select statement that has (no locks) stated. The query looks well formed.The query looked like the following:SELECT NAME,TEXTINTO #TEMPFROM DB with (nolock)WHERE TEXT like '%Stuff%'OR TEXT like '%Stuff2%'OR TEXT like '%Stuff3%'The query was run on a large number of records. Is it possible that this could still causes locks?Wed, 04 Nov 2009 09:56:17 GMThuston.dunlaphttp://www.sqlservercentral.com/Forums/Topic813660-149-1.aspxcreate table projdetails(ID varchar(10),year numeric(4,0),det1 numeric(10,2),det2 numeric(10,2))Table contains details ID Year Det1 Det2100 2001 0.0 0.0100 2002 0.0 0.0100 2003 0.0 0.0100 2007 0.0 0.0100 2008 0.0 0.0101 1998 0.0 0.0101 1999 0.0 0.0101 2000 0.0 0.0101 2005 0.0 0.0101 2006 0.0 0.0Here if you see for a particular ID, in the year column,there are certain years missing.For eg:for ID 100,3 yrs are missing.(i.e,2004,2005,2006) and for ID 101, 4 yrs are missing.The requirement is to find out the missing yrs for each ID and add dummy records for these yrs.Except for ID and Year,rest all fields remain same for the dummy records.Also as you can see the range of Years(max and min) for each ID varies.Wed, 04 Nov 2009 09:10:57 GMTrekha_sarahttp://www.sqlservercentral.com/Forums/Topic812372-149-1.aspxWe have a number of maintenance plans built in Management Studio. We change the owner on the Agent jobs that run the maintenance plans to a be a "AgentService" login so all our Agent jobs run under this one global login (each server has their own). But whenever I change a maintenance plan, the owner of the Agent job changes. I need to remember to change each step back to the "AgentService" owner. First of all, is there a way to change this so the Owner stays intact? Second, if not, does anybody know why SQL Server has this built in their system this way? It drives me crazy. Thanks for your help!Mon, 02 Nov 2009 07:45:53 GMTsford-721104http://www.sqlservercentral.com/Forums/Topic811471-149-1.aspxI need to know how to Control Source Code of SQL Server 2005 with Team Foundation Server.VS 2008 DB Edition does it all but it is VERY expensive. I am looking for third party tools that are integrateable with TFS.Please suggest if any tool available.Also, Is there any other way to get SQL Server Source Code Controlled.Reason for Not using or VS 2008 DB Edition: I have tested VS 2008 DB Edition and it allows you to Check-In and Check-Out the script file as VS C# or VB.NE Developers do. It provides complete integrated SSMS in Visual Studio IDE. But the cost of one License of VS 2008 DB Edition is almost equal to the cost of 55 copies of VS 2008 professional that we have for developers.Thanks in Advance...Fri, 30 Oct 2009 05:30:07 GMTAtif Sheikhhttp://www.sqlservercentral.com/Forums/Topic813453-149-1.aspxI have a date like 01.12.2005 in excel sheet. While i make a query and then run this in sql to insert it into a table, it is interpreting it as 12 Jan 2009. i want it to be 12 Jan 2009.Please tell me how can i control the insert.PramodWed, 04 Nov 2009 00:28:31 GMTpramod_yg