Friday, June 14, 2013

[SQL Server 2008 issues] Statistics DDL

[SQL Server 2008 issues] Statistics DDL

Statistics DDL

Posted: 13 Jun 2013 07:23 PM PDT

Hi Team,i've a table with statistics, how to view the DDL for the current statistics in Sql server 2008.Please...

SQL Server Full-Text Search: combining proximity term and thesaurus

Posted: 13 Jun 2013 07:11 PM PDT

[b]SCENARIO[/b]I am developing a full-text search over a products table.The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code.I succeed in querying the table using the CONTAINS predicate with NEAR terms:[code="sql"]/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */SELECT myFieldsFROM SEARCHABLE_PRODUCTSWHERE CONTAINS(myIndexedField, @mySearchTerm)[/code]Then I try to enhance the search, leveraging on the thesaurus feature in order to enable synonyms search. E.G. 'hard disk', 'hard disk drive', 'hdd'. So I have configured the thesaurus xml file, and I succeed in querying the table using the FORMSOF term.[b]ISSUE[/b]I do not succeed in combining the NEAR and the FORMSOF terms in the CONTAINS predicate, such as:[code="sql"]/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */SELECT myFieldsFROM SEARCHABLE_PRODUCTSWHERE CONTAINS(myIndexedField, ' FORMSOF (THESAURUS, @mySearchTerm) ')[/code]A syntax error is returned. As to the [url=http://msdn.microsoft.com/en-us/library/ms187787.aspx]MSDN reference[/url], it seems that it is not possible. Is there any solution or workaround?

Queries that cause full scan table

Posted: 13 Jun 2013 05:35 PM PDT

Hi everybody , I would like to retrieve queries that cause full scan table in sql server.Someone can help me please?? :ermm:

Database mail sending fails with SP_Send_DBmail.

Posted: 03 Jun 2013 05:27 PM PDT

Hi,I am stuck in below problem.I have two servers; ServerA and ServerB. I have linked serverA to serverB bysp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB', @srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.The Query is : EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',@subject = 'MySubject',@body = 'This Is Test.',@body_format = 'Text', @profile_name = 'Profile1',@query = 'set nocount onSELECT * FROM [ServerB].[Database].dbo.myTable AS MTWHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',@execute_query_database = 'master',@query_result_separator = ' ',@attach_query_result_as_file = 1,@query_attachment_filename = 'myFile.csv'I have created an proc on serverA's master database and calling this proc into a job.This job throws an error:" Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "Can Anybody help in solving this??It would be a great help.Thanks and Regards,Jignesh

creating table within procedure and bulk inserting

Posted: 13 Jun 2013 09:29 AM PDT

Hi ProfessionalsI am creating a procedure in SQL to create a table which i then bulk insert the spreadsheet into the table, the problem I have is that the first 3 columns will always be the same. Software_manufacturer,Product_name and Product_version but the 4th column onwards is unknown. In other words there could be 4 columns or there could be 10 columns. It does not matter what these are called but I need to capture the rest of the columns relating to the spreadsheet that is going to be imported.Is there a way round this, I am writing this in PHP SQL and HTML with a bit of javascripthere is my code[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[importspreadsheet] Script Date: 06/13/2013 13:38:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[importspreadsheet] @importedfile nvarchar(50), @table_name nvarchar(100) ASBEGIN IF EXISTS ( SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = 'dbo' AND sys.tables.name = 'newtable' ) declare @sql nvarchar(1000),@sqldrop nvarchar(1000)set @sqldrop = 'DROP TABLE dbo.' + quotename(@table_name); set @sql = 'create table dbo.' + quotename(@table_name, '[') + '(software_manufacturer nvarchar(max) null,product_name nvarchar(max) null,product_version nvarchar(max) null,col4 nvarchar(max) null);';exec (@sqldrop)exec (@sql)print (@sql)DECLARE @cmd nvarchar(max)SET @cmd = 'BULK INSERT newtableFROM ''C:\inetpub\wwwroot\uploads\'+ @importedfile +''' WITH ( FIRSTROW = 4,FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'--PRINT @cmd EXEC(@cmd)END[/code]

building up dynamic column creation within procedure

Posted: 13 Jun 2013 11:32 AM PDT

Hi ProfessionalsI have a procedure that passes in 1:CSV file, 2:Table_name 3:Column_countThe column_count can vary and this is where I am trying to build the mid part of my create table statement within the procedure.I am iterating through the records and trying to build however many mid columns there needs to be in the create table statement and cannot figure it out. I am almost there with my code.any ideas professionals, any help would be greatly appreciatedhere is my code with output underneath[code]USE [TestData]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[importspreadsheet] /*Passed in variables from PHP script*/ @importedfile nvarchar(50), /* The csv filename EG test.csv */ @table_name nvarchar(100), /* The tabke name to be created */ @column_count nvarchar(50) /* The total number of columns from spreadsheet */ASBEGIN declare @sql nvarchar(1000), /* The first part of the query build */ @fullquery nvarchar(1000), /* The full joined query */ @sqldrop nvarchar(1000), /* The drop table command */ @intflag int = 0, /* The loop counter for looping through total number of columns minus 1 */ @midcolumns nvarchar(50) = 'colname', /* The middle columns default name which will have intflag variable appended */ @lastcolumn nvarchar(50); /* The last column to close the Create Table query */--set @sqldrop = 'DROP TABLE dbo.' + quotename(@table_name); /*Building first columns of the create table statement here which are always going to be there*/set @sql = 'create table dbo.' + quotename(@table_name, '[') + '(software_manufacturer nvarchar(max) null,product_name nvarchar(max) null,product_version nvarchar(max) null,';/*Loop through and create the middle columns based on the column_count variable, Make sure to do LESS THAN column_count and NOT LESS THAN OR EQUAL TO so we can close final column outside of loop*/while (@intflag < @column_count) begin --print @intflag set @intflag = @intflag +1; set @midcolumns = 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,'; print @midcolumns; end/*Create last column in the create table statement*/set @lastcolumn = 'lastcolumn nvarchar(max) null);';/*Join the full create statements together to create dynamic table*/SET @fullquery = (@SQL + @midcolumns + @lastcolumn)print (@fullquery)--exec (@sqldrop)--exec (@sql)END[/code]colname[2] nvarchar(max) null,colname[3] nvarchar(max) null,colname[4] nvarchar(max) null,colname[5] nvarchar(max) null,colname[6] nvarchar(max) null,colname[7] nvarchar(max) null,colname[8] nvarchar(max) null,colname[9] nvarchar(max) null,colname[10] nvarchar(max) null,colname[11] nvarchar(max) null,colname[12] nvarchar(max) null,colname[13] nvarchar(max) null,colname[14] nvarchar(max) null,colname[15] nvarchar(max) null,colname[16] nvarchar(max) null,colname[17] nvarchar(max) null,colname[18] nvarchar(max) null,colname[19] nvarchar(max) null,colname[20] nvarchar(max) null,colname[21] nvarchar(max) null,colname[22] nvarchar(max) null,colname[23] nvarchar(max) null,create table dbo.[newtable](software_manufacturer nvarchar(max) null,product_name nvarchar(max) null,product_version nvarchar(max) null,colname[23] nvarchar(max) null,lastcolumn nvarchar(max) null);

suspect linked server problem ?

Posted: 13 Jun 2013 09:41 AM PDT

Hi I am running the follwoing 2 queries:-- query 1 takes > 20 secondsUPDATE [SERVER-P02].A.dbo.B_TABLEASET Comment = Cast(Cast(237 As Varchar(10))As Ntext)WHERE UserID = '6FE30BDA-A38B-4D0D-B10F-D11C0D324E7883'-- query 2 works ok under 1 secondUPDATE [SERVER-P02].A.dbo.B_TABLEBSET LastActivityDate = '13-jun-2013'WHERE UserID = '6FE30BDA-A38B-4D0D-B10F-D11C0D324E7883'I have run both queries actually on SERVER02-P02 and they both return the same execution plan , that is an INDEX SEEK (NON CLUSTERED) AT 25% cost and a CLUSTERED INDEX UPDATE AT 75% cost.When i run the queries from SERVER4 using the linked server i get 2 totally different execution plan. Query 1 does a massive REMOTE SCAN costing 100% and scans 353008 rows whereas Query 2's execution plan just says" REMOTE Query" and ACTUAL ROWS is 0 on the execution plan.Can anybody tell me how i get query 1 to stop doing the REMOTE SCAN - is this a linked server issue ? The index structure and primary key structure is the same on both tables too.

sql statement containing loop

Posted: 13 Jun 2013 07:16 AM PDT

Trying to accomplish something like the following:Select(SELECT Begin the loop:LTRIM(RTRIM(RIGHT(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,'')),LEN(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,'')),-CHARINDEX('-',(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,''))))))when result = '1a' Then 145when result = '1b' Then 146when result = '2a' Then 147when result = '2b' Then 148End the loopFROM tablename2) as Col1,Col2,Col3From tablename

