Friday, September 20, 2013

[SQL 2012] Build datawarehouse from scratch

[SQL 2012] Build datawarehouse from scratch

Build datawarehouse from scratch

Posted: 19 Sep 2013 11:47 PM PDT

Hi friends, I have couple of questions.I have experience working in datawarehouse projects but I am not sure how do you build a datawarehouse from scratch.What factors need to consider?What tools are used? (in Sql server)How to decide whether a dimension table or fact table?Please reply.thanksRaj

Failure Restoring Differential Backup

Posted: 20 Sep 2013 02:59 AM PDT

I have an issue with my SQL 2012 database server and restoring a full backup (norecovery) then applying a differential backup. The error message is that there is a break in the LSN chain. The First LSN of the full backup is the same as the Full LSN of the differential backup so I'm confident there is not a break in the backup chain. I am restoring a Production Backup to our DEV Server, so the issue is not currently critical, but I don't feel confident that I could recover to a point in time if I had a Production failure.Does anyone have knowledge of a Hot Fix or work around?

SQL Server 2008 r2 Standard Edition vs SQL Server 2012 Standard Edition

Posted: 20 Sep 2013 02:25 AM PDT

Hi Guys,I need to find out the difference between SQL Server 2008 r2 STD and SQL Server 2012 STD in terms of features, admistration tools, BI tools etc.As we all know Enterprise has all the features that most people would like, but, is there any major differences between the two ? I have been on the MS SQL website and found most new features are not on the STD edition.Any help would be really appreciated

create new login with powershell

Posted: 19 Sep 2013 10:43 PM PDT

I need to create create new login ( domain user ) with powershell?I used this script:[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null$Instance="*****machine name******"$LoginName="***Domain User********"$Password="*****password*****"$DBName="master"$Server=New-Object ("Microsoft.SqlServer.Management.SMO.Server") $instance$Login=$Server.Logins.Item($LoginName)$Login=New-Object ("Microsoft.SqlServer.Management.SMO.Login") ($Server, $LoginName) $Login.LoginType=[Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin$Login.DefaultDatabase=$DBName $Login.Create($Password)but script failed ERROR:New-Object : Exception calling ".ctor" with "2" argument(s): "SetParent failed for Login 'user name'. "At line:1 char:18+ $Login=New-Object <<<< ("Microsoft.SqlServer.Management.SMO.Login") ($Server, $LoginName) + CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

New Error Message - GetPageForRemoteCopy

Posted: 19 Sep 2013 10:18 PM PDT

Here's a new error message, with no results at all from Google.[quote]GetPageForRemoteCopy (db [17], Page [4:6170080] - IO Error -4 - Sending empty page[/quote][quote]GetPageForRemoteCopy (db [31], Page [4:6170080] - IO Error -4 - Sending empty page[/quote]I am running AlwaysOn and I have this message on the publisher and the subscriber. I examine sys.dm_hadr_auto_page_repair and find the following on BOTH sides.4:6170080 Bad Checksum Request sent to partner.Sounds like Automatic Page Repair is playing tennis. What a great way to spend a Friday.

SQL Server 2005 to 2012 XML Conversion

Posted: 19 Sep 2013 10:12 PM PDT

Dear All,We are about to migrate from SQL 2005 to SQL 2012, however we have an issue with XML basically that it is in UTF-8 format rather than UTF-16. I would like to ask what strategy people have come up with to solve this problem.I'm sort of hoping for a Magic Wand, however I doubt I will get one :D

Upgrade One File?

Posted: 19 Sep 2013 08:16 AM PDT

Is it possible to upgrade just one SSIS package? Basically I've got a 2005 SSIS package and I'd like to upgrade it to 2012 Data Tools but the only method I see described is the Upgrade Wizard that does everything at once.How can I just upgrade one SSIS package at a time?

SSRS 3.0 Parameters input issue

Posted: 18 Aug 2013 09:32 PM PDT

Hi all,I am building a report that will make a comparison with figures for the last 3 months, and last 12 months. For this I will build 3 datasets with the exact same structure, with the difference that I need each one of them the data will be different.The way I am trying to do it is to go to a specific dataset and on the parameters tab choose the parameter and do as example below:=Parameters!Period.Value-3 The parameter above would be for the dataset that needs to show the figures for the last quarter, the period field is an integer and every month as a number attached, so I figured that if for this specific dataset in the parameters tab there is an instruction for the parameter in the expression it should work... It doesn't, anyone has a suggestion for this?Best regards,Daniel

Join Domain after the fact

Posted: 19 Sep 2013 08:59 AM PDT

I'm considering building a Windows Server 2008 R2 Domain with VMs for my home lab for my own education & exam preparation. I have a Windows 2008 R2 VM with SQL 2012 in Workgroup mode I've been using for about a year. Can I join this VM to a new domain and have full functionality with domain security & other SQL VMs or does SQL 2012 need to be installed AFTER the server joins a Domain?

working policy with constraint.

Posted: 19 Sep 2013 05:51 AM PDT

Has anyone used policy to check the default names of the constraints?

Exporting a report to excel

Posted: 19 Sep 2013 04:21 AM PDT

Hi,When I export report to an excel sheet all the lines excel become plain except the report part. All the ther part of the workseet is like a white blank page except the report part.How can I make excel file to be the same format after the export is done?Thanks.
Posted by at No comments:

[T-SQL] Out of memory error

[T-SQL] Out of memory error

Out of memory error

Posted: 20 Sep 2013 01:01 AM PDT

I have a procedure, in which returns many results in the grid(over 1 million) and when I run it I get the out of memory error. I tried running the script with the "discard results after execution" box checked and not only was I able to process many more records, but the execution time was cut down from about an hour to 5 minutes. The checkbox I was referring to is located under Tools > Options > Query Results > SQL Server > Results to Grid > Discard results after execution. Is there an equivalent T-SQL command that can perform the same configuration? Having this box checked still processes the script, but doesn't produce any results in the grid. The script now runs so much faster and memory isn't getting used up by the massive amounts of rows that were previously being returned. Since this is will be run in production, i would like to include this option in the procedure/ script instead of selecting it from GUI.

Value updated in a cursor

Posted: 19 Sep 2013 11:56 PM PDT

One of my Transfer Document SSRS report, consists of a table report item showing 3 columns, Item, ItemFrom , Itemcontent.If it displays, 'n 'records, for the first record, ItemFrom column shows a correct value and rest of the records showing incorrect value. I went through the SP of this. It consists of cursors for updating and uses several variables. I set the variables to blank after declaring to show the correct value.Any other idea is highly appreciated.In the SP, the part is shown below:DECLARE @containerCode NVARCHAR(MAX)DECLARE @containerKey NVARCHAR(MAX)DECLARE @storingEventModifiedOn DATETIMEDECLARE @containerFrom NVARCHAR(MAX) SET @containerCode = '' SET @containerKey = '' SET @containerFrom = '' OPEN @containersFromStoringEventsCursorFETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFromWHILE(@@FETCH_STATUS = 0)BEGIN DECLARE @filteredSpecimens NVARCHAR(MAX) EXEC [dbo].[eusp_e5_eSM_SE_ChainOfCustody002_SubReport_GetFilteredSpecimensInContainer] @storingEventModifiedOn, @containerKey, @studyIdentifier, @destinationKey, @filteredSpecimens OUTPUT -- We must not report containers that contain no moved specimen. IF @filteredSpecimens != '' BEGIN INSERT INTO @Results SELECT item = @containerCode, itemFrom = @containerFrom, itemContent = @filteredSpecimens ORDER BY item ASC END FETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFromENDCLOSE @containersFromStoringEventsCursorSELECT * FROM @Results GROUP BY itemFrom,item,itemContent

Number Of Columns Per Table

Posted: 20 Sep 2013 12:39 AM PDT

Happy Friday EveryoneI am playing around with some code, and trying to learn some things. If you execute this query against any database, it will return the Rows Counts per table, and the Number of columns in said table. I am trying to code this to return only the MAX number, or the highest number of the Number Of Columns.[code="sql"]SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS TableName, i.rows, c.column_id AS NumberOfColumnsFROM sys.tables t JOIN sys.sysindexes i ON t.object_id = i.id AND i.indid < 2JOINsys.columns cONt.object_id = c.object_id ORDER BY i.rows ASC[/code]Results for only one of the tables,[quote]TableName rows NumberOfColumnsq_tx_adh_stat 0 1q_tx_adh_stat 0 2q_tx_adh_stat 0 3q_tx_adh_stat 0 4q_tx_adh_stat 0 5q_tx_adh_stat 0 6q_tx_adh_stat 0 7q_tx_adh_stat 0 8q_tx_adh_stat 0 9q_tx_adh_stat 0 10[/quote]I am trying to write the query that will return only this:[quote]TableName rows NumberOfColumnsq_tx_adh_stat 0 10[/quote]I have tried a few different things, like using a CTE, using a HAVING clause, MAX() and some other ways, but I am obviously doing something incorrectly. I would like the MAX number of columns listed for each table that the query returns.Thank you in advance for all your assistance, comments and suggestionsAndrew SQLDBA

help to get the output with the help of trigger

Posted: 19 Sep 2013 11:18 PM PDT

i have a table with records create table rstbljobs(AutoID identity(1,1),AssignedToRstEmpID int,priorityid int,Jobstatusid int)Insert into rstbljobs values ( 147,202,374)Insert into rstbljobs values ( 169,214,374)Insert into rstbljobs values ( 170,202,374)Insert into rstbljobs values ( 176,202,374)Insert into rstbljobs values ( 182,202,374)Insert into rstbljobs values ( 147,214,374)Insert into rstbljobs values ( 183,202,374)Insert into rstbljobs values ( 170,214,374)Insert into rstbljobs values ( 182,214,374)Insert into rstbljobs values ( 170,202,374)Insert into rstbljobs values ( 147,214,374)Insert into rstbljobs values ( 147,202,374)Insert into rstbljobs values ( 169,202,374)Insert into rstbljobs values ( 169,214,374)i have another table UsersRSDeskCreate table UsersRSDesk(userid int,IsRecruit bit,status bit,noofhighprtjobs varchar(50))The records are insert into UsersRSDesk values (147,1,0,null)insert into UsersRSDesk values (169,1,0,null)insert into UsersRSDesk values (170,1,0,null)insert into UsersRSDesk values (176,1,0,null)insert into UsersRSDesk values (182,1,0,null)insert into UsersRSDesk values (183,1,0,null)The query is as follows:select AssignedToRstEmpID,COUNT(AssignedToRstEmpID)as Noofcount from RStblJobs group by AssignedToRstEmpIDfrom the above query i want the noofcount from rstbljobs to be inserted into usersrsdesk.noofhighprtjobs column.Whenever i update rstbljobs table then also the same count should be reflected here.

Rapid Growth Of Data and Purging Issues

Posted: 19 Sep 2013 06:56 PM PDT

Hi,I tried a few different things which though work well but needs atleast few minutes of downtime so I am coming to my favorite place to seek opinion.We have a gaming website which is open 24*7. The data in the tables grow rapidly. The problem is that when I try to delete the data, it does work and in 15 minutes or so, I can delete the data accumulated for over 24 hours but this does hamper transactions and because delete locks down the table, this process although working well does not suit my current requirements. A senior developer tried deleting row by row so that only rowlocks are taken but that is not purging data fast.If somebody has worked in such an environment before, please give your suggestions and inputs on how to take this further.ThanksChandan Jha

Better way to write this query

Posted: 19 Sep 2013 12:31 PM PDT

I have inherited this query that is comparing data from one table to another. It is using a combination of columns to "build" a unique column that gets inserted into the other table should it not exist. The query is below [code="sql"]INSERT INTO ReportRecipientsSELECT rs.[ReportID] ,rs.[ATID] ,rs.[AID] ,rs.[RecipientId] ,rs.[Source] ,rs.[Frequency] ,rs.[DeliveryMethod] ,'NEW RECORD' ,CONVERT(VARCHAR(10),rs.[ATID]) + CONVERT(VARCHAR(10),rs.[AID]) + CONVERT(VARCHAR(10),rs.[ReportID]) + CONVERT(VARCHAR(10),rs.[Frequency]) + CONVERT(VARCHAR(10),rs.[RecipientId]) + CONVERT(VARCHAR(10),rs.[DeliveryMethod]) ,rs.[scheduleid] ,rs.FeeAmount ,rs.FeeTranCode ,0FROM [dbo].[ReportSchedules] rs WHERE CONVERT(VARCHAR(10),rs.[ATID]) + CONVERT(VARCHAR(10),[AID]) + CONVERT(VARCHAR(10),rs.[ReportID]) + CONVERT(VARCHAR(10),rs.[Frequency]) + CONVERT(VARCHAR(10),rs.[RecipientId]) + CONVERT(VARCHAR(10),rs.[DeliveryMethod]) NOT IN (SELECT DISTINCT [id] from dbo.ReportRecipients)[/code]I realize the WHERE clause poses an issue due to the non-sargable concatenated columns. The problem with this query is that in both tables, there are millions of rows. In it's current state it takes about 2 minutes to run, generating 4 million reads.The "CONVERT(VARCHAR(10),[ATID]) + CONVERT(VARCHAR(10),[AID]) + CONVERT(VARCHAR(10),[ReportID]) + CONVERT(VARCHAR(10),[Frequency]) + CONVERT(VARCHAR(10),a.[RecipientId]) + CONVERT(VARCHAR(10),a.[DeliveryMethod])" business creates the "ID" column in the other table...which when new rows are added into the main table, it uses the combination of the CONVERTS to match against the "ID" column to determine if it's a new record or not.Here's some sample data and DDL:[code="sql"]/* Create Main source table */CREATE TABLE [dbo].[ReportSchedules]( [ScheduleId] [int] NOT NULL, [ReportID] [int] NULL, [ATID] [int] NULL, [AID] [int] NULL, [RecipientId] [int] NULL, [Source] [varchar](5) NULL, [Frequency] [tinyint] NULL, [DeliveryMethod] [tinyint] NULL, [FaxTime] [varchar](50) NULL, [FeeAmount] [money] NULL, [FeeTranCode] [char](5) NULL, CONSTRAINT [csPk_ReportSchedules] PRIMARY KEY CLUSTERED ( [ScheduleId] ASC ) ON [PRIMARY]) ON [PRIMARY]GO/* Populate Main source table with some data */INSERT INTO ReportSchedulesSELECT 1,34,51,7301,7481,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 2,34,51,7271,7451,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 3,24,51,7302,7482,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 4,24,51,7273,7453,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 5,24,51,9905,10395,'CI',2,1,'Not Specified',NULL,NULL/* Create table to compare against source table */CREATE TABLE [dbo].[ReportRecipients]( [ScheduleID] [bigint] IDENTITY(1,1) NOT NULL, [ReportID] [int] NOT NULL, [ATID] [int] NOT NULL, [AID] [int] NOT NULL, [RecipientId] [int] NOT NULL, [Source] [varchar](5) NOT NULL, [Frequency] [tinyint] NOT NULL, [DeliveryMethod] [tinyint] NOT NULL, [FaxTime] [varchar](40) NULL, [ID] [varchar](60) NOT NULL, [CCScheduleID] [varchar](10) NULL, [FeeAmount] [money] NULL) ON [PRIMARY]ALTER TABLE [dbo].[ReportRecipients] ADD [FeeTranCode] [char](6) NULLALTER TABLE [dbo].[ReportRecipients] ADD [FeeFired] [bit] NULLPRIMARY KEY CLUSTERED ( [ScheduleID] ASC) ON [PRIMARY]GO/* Insert sample data */INSERT INTO ReportRecipients SELECT 34,51,7301,7481,'CI',2,1,'Not Specified',51730134274811,6229,NULL,NULL,0 UNION ALL SELECT 34,51,7271,7451,'CI',2,1,'Not Specified',51727134274511,6231,NULL,NULL,0 UNION ALL SELECT 24,51,7302,7482,'CI',2,1,'Not Specified',51730224274821,6234,NULL,NULL,0 UNION ALL SELECT 24,51,7273,7453,'CI',2,1,'Not Specified',51727324274531,6235,NULL,NULL,0 [/code]This will set up the example so there is a new record to insert into the ReportRecipients table when the first query (the first SQL above) is run

Please Please help on this Trigger

Posted: 19 Sep 2013 06:05 AM PDT

In my organization they create the database through the application and I want to create the trigger to fire the backup whenever the new database are created. I have created the following script. It's creating the backup when I manually create the database, but the trigger is not firing when the database is created by application. Do I need to change the script or anything?CREATE TRIGGER TRG_BackupNewDatabase ON ALL SERVER FOR CREATE_DATABASE AS declare @database sysname, @event_data XML = EVENTDATA(), @folder nvarchar(4000), @file nvarchar(4000) SET @database = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname') set @folder = 'X:\Backups\' + @database set @file = @folder + '\' + @database + '.bak' if exists (select * from sys.databases where name = @database and source_database_id is null) BEGIN EXEC master.dbo.xp_create_subdir @folder COMMIT BACKUP DATABASE @database to disk=@file raiserror( 'You can ignore the error message which says that the transaction ended within the trigger.', 16,1) END GO

T SQL advice sp_MSforeachdb

Posted: 19 Sep 2013 07:12 AM PDT

Hello everyone.Can anyone please offer me some advice on this query I am trying to put together?I want the query to be run on each database on an instance.I am stuck on getting it to output database name.EXEC sp_MSforeachdb 'USE ? SELECT ''?'','Count' = COUNT(*), 'Type' = CASE type WHEN 'C' THEN 'CHECK constraints' WHEN 'D' THEN 'Default or DEFAULT constraints' WHEN 'F' THEN 'FOREIGN KEY constraints' WHEN 'FN' THEN 'Scalar functions' WHEN 'IF' THEN 'Inlined table-functions' WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints' WHEN 'L' THEN 'Logs' WHEN 'P' THEN 'Stored procedures' WHEN 'R' THEN 'Rules' WHEN 'RF' THEN 'Replication filter stored procedures' WHEN 'S' THEN 'System tables' WHEN 'TF' THEN 'Table functions' WHEN 'TR' THEN 'Triggers' WHEN 'U' THEN 'User tables' WHEN 'V' THEN 'Views' WHEN 'X' THEN 'Extended stored procedures' END, GETDATE()FROM sysobjectsGROUP BY typeORDER BY typeGOmany thanks for any advice offered.

Send Alert when no log backup completed in 24 hours

Posted: 04 Dec 2012 05:09 AM PST

Dear Forum, I'm sure someone has completed this type of request, but here is the back ground. I have a thrid party tool to complete all forms of backups on SQL databases. I have recently found that from time to time this 3rd party backup tool will drop a backup schdule. For example I have a instance where Transaction Log backups have not occured in months. So now I must Mirco manage the backups across some 600 instances. My questions does any have the code or an example on how send or create an alert if a database has not had a log backup with in the last 24 hours?All Enviroments are either 2K8 or 2K8R2.Any help or direction would be gladly execpted.Fishing for help..:hehe:

add bold in for xml type

Posted: 15 Nov 2011 01:31 AM PST

Hello,I have a query that returns in xml format the contents of a table and I want to bold lines where run_status = 0the initial query is :[code="sql"]set @tableHTML = N'<table border="1">' + N'<tr><th></th>' + N'<th>srvname</th>' + N'<th>jobname</th>' + N'<th>date</th>' + N'<th>duration</th>' + N'<th>status</th></tr>' + CAST(( select td = t.num, '', td = t.srvname, '', td = t.jobname, '', td = t.run_datetime, '', td = t.run_duration, '', td = t.run_status from @t t where t.run_datetime > dateadd(dd, -1, GETDATE()) order by t.run_status, t.srvname FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>';[/code]so I wrote[code="sql"]select td = '<B>' + convert(varchar(3), t.num) + '</B>', '', td = '<B>' + t.srvname + '</B>', '', td = '<B>' + t.jobname + '</B>', '', td = '<B>' + t.run_datetime + '</B>', '', td = '<B>' + t.run_duration + '</B>', '', td = '<B>' + convert(varchar(3), t.run_status) + '</B>'from @t twhere t.run_datetime > dateadd(dd, -1, GETDATE()) and t.run_status = 0order by t.run_status, t.srvname[/code]but <B> is replaced by "& l t ; B & g t ;" (without spaces : same here I cant write it!!! :-D)So how can I doThank you for your help.

calculating a median for groups

Posted: 14 Nov 2011 04:49 AM PST

I have the following table that I need to get a median for one of the columns mytableState City NumberCA LA 20CA LA 25CA LA 15CA SD 35CA SD 12CA SD 17NY NYC 20NY NYC 22NY NYC 8NY NYC 36NY NYC 15What I want is to create a new table that has the median for thecolumn Number like this:State City MedianCA LA 20CA SD 17NY NYC 20Any help would greatly be appreciated.

WAITFOR DELAY - Shortest Delay Possible?

Posted: 19 Sep 2013 01:48 AM PDT

This is baffling me!I have a proc that will call another proc to clear a record (if one exists) before adding a new one. Both procs work fine and the table ends up correct. However, I've noticed that the proc that clears the record logs that it executed at exactly the sime timeas the new record. This means that I can't see the correct order in my log table. Obviously, as the clearing proc runs BEFORE the proc that adds the new record, it should write it's log record first. The clearing proc runs at least three statements before the second one.No problem I thought, I'll just drop a WAITFOR DELAY '00:00:00.001' after the clearing proc so that they are at least a millisecond apart and that will fix it.Trouble is, it doesn't. The still come in at exactly the same time. If I make the delay a second, it works, but that's crap.I tried:select GETDATE()waitfor delay '00:00:00.001'select GETDATE()And I seem to get some spurious results for anything less than 10 miliseconds.Any ideas? Have I found a bug? Do I win a prize?
Posted by at No comments:

[SQL Server 2008 issues] Test if query running against multiple servers

[SQL Server 2008 issues] Test if query running against multiple servers

Test if query running against multiple servers

Posted: 19 Sep 2013 06:54 PM PDT

Hello,To start out I'll make the statement that I don't think this is possible, but...Does anyone know if a T-SQL statement can test to see if it is running against multiple servers via the Registered Servers feature? As I say, I don't think it can, but I just want to check. My reason for thinking that it isn't possible is that I believe the Registered Server feature simply runs the same script in a loop against a collection of instances. Is this correct?My reason for asking is I have a script to check properties on an instance and need to run it against my Registered Servers group for it to obtain meaningful results. I would like to alert the user of the script if they are not running it against a Registered Server group so that they know not to trust the results.

Tooltip On Image not getting displayed on FireFox & Chrome

Posted: 19 Sep 2013 01:59 PM PDT

I am using SSRS 2008 and have a requirement to show the tooltip on Image. Tooltip is coming fine on IE but not getting displayed on any other Browsers Viz. FireFox , Safari & Chrome. Tooltip on Text is coming on all browsers . This problem seems to be specific with Image only. Please let me know if this is known issue or is there any workaround to get it working ? Any help will be highly appreciated.

Uninstall SQL Server 2008 R2 but KEEP Native Client

Posted: 19 Sep 2013 01:49 AM PDT

hello all,I want to remove (thru Add/Remove Programs) SQL Server 2008 R2 - but KEEP the 2008 R2 Native Client.Does the Native Client automatically get uninstalled along with the database engine and agent?Thanks

best way to approach this query

Posted: 19 Sep 2013 10:10 AM PDT

Hi all,Looking for the best way to approach this with SQL.In this test (dml/ddl below), I am looking to retrieve only rows that meet these conditions:where a transaction number exists more than once and where at least one existence has CancelDueToCorrection ='Y',any row that may not exist more than once - but when only once then those where CancelDueToCorrection ='Y' need to be omitted.In this example, the query would return all rows except where TransactionNumber is 3.TransactionNumber CancelDueToCorrection CancelDueToMatch1 N NULL1 Y NULL2 N NULL3 Y NULL4 N NULL5 N NULL5 N NULLThank you! Sscript to create test scenario:create table test(TransactionNumber int, CancelDueToCorrection char(1))insert into test (TransactionNumber, CancelDueToCorrection)values (1,'N')insert into test (TransactionNumber, CancelDueToCorrection)values (1,'Y')insert into test (TransactionNumber, CancelDueToCorrection)values (2,'N')insert into test (TransactionNumber, CancelDueToCorrection)values (3,'Y')insert into test (TransactionNumber, CancelDueToCorrection)values (4,'N')insert into test (TransactionNumber, CancelDueToCorrection)values (5,'N')insert into test (TransactionNumber, CancelDueToCorrection)values (5,'N')

Error In Trigger : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression

Posted: 19 Sep 2013 09:00 AM PDT

Hi,I have this trigger on table INVENTSUM that insert multiple rows in table ESHOPINVENTSUM.But I get error "SQL error description: [Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "Trigger in table InventSum is the followingCREATE TRIGGER [dbo].[TR_INVETSUM] ON [dbo].[INVENTSUM]AFTER INSERT,UPDATE ASIF UPDATE(AVAILPHYSICAL)BEGIN insert into ESHOPINVENTSUM (ITEMID,AVAILPHYSICAL) select I.ITEMID,SUM(I.AVAILPHYSICAL) from Inserted i GROUP BY I.ITEMIDENDTables are CREATE TABLE [dbo].[INVENTSUM]( [ITEMID] [nvarchar](25) NOT NULL, [POSTEDQTY] [numeric](28, 12) NOT NULL, [POSTEDVALUE] [numeric](28, 12) NOT NULL, [DEDUCTED] [numeric](28, 12) NOT NULL, [RECEIVED] [numeric](28, 12) NOT NULL, [RESERVPHYSICAL] [numeric](28, 12) NOT NULL, [RESERVORDERED] [numeric](28, 12) NOT NULL, [ONORDER] [numeric](28, 12) NOT NULL, [ORDERED] [numeric](28, 12) NOT NULL, [QUOTATIONISSUE] [numeric](28, 12) NOT NULL, [QUOTATIONRECEIPT] [numeric](28, 12) NOT NULL, [INVENTDIMID] [nvarchar](20) NOT NULL, [CLOSED] [int] NOT NULL, [REGISTERED] [numeric](28, 12) NOT NULL, [PICKED] [numeric](28, 12) NOT NULL, [AVAILORDERED] [numeric](28, 12) NOT NULL, [AVAILPHYSICAL] [numeric](28, 12) NOT NULL, [PHYSICALVALUE] [numeric](28, 12) NOT NULL, [ARRIVED] [numeric](28, 12) NOT NULL, [PHYSICALINVENT] [numeric](28, 12) NOT NULL, [CLOSEDQTY] [int] NOT NULL, [LASTUPDDATEPHYSICAL] [datetime] NOT NULL, [LASTUPDDATEEXPECTED] [datetime] NOT NULL, [DATAAREAID] [nvarchar](4) NOT NULL, [RECVERSION] [int] NOT NULL, [RECID] [bigint] NOT NULL, CONSTRAINT [I_174ITEMDIMIDX] PRIMARY KEY CLUSTERED ( [DATAAREAID] ASC, [ITEMID] ASC, [INVENTDIMID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]) ON [PRIMARY]AndCREATE TABLE [dbo].[ESHOPINVENTSUM]( [DATAAREAID] [nvarchar](4) NOT NULL, [RECVERSION] [int] NOT NULL, [RECID] [bigint] NOT NULL, [ITEMID] [nvarchar](25) NOT NULL, [AVAILPHYSICAL] [numeric](28, 12) NOT NULL, [INVENTDIMID] [nvarchar](20) NOT NULL, [INVENTLOCATIONID] [nvarchar](10) NOT NULL, CONSTRAINT [I_50258ITEMDIMIDX] PRIMARY KEY NONCLUSTERED ( [DATAAREAID] ASC, [ITEMID] ASC, [INVENTDIMID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Please help!

Any *easy* way to compare database schemas, without a 3rd party tool?

Posted: 18 Sep 2013 11:55 PM PDT

I've been asked to compare an older copy of a DB to a current copy, to look for any schema changes. The problem is, while I suspect Red Gates SQL Compare would do this zip-zip-easy, due to the security of the environment it's a PITA to get a 3rd party tool approved.So, is there any easy way in SSMS to compare two DBs? Everything I've found so far indicates no, seeing as I have no access to Visual Studio (which has a tool built-in, but while this is approved software it has it's own issues with getting it,) and my comment above about a 3rd party tool.Am I going to be reduced to coding up something to list each column, its datatype, FK relationships, etc, then running it against the current and previous DB and eyeballing the list for changes?Thanks,Jason

Dynamic Data Export

Posted: 19 Sep 2013 09:49 AM PDT

Hi guys, Posting after a long time. How is everyone doing? I was working on an SSIS project in which I need to: 1. Loop through a folder containing multiple text files and get their name into a variable. 2. Append the current system date to that variable so that the variable now contains txtfilename + Date. 3. Create a table in the database with the name as value of the variable.4. Transfer data of the txt file into the newly created table. The first two steps I accomplished using a For Each Loop Container and i am able to get the file name into the variable. The second step I accomplished by just appending GetDate() to the value of the variable.The third step I accomplished using an Execute SQL task for creating table. What i am not able to do is get the data into the table with a Data Flow Task. The problem is that it requires me to give table name as input, which i cannot as the table is being created by the same package dynamically. I need to automate this task so I would always have to create the table dynamically. Please be patient enough to go through my problem and provide a solution. Thanks a tonne in advance. [b][u]PS[/u][/b]: I am not very good with C# or other scripting languages so I won't be able to work with a Scripting task in SSIS. So, please give me a solution other than scripting.

Database Project merge in TFS

Posted: 19 Sep 2013 09:35 AM PDT

Hi, We are using VS 2010 TFS database project for version control and deployment. However I would like to know the best practices for merging the changes from one branch to another. Here is the details below on the TFS branching Main - UAT - QA - DEVAll our development work is being checked in at the DEV branch and after that we want to merge the development changes into QA followed by UAT branches.Currently I am using DB Schema compare option to merge the changes from DEV--> QA and QA-->UAT etc. This approach is manual and it needs lot of effort if the changes are more and moreover the DB compare does not clean and easy to use for merging whereas it will show the changes in components which does not have any change at all etc...So please help me with the best approach on merging DB projects.

Nesting Error for CASE Statement that Isn't Nested

Posted: 19 Sep 2013 03:59 AM PDT

Hello all,I'm getting an odd set of errors when trying to run the following:[code="sql"]declare @FY1 char(4)set @FY1 = (DATEPART(yy,DATEADD(m,-2,GETDATE()) ))declare @fy char(2)set @fy = SUBSTRING(@fy1,3,2)declare @MonthUnits char (2)set @MonthUnits = datepart(mm, dateadd(mm, -2, getdate()))select '000' + ltrim(MCMCU), CASE @MonthUnits WHEN 1 THEN GBAN01/100 -- January WHEN 2 THEN GBAN02/100 -- February WHEN 3 THEN GBAN03/100 -- March WHEN 4 THEN GBAN04/100 -- April WHEN 5 THEN GBAN05/100 -- May WHEN 6 THEN GBAN06/100 -- June WHEN 7 THEN GBAN07/100 -- July WHEN 8 THEN GBAN08/100 -- August WHEN 9 THEN GBAN09/100 -- September WHEN 10 THEN GBAN10/100 -- October WHEN 11 THEN GBAN11/100 -- November WHEN 12 THEN GBAN12/100 -- December END, mcdl02 from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902, [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006 where GBMCU = MCMCU and GBFY = @fy and GBLT = 'AU' andGBOBJ = '9900' and GBSUB = '006' andMCSTYL in ('R') AND MCMCU<' 999' AND MCRP22<>'X'[/code]Here's the error message:[code="sql"]Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Qry1043'.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'Qry1016'.Msg 125, Level 15, State 4, Line 1Case expressions may only be nested to level 10.[/code]Apart from the fact that the CASE isn't nested, what's odd about this is that if I create a temp table, use this code as part of an INSERT to populate that table, then SELECT from the temp table, everything runs just fine. I've been Googling for over an hour and I can't figure out why. Any help will be appreciated!EDIT: If I comment out any two of the WHEN...THEN lines it runs fine. It really thinks those are nested CASE statements - but they're not. Weird...

Sql server sorting issues

Posted: 19 Sep 2013 12:33 AM PDT

Hi Guys.I need some helpI'm doing a sellers report for my boss.I have done a top sellers and it worked fine.I have even done a worst sellers and it workedI need to do something that is neither top nor worst.It has to be a middle sellers reportDoes sql have any syntax for sorting that outIf i sort by asc it gives me the lowest figuresIf i sort by desc it gives me highest figures.Is there any way to sort the middle figures out?

convert string to date SSIS 2008 R2

Posted: 19 Sep 2013 03:41 AM PDT

hi all,I never found the SSIS 2008 R2 section so I am posting this here since it really is of somewhat general interest:how do I convert a date in string format into a string that can be converted to a date.If my string were YYYY-MM-DD HH:MM:SS.000 the conversion goes well.I know how to convert YYYYMMDD into the format above, however my date strings are M/D/YYYY and they are not even padded.Do you know how to parse M/D/YYYY and get YYYY-MM-DD HH:MM:SS.000 with the syntax of SSIS?I tried ISNULL(DATE) ? NULL(DT_DBDATE) : (LEN(DATE) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,2),4) + "-" + SUBSTRING(TRIM(DATE),1,FINDSTRING("/",DATE,1) - 1) + "-" + SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,1),FINDSTRING("/",DATE,2) - 1)))but there's something wrong and I can't find the mistake,Thanks,kowlasky

Execution Plans Question

Posted: 18 Sep 2013 10:52 PM PDT

I'm playing with a stored proc that takes 2 days to run. I am pretty certain the issue was the lack of indexes on the Staging table so I created a clustered index and a few nonclustered indexes. Now I'm running the proc with Display Actual Execution Plan.The proc just started and is still running. But as I'm looking at the initial EP results, the first query (which is only 7% of the batch cost) has a Key Lookup of 94% on my new clustered index.I know Index Seeks are best, Index Scans are okay, and Table Scans (when they can be avoided) are not the best. But I am not sure where Key Lookup falls amongst those. Is it a good thing? Is it a bad thing?Before I created the clustered index, I did nonclustered indexes only and ran an estimated EP. The estimate had 0 cost relative to the rest of the batch and an Index Seek of 9% in the place where the Key Lookup used to be. The majority of the cost of this query was in a Table Update (73%), but in the Key Lookup plan, there's a Clustered Index Update of 6%...I think this is a good change (better than it used to be), but I'm not 100%, so I'm throwing this up here. Does anyone have a definitive "it depends" answer? @=)To be clear, I set Statistics on the actual execution and received the following information:Scan count 1, logical reads 22236, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Connection issue

Posted: 19 Sep 2013 07:26 AM PDT

Hello all it seems that the SQL server receives a lot of select statements coonections from an asp .net application but the packages that leave the SQL are malformed can that be an issue with SQL ? Or it is an issue with the network how can I prove that it is the network ?

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Posted: 12 Apr 2012 01:51 AM PDT

Dear All,I have a stored procedure and I keep getting the following error message:Msg 468, Level 16, State 9, Procedure "procedurename", Line 129Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.I looked at the Collation for the Database and it is SQL_Latin1_General_CP1_CI_AS but I don't know what else I need to do to get it working please?Thank you in advance!

Extracting and parsing XML from fields (CRM related)

Posted: 03 Jan 2012 08:58 PM PST

I'm a relative novice when it comes to SQL, so please be gentle with me. :-PI've been getting on reasonably well producing reports from our Microsoft CRM-based application, but have come across something I've no experience of before. CRM allows ResourceGroups to be created to which you can assign resources. In our case, we're using these ResourceGroups to create team groupings, which we can then use in reports without having to hard code the groupings.The problem I have is the way in which CRM stores these groupings. I was expecting that the relationships would be defined in a table, (one group to many resources), but it appears that they are actually stored as XML within a field so the SQL would be:[code="sql"]SELECT ResourceGroupBase.*, ConstraintBasedGroupBase.*FROM ResourceGroupBase INNER JOIN ConstraintBasedGroupBase ON ResourceGroupBase.ResourceGroupId = ConstraintBasedGroupBase.ConstraintBasedGroupId[/code]and the XML that describes members of the group is contained in the 'Constraint' fiels:[code="xml"]<Constraints><Constraint><Expression><Body>resource["Id"] == {005b7cce-de01-e011-a55b-0050569d00bf} || resource["Id"] == {3dcbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {3fcbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {49cbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {4ccbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {4dcbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {4ecbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {4fcbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {53cbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {55cbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {5acbc1d6-a3e1-df11-a501-00188b8b43b5} || resource["Id"] == {71cbc1d6-a3e1-df11-a501-00188b8b43b5}</Body><Parameters><Parameter name="resource" /></Parameters></Expression></Constraint></Constraints>[/code]This XML contains the GUIDS / IDs I require to link to the TeamBase table and get my membership, but I've never come across a situation such as this and don't know how to proceed with my query.Is it possible (using SQL) to make a query that returns the resource group and associated teams such as:ResourceGroup TeamGroup1 Team AGroup1 Team BGroup1 Team CGroup2 Team YGroup3 Team ZIf so, how would I go about it? I'm guessing it would involve parsing the XML, but I have no experience of this or how / if it is possible?Stuart

SSRS Hide a column based on user login

Posted: 19 Sep 2013 05:15 AM PDT

HI, I need to create a report in which I can show all the column to my manager but if supervisor want view the same report then I need to hide 1 column from that report.Any suggestion or help would be much appreciated Thanks

SSIS - varchar max column to varchar

Posted: 19 Sep 2013 04:36 AM PDT

Source: large object column, varchar(max) in a tableDerived column: substring((DT_STR,[b]8000[/b],1252)[Column 0] ,1,10)Derived column: substring((DT_STR,8000,1252)[Column 0] ,5,10)Derived column: substring((DT_STR,8000,1252)[Column 0] ,7,10)......Destination: SQL tableNote that 8000 is not the maximum i need, there are values after 8000 too. I need to read and load them tooHow to achieve this? Since I can go with the above steps till i hit 8000. But what after that? we cant give like substring((DT_STR,[b]9999[/b],1252)[Column 0] ,5,10)

Windows XP Conversion

Posted: 19 Sep 2013 12:53 AM PDT

Hi, I am new to SQL Server but am looking to make the switch from Oracle 11g. Is it possible to install SQL Server 2008 + SSMS onto a Windows XP machine and then upgrade to Windows 7 after? Also, how much memory does SQL Server generally require for a small scale Database?Thanks,J

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)?

Random SQL connection drops .

Posted: 19 Sep 2013 03:47 AM PDT

In our enviornment we have encountered recent SQL connection issue which lasted from 10 seconds to 2 mins . Observations:-1. Nothing funky was going in SQL just before the connection issue started . 2. As SQL refuses connection so our monitoring tool can't connect during that time to report the cause . 3. Memory might be one of the reason but it was not triggered by any SQL query . 4. I set some counter to isolate the issue and cause . Below is the counter history which shows that when Page/sec and Page Reads/Sec spiked at 2:35pm as in example here . CST Page Faults/sec Page Reads/sec Pages/sec9/18/13 2:34 PM 4965.79457 0.133361834 09/18/13 2:34 PM 4318.383021 1.499832023 09/18/13 2:35 PM 22507.73607 441.6537764 2577.6726149/18/13 2:35 PM 54878.84902 813.0582928 3594.6191629/18/13 2:36 PM 35788.83947 804.7487049 4166.6909959/18/13 2:36 PM 41066.90062 917.2741782 3384.9646329/18/13 2:37 PM 7825.444906 1081.125176 1581.2552769/18/13 2:37 PM 1589.648151 222.6780809 3392.373899/18/13 2:38 PM 6247.529166 513.3257095 1567.539806Can anyone give direction to troubslehoot how I can fix this issue or to capture which process can cause this spike as if not SQL .

Access rights

Posted: 18 Sep 2013 11:33 PM PDT

How to provide only rights to execute sp_who2 & killing the blocking SPID if any without giving sysadmin rights, is this possible???

Form showDialog tried to set an ineligible form as its owner

Posted: 18 Sep 2013 09:35 PM PDT

Hi,I have SQL Server 2008 R2 Standard x64 installed on my Windows 7 SP1 workstation. I applied SP2 a couple of months ago without any problem. Now I'd like to uninstall it but when I try I get the error:TITLE: SQL Server Setup failure.------------------------------SQL Server Setup has encountered the following error:Form showDialog tried to set an ineligible form as its owner. Forms cannot own themselves or their owners.Parameter name: owner.I've tried the following:From Add/Remove Programs I right-click "Microsoft SQL Server 2008 R2 (64-bit)" and select "Uninstall/change" and then Remove.Re-run SQL setup from the installation media, move to Maintenance in the Installation Center and choose Repair.Try to re-run SQL 2008 R2 SP2.In every case the same dialog box comes up (have tried stopping the services first). Other software seems to be behaving normally.Searching online I don't find anything specific to SQL install/uninstall.Hoping somebody can suggest something to avoid re-imaging this machine.

SSMS closes without warning during query run

Posted: 19 Sep 2013 01:01 AM PDT

Does anyone know why SSMS would close without warning during the middle of a query run?It's done this to me twice this morning. I do have Statistics_IO and Statistics_Time and Include Actual Execution Plan on (which I usually have off), but it seems odd to me that that would be the only reason why SSMS shuts down.No error BTW. The window just vanishes. I haven't checked the logs yet. Doing that after I finish posting. Would love to know if anyone else has seen this problem before.

Script to find all filestream databases with list of Tables (contain filestream column)

Posted: 18 Sep 2013 10:56 PM PDT

Hello,Do you have a script to find all filestream databases with list of tables containing Filestream Column ?I would like a result like this :Name of database, Name of TableThanks,Eric

MSDTC config in cluster..

Posted: 18 Sep 2013 09:02 PM PDT

Hi All,Windows server 2008 r2I have to install sql 2008 r2 cluster for i need a help for msdtc config?Steps that i have followed:1)MSDTC configuration:i have configured msdtc instance in seprate group.2)install sql clusternow my dout is do we need to map the sql instance with MSDTC instance or not.?Please reply its urgent...

Backup SQL agent Job

Posted: 18 Sep 2013 11:20 PM PDT

Can I setup a single agent job to handle Full(Every Sunday at 00:00), Differential(Everyday at 00:00) and log backups(every 30 mins) in a single job without stored proc logic, or do I need to setup 3 different jobs?If different jobs are needed because of the different schedules, how can I make sure that jobs don't overlap(I don't need the Diff job at 00:00 if a full backup is running, or a log backup when I also do either of the previous two). I cant seem to figure this out in the job schedulerThanks.

Moving a database log file question

Posted: 05 Sep 2013 06:20 PM PDT

SQL Service was down, our storage engineer created a new LUN and then moved the log file to the new location ...Database is therefore unaccessible ...after running a Alter database script to point the log file location, The only way to get the DB online is to restart SQL Service??? this will in effect take the whole instance DB's down ...Is there another way to just get that 1 DB online???I know the sequence of events is not exactly best practice but is there a way around this scenario???

Script to check the progress of rebuild Index?

Posted: 18 Sep 2013 08:50 PM PDT

Hi,Is there any good script available to check the progress of Rebuild Index?Would be helpful.
Posted by at No comments:

Thursday, September 19, 2013

[SQL Server] How to insert in 3 tables using stored procedure

[SQL Server] How to insert in 3 tables using stored procedure

How to insert in 3 tables using stored procedure

Posted: 19 Sep 2013 02:20 PM PDT

Hello Everyone and Hello WorldHow should i use stored procedure in inserting in three tables? like this one...i Have 3 tables namely Bio, Sex, and Status*BiobioIDFirstnamemiddlenamelastnamesexIDstatusID*SexsexIDsex(Male or Female only)*StatusstatusIDstatus(Single or Married or Divorce only)i have a code like this....-----------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SaveBiography] @firstname varchar(50), @middlename varchar(50), @lastname varchar(50), @sex varchar(50), @status varchar(50), @sexID int, @statusID int, @bioID int outputASBEGIN SET NOCOUNT ON; SET @firstname = LTRIM(RTRIM(@firstname)) SET @middlename = LTRIM(RTRIM(@middlename)) SET @lastname = LTRIM(RTRIM(@lastname)) SET @sex = LTRIM(RTRIM(@sex)) SET @status = LTRIM(RTRIM(@status)) IF EXISTS(SELECT * FROM Biography.dbo.Bio WHERE firstname = @firstname AND middlename = @middlename AND lastname = @lastname AND bioID <> @bioID) BEGIN RAISERROR ('[Error]Duplicate name', 16, 1) RETURN END IF @firstname = '' BEGIN RAISERROR ('[Error]No first name', 16, 1) RETURN END IF EXISTS (SELECT * FROM Biography.dbo.Bio WHERE bioID = @bioID) BEGIN UPDATE Bio SET firstname = @firstname, middlename = @middlename, lastname = @lastname, sexID = @sexID, statusID = @statusID WHERE bioID = @bioID END ELSE BEGIN IF @sex = 'Male' BEGIN @sexID = 1 END ELSE BEGIN @sexID = 2 END IF @status = 'Single' BEGIN @statusID = 1 END ELSE IF @status = 'Married' BEGIN @statusID = 2 END ELSE @status = 'Divorced' BEGIN @statusID = 3 END INSERT INTO Bio(firstname,middlename,lastname, sexID, statusID ) VALUES (@firstname, @middlename, @lastname, @sexID, @statusID ) SELECT @bioID = SCOPE_IDENTITY() END END-----------------------------------------------and i'm getting this kind of error..------------------------------------------------Msg 102, Level 15, State 1, Procedure SaveBiography, Line 41Incorrect syntax near '@sexID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 59Incorrect syntax near '@statusID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 67Incorrect syntax near '@statusID'.Msg 102, Level 15, State 1, Procedure SaveBiography, Line 112Incorrect syntax near 'END'.--------------------------------------------------------------Please Help me Lord:crying:

Day of week comparison for contact availability

Posted: 19 Sep 2013 12:05 PM PDT

Hey all, I have a problem I'm trying to figure out here. I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. IE, the contact has 7 BIT fields in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field based on the day of the week to see if their available (T) that day for a notification. Each field name in the table is as such: mon, tue, wed, etc. I can get the current DOW from sql and trim it to the same length and case of the field names to try and figure out which one I need to check whether that fields contents are true or not, but I'm stumped on where to go from here. I can basically get around sql server MOST of the time, but I am by no means a pro, and this one has me stumped. I'm sure I have to be missing something obvious here. Any help is greatly appreciated!Kevin

Ignoring parenthesis in a name field

Posted: 19 Sep 2013 09:15 AM PDT

I have a name field as [b]Dow, Jones (NewYork)[/b] and i can get the fist name with the query below as Sean suggested yesterday as[quote]select SUBSTRING(_Name, patindex('%[,]%',_Name) + 1, len(_Name)) as OwnerFirstName from CC[/quote][i]with the result as Jones (NewYork) [/i]and the last name as [quote]select substring(_Name, 0, charindex(',',_Name)) as OwnerFirstName from CC [/quote][i]with the result as "Dow"[/i]but i want the first name only as Jones i.e. ignoring the (newYork) stringany help is appreciated thanks

How to set values to a field in database using sql server 2005

Posted: 18 Sep 2013 04:20 PM PDT

Hello World:-)How should i set a values from my database like...I have 3 tables...namely Bio Table, Sex Table, Status Table*Bio TableBioIDFirstnameMiddlenameLastnamesexIDstatusID*Sex TablesexIDsex(male or female)*Status TablestatusIDstatus(single, married or divorced)question..how should i set the sex and status table, in such a way in only contains male or female,,,,or single or married or divorcePlease help me??...God Speed!!!

Why doesn't my index get used?

Posted: 19 Sep 2013 09:56 AM PDT

Hi,I had a query that I was running to get the top n records (eg, top 1,000). I changed the query so that it does row_number() and then gives back rows numbered between i and j (eg, rn between 1,001 and 2,000). My query was using an index that worked very fast (0 - 3 secs). When I made the change, it decided not to use that index anymore and now it is very slow (5 - 120 secs). I tried using >1000 and <2000 instead of between, and a few other small things that did not work. Putting in an index hint made the query run at full speed again - but this is the first time I've ever done that and as far as I understand I should undo it and make it the last. How can I make this choose my index without giving it a hint??I have so many tables and indexes involved, I am not sure what to show exactly...This is my query:[code]ALTER PROCEDURE [dbo].[sp_SFP099_NonLab_Drilldown] -- Add the parameters for the stored procedure here --@myCurr varchar(10) = NULL -- 'Mixed$' or 'Can$' @L3 varchar(50) = NULL,@L4 varchar(50) = NULL,@L5 varchar(50) = NULL,@L6 varchar(50) = NULL ,@L7 varchar(50) = NULL,@L8 varchar(50) = NULL,@L9 varchar(50) = NULL,@L10 varchar(50) = NULL,@CC varchar(50) = NULL ,@myFunc varchar(25) = NULL -- 'Engineering' or 'Mechanical' or etc. If passed blank, not used ,@myPer varchar(2) = NULL, @myYTD_1_0 bit = 0 -- the month number (eg, '8'); 1 means YTD, 0 CM only ,@numRecs bigint = 1, @expL2 varchar(4) = NULL, @primLev varchar(2) = NULL ,@desc1 varchar(100) = NULL, @desc2 varchar(100) = NULL ,@myUser varchar(50) = NULL -- info about the person who called the procedure ,@1stRec bigint = 1, @myLY_1_or_0 bit = 0 --1 means last year, 0 means current yearASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @startTime as datetime set @startTime = getdate()--compose dynamic SQL declare @myAcctKey as varchar(8) set @myAcctKey = right('00' + @primLev,2) + case when @expL2 is null then '' else right('0000' + @expL2,4) end declare @myOrg as varchar(50) = 'All' -- 'All' gives the whole company declare @myLev as varchar(20) = 'All' if @CC is not null begin set @myOrg = @CC; set @myLev = 'CC'; end else if @L10 is not null begin set @myOrg = @L10; set @myLev = '[Level 10]'; end else if @L9 is not null begin set @myOrg = @L9; set @myLev = '[Level 9]'; end else if @L8 is not null begin set @myOrg = @L8; set @myLev = '[Level 8]'; end else if @L7 is not null begin set @myOrg = @L7; set @myLev = '[Level 7]'; end else if @L6 is not null begin set @myOrg = @L6; set @myLev = '[Level 6]'; end else if @L5 is not null begin set @myOrg = @L5; set @myLev = '[Level 5]'; end else if @L4 is not null begin set @myOrg = @L4; set @myLev = '[Level 4]'; end else if @L3 is not null begin set @myOrg = @L3; set @myLev = '[Level 3]'; end; declare @totRows as bigint set @totRows = 0 declare @mySQL as nvarchar(max) set @mySQL = 'SELECT @_myOrg as [myOrg]--------THIS PART USES MY INDEX BY DEFAULT------------- ;with myRows as ( SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id] WHERE ' + case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts + case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org + ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0 + case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end +') SELECT @_totRows = Count([RowNum]) from myRows------THIS PART USED THE INDEX UNTIL I CHANGED FROM TOP @_numRecs to BETWEEN @_1stRec and (@_1stRec + @_numRecs - 1), NOW ONLY USES INDEX WITH HINT---- SELECT @_totRows as [totRows] ' + ';with myRows as ( SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a ' + ' WITH (INDEX(IX_CostCtr_AcctKey_Mo_RowNum)) ' + ' INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id] WHERE ' + case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts + case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org + ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0 + case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end +') , myTopRowNums as ( SELECT [RowNum] from myRows where rn between @_1stRec and (@_1stRec + @_numRecs - 1) ) SELECT a.[CC] , a.[Account] , a.[Vendor] , a.[Doc Header Text] as [Doc] , a.[User Text] as [UserText] , a.[Description] , a.[Year] , a.[Month] , a.[Quantity] , a.[Unit] , a.[Dollar Amount] as [DollarAmount] FROM ' + case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end + ' a INNER JOIN myTopRowNums b on a.[RowNum] = b.[RowNum] ORDER BY a.[RowNum]' PRINT @mySQL --execute dynamic SQL (using sp_executesql instead of exec(@sql) means not vulnerable to sql injection) EXEC sp_executesql @mySQL, N'@_myOrg varchar(50), @_myFunc varchar(25), @_myPer varchar(2),@_myAcctKey varchar(8) ,@_numRecs bigint, @_expL2 varchar(4), @_primLev varchar(2), @_1stRec bigint, @_totRows bigint output', @_myOrg = @myOrg, @_myFunc = @myFunc, @_myPer = @myPer, @_myAcctKey = @myAcctKey ,@_numRecs = @numRecs, @_expL2 = @expL2, @_primLev = @primLev, @_1stRec = @1stRec, @_totRows = @totRows outputEND[/code]The index that was being used is a non-clustered index defined like this (my NonLab_Read synonym points at either an A or B table):[code]CREATE NONCLUSTERED INDEX [IX_CostCtr_AcctKey_Mo_RowNum] ON [dbo].[_SFP099_NonLabB] ( [Cost Ctr] ASC, [AcctKey] ASC, [Month] ASC, [RowNum] ASC)[/code]When I don't give the hint, it now wants to use a clustered index, even though the only thing it gets from there is the cost center #:[code]CREATE CLUSTERED INDEX [CX_Cost_Ctr_Cost_Elem] ON [dbo].[_SFP099_NonLabB] ( [Cost Ctr] ASC, [Cost Elem] ASC)[/code]I'm at a loss here... Can anyone see based on this what might make my preferred index be used without the hint? Are there other things I should post to help the experts see the answer? Should I just go ahead and use the hint?Thanks for the help.Tai

Run a query against registered servers and insert results into table

Posted: 01 Mar 2012 07:52 PM PST

Hi all, Is it possible to run a query against multiple registered servers and have the results inserted into a table? Trying to get details of SQL boxes and wanted to keep the data dynamic and always up to date. So id like to run say[code="sql"]SELECT @@VERSION AS [SQL Server and OS Version Info];[/code]Thanks S

Relationship Question

Posted: 19 Sep 2013 01:24 AM PDT

Hey guys - I have a relationship question....for SQL Server that is. ;)I'm in the early stages of my first large "ETL" project which I'll take data from a lot of old tables and insert them into a more normalized design. I have a little test database which I'll test out new scripts on..over and over. [i]I would like the ability to run a SP to clear out all the test tables in preparation for the next test. [/i] So, after creating a "dump' SP, I ran into some very basic, fundamental table relationship problems. As a test, I have two basic tables. See screenshot from SSMS. Very common relation set up there.[img]http://www.mvdb.virginia.gov/assets/images/sqlscreenshot.jpg[/img]I have set up a couple integrity rules: Set the FK in "NewCustomers" to go to it's default value of 0 in the event that the child record (NewAccounts) is deleted. Cascade the changes to NewCustomers in the unlikely event that the PK in NewAccounts is changed. Seems logical and common practice. Right??So with both tables populated with test data, I get constraint errors when I try to DELETE or TRUNCATE TABLES.So when I try to (in this order):DELETE FROM NewAccounts (and truncate)DELETE FROM NewCustomers (and truncate)I get:[font="Courier New"][color=#ff0000]The DELETE statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_NewAccounts". The conflict occurred in database "AcmeCars", table "dbo.NewAccounts", column 'NewAccountID'.The statement has been terminated.[/color][/font]It still deletes all the Customers but keeps the Accounts.I thought by deleting the accounts FIRST, it would set the FKs all to "0" and then turn around and delete the Customers. I must be missing an obvious concept. Can you enlighten?

SQL Server 2000 upgrade issue...

Posted: 19 Sep 2013 02:06 AM PDT

I'm attempting to migrate SQL Server 2000 database to SQL Server 2008. After reviewing the results of the upgrade advisor (no issues), I restored the database to 2008. It was subsequently identified that one of the fields had its length changed from 30 to 10 (nvarchar!10), null). My experience with upgrades is limited (first time effort). Is there something I missed when moving the database over? Copied the backup to the new environment and restored the database.Any comments / URLs would be appreciated. Thanks.

Adding User to SQL Server

Posted: 05 Sep 2013 02:13 AM PDT

Hi,I got a scenario where I am using [b]Windows Application[/b].I want to create a SQL Server User satisfying the password policy. I have couple of questions related to it.1. How can I create such a user?2. After creation of the user, I want to store its password somewhere at a central location[don't know where? can somebody help?]. While connecting to DB, read this password from central location on the network and login to SQL Server.Please help.Thanks,Paresh
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog