Friday, September 13, 2013

[SQL Server 2008 issues] Finding 2nd and 4th Saturdays of Current Year

[SQL Server 2008 issues] Finding 2nd and 4th Saturdays of Current Year

Finding 2nd and 4th Saturdays of Current Year

Posted: 12 Sep 2013 06:25 PM PDT

Hi all,Please help me to find out 2nd and 4th saturdays of current year.Thanks and RegardsShirish Phadnis

Compare View and Table...

Posted: 12 Sep 2013 03:52 AM PDT

A Table and View having same structure and amount of data 40 Columns and 1.5 Million Data...This data has to be fetched by a BI Tool Which will give better performance...

Conflict between Commvault and SQL Server Backup

Posted: 12 Sep 2013 04:19 PM PDT

I have a strange sittuation here, On one of the live server(2008 R2+SP2) we have Commvault as well as SQL Native backups enabled, below are the schedule details:A]Commvault Backup Schedule a)7:30PM-Full backup(daily) b)9:00 PM – 11:00PM log backup(every 2 hours), c)11:00PM-7:00AM-no backup at all d)7:00 AM to 7:00PM-log backup(every 2 hours) and the cycle continues.B]SQL Native Backup Schedule a)11:50 PM-Full Backup(Daily)Please hold your laugh and spare the questions as why? how? etc., It has been recently overtaken by our team.My question is:1)Will the SQL Native backup at 11:50 PM, break the commvault log backup chain?2)If yes, will i be able to use Commvault log backups taken after SQL Native(Full) backup in case of point in time recovery?(ie keeping SQL Native Full backup as base and applying commvault log backups sequentially)

Need to run few sql scripts and store the results in a CSV file

Posted: 12 Sep 2013 02:37 PM PDT

Hi All,I have a requirement where in, I need to automate to run all the scripts from a specific location like 'c:\xyz' on a sqlserver and then need to store the results in a CSV. Each script results has to be stored in individual sheet of CSV. Appreicate your help.Thanks,Vamsi

Error DTExec: The package execution returned DTSER_FAILURE (1)

Posted: 09 Sep 2013 09:45 PM PDT

Hi,A Maintenance Plan Job for UpdateStatistics is scheduled from SQL Server 2008 and it is a weekly job (Sunday). Last Sunday it failed with the below error. Can some body help me to fix the issue.Error:Executed as user: ABC\XXX. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 6:00:02 AM Progress: 2013-09-08 06:00:11.52 Source: {9B20A854-9645-4762-B4C7-8B5DA1A19210} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2013-09-08 06:22:09.05 Code: 0xC0024104 Source: Update Statistics Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:00:02 AM Finished: 6:22:09 AM Elapsed: 1327.41 seconds. The package execution failed. The step failed.Thanks in advanceTapas

How to import a sql server database ?

Posted: 12 Sep 2013 02:03 PM PDT

I have installed SQL Server 2008 - R2 version in my local machine.I want to import database from another remote SQL Server 2008 database inside a LAN.How to do this ? Can you please tell me the steps ?Also , do I have to create user and give permission in my database after import ? or users and permission also be copied from remote database to my database ?

temp table in ssis

Posted: 12 Sep 2013 10:40 AM PDT

I have a store procedure that uses temp tables and CTE . I am calling the stored procedure from Oledb source in data flow task in ssis. It gives me some error like " The metadata could not be determined because statment ' WITH CTE1 AS (...... unable to retrieve column information from the data source. Make sure the target table in the database is available. There seem to be dislike/disconnect between temp table and ssis oldb source using stored proc- anybody has any idea for resolving this?Also i am trying to push data to a database that is in a completely different network. I can ping it but it is not visible through sql server management or ssis. how can dump data into a table in a different network-ed database?

Select record from group

Posted: 12 Sep 2013 01:19 AM PDT

I was assigned a difficult project. It is beyond by skill. I need expert to help me.From sample data, 1) Group by ID and COLOR2) From this group, select ID, min(DATEFROM),max(DATETO) and Color and then insert into a new table,3) For example, goup 111 and blue, select min(DATEFROM)=01/10/2012 and max(DATETO)=03/16/20124) Keep on selecting until end file.Here is sample data:ID DATEFROM DATETO COLOR111 01/10/2012 01/11/2012 BLUE111 02/02/2012 02/02/2012 BLUE111 03/15/2012 03/16/2012 BLUE111 04/05/2012 04/05/2012 GREEN111 05/25/2012 05/26/2012 GREEN111 06/06/2012 06/16/2012 GREEN111 07/17/2012 07/17/2012 BLUE111 09/08/2012 10/10/2012 BLUE222 01/10/2013 01/11/2013 RED222 02/02/2013 02/02/2013 RED222 03/15/2013 03/16/2013 RED222 04/05/2013 04/05/2013 GREEN222 05/25/2013 05/26/2013 GREEN222 06/06/2013 06/16/2013 GREEN222 07/17/2013 07/17/2013 BLUE222 09/08/2013 10/10/2013 BLUEFinally, I need select records and insert a new table like below:111 01/10/2012 03/16/2012 BLUE111 04/05/2012 06/16/2012 GREEN111 07/17/2012 10/10/2012 BLUE222 01/10/2013 03/16/2013 RED222 04/05/2013 06/16/2013 GREEN222 07/17/2013 10/10/2013 BLUE

get only the alphabets from string

Posted: 12 Sep 2013 11:08 AM PDT

i have productname column in my table and the query i need to get only the alphabets.column contains the special characters (!@#$%^&*():'"{}[]\|?-+=,) and numbers(0-9), spacesample dataFoodhold USA,mlc.Beverage Partners Worldwide (North canada)......Bread World 8my expected output will be FoodholdUSAmlcBeveragePartnersWorldwide(Northcanada)BreadWorld[code="sql"]SELECT productname, SUBSTRING(Name,1,ISNULL(NULLIF(PATINDEX('%[^A-Za-z.''0-9]%',LTRIM(RTRIM(productname))),0)-1,LEN(productname))) AS noSpecials FROM Manufacturer [/code]but it is not working. can anyone please show me sample query..

Best way to move Several Million Record query results to a table?

Posted: 12 Sep 2013 04:29 AM PDT

I have a query that will utlimatley return several million records. The query itself is too much for the memory on the particular server it's on; however, we used the Import Export wizard to push it to a table and it zips right along. Can anyone explain why this is more efficient? The wizard is using the same query as it's source. Indexes are on the join fields.ThanksCrusty

Set DTS Globalvariable & file path from Java

Posted: 12 Sep 2013 09:09 AM PDT

Hi All,Greetings!!!I am new to DTS and i need some help regarding the followingOur environment is SQL 2008 but we have 2005 backward compatability pack on our server so we have dtsrun.exe.We have the DTS placed on a shred drive and by using xp_cmdshell we run the dts packages. Our application was based on MS Access before and now we are trying to migrate to Java. We have a stored procedure that we call from java to run the DTS.The Java application will run on a unix machine.Currently we are able to execute the DTS from the java application but we are facing difficulties updating the global variables. I tried thisexec databasename..xp_cmdshell 'DTSRun.exe /a VariableName:String="21713-1" /f "D:/DTS/Load table.dts"' but when i execute this the query is running for long time and nothing is happening.I also have one more DTS, which accepts the input file and updates the tables. The user can browse the file from any location and upload. In the DTS, if i right click for the connection properties we have a kept the datasource as text source and hardcoded a file name now. How can we update the file name location from java.This is what we have in Access applicationmyPkg.Connections("Load File").DataSource = stFilePlease help

Set DTS Globalvariable & file path from Java

Posted: 12 Sep 2013 08:59 AM PDT

Hi All,Greetings!!!I am new to DTS and i need some help regarding the followingOur environment is SQL 2008 but we have 2005 backward compatability pack on our server so we have dtsrun.exe.We have the DTS placed on a shred drive and by using xp_cmdshell we run the dts packages. Our application was based on MS Access before and now we are trying to migrate to Java. We have a stored procedure that we call from java to run the DTS.The Java application will run on a unix machine.Currently we are able to execute the DTS from the java application but we are facing difficulties updating the global variables. I tried thisexec databasename..xp_cmdshell 'DTSRun.exe /a VariableName:String="21713-1" /f "D:/DTS/Load table.dts"' but when i execute this the query is running for long time and nothing is happening.I also have one more DTS, which accepts the input file and updates the tables. The user can browse the file from any location and upload. In the DTS, if i right click for the connection properties we have a kept the datasource as text source and hardcoded a file name now. How can we update the file name location from java.This is what we have in Access applicationmyPkg.Connections("Load File").DataSource = stFilePlease help

Early Month end processing, How do you handle the dates?

Posted: 12 Sep 2013 03:35 AM PDT

We have always waited until the first of each month to start our month end processing. We would use code like the following to determine the prior months last day.[code="sql"]declare @runDate datetime;declare @evalDt datetime;set @runDate = convert(char(10),getdate(),101);set @evalDt = convert(char(10),(dateadd(dd, - datepart(dd,@runDate) , @runDate)),101);[/code]We have been asked by management for awhile now that they want us to start processing sooner. Like when the first falls on a Monday or Sunday to be able to start on Saturday. So we have thought about how to make this work with all of the processes that have the above calculation, something similar would be done to find the first day of the prior month. So we looked at the dates through the years and determined that the earliest date we could potentially need to start would be the 26th, we always have Thanksgiving and the day after off. So we started adding the following code to our processes.[code="sql"]set @rundate = (case when datepart(dd, GETDATE()) >= 26 then dateadd(dd, 10, GETDATE()) else GETDATE() end);[/code]Now management is throwing us a new curve. For this coming November they want to cut off our month at 11/22/2013. And they want anything processed after that date to fall into December results. As you can see the logic we were adding isn't going to work for this situation.So my thoughts went to setting up a table that we could update that would contain the start date for that month and an end date. So in the this situation I would have a start date for the month of November as 11/01/2013, the end date would be 11/22/2013, these all would go under the month end date of 11/30/2013. Then for December the start date for the month would be 11/23/2013 and the end date would be 12/31/2013, month end date for these would be 12/31/2013.I'm curious to know if or how others handle their dates? Do you think I'm on the right track with using a table? Any issues you've run into?Thanks,

Sql 2008 - Query Response Tiem v/s High CPU and IO Related Query

Posted: 12 Sep 2013 06:17 AM PDT

Hi,I have a question regarding Query tuning that if it shows high CPU and IO intesive query but resposne time is ok so still nedds to be tuned or optimized?Example:While Runing tining script, if it shows Total_CPU_Time = 2094320785, MAx_CPU_Time = 684039, Execution counts = 94903 and total Logical Reads = 602159535 but query response time is ok then what will be the effect if i don't create the Index on filtered criteria or suggested index from the Execution plan or Tuning Advisor?I have Index also ReBuilded and updated the stats.I would liek to know any performance impact within a IO and COU intensive query but resposne time is ok.Thanks.

Delay in Update Large Table Wtih Trigger

Posted: 12 Sep 2013 01:35 AM PDT

Hi all,I have the following statement in a trigger, in order to update a link table that has 5000000 records.UPDATE LINK.DATABASE.dbo.PPP1 SET KODAN= @ITEMID ,ONANT=@ITEMNAME --,REMOTE_PPP1.KODKA=SUBSTRING(I.ITEMGROUPID,1,17) ,KODKA=SUBSTRING(@AMDEVICEBRANDID,1,17) ,MM=SUBSTRING(@UNITID , 1 , 5) ,TVAL=@AMOUNT ,THON=@WHOLESALEAMOUNT WHERE KODAN2= cast(@REFRECID as char(25))When I run it from managemet studio it runs quickly. When it runs from the trigger it takes about 30 minutes to do the update.I would appreciate if someone could help.Thanks,John

Import Text File to SQL Database

Posted: 12 Sep 2013 03:08 AM PDT

I have a text file with no delimiters and fixed width fields that I need to import into a SQL Server database. An example of the text file is:012345678901012013TYPEADESC FIELD 8.00123456789001022013TYPEBDESC FIELD 14.00234567890101032013TYPECDESC FIELD .75Text File is set up as EmpID: 10Date: 8 (mmddyyyy)Type: 5Desc: 12Hours: (6,2) DecimalSQL Server table is set up as:Date (datetime)Type: varchar(5)Desc: varchar(12)Hours: decimal(6,2)EmpID: varchar(10)The problem I'm having is with the Date field. How do I convert the date from the text file (mmddyyyy) to the sql server date field (yyyy-mm-dd hh:mm:sss)?

Cluster Upgrade -- some suggestions please

Posted: 12 Sep 2013 05:04 AM PDT

We currently have a three node cluster running on Windows Server 2008 SP1 (not R2) and two of the Nodes are Active with four MS SQL 2008 SP1 Instances on each and the third node is passive for all 8 instances. Below I'll call the Active Nodes A1 and A2 and the Passive node P1 just to keep it simple :)I need to install several updates plus MS SQL 2012 as a new Instance on the cluster, but given this requires many updates to accomplish I wanted to bounce our suggested plan to the group in-case others have experienced problems going this route or with any of these updates.Here are the updates and applications we plan on rolling out:- KB948465 – Windows Server 2008 Service Pack 2- KB956250 – Microsoft DotNet 3.5 Service Pack 1 Update- KB2546951 – Microsoft SQL Server 2008 SP3 on all Instances- KB968930 – Windows Management Framework Core Package (WinRM 2.0 and PowerShell 2.0)- Microsoft .NET Framework 4.0 – Required for future software updates- Microsoft OLEDB Provider for DB2 v4.0My plan is to install Windows Service Pack 2 on the Passive node (P1), then after reboot move all Instances from A1 to P1 then back to A1, then move all instances from A2 to P1 and back just to verify everything moves across okay. If no hiccups then we'll move all instance from A1 to P1, install Windows SP2 to A1, then move the instances back and do the same to A2.Next we'll install KB956250 then KB2546951 on the Passive node (P1) and move the four instances on A1 and A2 over to P1, one node at a time, and if everything moves without issues move the Instances on each node one at a time to P1 and update each node as before.. After all this I'll install KB968930, DotNet 4.0, and OLEDB Provider for DB2 to passive node then once again move everything over and back one node at a time to test, and if all works install on Active nodes as before.Then after all this is done I should be ready to install SQL 2012.. Sheesh! It's a lot, but I'm not sure how else to get everything caught-up and tested in between updates so if something fails I can pinpoint the cause. Any other suggestions or ideas for this? Or has anyone ran into caveats with any of these updates on a Failover Cluster?Thanks,Sam

Datediff in means

Posted: 11 Sep 2013 11:52 PM PDT

Hi all,Please what is the syntax for datediff in minutes.I have tried the expression datediff("m", fields!startddate.value, fields!enddate.value) but it is not working. I have equally use the full minutes as in "minutes" but still not working. "mm" does not work either.

Correct permissions after restore from another domain?

Posted: 12 Sep 2013 03:27 AM PDT

Hi,I've been restoring databases from another domain. As a result the users that are restored with the db have the user accounts from the other domain, and are not linked to a login.So I've been going in to each db I restore and looking at the permissions, and then adding a login with the matching permissions to that db with the correct domain account. The names for the most part are the same just with a different domain prefix.This is taking forever, because I'm also removing the old domain db users, and I have to change schema owners in some cases to delete the user.Has anyone found a faster way to do this, even just a little bit faster? If you can help thanks....

How to add this condition into where clause? Thanks.

Posted: 12 Sep 2013 12:44 AM PDT

I need to have a query like:declare @dt bitselect * from document where case @dt when 1 then doctype in (1, 2) when 0 then doctype = 1 end How do I write the query? Thank you in advance

replication

Posted: 11 Sep 2013 08:09 PM PDT

i did not implement replication in my current environment , i have 4 publishers iwant to know how many distribution and log agents for this ?

SSAS Instal in a Cluster

Posted: 11 Sep 2013 07:23 PM PDT

I need to install SSAS as an extra feature on a 2 node SQL Cluster. This will have to run as a separate instance, what is the process to get it installed. Are there any gotcha'sI imagine I need to pause the node and install SSAS. The failover and do the same?

Relation ship in SQL Server

Posted: 11 Sep 2013 11:38 PM PDT

i want to create Primary key for 3 columns in parent table and foreign key in for 2 coloumns in child table.can i refer these 2 foreign key columns to those 3 primary key columns ?

MS Security patch MS11-049 (KB2494086) fails on SQL R2 build version 10.50.1753.

Posted: 12 Sep 2013 12:24 AM PDT

Trying to install MS Security patch MS11-049 (KB2494086) on SQL Server 2008 R2 build version 10.50.1753.The patch installation fails in midway with the log file error message as :Overall summary: Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Exit code (Decimal): -2061893602 Exit facility code: 1306 Exit error code: 30 Exit message: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Start time: 2013-08-31 16:48:44 End time: 2013-08-31 16:50:03 Requested action: PatchInstance MSSQLSERVER overall summary: Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Exit code (Decimal): -2061893602 Exit facility code: 1306 Exit error code: 30 Exit message: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Start time: 2013-08-31 16:49:20 End time: 2013-08-31 16:50:02 Requested action: Patch Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130831_164844\MSSQLSERVER\Detail.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0x4E0712AD%400x2F2F19BE%401306%4030Only the Database Engine Services is not getting patched. The remaining components seem to have got patched as below :[size="2"] Product Instance Instance ID Feature Language Edition Version Clustered Sql Server 2008 R2 MSSQLSERVER MSSQL10_50.MSSQLSERVER Database Engine Services 1033 Enterprise Edition 10.50.1753.0 No Sql Server 2008 R2 Management Tools - Basic 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Management Tools - Complete 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools Connectivity 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools Backwards Compatibility 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools SDK 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Integration Services 1033 Enterprise Edition 10.50.1790.0 No [/size] Tried repair and install multiple times, but the patch install fails. Let me know if you require any more details.Can someone please help. Urgent.

SQL job owner

Posted: 11 Sep 2013 10:15 PM PDT

What is the purpose and significance of the SQL job owner setting on a job?I've never considered this before, but it has come up today as we have found jobs where the owner was set to a particular user account.How is this property supposed to be used? What is the significance of having it set to a particular account?Thanks.

Extracting Data From a MySQL Linked Server

Posted: 17 Dec 2010 12:53 AM PST

Hi all,I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need, and I was unable to find a post where someone had a similar issue.In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.I have read articles on this forum about people who couldn't see the tables in the explorer, but could access them in a query. I've read articles where people could see some system tables, but not user tables. I find it weird that I can see them ALL in the explorer, but can't access any of them.Any help would be greatly appreciated.Thanks,Bill

Run Query and email results as CSV.

Posted: 11 Sep 2013 07:05 PM PDT

Morning everyone.I have a query that works ok , and uses msdb.dbo.sp_send_dbmail to send the results.The results in .csv look terrible.Can anyone recommend anything to format the results ?Query results to grid look fine , results to text look terrible. Does anyone have any recommendations on how to out put in a better format ?many thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog