Tuesday, June 25, 2013

[MS SQL Server] Linked Server - Missing Feature "Provider Options"

[MS SQL Server] Linked Server - Missing Feature "Provider Options"

Linked Server - Missing Feature "Provider Options"

Posted: 25 Jun 2013 03:01 AM PDT

I see references to changing setting on the "Provider Options" page, but on my 2005 and 2008 servers, I don;t see that option. I have "General", "Security" and "Server Options". Where are these "Provider Options" they speak of ? What am I missing ? I have 2005 SP4, and 2008 R0

Sql Server doesn't use the right index

Posted: 25 Jun 2013 01:13 AM PDT

Hello,I've a strange issue with an index. I discover the sql have not used the right index last week.This's my SQL code:SELECT -- type 'C ' AS [type], -- item RTRIM([models].[mode]) + '@' + RTRIM([skus].[grid]) AS [item] , -- ware 'PFG0' AS [ware], -- duedate LEFT([dbo].[get_date_fmt]([commessa_C_excp].[duedate],'GGMMAA',NULL),6) AS [duedate], -- outflow 0 AS [outflow], -- backorder [PRODSTARTS].[maxqm] AS [backorder], -- mingrossreq 0 AS [mingrossreq], -- maxgrossreq [commessa_C_excp].[maxgrossreq] AS [maxgrossreq] INTO #COMM_C_EXCP FROM [commessa_C_excp] INNER JOIN [skus] ON([skus].[id] = [commessa_C_excp].[sku_id]) INNER JOIN [models] ON([models].[id] = [skus].[mode_id]) INNER JOIN [fitcode] ON([fitcode].[id] = [skus].[fitcode_id]) INNER JOIN [prod_starts] [PRODSTARTS] -- -- -- WITH (INDEX(MI_prod_starts_linea_id_tipo_id_cod_rel_id_fitgrp_id_year)) -- -- -- ON([PRODSTARTS].[linea_id] = [models].[linea_id] AND [PRODSTARTS].[tipo_art_id] = [dbo].[tipo_id_std]([models].[tipo_id]) AND [PRODSTARTS].[cod_rel_id] = [dbo].[get_release_id]([skus].[id]) AND [PRODSTARTS].[fitgrp_id] = [fitcode].[fitgrp_id] AND [PRODSTARTS].[year] = YEAR([dbo].[skus_min_drel]([skus].[id])) )GOI'm forcing the select to use the index now, the problem is I don't understand why it stopped to use it.I try to rebuild it but no change.Best RegardsMarco

sql job showing wrong execution time in job's history

Posted: 25 Jun 2013 01:43 AM PDT

hi i have a sql server 2008 r2 instance, after checking a few of my sql agent jobs, i see the last execution time being in the future. today's date is 6/24/13 and is showing it last executed on 6/28/13. i ran one of these job's manually and then it shows correct current time. I also checked server's time and it shows correct current time. I also checked the following: SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE() and they show the correct current time. Can anyone help?

Restore a development DB form Prod.

Posted: 24 Jun 2013 10:09 AM PDT

I'm trying to restore a development DB form Prod.I get an error on the Log file but it is a valid path.I do not get it.Any help would be greatly appreciated.[code="plain"]RESTORE DATABASE CypressFROM DISK = 'I:\Backups\CYP_DW\Full\MyDBDW\MyDBDW_backup_2013_06_24_163430_3276765.bak'--WITH REPLACE,WITH NORECOVERY,MOVE 'MyDBDW' TO 'E:\MSSQL\Data\MyDBDW.mdf',MOVE 'MyDBDW_Horizon' TO 'E:\MSSQL\Data\MyDBDW_HorizonObjects1.NDF',MOVE 'MyDBDW_log' TO 'E\MSSQL\Log\MyDBDW.ldf'Msg 5105, Level 16, State 2, Line 1A file activation error occurred. The physical file name 'E\MSSQL\Log\MyDBDW_New.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.Msg 3156, Level 16, State 3, Line 1File 'MyDBDW_log' cannot be restored to 'E\MSSQL\Log\MyDBDW_New.ldf'. Use WITH MOVE to identify a valid location for the file.Msg 3119, Level 16, State 1, Line 1Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.[/code]

Licensing standalone SSRS (2008 R2 or 20012)

Posted: 24 Jun 2013 09:16 AM PDT

Hi GuysWe currently run 2008 R2 and SSRS (not sharepoint) on a 4 core VM, so license is around $8K. We have more and more needs for data driven subscriptions, but can't justify upgrading to enterprise version on 4 core machine ($28k). I was thinking that spinning up a dedicated VM for SSRS with a single core and running enterprise version of SQL Server would get us what we need fro about $8K (SQL and WIn licenses) -- we have enough RAMI've read that having SSRS on a dedicated box improves performance too.So would this work if we offloaded all of our SSRS to a new single core VM? Can you buy just an Enterprise SSRS licenses or must you always buy it with SQL server?I've scoured the web, but couldn't find an answerBTW, we own SQL2012 licenses, can't use it yet because of 3rd part app compatibility, but I figured if we went this route we could use some of the new features of SSRS 2012 with 2008 R2 data sourcesThanksMark

Error Restoring Database

Posted: 24 Jun 2013 11:09 AM PDT

I execute the following:[code="plain"]FROM DISK = 'I:\Backups\CYP_DW\Full\MyDBDW\MyDBDW_backup_2013_06_24_163430_3276765.bak'WITH RECOVERY,MOVE 'MyDBStaging' TO 'E:\MSSQL\Data\MyDBStaging.mdf',MOVE 'MyDBStaging_log' TO 'E:\MSSQL\Log\MyDBStaging.ldf',REPLACE[/code]RESTORE DATABASE MyDBStagingI get the following error. I'm not trying to create the DB on but on E?[code="plain"]Msg 3257, Level 16, State 1, Line 2There is insufficient free space on disk volume 'D:\' to create the database. The database requires 216796037120 additional free bytes, while only 160890318848 bytes are available.Msg 3119, Level 16, State 4, Line 2Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.[/code]
Posted by at No comments:

[SQL 2012] FlushCache messages ... the ongoing quest for information

[SQL 2012] FlushCache messages ... the ongoing quest for information

FlushCache messages ... the ongoing quest for information

Posted: 24 Jun 2013 06:26 PM PDT

With some of our SQL2012 instances we get frequent checkpoint messages during data load[quote]2013-06-25 06:14:04.84 spid16s FlushCache: cleaned up 184496 bufs with 11466 writes in 507527 ms (avoided 125257 new dirty bufs) for db 7:02013-06-25 06:14:04.84 spid16s [b]average throughput: 2.84 MB/sec[/b], I/O saturation: 21329, context switches 485042013-06-25 06:14:04.84 spid16s last target outstanding: 2400, [b]avgWriteLatency 990[/b][/quote]as per CSS blog post [url]http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx[/url]these are supposed to be informational and might point you to investigate further.[quote]...the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval. If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.[/quote]At SSC forum [url=http://www.sqlservercentral.com/Forums/Topic1363610-2799-1.aspx#bm1435783]system FlushCache happening often[/url] [url=http://www.sqlservercentral.com/Forums/UserInfo498037.aspx]SpringTownDBA[/url] added great information with regards to how to investigate the issue and some tips on how to avoid dirty pages.As I have [u]no windows admin authority[/u] for the concerned instances, I cannot just launch perfmon to investigate the thing.[b]Does someone have a ref that explains the details of it all ?[/b][u]What do these measures mean?[/u]- I/O saturation mean - last target outstandingWhy is there so much variation in the figures of the different FulshCache messages?The only thing going on at that load time is a single database being loaded. No other activity is ongoing at that time span.I think the server raising the posted message is not configured in an optimal way.Mainly based on its value for [i]average throughput [/i]is [b]low[/b] (even 0.42 MB/sec in some messages)and the value for [i]avgWriteLatency[/i] is [b]high[/b] (varying from 65 to 990) for a dedicated RAID1 disk ( DASD 15K ) I think adding RAM to this instance might cause the frequency of these message to lower down, but if the issue is the I/O subsystem, the yield for such operation would be low.Could optimising the NTFS block size to 64K help out ? ( is currently 4K )

SQL 2012 cluster - changing IPs and instance name

Posted: 24 Jun 2013 09:56 AM PDT

We have SQL 2005 cluster (active-passive) in our production environment. Customer wants us to build new cluster with SQL 2012 active-passive configuration and run it in parallel with the existing SQL 2005 cluster for a while (the same network, the same subnet). Later on when all databases get migrated and apps get tested, the old cluster will be removed and the new cluster has to pick up the same IPs, names and instance names as the old had. My concern is will we be able to make these changes (IPs, instance names) in SQL 2012 cluster environment and what problems (if any) we might run into? Great thanks in advance for your opinion

Granting access to remote location to SQL Server 2012

Posted: 24 Jun 2013 11:45 PM PDT

Briefly, what I'm looking to do is set up a job which takes backups locally and then moves them to a server dedicated to holding SQL Server backups.Now, in previous versions of SQLServer, nice and easy, for Server name Wibble using system account for SQL Server services - create directory on target, share, add Wibble$ - set full control permissions to wibble$, works fine.With the different approach to default SQL Server services in 2012 - I can't get this working. This will have an account MSSQL$Wibble (for named instance Wibble) - Assigning Wibble$ full control permissions to its directory on the remote backup server doesn't work - the copyover job fails with an access error. The following suggests it should work as I understand it - but I can't get it to. "Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$." at http://technet.microsoft.com/en-us/library/ms143504.aspx#Windows Can anyone who's done this point me in the right direction?

SSMS 2012 error when connect to sql server with windows authentication after VS instalation

Posted: 09 Apr 2013 07:33 PM PDT

Hi.After install VS 2012 in my pc i can't connect with SSMS 2012 to my SQL Server 2012 server (i can connect to other server -only SQL Server 2000 server-). I receive the next error message:"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)".I already execute 'netsh winsock reset' command but the result is the same. I don't know what more doing.Somebody could help me?Thanks.
Posted by at No comments:

[T-SQL] Another grouping problem

[T-SQL] Another grouping problem

Another grouping problem

Posted: 09 May 2013 10:34 PM PDT

I have found a bug in my duplicate macthing application and I believe it's with the way I group my records at the end of the matching process.Consider the following example (details are not real!)[img]http://i.imgur.com/zZy2PqS.jpg[/img]52941 matches 8388 and 15799 because mobile number and name is the same80833 matches 8388 and 15799 because name and address matchso this is same as saying 80833 matches 52941, 8388 AND 15799So based on the second result set, I would like to perform a grouping of the outcome to look like this:[img]http://i.imgur.com/NibYbnP.jpg[/img]NOTE, I picked the highest value ID as my Base or Group ID.Any suggestions?

Date Comparision

Posted: 24 Jun 2013 11:04 PM PDT

Table1 Callstatusflag Generateddate N 2013-06-24 00:00:00.000 N 2013-06-24 00:00:00.000 B 2013-06-22 00:00:00.000 C 2013-06-22 00:00:00.000select count(Call_status_flag) Total from Table1 where '2013-06-22 00:00:00.000'>='2013-06-22 00:00:00.000' and Call_status_flag='N' O/P:2select count(Call_status_flag) Total from Table1 where Generateddate='2013-06-22 00:00:00.000' and Call_status_flag='N' O/P:0Why?

Max of 2 dates

Posted: 24 Jun 2013 07:40 PM PDT

I search for Date functions for finding the max of the 2 dates. But such function does not exits.Is there any easy way to do it.Thanks in advance.
Posted by at No comments:

[SQL Server 2008 issues] Large Records -- No Successfull Result

[SQL Server 2008 issues] Large Records -- No Successfull Result

Large Records -- No Successfull Result

Posted: 24 Jun 2013 01:01 AM PDT

Hi Team,I've a simple table USG_Data with below columns, but having 2.5 lac records.id, Name, Address, City, Phone.when i select count(*) from USG_Data, gettting result with count of records, butwhen i select * from USG_Data am not gettting the complete result set, after displaying some records sql server displaying that execuing query....Want to know the reason please.

Stored procedure executing another SP

Posted: 23 Jun 2013 11:31 PM PDT

Hi allI have stored procedure DoTransactions that is used to create a number of transactions in a database where the front-end application would usually have done the data inserts/updates. The SP on completion (whether failure or success) then passes a identifying ID to another stored procedure SendEmail which in turn sends an e-mail to a user confirming that the process had completed with whatever results.DoTransactions must run as a specific SQL user. The transactions it creates all confirm whether this user is created in the application tables and has sufficient rights. When logged into SSMS as this user and manually executing DoTransactions, SendEmail sends e-mails without any problem. DoTransactions however has to be scheduled to run hourly and I have set up a SQL Agent job for this. The job executes as the specific user and I know that is working correctly or else all the transactions would fail. It also successfully executes stored procedure SendEmail however in SendEmail there is a check that always fails (below) although the job step completes correctly:[code="sql"]IF EXISTS ( SELECT * FROM master.dbo.sysdatabases s WHERE s.name = 'msdb' )[/code]If logged into SSMS as the specific SQL user, I can execute the above and get the expected result although from the job it seemingly doesn't. Is it possible that the user executing SendEmail differs from the one executing the job (and therefore DoTransactions)?Snippet where DoTransactions calls SendEmail:[code="sql"]EXEC SendEmail @logid[/code]Snippet where SendEmail always fails when executed from DoTransaction via SQL Agent. It always ends up at 'Database "msdb" does NOT exist':[code="sql"]IF EXISTS ( SELECT * FROM master.dbo.sysdatabases s WHERE s.name = 'msdb' ) BEGIN PRINT 'Database "msdb" does exist.' IF EXISTS ( SELECT * FROM msdb.sys.objects so JOIN msdb.sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name = 'sp_send_dbmail' AND sc.name = 'dbo' ) BEGIN IF EXISTS ( SELECT * FROM msdb.dbo.sysmail_profile ) BEGIN DECLARE @MailSubject NVARCHAR(MAX), @ProfileName NVARCHAR(128) SELECT @ProfileName = s.name FROM msdb.dbo.sysmail_profile s IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysmail_principalprofile p WHERE p.principal_sid = 0x00 ) BEGIN EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @principal_name = 'public', @profile_name = @ProfileName, @is_default = 1 ; END EXEC MSDB.dbo.sp_send_dbmail @profile_name = @ProfileName, @recipients = 'test@test.test', @subject = 'Automated Notification: DoTransactions', @body = @MailOutBody, @body_format = 'TEXT', @importance = 'NORMAL' END END ELSE BEGIN PRINT 'Procedure "dbo.sp_send_dbmail" does NOT exist.' END ENDELSE BEGIN PRINT 'Database "msdb" does NOT exist.' END[/code]I appreciate that I could remove most of the checks above so that it simply sends the e-mail or even copy the above into DoTransactions so that I know the correct user is running this piece of SQL but I would like to understand better why this is happening in SQL Agent and not SSMS.

How to send email after job fail or complete in sql server agent

Posted: 24 Jun 2013 05:37 PM PDT

I am created a job for run .dts and after job is completed or failed I want to send mail to another person . How can I do.!! I'am sorry for my English language . I'am Thai. I live in BangkokThank you very much

Tcp/ip port named instance

Posted: 24 Jun 2013 07:25 AM PDT

Hi -I have setup a clustered server, I used named instance for both active/passive. The tcp port is set to dynamic sdince it is a named instance, I know if you use default the port is set to 1433. What happens if you have an application using default port number 1433 hard coded? Should I change the port to a static 1433 or leave the settings as is? Thanks,Lava

Installing Windows Server 2008 on Windows 7 Pro

Posted: 24 Jun 2013 03:45 AM PDT

HiI have installed Windows 7 pro installed on my pc on 'C' partition. Have two other spare partitions D and E drives. I download Windows Server 2008 with genuine key. I have tried to install Windows Server 2008 R2 through setup.exe file. I created bootable dvd which does not boot. Each time I run setup file I get error:"Microsoft .NET Framework 3.5 installation has failed.SQL Server 2008 Setup requires .NET Framework to be installed."I came across this website while searching for answers:{So, if it can't find it, it won't install it. According to the developer team at Microsoft, the error code means the setup can't determine the version of .NET contained on the media for your architecture. Therefore, ensure your media contains the following paths and .NET EXE packages: ia64\redist\DotNetFrameworks\dotNetFx35setup.exex86\redist\DotNetFrameworks\dotNetFx35setup.exex64\redist\DotNetFrameworks\dotNetFx35setup.exeIf you find the dotNetFx35setup.exe among the installation files, you can create these paths, and rerun setup again. This particular fix has gotten me past the .NET v.3.5 failed error.}My question is where do I create the above path is it on my pc which has Windows 7 Pro installed or do I create above path in the installation files I have on dvd or copied files of server 2008 on my D drive?Please can someone advise me, I cannot seem to get pass this error:Microsoft .NET Framework 3.5 SP1 installation has failed.SQL Server 2008 Setup requires .NET Framework 3.5 SP1 to be installed.I have checked my Windows 7 Pro installed on C drive, .NET 3.5 and 4.0 versions are installed.Not sure how to get past this error?Please can someone help?Raghbir

SSPI handshake failed with error code 0x8009030c...Someone help please

Posted: 12 Mar 2012 10:11 PM PDT

a new instance of SQL Server 2008 was installed and it would only allow you enter with Windows authentication. When you try sa logon it fails. Please what's the issue? I saw this error in the Event viewer log, thought it might be connected:SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: 192.168.43.69]. Any solutions?Charles.

How to format the data in column in sql server

Posted: 12 Jun 2013 12:38 PM PDT

Hi, Please help me, In my table have a one date column. It contains data in different formats .I want to set all these different formats into a single format. Like... Date 10.12.2012 ---in this '10' is Day and 12 is Month and then Year. 12-10-2012 10.12.2012 2012/10/12

collation difficulties when BULK inserting CP1 data in Japanese server

Posted: 24 Jun 2013 10:26 AM PDT

I'm experiencing confusing behavior when bulk inserting data in SQL_Latin1_General_CP1_CI_AS, on a Japanese server. Extended characters like é are being converted to question marks when selected. This makes me think it's being converted to Unicode somewhere, but I'm not really sure where to look or where the conversion could occur... as part of the BULK load? During the insert? Maybe during the select?Here are the different components:1. Unzip text file and load it with:MERGE INTO [table] AS [alias] USING (SELECT [columns] FROM OPENROWSET (BULK [datafile], FORMATFILE=[formatfile] ...2. The format file specifies the columns like:2 SQLCHAR 0 0 "\"|" 1 MYCOL1 SQL_Latin1_General_CP1_CI_AS3. The table columns are VARCHAR and have SQL_Latin1_General_CP1_CI_AS collation. However, the database itself could be something else (? - it's not my database so I don't have all the details yet). It might be Japanese or Unicode or Latin1_General_CI_AS.4. When I select in SQL Server Management Studio:SELECT mycol FROM mytable WHERE active='yes';I get cells like:Associ?Unfortunately, I can't reproduce this on my own laptop/server which is a US configuration. I've fiddled around to change settings to Japanese but I always get the correct answer:AssociéeRight now I'm not even sure where to look. Maybe the issue is with the command, maybe with the table, maybe even with how I'm using SQL Server Management Studio? Can anybody provide a suggestion for narrowing down the problem further?

general

Posted: 23 Jun 2013 11:39 PM PDT

i have table with name orders and field as order_number,order_amt,order_date,order_customer,order_salepersoni want to write a query that selects all orders save those with zeroes or NULLs in the order_amt field.how can i do this?

Microsoft Access forms/queries to Filter SQL Tables

Posted: 24 Jun 2013 03:29 AM PDT

Question/Problem: I am using Microsoft Access forms/queries to filter on a SQL table as in the following:SELECT dbo_Premier.CustName, dbo_Premier.Address, dbo_Premier.City, dbo_Premier.State, dbo_Premier.[Zip Code]FROM dbo_PremierWHERE (((dbo_Premier.Address) Like [Forms]![frmRosterSearch]![FindAdd] & "*"));This filter returns incorrect results. The results seem to have no logical pattern that I can see what my error is. It appears the correct records are returned, but with lots of incorrect results also.When I do the exact same filter on a local table in Microsoft Access with the same information in it, the results are returned correctly. At first I thought that somehow maybe the information wasn't being cleared out of the SQL table correctly because weekly I clear and repopulate this table, however, when I tried to build a brand new table in SQL from the local table, the filter was still incorrect. Using:SQLServer 2008 StandardAccess 2007

Trying to access the Report Manager URL Though newly added identity for Reportmanager

Posted: 24 Jun 2013 08:54 AM PDT

Hi ,Hi All,I've installed the SQl Server 2012 Reporting services and configured it to the default URL i.e (http://localhost:80/Reports) but when I hit advanced and try to add an other URL like (http://devreports.company.com:80/Reports) Iam able to add but not able to see any thing when i use the new URL that i just added. I first get prompted to log on, and then after several attempts I end up at a blank screen.We made the DNS name (devreports.company.com) resolve to servers IP address .Please through some light .Thanks,

SP producing an impossible error

Posted: 24 Jun 2013 08:08 AM PDT

ISSUE: Application is throwing UNIQUE KEY insert errors in PROD that [i]should[/i] be logically impossible from the stored procedure that is generating them. The error would be expected with the previous version of the proc, but proc is confirmed (via definiton in sys.sql_modules) as new code that should not produce this error.The proc is a fairly standard IF NOT EXISTS INSERT ELSE SELECT Existing matching row. Multiple people have reviewed that there should be no way to reach the INSERT if the record is a duplicate. FWIW, this proc was recently upgraded to this pattern from a basic INSERT statement with no checking for uniquenessThis morning the same test case would reliably cause the same error in DEV. I execute the SQL code as an ad-hoc query with identical parameters in DEV through SSMS (instead of the application) Oddly, this ran without out error, and [i]the failing test case suddenly started working in DEV, and has never failed since[/i].I attempted running the code ad hoc in Production. It did run, but did not cause the test case to start working.We've recompiled the SP, rebuilt all relevant Indexes and Statistics, with no success.We've confirmed that both procs are identical in definition in both environments. Application code is identical in both environments.I've got a request started to do a profiler trace in PROD to see exactly what is being run and exactly what the arguments are. However, it will take some time before it will be run due to the red-tape associated with production apps.Any suggestion on what might be causing a stored proc to execute as a previous version of itself rather than it's modern version? If so, how can I force the PROD version to refresh its cached version? Any likely alternate causes (other than it might be executing its previous version)?

Errors in SQL Server

Posted: 24 Jun 2013 02:52 AM PDT

Other than the SQL Server Logs, SQL Server Agent Logs, and Event Viewer; is there another place to view error information regarding SQL Server? My SR. DBA, is stating there is and wants me to figure it out on my own. Which I have been trying to.I have been Googling to see if there are system tables that contain error information (I am not finding any), searching for DMV's that may contain this information (not finding any), found the sp_readerrorlog/xp_readerrorlog but they tell me the same thing that I see when viewing the log via SSMS and googled RAISERROR to see how it works and where it writes to, etc....We are using a RAISERROR function to log errors, is there a certain log file that these are written to?Any and All help will be greatly appreciated...Thank You

Conversion failed...!!!

Posted: 24 Jun 2013 07:45 AM PDT

create table #temp(id int,strg varchar (5),[index] tinyint)insert into #tempvalues (1, 'abc', 1)declare @strg varchar (5)select @strg = case when ID = 1 and [INDEX] = 1 then strg else 0 endfrom #tempselect @strg Msg 245, Level 16, State 1, Line 12Conversion failed when converting the varchar value 'abc' to data type int.what is wrong here??Thanks..

How to insert result from cross-tab query into a table?

Posted: 24 Jun 2013 06:44 AM PDT

I used code below to create a cross-tab query. It works fine.How to insert result from cross-tab query into a table?SELECT *FROM ( SELECT year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice) as sPIVOT( SUM(Amount) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))AS pivot

Do I need a group by to get last value ??

Posted: 24 Jun 2013 05:35 AM PDT

HiI will butcher this question...I am selecting data where I assign a value to a field where the first character is a value for its position and the second the valueexample a is first and b is second, so field is 1A OR 2B call it BOXsome records have more than one value on the same day but I want the one if the first positionso I order by name, date, BOX descI want just the last value for the name and the dateI am try to put this in a matrix SSRS report so my end results would look sort of like. Date Date ....name A Aname2 B A .. etcThanksJoe

Time out error.

Posted: 24 Jun 2013 01:43 AM PDT

Hi, When a user tries to edit a table, he is getting a timeout error.Here is a brief details about the error."Executed SQL Stmnts: "the stmnts" from dbo.tablename,Error source: .Net SqlClient Data ProviderError Message: Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."Also, when he tries to do a select query, it prompts him to login the server\instance again, eventhough he is already logged in.Please share your knowledge, if you know about this error.Thanks,SueTons.

How to create Distribution Group in sql server 2008?

Posted: 24 Jun 2013 03:15 AM PDT

I need to create a Distribution Group in sql server 2008, i have 100 SQL Users who wants readonly Access on SOme Databases :w00t:, now i dont want to create individual logins for them.. so how to do this easily??Please provide detailed information ;-)

Find records that match two criteria

Posted: 24 Jun 2013 02:59 AM PDT

In the table Diagnosis I am trying to pull only those records that have left(Code,3)='491' AND Code of '305.1'Any thoughts?[code="sql"]create table Diagnosis(IDXMRN varchar(10),Problem varchar(100),Code varchar(6))insert into Diagnosis(IDXMRN, Problem, Code) values('00001768','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00003060','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00003172','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00003239','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00004700','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00006190','Chronic bronchitis with emphysema','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00006190','Chronic bronchitis with emphysema','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00006578','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00006578','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00008365','Obstructive chronic bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00008671','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00008671','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00008821','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00008821','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00009274','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00010684','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00010777','COPD (unspecified)','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00013245','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00014171','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00015281','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00015281','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00016275','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00016275','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00019049','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00019492','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00023687','Diffuse Obstructive Chronic Bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00027188','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00027188','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00029298','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00031647','Chronic Bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00033274','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00033274','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00038404','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00040058','Obstructive chronic bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00040058','Obstructive chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00040737','COPD (unspecified)','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00041056','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00043090','COPD with acute bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00045601','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00047657','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00048938','Obstructive chronic bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00049077','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00050611','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00050611','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00051378','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00051378','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00052112','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00052112','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00052676','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00052676','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00053093','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00053661','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00055080','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00056369','COPD (unspecified)','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00056595','COPD with acute bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00059502','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00061302','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00063289','COPD (unspecified)','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00063453','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00063453','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00064343','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00068729','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00069423','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00071895','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00072552','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('077171','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('077171','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00079358','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00079705','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00079705','Chronic Bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00079752','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00081150','COPD with acute bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Diffuse Obstructive Chronic Bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00085093','Diffuse Obstructive Chronic Bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00086068','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00090144','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00092788','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00092788','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00092798','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00092798','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00095573','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00095573','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00101205','Obstructive Chronic Bronchitis With Acute Exacerbation','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00101205','Obstructive Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00103728','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00103744','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00104403','COPD exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00105083','Obstructive chronic bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00105083','Obstructive chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00105868','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00107160','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00110001','Chronic bronchitis with emphysema','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00111896','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00113969','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00113969','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00114392','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00114392','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00114776','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00115426','Chronic Bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00115426','Chronic Bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00116270','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00117604','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00118102','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00118102','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00118335','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00118335','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00119326','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00119363','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00119363','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00119701','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('00119701','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00121040','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00121040','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00122601','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00500487','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','491.22')insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00504921','COPD with acute bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00529796','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00529796','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00562184','Obstructive chronic bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('00562184','Obstructive chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('00593674','Chronic bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('724341','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('724341','COPD exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('724341','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('724341','COPD exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('736385','Chronic bronchitis','491.9')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','491.21')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','491.2')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Chronic Bronchitis With Acute Exacerbation','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('788882','Diffuse Obstructive Chronic Bronchitis','493.2')insert into Diagnosis(IDXMRN, Problem, Code) values('724399','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('736399','Chronic bronchitis','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('788899','Chronic Bronchitis With Acute Exacerbation','305.1')insert into Diagnosis(IDXMRN, Problem, Code) values('788999','Diffuse Obstructive Chronic Bronchitis','305.1')[/code]

SQL 2008 64 bit, SSIS & Excel (ACE OLE DB driver)

Posted: 02 Jun 2010 10:17 AM PDT

Specs:[b]Windows Server 2003 R2 x64 SP2SQL Server 2008 10.0.2531 (64 bit)Visual Studio 2008 v9.0.30729.1 SP.NET Framework v 3.5 SP1[/b]I noticed that Microsoft finally came out with a 64 bit version of their ACE OLE DB driver for connection to Excel. I downloaded it from Microsoft downloads (Microsoft Access Database Engine 2010 Redistributable). I have several SSIS packages that need to output SQL data to Excel files (.xls & .xlsx). This works just fine in 32 bit mode using the 32 bit version of the download, but fails in 64 bit mode with the following error:[i]Error: 2010-06-02 19:12:44.11 Code: 0xC00F9304 Source: ExcelTest Connection manager "Excel 2007 Connection Manager" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2010-06-02 19:12:44.11 Code: 0xC020801C Source: Data Flow Task 2007 Excel 2007 Destination 1 [19] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel 2007 Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2010-06-02 19:12:44.11 Code: 0xC0047017 Source: Data Flow Task 2007 SSIS.Pipeline Description: component "Excel 2007 Destination 1" (19) failed validation and returned error code 0xC020801C. End Error[/i]So, essentially, it doesn't recognize the 64 bit drivers as being installed. What's also maddening is that you can't install the 32 bit and 64 bit drivers side-by-side, so any troubleshooting in BIDS is completely useless. Had anyone else run into this?

Need tree structure

Posted: 23 Jun 2013 10:52 PM PDT

Hi,Please find the following script.Basically I want the tree structure to find the parent templates.It should be 'N'number of output.But current i can get only two set of output.Before cursor one set of parent templates, after cursor second set of parent templates..Actual work for the below script: giving input, it finds all parent template(before cursor) and am sending the result to cursor-its giving another set of parent list.But again after cursor, what ever templates is displaying it can call from other templates.So the tree structure keep on goes. Please help me how can i do it dynamically or any other way to get the 'N'number of parent templates.I can't go another cursor, its really bad way.it should like 'tony>test1>test3>datatest>1>2>3>5>..................>>> .. keep on goes.IF OBJECT_ID('helper_template') IS NOT NULLBEGIN DROP PROC kbmhelper_templateENDGOCREATE PROCEDURE helper_template ( @input VARCHAR(100) ) AS BEGINSET NOCOUNT ONdeclare @table_name varchar(35)declare @field_name varchar(35)IF OBJECT_ID('tempdb..#Search_results') IS NOT NULLBEGIN DROP TABLE #Search_resultsENDcreate table #Search_results ( called_from varchar(35), template_display_name Varchar(200) default '', field VARCHAR(300), field_name VARCHAR(200), field_value VARCHAR(200), template_type varchar(200) default '' , con_template_field varchar(2000) default '' )CREATE NONCLUSTERED INDEX IX_NON_RESULTON #search_results(called_from) insert into #Search_results (called_from, field, field_name,field_value) select distinct template_name, tr.field, '' ,@input from test tr WITH(NOLOCK) inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id where tr.action in ('launch template', 'template pop-up') and tr.parm1 = @input and tr.parm1 not like ('{%') and tr.parm1 not like ('@%') UNION ALL select distinct t.template_name,tr.field, xr.field_name,xr.field_value from test tr WITH(NOLOCK) inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50)) inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id and tr.action = 'assign field value' and '{'+xr.field_name+'}' in ( select parm1 from test where action in ('launch template', 'template pop-up') and parm1 like '{%' and template_id = xr.template_id ) and xr.field_value = '"'+@input+'"' and xr.field_value not like '{%' and xr.field_value <> '""' UNION ALL select distinct t.template_name,tr.field,xr.field_name,xr.field_value from test tr WITH(NOLOCK) inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50)) inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id and tr.action = 'assign field value' and xr.field_name in ( select parm1 from test WITH(NOLOCK) where action in ('launch template', 'template pop-up') and parm1 like '@%' and template_id = xr.template_id ) and xr.field_value = '"'+@input+'"' and xr.field_value not like '{%' and xr.field_value <> '""' UNION ALL select distinct table_name,'',tf.field_name,@input from template_fields tf WITH(NOLOCK) where table_name not like '%[_]' and table_name <> '' and default_value <> '' and default_value not like 'summary%' and default_value not like '%Sheet%' and field_type <> 'spgrd' and default_value <> 'Family Unit' and field_type <> 'relation' and table_name not in ('date', 'data', 'data_in') and field_name = @input UNION ALL select distinct t.template_name,tr.field,tm.field_name,tm.field_value from test_1 tm join test tr WITH(NOLOCK) on tm.template_id = tr.template_id join templates t on tr.template_id = t.template_id where tr.action in ('launch template','template pop-up') and tm.field_name = Replace(REPLACE(tr.parm1,'{',''),'}','') and REPLACE(tm.field_value,'"','') = @input UPDATE st set st.template_type = t.template_type, st.template_display_name = t.template_display_nameFROM #Search_results STJOIN templates t on st.called_from = t.template_name UPDATE sr set con_template_field = @input +' > '+ called_from +' (Field: '+sr.field+') (Template Type: '+sr.template_type+')'FROM #Search_results sr --select distinct called_from,template_display_name,field, field_name,field_value,template_type ,con_template_field--from #Search_results WITH(NOLOCK) --order by called_from IF OBJECT_ID('tempdb..#final_results') IS NOT NULLBEGIN DROP TABLE #final_resultsENDCREATE TABLE #Final_results ( parent_name Varchar(100), parent_field Varchar(300), parent_type VARCHAR(100), child_name VARCHAR(100), child_field VARCHAR(200), child_flow VARCHAR(500), parent_flow VARCHAR(500) ) CREATE NONCLUSTERED INDEX IX_NON_FINALON #final_results(parent_name) DECLARE @template_name VARCHAR(100)DECLARE @child_field VARCHAR(200)DECLARE Cur_results CURSOR FORSELECT called_from, fieldFROM #Search_results ORDER BY called_fromOPEN Cur_resultsFETCH NEXT FROM Cur_results INTO @template_name, @child_fieldWHILE @@FETCH_STATUS = 0BEGIN insert into #Final_results (parent_name) select distinct template_name from test tr WITH(NOLOCK) inner join templates t on tr.template_id = t.template_id where tr.action in ('launch template', 'template pop-up') and tr.parm1 = @template_name and tr.parm1 not like ('{%') and tr.parm1 not like ('@%') UNION ALL select distinct t.template_name from test tr WITH(NOLOCK) inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50)) inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id and tr.action = 'assign field value' and '{'+xr.field_name+'}' in ( select parm1 from test where action in ('launch template', 'template pop-up') --and parm1 like '{%' AND PATINDEX('{%',parm1)>0 and template_id = xr.template_id ) and xr.field_value = '"'+@template_name+'"' and xr.field_value not like '{%' and xr.field_value <> '""' UNION ALL select distinct t.template_name from test tr WITH(NOLOCK) inner join test_1 xr WITH(NOLOCK) on tr.parm2 = cast(xr.trig_id as varchar(50)) inner join templates t WITH(NOLOCK) on tr.template_id = t.template_id and tr.action = 'assign field value' and xr.field_name in ( select parm1 from test WITH(NOLOCK) where action in ('launch template', 'template pop-up') --and parm1 like '@%' and PATINDEX('@%',parm1)>0 and template_id = xr.template_id ) and xr.field_value = '"'+@template_name+'"' and xr.field_value not like '{%' and xr.field_value <> '""' UNION ALL select distinct table_name from template_fields tf WITH(NOLOCK) where table_name not like '%[_]' and table_name <> '' and default_value <> '' and default_value not like 'summary%' and default_value not like '%Sheet%' and field_type <> 'spgrd' and default_value <> 'Family Unit' and field_type <> 'relation' and table_name not in ('person', 'patient', 'person_encounter') and field_name = @template_name UNION ALL select distinct t.template_name--,tr.field,tm.field_name,tm.field_value from test_1 tm WITH(NOLOCK) join test tr WITH(NOLOCK) on tm.template_id = tr.template_id join templates t on tr.template_id = t.template_id where tr.action in ('launch template','template pop-up') and tm.field_name = Replace(REPLACE(tr.parm1,'{',''),'}','') and REPLACE(field_value,'"','') = @template_name UPDATE FS SET FS.parent_field = TR.field, FS.parent_type = T.template_type, FS.child_name = @template_name from #Final_results FS join templates t on FS.parent_name = t.template_name join test tr on t.template_id = tr.template_id where tr.parm1 = @template_name FETCH NEXT FROM Cur_results INTO @template_name, @child_fieldENDCLOSE Cur_resultsDEALLOCATE Cur_resultsSELECT DISTINCT parent_name, parent_field, parent_type, t.template_display_name as 'Parent_display_name', child_name FROM #Final_results fr WITH(NOLOCK)JOIN templates t on fr.parent_name = t.template_nameWHERE parent_field is not nullAND parent_name not in (select distinct called_From from #Search_results WITH(NOLOCK))--@input +' > '+ called_from +' (Field: '+sr.field+') (Field Value: '+sr.field_value+') (Template Type: '+sr.template_type+')'select distinct Case when sr.called_from = fr.child_name then sr.con_template_field +' > '+ fr.parent_name +' (Field: '+fr.parent_field+') (Template Type: '+fr.parent_type+')' else sr.con_template_field end AS 'Template Workflow'from #Search_results srleft join #Final_results fron sr.called_from = fr.child_nameorder by Case when sr.called_from = fr.child_name then sr.con_template_field +' > '+ fr.parent_name +' (Field: '+fr.parent_field+') (Template Type: '+fr.parent_type+')' else sr.con_template_field endSET NOCOUNT OFFendThanks,Tony

Rebuild index maintenance plan

Posted: 23 Jun 2013 10:42 PM PDT

A bit of a under the hood question as it where.When using the maintenance task to rebuild indexes and specifying ALL does the process ignore indexes if the fragmentation is low, below 10% for example or if the pages are < 1000?Many thanks

Query to search for a substring in xml

Posted: 24 Jun 2013 12:29 AM PDT

I use below query to search for a substring in whole xml(including node name and node value)[code="sql"]SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) [/code]I want an alternative query which has good performance than this. So please suggest some.

Restoring failed

Posted: 23 Jun 2013 08:19 PM PDT

Hi all, When i am trying to restore a database with the backup i am getting this error06/24/2013 14:30:54,spid59,Unknown,The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IPE_OCT_120_log'.06/24/2013 14:30:54,spid59,Unknown,Error: 3634<c/> Severity: 16<c/> State: 1.i am unable to figure it out.

Index Rebuild Failed - Need More Information

Posted: 24 Jun 2013 12:24 AM PDT

I have an Index Rebuild that has been failing. It fails on the same table/index everytime. Looking at this history of the index job, I see entries like this, "Rebuild - [PK_CCX] [SQLSTATE 01000] (Message 0)" and then the very last entry is, "Rebuild - [PK_CCW_TOX] [SQLSTAT... The step failed." I am needing to find more information on this failure. I have checked the SQL Agent Error Logs and there is not a whole lot there, as in maybe 25 entries total. I then try the SQL Server Error Logs and search for the index and nothing comes back.I have queried the table to see if there is some data out of place for these two columns that make up the PK and everything seems correct. To add to this, the table is not very big (almost makes me wonder why it needs to be rebuild, but I can worry about that once I get this corrected).Can anyone point me in the right direction on where I can look to get more information on why this Index Rebuild is failing?Any and all advice on this subject will be greatly appreciated...Thank You.

How to get user position in table i.e 1st 2nd, 3rd

Posted: 19 Jun 2013 07:09 PM PDT

hello,Im having a bit of trouble trying to return the current users position, iv been looking at this for the past 3 days browsed many forums tried many situations but with still no luck.I have a table called prospectlead which has a column called ReviewedBy this gets populated when the user checks a record etcthe desired output would be 1st John2nd Me3rd Sarah This will obviously change throughout the day depending on how the individuals get on, so mid morning i could go from 2nd position down to 10th but i need to return my position plus the person above me and the person below me, the way im getting the amount of records checked is by using a COUNT, the parameter passed in to the stored procedure is the ShortAbbr which would be 'D13' (again thats hard coded to get it working, it would actaully be @ShortAbbr passed in from the front end)Can any one help me on this please?[code="sql"]Select ROW_NUMBER() over(order by u.UserID) as RowNumber, count(p.ID) as TotalCount, u.Firstname + ' ' + u.Surname as DataCheckerfrom ProspectLead p join UserAccount u on p.reviewedby = u.ShortAbbrwhere p.ReviewedBy is not null and convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())and u.Responsibility = 16and u.ShortAbbr = 'D13'group by u.Firstname, u.Surname, u.UserID order by TotalCount desc[/code]

Script Out Database Mail Settings?

Posted: 08 Sep 2010 06:30 AM PDT

Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings?I set up a profile to use gMail, and it seems logical for me to export out the settings to a script, then run the script on my laptop, other servers, etc.There's no built in option, so I figured i'd ping the forum before i do it myself.by the way, my google-fu [i]is strong[/i], and there are example scripts where you fill in the blanks, examples how to set up dbmail, but i did not find anything that scripts out existing settings.[img]http://www.stormrage.com/SQLStuff/mail_gmail.gif[/img]

MDX Query

Posted: 23 Jun 2013 10:27 PM PDT

Hi all,does anyone know who to solve this with MDX? I'm trying to get the average sale form each Saturday in year 2005 and in the end the average from ALL Saturdays in 2005.This is how it's supposed to look likeOrderDate AverageSale01.01.2005 1857,1208.01.2005 1754,2510.01.2005 1539,86... ...... ...17.12.2005 1754,5724.12.2005 2076,1630.12.2005 1829,79ALL 1947,64This is a try with some lacking expressions in comments in my logic :WITHMEMBER MEARSURES.AverageSales AS/*function for: average of (MEASURES.ProductPrice * MEASURES.OrderQuantity) */SELECT {OrderDate, MEASURES.AverageSales}ON COLUMNSDESCENDANTS(TimeByDay.Calendar.[2005],TimeByDay.Calendar.TDay[/*all Dates of Saturdays in 2005*/] SELF)ON ROWSFROM SalesCubeThanks for any advice!!anna

Is this behavior is by design

Posted: 23 Jun 2013 07:42 PM PDT

while in transaction I am creating a global temporary table and then Insert some data into itlater in the transaction i am using a xp_cmdshell to bcp out the table I am getting a lock dbcc inputbuffer : set fmtonly on select * from mytable set fmtonly offI tried to bcp out the table using a query and I got a lock also I resolved the problem by using a regular table .why this happend ?

DBCC CHECKDB performance

Posted: 03 Sep 2012 04:47 PM PDT

I have a server and two instance SQL SERVER 2008 R2 SP2and SQL SERVER 2012 and firstly ı try DBCC CHECKDB in SQL SERVER2008 R2 2 times and it takes 5 hours and 30 minutes after I deattched db and ı atched db to SQL SERVER 2012 and ı try DBCC CHECKDB in SQL SERVER 2012 and it takes 2 hours and 28 minutes what is the new feature of SQL SERVER 2012

regarding restore problem

Posted: 20 Jun 2013 02:38 AM PDT

hie frnds,i had a database let me called 'job' and having 5 tables that database is in production server.now when i took backup and restored in development server 'job' database had only four tables .so what should i do now?please help me...

Disk format as 64KB

Posted: 23 Jun 2013 09:16 PM PDT

I had format the disk to 64KB and perform a test case using CrystalDiskMark. The result is under expectation. Why i cant get the 20% performance improvement if compare to the default disk format 4096 bytes?
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog