Sql samples, sql tips

Monday, April 15, 2013

[SQL Server] Testing with Profiler Custom Events and Database Snapshots

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server Data Warehousing] How to copy files from sftp location to my local system using SSIS Script Task.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.

Would you like to participate?

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SQL

Hi all!

I configure OLAP cube.

I have a few projects using cube via c # (AdomdCommand) and some reports (ReportingServises) working directly via MDX.

Where can I save the same requests to avoid duplicating code?
Need something like SP in SQL.

social.technet.microsoft.com/Forums

[Articles] Modeling the Earth

Modeling the Earth

Posted: 14 Apr 2013 11:00 PM PDT

Microsoft Research has a variety of interesting projects underway, including one to try and model the Earth. Many of these involve lots of data in some way, and would be interesting projects to work on, according to Steve Jones.

[MS SQL Server] dbWarden questions

dbWarden questions
Restore Transactional Log Backup
CPU on SQL server
No performance gain on queries on different servers with considerable hardware change
Encrypted File system(EFS) in sql Server 2008
Simple Replication Question
Rebuilding All Indices

dbWarden questions

Posted: 15 Apr 2013 03:48 AM PDT

Hello --I am trying out the dbWarden application by running it within the 2008 Standard R2 SQL Studio application. It appears to have executed successfully, but I am having trouble identifying and locating the output file. Can anyone provide information on this? Thanks.

Restore Transactional Log Backup

Posted: 15 Apr 2013 03:33 AM PDT

Hi,I am new to this forum.Could anyone help me with this questions please. I am not able to resore my log backup.My plan for backup is daily full backup, and transactional log backup for every 5 minutes because we do not have much transactions.My data base recovery model is "Full", and I scheduled transactional Backup with time stamp file name, and it works. Now I have problem with restoring transactional log backup. I restored my Full backup with Recovery state " RECOVERY WITH STANDBY" , and Restore options "WITH REPLACE", but when I want to restore transactional log backup, it is grayed out. Any help would be greatly appreciated. Thank in advance,

CPU on SQL server

Posted: 15 Apr 2013 03:25 AM PDT

Our .net application runs slow from a couple of weeks ago, it is using the SQL database, I see the CPU usage on SQL server sometimes goes up to 96% above, how can I troubleshooting this to see what is using the CPU?Thanks

No performance gain on queries on different servers with considerable hardware change

Posted: 15 Apr 2013 12:02 AM PDT

I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied. The machine is a 64 bit windows machine with 32 processors and 48 GB Ram.I have moved a database from my existing UAT server to the new server(There is a drastic change in the server hardware) but can't find increase in performance when I run the same queries. What could be the reason ? Any suggestion on changes to be made ?

Encrypted File system(EFS) in sql Server 2008

Posted: 03 Apr 2013 08:22 PM PDT

Hi, how can i impleted EFS in sql level user in sql server 2008r2..?is it possiable please help me..

Simple Replication Question

Posted: 14 Apr 2013 12:03 PM PDT

I am doing some testing with Replication, I have not used much replication before.I have set up a Subscription that uses Transactional Replication and works fine when I update or insert.I added a new table, went to the Publisher and added the table as a new Article.I could not see the new table in the Subscriber database.In the end I did a "ReInitialize" at the Publisher with creating a new Snapshot.I can see the new table and the errors have gone.Is this the correct way to get the new article to the Subscriber?When it does this new Snapshot does it consist of the ALL the Articles and data (could be big) or just and changing like the new table?thanks

Rebuilding All Indices

Posted: 14 Apr 2013 07:53 AM PDT

Hello EveryoneI hope that everyone is having a very nice weekend.I am working to create a maintenance plan. I am looking for some great advice on how one could rebuild all indices. Of course using a stored procedure that is executed by a SQL job. I have a query that will return all index names that are fragmented above a set percentage. I would like to use that to get the name. But after that, I am not sure what the best performing code would be to write to rebuild each index in the list returned.I great appreciate any and all advice, suggestions and commentsAndrew SQLDBA

[SQL 2012] Comparing column data in 2 tables with the same schema containing 1.5 billion records

Comparing column data in 2 tables with the same schema containing 1.5 billion records
multiple instances of sqlserver different dns alias
multiple instances of sqlserver different dns alias
Configure the SSAS to web using TOMCAT

Comparing column data in 2 tables with the same schema containing 1.5 billion records

Posted: 14 Apr 2013 10:07 AM PDT

I have had the task of recreating a table in our data warehouse which contains 1.5 billions rows to make use of partioning on the clustered index by date. To keep the tables in sync over the past few weeks I have had to run import jobs on both tables.Before moving across to the new partitioned table (by renaming it to bring it into production) I need to ensure that the EffectiveEndDate column in both tables is identical for every row (which has a PK).So my question is how best is it to compare 2 tables with 1.5 billion records to ensure the data in both is the same?At this stage 3rd party tools are not an option and I am using SQL Server 2012 SP1 Enterprise Edition.Any help greatly appreciated!!

multiple instances of sqlserver different dns alias

Posted: 14 Apr 2013 01:33 AM PDT

Hi , I have installed named instances sqlserver2005 express and sqlserver2012 on the same Windows 2008 server Virtual machine ,configured sqlserver2012 to use NON Default TCP IP port .we need 2 different DNS Aliases for these 2 database Servers i.e I should be able to access by specifying the following properties in my applicationuser=sapassword=myPwdhostname=mysql2005.us.comp.comport=1433instanceName=sqlserv2005user=sapassword=myPwdhostname=mysql2012.us.comp.comport=1533instanceName=sqlserv2012Is this possible?Can you give pointers on how to achieve thisManyThanks,BG

multiple instances of sqlserver different dns alias

Posted: 14 Apr 2013 08:35 PM PDT

Hi ,I have installed named instances sqlserver2005 express and sqlserver2012 on the same Windows 2008 server Virtual machine ,configured sqlserver2012 to use NON Default TCP IP port .we need 2 different DNS Aliases for these 2 database Servers i.e I should be able to access by specifying the following properties in my applicationuser=sapassword=myPwdhostname=mysql2005.us.comp.comport=1433instanceName=sqlserv2005user=sapassword=myPwdhostname=mysql2012.us.comp.comport=1533instanceName=sqlserv2012Is this possible?Can you give pointers on how to achieve thisManyThanks,BG

Configure the SSAS to web using TOMCAT

Posted: 14 Apr 2013 05:13 PM PDT

Hi, Am using the SQL SERVER 2012,am trying to configure the ssas in to web using Tomcat.If anybody know please help me out.Thanks,Basee

[T-SQL] CROSS APPLY - Logical Query Processing

CROSS APPLY - Logical Query Processing
Arithmetic overflow error - double join?
Alternative UPDATE Syntax
please help with cursor syntax
insert recursive days data into table.
Dynamic Date Columns
How to call a batch file to execute from an SP
How To Split The Data...Using The Stored Procedure...

CROSS APPLY - Logical Query Processing

Posted: 15 Apr 2013 01:54 AM PDT

I have a question about when a CROSS APPLY is processed in a queryUsing the following code as an example:[code="other"]DECLARE @OC TABLE ( Batch VARCHAR(5), DateData DATETIME)INSERT INTO @OCSELECT 'ABCDH', '20130101' UNION ALLSELECT 'DFGHY', '20130102' UNION ALLSELECT 'DFGJU', '20130103' UNION ALLSELECT 'DFGHP', '20130104'DECLARE @PO TABLE ( Batch VARCHAR(5), DateData DATETIME)INSERT INTO @POSELECT 'ABCDH', '20130111' UNION ALLSELECT 'DFGHY', '20130112' UNION ALLSELECT 'DFGJU', '20130113'SELECT O.Batch, CA.LBat, CA1.POLBat, O.DateData, P.DateDataFROM @OC AS OCROSSAPPLY (SELECT LEFT(O.Batch,2) AS LBat) AS CACROSSAPPLY (SELECT LEFT(P.Batch,2) AS POLBat) AS CA1LEFTJOIN @PO AS P ON O.Batch = P.Batch[/code]This returns:[code="other"]Msg 4104, Level 16, State 1, Line 37The multi-part identifier "P.Batch" could not be bound.[/code]However, if you switch the second CROSS APPLY to after the JOIN it appears to work:[code="other"]SELECT O.Batch, CA.LBat, CA1.POLBat, O.DateData, P.DateDataFROM @OC AS OCROSSAPPLY (SELECT LEFT(O.Batch,2) AS LBat) AS CALEFTJOIN @PO AS P ON O.Batch = P.BatchCROSSAPPLY (SELECT LEFT(P.Batch,2) AS POLBat) AS CA1[/code]Would any joins not be processed before the CROSS APPLY?

Arithmetic overflow error - double join?

Posted: 15 Apr 2013 01:46 AM PDT

Guys,I've created a temp table which has a bunch of IDs and dates, something dead simple like this:[quote]SELECTPg.ID,pg.Name,COUNT(pa.Log_ID) as A,FROM tbla pgJOIN #log pa on pa.ID = pg.IDGROUP BY pg.id, pg.name[/quote]Works just fine, if I try to join to the same table again (with a different alias) and do another count it gives me an error:[quote]Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.[/quote][quote]SELECTPg.ID,pg.Name,COUNT(pa.Log_ID) as A,COUNT(pb.Log_ID) as B,FROM tbla pgJOIN #log pa on pa.ID = pg.IDJOIN #log pb on pb.ID = pg.IDGROUP BY pg.id, pg.name[/quote]My initial reason for this double join was thinking I could restrict based on date - so whilst my temp table has the data going back over 1 year I could join to it several times, restricting my data set on each join - e.g. once for the full year, once for 6 months, once for 3 months etc.I've not come across this before, does anyone know what might be causing it? I tried casting to a bigint but it just ran for ages (well over 10mins when the origina query takes seconds).Thanks!

Alternative UPDATE Syntax

Posted: 15 Apr 2013 01:11 AM PDT

Hi all,I have a question about the UPDATE syntax.There are the following - fictive - tables:Table ParamValues:[code="other"]PV_ChangeDate PV_ChangeUser PV_ID PV_Item PV_PK_ID PV_IntValue----------------------- ------------- ----------- -------------------------------------------------- ---------- -----------2013-04-15 16:00:15.477 Peter 1 wall 1 9002013-01-01 00:00:00.000 Peter 2 house 2 2002013-01-02 00:00:00.000 Doris 3 lantern 1 1002013-04-15 16:00:56.130 Peter 4 wall 2 70[/code]Table ParamKeys:[code="other"]PK_ID PK_KeyName PK_Description----------- -------------------------------------------------- --------------------------------------------------1 height The Height2 width The Width[/code]Usually, I use the following syntax - a example:[code="other"]-- changing the IntValue...UPDATE ParamValuesSET PV_ChangeDate = GETDATE(), PV_ChangeUser = 'Peter', PV_IntValue = 1000WHERE PV_ID = 123;[/code]Now, I have to use an INNER JOIN. I've read about an alternative syntax (UPDATE ... SET ... FROM ... WHERE) where an alias for the tables is to be used.For example:[code="other"]-- changing the height of the wall to 1000 :)UPDATE pvSET pv.PV_ChangeDate = GETDATE(), pv.PV_ChangeUser = 'Peter', pv.PV_IntValue = 1000FROM ParamValues pvINNER JOIN ParamKeys pk ON pk.PK_ID = pv.PV_PK_ID AND pk.PK_KeyName = 'height'WHERE pv.PV_Item = 'wall'[/code]But... in my situation I would like to (have to) avoid the alias "pv" in the UPDATE syntax. I tried the following, and it worked for me. But I never read or heard about it. So I don't know if it is an "official allowed" syntax:[code="other"]UPDATE ParamValuesSET PV_ChangeDate = GETDATE(), PV_ChangeUser = 'Peter', PV_IntValue = 1000FROM ParamValuesINNER JOIN ParamKeys ON PK_ID = PV_PK_ID AND PK_KeyName = 'height'WHERE PV_Item = 'Wall'[/code]Is it secure to use this last syntax?Many thanks in advance for you replies!Greg

please help with cursor syntax

Posted: 29 Mar 2013 03:56 AM PDT

I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.For the cursor part I'm getting the following error:Msg 16915, Level 16, State 1, Line 4A cursor with the name 'getDatabaseName' already exists.Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@DatabaseName".my cursor script[code="plain"]use MiguelsAppDECLARE @DatabaseName varchar(max)DECLARE getDatabaseName CURSOR FOR SELECT name, database_id, create_dateFROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb');GOOPEN getDatabaseNameFETCH NEXTFROM getDatabaseName INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGINprint @DatabaseNameFETCH NEXT FROM getDatabaseNameINTO @DatabaseNameENDclose getDatabaseNamedeallocate getDatabaseName[/code]Will someone please tell me where my syntax is wrong so it will run?

insert recursive days data into table.

Posted: 14 Apr 2013 10:16 PM PDT

Hi,Please guide me on following scenario.In one of my application i am giviving provision to user select startdate and enddate data to insert records. This functrionality is working fine.Also user is asking to provide check boxes for slection of day like sun,mon,tue....sat. if user selects date range like 01/01/2013 to 01/31/2013 and check for Tue and Fri only, then i want to insert data for that renage belonging to tuesday and friday only. there is no day column in my database. how can i handle this in SP?thanksAbhas.

Dynamic Date Columns

Posted: 14 Apr 2013 06:35 PM PDT

Is it possible to create dynamic dates with SQL. I want to display all the dates for a particular date range as column headers? How can it be achieved?

How to call a batch file to execute from an SP

Posted: 20 Mar 2013 04:15 AM PDT

Hi All,Need your assistance please, I am not very good with scripting.I have created a draft of SP, and I need syntax to make a call to a batch file(.bat) from within the SP. Once I have that I can incorporate it in the code and begin testing.Can someone please provide sample script.Thanks,SueTons.

How To Split The Data...Using The Stored Procedure...

Posted: 14 Apr 2013 03:06 AM PDT

Hi..I Want to Spliting the Data for The Following Table. Using The Stored Procedure....[code="sql"]Create Table Source (Owner_Name varchar(100),SeatNo Varchar(100))insert into Source values ('A','10,11,12'),('B','20,21,22'),('C','30,31,32')select * from sourceA 10,11,12B 20,21,22C 30,31,32[/code]But My Requirement Structure Sholud like below Mentioned..[code="sql"]Name NoA 10A 11A 12B 20B 21B 22C 30C 31C 32[/code]Thanks & Regards,K.D.Saravanan

Monday, April 15, 2013

Search This Blog