Few tricky Questions about SQL Server 2008 that I came across...

Posted: 10 Jan 2012 06:31 AM PST

Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of a transaction log?[/b]A. Transaction logs are truncated when you use SQL Profiler.B. Transaction logs are truncated when you use the following syntax: truncate table AC. Transaction logs are truncated when you backup your database.D. You can not backup transaction log.[b]Q: Which of the following statements about unique Constraints are TRUE?[/b]A. You can have only one unique constraint applied to a table.B. Unique constraints can be used to establish a link between two tables.C. You can only have one column assigned to a unique constraint.D. If a primary key was already assigned to a table, the unique constraint would identify a special column used for indexing, [b]Q: Which of the following is an improved performance feature that uses bitmap filters for selecting minimal rows with queries?[/b]A. Table partitionsB. Star JoinC. Where clauseD. Outer Join[b]Q: Using a join in your select statement instead of a where clause is more efficient because the join pulls less data into your working memory.[/b]A. TrueB. False[b]Q: Full text indexes can be backed up and restored with the rest of the database due to the ...[/b]A. ...new index organization systemB. ...overall size of the index getting smaller.C. ...index becoming a part of the transaction logs.D. ...indexes no longer being stored outside of the database.[b]Q. Which of the following statements, if used for creating a new login id, will result in an error?[/b]A. CREATE LOGIN [HumanResources/JohnSmith] FROM WINDOWSB. CREATE LOGIN AndrewWalters WITH PASSWORD '!^%&7', DEFAULT DATABASE = HRC. CREATE LOGIN John WITH PASSWORD '*&!@*&'D. CREATE LOGIN [HumanResources/Jim] FROM WINDOWS, DEFAULT DATABASE=CRM[b]Q. Which of the following statements can be used to provide sorted result set based in the CustomerName column?[/b]A. SELECT ROW_NUMBER() AS RN, CustomerName from Customers order by CustomerNameB. SELECT ROW_NUMBER() OVER (ORDER BY CustomerName) AS RN, CustomerName FROM CustomersC. SELECT ROW_NUMBER() (ORDER BY CustomerName) AS RN, from CustomersD. SELECT ROW_NUMBER() AS RN, CustomerName from Customers

What Queries/SPs Are Running Right Now ?

Posted: 13 Jun 2013 12:47 AM PDT

Is there a way to identify actively running queries or stored procedures ? If I am in Activity Monitor, and click "Details" on a spid, sometimes I can see what an application or user is running.Is there a way to dynamically capture the same information in one place for all connections so I can see all processes hitting SQL ?EDIT: [b]Running SQL 2005[/b], so maybe I posted in the wrong forum.

Dynamically choose source and destination

Posted: 13 Jun 2013 06:45 AM PDT

Hey Gurus, I have just started working with SSIS and have following task at hand and I feel lost. Can any of you please help me?Currently, I have a 2 different data flow tasks that do the same thing: Accept data from a SQL Store proc, put it to flat file destination and archive it. One of them is stored procedure giving us revenue data and the other gives us accounts data. Now, I need to combine both of these different data flows as a single data flow using parameters / variables for picking up appropriate stored procedure based on whether user wants to see account data or revenue data and then, out it in the correct file and file location (Files and file locations are different for revenue and accounts data).I do understand I should use a for-each loop maybe. However, I really need assistance in getting started with how to set up variables (system / user defined) and how can I go about it. I have attached a sample data flow diagram of how I need it. Can you please pass me some tips / ideas. Please note that am a novice is SSIS and just started. Thanks in advance!

Client Statistics: Total exectuon time

Posted: 13 Jun 2013 04:52 AM PDT

Hi allSo, I've got this batch (I hope it is correct to call it a batch, this is just a few variables declarations, queries to assign values to these variables and then just a select statement that returns a data set) open i SSMS that takes 8 sec to execute (this value is taken from the yellow bar under the query window). Now if I turn on "Include Actual Execution Plan" and "Include Client Statistics" and execute the Client Statistics tab pops up and the "Total execution time" has value of 161. Now I always thought this was the time it took the server to execute the hole batch in milliseconds. I don't know if this is relevant or not but the Client processing time is 1 and Wait time on server replies is 161. The data set returned has 210 rows and and 8 columns, so it ain't big.Now, if I the execute the same batch with "Include Actual Execution Plan" turned off the "Total execution time" becomes 7844 which is a value I would have expected in the first place.Can anyone explain what is going on here?I have always used the "Total execution time" when I'm comparing different queries, is this a bad practice, should I be using something else, if then what?I'm running SQL Server 2008 R2 on a Windows Server 2008 R2 Datacenter.Thx in advance

Verify Database was restored

Posted: 13 Jun 2013 05:04 AM PDT

HelloI need to verify that a database was restored from a particular backup.I used RESTORE HEADERONLY on the BAK an can see FirstLSN, LastLSN. Is there a way to match the LSN to the database that was already restored?ThanksDave

Get Notification when a new column added to flat file

Posted: 13 Jun 2013 05:45 AM PDT

I am working on sql server 2005. We have some SSIS packages which loads data from multiple flat files. Flat files comes from other group. Sometimes they just add a new column to the file(remove a column also) and process without letting us know. I just wonder is there any script which catch the changes at source end wheteher its a flat file or a sql table and let us know the change. Appriciate any feedback.

Crosstab query for date

Posted: 13 Jun 2013 05:53 AM PDT

I am learning crosstab query and create a sample. It works fine.Since my real project needs date range is mydate >= (getdate() -15) which will make mydate are different everyday.How to modify code to meet this condition? That is, column's name for mydate are change everyday. create table #TestPivot( MyValue int, MyDate datetime)insert into #TestPivot values(20,'11/11/2012');insert into #TestPivot values(30,'11/12/2012');select * from(SELECT [MyValue] ,[MyDate] FROM #TestPivot) as sourceTablePivot( sum([MyValue]) for [MyDate] in ([2012-11-11],[2012-11-12])) as PivotTableresult2012-11-11 ----- 2012-11-1220---------------30

Get 2 column result from 1 field

Posted: 13 Jun 2013 04:50 AM PDT

Hi geniusesLet say I got a table:[code="sql"]Years | Value2000 | 22001 | 42002 | 52003 | 32004 | 2[/code]I need to get the sum of the Value for all the years in 1 column and the sum(Value) to 2002 in another column.in order to give:[code="sql"]Sumof.All | Sumto.200216 | 11[/code]Thanks in advance!

Phase of Recovery (Log Shipping)

Posted: 13 Jun 2013 03:50 AM PDT

Can anyone elaborate on the phases of recovery or point me to an article, for when you recover a database that has been log shipped? When Googling "Phases of Recovery Log Shipping" (I also tried Googling different variations of that string with no luck), I get a bunch of step-by-step articles on how to set up Log Shipping. I am interested in learning what goes on during that recovery.Thanks for any and all responses...

Import CSV File

Posted: 12 Jun 2013 09:36 PM PDT

Hi,I have a 4 GB CSV file , which i am unable to import in SQL Server 2008 R2, even I not able to open file also. I tried import export wizard also but when i am importing the file , it displaying the records with double quote("").For Ex:- "Charles" "$1.2"Is there any alternate option to import large file into the SQL SERVER or any third party tool which will import the file without double quote.Thanks in advance-------

Login History - who connected to a database?

Posted: 12 Jun 2013 09:22 PM PDT

Hello people,is there a way that we can display all the people who have connected to a certain database over a given time? For instance, we have a read-only user name for people wishing to connect to our live database, in the normal course of our day to day activity. All the users also have a read-write access, but we would like to audit the times that people are connecting with this read-write access - this is independent of any actual changes to the routines that they may make while logged in.Is the information stored anywhere in SQL server management studio, from which we would be able to make a report or similar to show all logins, with time and duration?Thanks,Mark

CLR function not returning results for GetRunningPackages

Posted: 13 Jun 2013 01:07 AM PDT

Hi there,I am working on creating a CLR that can return the list of running SSIS packages on a given server, managed to implement everything, compiled, deployed successfully, ran without errors.... but not results returned, although clearly packages are running on that server (could see then in Integration Services).Moreover when I change the servername to a nonexisting server I get the errors, and if I use the same c# code outside the CLR I get the correct results.Not sure what to try next, feels like a security/permissions issue...Used Application.GetRunningPackages method, SQL 2008R2 Developer edition, .Net 2.0Thanks in advance for your help/feedback.-A

Determine the row of data in a table through SSIS

Posted: 13 Jun 2013 12:52 AM PDT

Hi,I have a requirement based on 2 parts to determine a row of data in a table [b]through SSIS [/b] :a. Work out the LEG_SEQ_NBR of the VESSEL, VOYAGE and LEG already stored in table1.To do this join table1 and join table2 through BL_ID column.b. Work out the 2ndVESSEL, 2ndVOYAGE and 2ndLEG.Once we have identified the LEG_SEQ_NBR of the VESSEL,VOYAGE, LEG already stored in table1 we need to add '1' to this value and then find that LEQ_SEQ_NBR in table2.The DDL of table1 and table2 along with the test data are as below:------------------------------------------------------------CREATE TABLE [dbo].[table1]( [BL_ID] [decimal](10, 0) NOT NULL, [VESSEL] [nvarchar](10) NULL, [VOYAGE] [nvarchar](12) NULL, [LEG] [nchar](3) NULL, [BLNO] [nvarchar](17) NULL ) ON [PRIMARY]CREATE TABLE [dbo].[table2]( [BL_ID] [numeric](10, 0) NULL, [LEG_SEQ_NBR] [numeric](3, 0) NULL, [VESSEL_CD] [varchar](10) NULL, [VOYAGE_CD] [varchar](12) NULL, [LEG_CD] [char](1) NULL, ) ON [PRIMARY]INSERT INTO [table1]VALUES('1','CEUR','032E','E','21')INSERT INTO [table2]VALUES('1','1','CEUR','032E','E')INSERT INTO [table2]VALUES('1','2','MARB','794S','S')---------------------------------------------------------For Example:BLNO = 21 and BL_ID = 1Current Vessel/Voyage/Leg in table1 = CEUR 032E ELEG_SEQ_NBR of this Vessel/Voyage/Leg in table1 = '1'Therefore if we add '1' to this value we'd be looking for LEG_SEQ_NBR '2' as the 2ndVESSEL,2ndVOYAGE,2ndLEG. In this case that would = MARB 794S S Does somebody pls know how this can be worked out through an SSIS job ?Thanks.

My row_number() is misbehaving or I'm an not a smart man

Posted: 12 Jun 2013 10:33 PM PDT

OK I've got some sample setup and data below; basically i need a row_number() i've aliased as [BuildingID], to be a unique value for each value in [ACTV_HOME_PROPERTY_ID] to be generated; if there was two unique values in those five rows, i'd expect values of 1 or 2, but i'm not doing something right today. in the sample data below, there is only ONE unique value, so I expect only the value [1] for all five rows;I know i'm pre-coffee, but i keep getting the same values in row number, no matter what i partition by;[code]CREATE TABLE [dbo].[#MYSAMPLEDATA] ( [ACTNBR] VARCHAR(50) NULL,[ACTNAME] VARCHAR(100) NULL,[RELATEDYEAR] VARCHAR(4) NULL,[IDIS_ACTV_ID] VARCHAR(12) NULL,[ACTV_HOME_PROPERTY_FUND_ID] VARCHAR(15) NULL,[ACTV_HOME_PROPERTY_ID] VARCHAR(15) NULL,[FUND_CAT_ID] VARCHAR(15) NULL)INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315449','900000000105337','122')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000329445','900000000105337','22')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315451','900000000105337','302')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315452','900000000105337','303')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315450','900000000105337','21')--edit: adding five other rowsINSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315449','900000000105338','122')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000329445','900000000105338','22')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315451','900000000105338','302')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315452','900000000105338','303')INSERT INTO [#MYSAMPLEDATA] ([ACTNBR],[ACTNAME],[RELATEDYEAR],[IDIS_ACTV_ID],[ACTV_HOME_PROPERTY_FUND_ID],[ACTV_HOME_PROPERTY_ID],[FUND_CAT_ID])VALUES('2011-03699','RILEY & MERIAL UZEL','2011','3699','900000000315450','900000000105338','21')SELECT 1 As TheExpectedValue, IDIS_ACTV_ID AS ID, ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID, ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID ORDER BY IDIS_ACTV_ID) AS BUILDINGID_V2, ROW_NUMBER() OVER (PARTITION BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID ORDER BY IDIS_ACTV_ID,ACTV_HOME_PROPERTY_ID) AS BUILDINGID_V3, Z.ACTNBR, Z.ACTNAME, Z.RELATEDYEAR, Z.IDIS_ACTV_ID, Z.ACTV_HOME_PROPERTY_FUND_ID, Z.ACTV_HOME_PROPERTY_ID, Z.FUND_CAT_IDFROM #MYSAMPLEDATA Z [/code]

how to caluculate half day

Posted: 12 Jun 2013 10:50 PM PDT

hii want to caluclate half day my actual data is like this[size="1"]date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short[/size]i want this data[size="1"]date-----------------------------eid---------------timein---------------------timeout-----------spend-----excessshort-excess----Remarks2013-03-21 00:00:00.000--26446--2013-06-13 09:13:00.000--2013-06-13 3:46:00.000-06:33:00--02:27:00---Short------HALFDAY[/size]employee timing is 9am to 6pm if he leaves from factory at 4pm or before 4 pm then remarks say halfdaybecouse we have 2 hours half day mean if he go for some work and didnt come back for 2 hours our more then 2 hours then half day implementedplease implement on this query[size="1"]drop table #temp1select[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from attend_LOGwhere eid = 17090group by [date]GOselectt.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,case when (540 - Time_Minutes) > 0 Then '- ' else '+ ' end+CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttimeFROM attend_LOG tleft join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere eid = 17090order by t.[date], t.[Timein][/size]please help me out thanks for the help

Three months before current month

Posted: 12 Jun 2013 11:48 PM PDT

I need to set [OrderDate] three months before current month.For example, as if June, [OrederDate] should be "Between '1/1/2013' and '03/31/2013'.For July, [OrederDate] should be "Between '1/1/2013' and '04/30/2013'.I want to use code like [OrederDate] Between @datefrom and @datetoI set @datefrom = '01/01/2013' (always), how to set @dateto three months before current month?

Extended Events - Filtering based on sql_text

Posted: 04 Feb 2013 08:50 PM PST

I need to leave a monitor runing long term to capture all calls to procs beginning with 'val_'Extended events seem to be a good fit here, but when adding the sqlserver.sp_statement_starting event, I cant filter on the sql_text column:[code="sql"]CREATE EVENT SESSION CSP_ProcsON SERVER ADD EVENT sqlserver.sp_statement_starting ( ACTION(sqlserver.nt_username, sqlserver.username, sqlserver.client_hostname, sqlserver.sql_text, sqlserver.database_id) WHERE sqlserver.sp_statement_starting.sql_text LIKE '%val%'--this line is underlined in red by the parser )[/code]is it even possible to filter like this, as its pretty simple in profiler/server side tracing

If one of two values exists they take priority over other values, otherwise select random

Posted: 12 Jun 2013 11:26 PM PDT

Hello, I have the below sql statement, where im trying to return a columns value depending on what value is in side.I have a table called orderhandsets which is associated to the table Order each order can have more then 1 row linked to it, so i could have an order 1234and inside orderhandsets i could have 5 rows assigned to order 1234the two values im looking for are 1 = new3 = portwhich are inside the column oh.OrderTypeif 3 exists then i need to return this as its this value takes priority over all other values, if 3 DOES NOT exists i need to check for1 and return it, if neither of them exists i just need to pull the value thats inside the columnthis is my statement[code="sql"]select top(1) oh.OrderTypefrom OrderHandsets oh where oh.OrderId = co.OrderId and (oh.Ordertype = 3 or (oh.OrderType =1 or oh.OrderType not in(1,3)))[/code]see now inside the where clause im first checking for 3, if that doesnt exists then i look for one, otherwise just get any other value, as you can also see im using Top(1) which again is causing the problem because if i have rows like the attached pic it always returns one when i need it to return 3[img]http://http://businessphonesdirect.co.uk/img/logo/snippet.png[/img]Can someone help me achieve what im looking for?So check for 3 first, if that doesnt exists check for 1if none of the above exists then just pull the value from the column

variable with multiple values from SP

Posted: 12 Jun 2013 10:21 PM PDT

hi All,I have a stored procedure where in I pass two paramaters StartDate and EndDate and return the start of the week -------Query-----DECLARE @Sample TABLE (Week Date, TotalSUm int) ---Declare @Testvariable Date---INSERT INTO @Sample---EXEC dbo.sp_testsp '2013-05-02','2013-05-04'---set @Testvariable = (select distinct week from @Sample)-- this returns start of the week i.e--2013-04-29---select @Testvariable --returns the start of week--And then I pass on the @Testvariable in the some other select statement.The above query returs fine, but if the EXEC dbo.sp_testsp is being passed with multiple values, then the error --Msg 512, Level 16, State 1, Line 7Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.--I understand Set variable can accpet only a Scalar variable. if in case I pass paramaters to procudre as belowEXEC dbo.sp_testsp '2013-05-02','2013-05-07',it should return2013-04-292013-05-06and I wanted to pass the above to select statement and retirve the results for corresponding weeks.ANy help on this? thanks

SQL Agent not able to start on SQL Server 2008 Developer Edition

Posted: 25 Feb 2011 03:28 AM PST

Dear All,I have SQL Server 2008 Developer Edition (64-bit) SP1 installed on a Windows Server 2008 R2 machine. For some reason, I can't get the SQL Agent service to start. When I go into the SQL Server Configuration Manager and select the properties of the Agent service and try to change the start mode from 'Disabled' to 'Manual', it comes back with the following error message after a few seconds:"WMI Provider ErrorThe remote procedure call failed. [0x800706be]"Both SQL Server and SQL Agent service are set to log on as the same domain account, which we have on many other SQL servers and the SQL Server service itself is running under this account quite happily.I've had a look around generally at articles etc... but most of them refer to the Express Edition, which this is not. I've tried temporarily disabling the Windows Firewall, but it seems to make no difference.Does anyone have any ideas? This is a newly created server and the only SQL installation that has ever been on it is the one referred to above. There's nothing else SQL or otherwise that isn't working fine - just the Agent! Could it be a problem with a setting in the O/S?Any help would be much appreciated.Regards,Chris Stride

how to caluculate half day

Posted: 12 Jun 2013 10:46 PM PDT

hii want to caluclate half day my actual data is like this[size="1"]date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short[/size]i want this data[size="1"]date-----------------------------eid---------------timein---------------------timeout-----------spend-----excessshort-excess----Remarks2013-03-21 00:00:00.000--26446--2013-06-13 09:13:00.000--2013-06-13 3:46:00.000-06:33:00--02:27:00---Short------HALFDAY[/size]employee timing is 9am to 6pm if he leaves from factory at 4pm or before 4 pm then remarks say halfdaybecouse we have 2 hours half day mean if he go for some work and didnt come back for 2 hours our more then 2 hours then half day implementedplease help me out thanks for the help

Edit data in Excel

Posted: 12 Jun 2013 10:04 PM PDT

Just to let you know Excel Database Tasks (EDT) version 2.7 has been released : New features include :One click saveImmediate row validationRelational data / drop down listsData filteringFor further info :[url=http://leansoftware.net/en-us/help/exceldatabasetasks/introduction.aspx]>>EDT Introduction[/url]In production use now at some pretty major companies including Nissan Motor (UK) & CommerceHub (US)Thanks for all the interest and support from people here..

ORDER BY the total of a GROUP BY

Posted: 12 Jun 2013 08:29 PM PDT

I have a table which contains order information. I need to SUM up the quantities ordered on an account every day. For example i could have the following records in a tableAccount Number QTY Name ROWID123456 4 Example 1123456 6 Example 2Now I have the following query which gets me the total of the quantities for the accountnumber[code="sql"]SELECT AccountNo, SUM(CONVERT(INT,QTY))AS UOMFROM tfrOrderTransmissionDetailLogWHERE DateDiff(dd, [TIMESTAMP],GetDate())=0 GROUP BY AccountNoORDER BY UOM DESC[/code]What I need is all the information in that the above table ordered by which account has ordered the most. With a field at the end giving the total qty ordered. Something like this.Account Number QTY Name ROWID UOM123456 4 Example 1 10123456 6 Example 2 10654321 8 Example 1 17654321 9 Example 2 17Can anyone help me with this or tell me that it isn't possible.Many Thanks in advanceMark D
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog