Wednesday, May 22, 2013

[SQL Server 2008 issues] How to use View Result in Insert trigger in sql server 2008 ?

[SQL Server 2008 issues] How to use View Result in Insert trigger in sql server 2008 ?

How to use View Result in Insert trigger in sql server 2008 ?

Posted: 21 May 2013 04:29 PM PDT

i am having two tables A,BOn Insertion in A, i insert data in B table(using Insert trigger) but i need to get data for some columns in B table from a view.But it's causing an error - Invalid object name - view name./**/CREATE TRIGGER [dbo].[InsCsBpAsset] ON [dbo].[CsAsset] AFTER INSERT AS BEGIN declare @Tax decimal(18,4), @Ret decimal(18,4), @EfctRet money, @IntAmt money, @Amt money,/*final*/ @CsId int, @NetR money, @RealR money, @Inflation decimal(18,4)money,@AsetId smallint -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select @Amt=AsetAmt from inserted; select @AsetId=AsetId from inserted select @Tax=isnull(cast(Tax as decimal(18,4)),0.0000) from VwBPAssumption where Assetid=AsetId and lkptyp='AST'; select @Ret= isnull(cast(Rtrn as decimal(18,4)),0.0000) from VwBPAssumption where Assetid=AsetId and lkptyp='AST'; select @EfctRet=((@Ret/100)*(1-(@Tax/100))); select @IntAmt=(@Amt*(@EfctRet/100)); /* insert into csbpasset (CsId,MemId,AsetId,AsetHd,AsetAmt,RetId,TaxId,EfctRet,IntrstAmt) values(CsId,MemId,AsetId,AsetHd,AsetAmt,@Ret,@Tax,@EfctRet,@IntAmt) */ insert into csbpasset (CsId,MemId,AsetId,AsetHd,AsetAmt,RetId,TaxId,EfctRet,IntrstAmt) select CsId,MemId,AsetId,AsetHd,AsetAmt,@Ret,@Tax,@EfctRet,@IntAmt from inserted -- Insert statements for trigger here /*Update final amounts in table - csbpfnlasset*/ select @NetR= ((isnull(sum(IntrstAmt),0.0000)/sum(AsetAmt))*100) from inserted; select @Inflation= isnull(Rtrn,7.0000) from vwBPAssumption where Asmptnid=18; select @RealR=((@NetR-@Inflation)/(100+@Inflation)) ; if exists(select 1 from csbpasset where csid=@CsId) begin insert into csbpfnlasset(csid,NetRet,RealRet) values(@CsId,@NetR,@RealR) end else begin update csbpfnlasset set NetRet=@NetR,RealRet=@RealR where CsID=@CsId end END

SQl Server 2008 Query Performance

Posted: 21 May 2013 07:01 PM PDT

Hi Guys,The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index but i was getting the same time. How can I avoid this issue?. select T.Name as name,T.Id as id,COUNT(distinct(DD.dynamictableid)) as counts ,ROUND(SUM(D.[employees]),0) as measure1 from dbo.TreeHierarchy T left join dbo.DynamicDataTableId DD on T.Id= DD.HierarchyId AND T.DataViewId=DD.DataViewIdleft join dbo.Demo1 D on D.[Demo1Id] = DD.DynamicTableIdwhere T.DataViewId=2 AND T.ParentId=0group by T.Id, T.Name Thanks in Advance!!!!.

Initial data dumps

Posted: 21 May 2013 09:04 AM PDT

We have a fairly large production database for few hundred customers. When we add a new customer, we need to import data from the customer system into several tables. Currently we do it straight into production tables that are in use by existing customers. The import process is asynchronous which doesn't guarantee correct order of loading tables, so we are running into reference integrity errors. Another problem is timeouts for existing customers that happen frequently during adding new customers.What would be a good solution?

Memory is not releasing in sql server 2008

Posted: 21 May 2013 06:36 PM PDT

Dear All,We have sql server 2008 with two node cluster.during operation memory is reaching up to 90% but its not releasing after decreasing the load.Memory is freezing at one limit.After restarting its coming down but automatically its not coming down.Pls suggest how to rectifiy this

SQLCMD Issue

Posted: 21 May 2013 06:35 PM PDT

I am facing error with SQLCMD(sql 2005 EE)SQLCMD -E -SSqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process due to delay in opening server connection.When I triedSQLCMD -E -S -l20it connected.I tried with sql authenticationSQLCMD -S -U<username> -P<pwd>It worked fine.issue with windows authenticationFacing some delay while connecting thorugh SSMS

Time out from App but not SSMS

Posted: 21 May 2013 06:16 PM PDT

HiIn a web app (linq to sql),I have a query with some parameters I gave the actual query with special params from sqlprofiler.The problem is that I have time out when I run this query from App but when I run the query with this format (that I gave from profiler) EXEC sp_executesql N'select statement ',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 datetime,@p5 datetime', @p0 = 1, @p1 = 2, @p2 = 1, @p3 = 1003, @p4 = '2012-02-07 00:00:00', @p5 = '2012-06-10 07:00:00' directly from SSMS ,it takes 0 second to run.I know that if I run the query in this format with parameter from sp_executesql the plan in both app and sql is the same,so I am sure that both of them are using the same plan(and there is not parameter sniffing problem).How can I check the reason of time out that convince me the problem is not from sql ?

Need to be alerted to disallowed email addresses being entered - Admin cleaning up after App programmers! :-)

Posted: 21 May 2013 06:06 PM PDT

Hi allI think I may be on the right track when considering creating a scheduled job. I need to capture and correct (probably by a simple delete) instances where Application users enter a disallowed email address like '*@specificdomain.com' into the database. We're supporting a SQL2008r2 Server that is used by a third party application developer. They, in their wisdom, have created a nasty side effect that I won't bore anyone with here.We are unable to alter an stored procedures etc in the DB as they're encrypted, so instead of being able to stop the problem occurring at source we need to cure the issue after the event. I want to be able to generate a job that will scan 2 of the DB's tables for any occurrence of '*@specificdomain.com' that exist in the table that would be identified thus:SELECT FIRST_NAME, LAST_NAME, EMAILFROM CandidatesWHERE EMAIL LIKE '%@specificdomain.com%'SELECT FIRST_NAME, LAST_NAME, EMAILFROM CONTACTSWHERE EMAIL LIKE '%@specificdomain.com%'Can a job be created and scheduled to check the DB and either replace or just delete the disallowed email addresses?Thanks in anticipation of someone enlightening me!S

Import of huge XML file

Posted: 21 May 2013 06:01 PM PDT

Hi All!I have an xml file of 44 Gb (Not Meg, its really GB)Delivered by the Danish custom authorities.My problem is simple - How to import such a beast?I have seen a limit of 2.1 Gb everywhereBest regardsEdvard Korsbæk

Guideline question

Posted: 21 May 2013 02:29 PM PDT

I am looking for your advice.We have a calendar for each organization where the total count of the org events is kept. Each organization can have 1 or more business units. Each business unit can have 0 , 1 or more events.The count of events rolls up from the business units. The business unit can update or delete these events and the count of the org will change accordingly. All events are kept on an event table.There are 3 options to implement the count on the calendar:1. Create trigger on the event table that changes the count of org events on the calendar on event insert, update or event delete.2. Increment or decrement the org count on every stored proc that performs an insert/update/delete of events.3. Create a view ( indexed, with (nolock)) that queries the counts of all organizations and use it for display on each org calendar.For example: if there are 10 orgs, there will be 10 counts. User of Organization 1 will see the count of Organization 1 from the view.Can you advice which option is the most viable and which is the worst? I'd like to see it work correctly first then fast next.Thank you very much!

Normalization (DB Design)

Posted: 21 May 2013 05:43 PM PDT

[img][/img]Is my table schema normalized? Please Suggest.

How to set PacketSize in JDBC Driver?

Posted: 21 May 2013 05:32 PM PDT

I am using, Java, Jboss, Hibernate, SqlServer 2008 R2, getting following exceptionWARNING [com.microsoft.sqlserver.jdbc.internals.TDS.Reader] (pool-26-thread-7) ConnectionID:4 TDS header contained invalid packet length:12590; packet size:80002013-05-21 15:59:15,777 SEVERE [com.microsoft.sqlserver.jdbc.internals.TDS.Reader] (pool-26-thread-7) ConnectionID:4 got unexpected value in TDS response at offset:79922013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) Exception in thread "pool-25-thread-2" 2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) org.hibernate.TransactionException: JDBC rollback failed2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:204)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.wideplay.warp.persist.hibernate.HibernateLocalTxnInterceptor.rollbackIfNecessary(HibernateLocalTxnInterceptor.java:149)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.wideplay.warp.persist.hibernate.HibernateLocalTxnInterceptor.invoke(HibernateLocalTxnInterceptor.java:72)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.softpak.marserm.engine.library.impl.JobReportStatusUtil.updateReportStatus(Unknown Source)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.softpak.marserm.engine.library.impl.ExecutableJob.run(Unknown Source)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at java.lang.Thread.run(Thread.java:662)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:304)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:1655)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcRollback(BaseWrapperManagedConnection.java:697)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at org.jboss.resource.adapter.jdbc.WrappedConnection.rollback(WrappedConnection.java:500)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:217)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:196)2013-05-21 15:59:15,808 ERROR [STDERR] (pool-25-thread-2) ... 7 moreI need to increase packetSize given in JDBCDriver (sqljdbc4), How can I do this?I tried many things including defining <packetSize>32767</packetSize> in ds.xml but no use.

SQL Server Native Cleint and PB

Posted: 21 May 2013 12:52 AM PDT

Dear Gurus,How to the apply the 'Auto Translate' property of the SQL Native client (OLE DB) connection string in Power Builder 12.5 ?ThanksMalik Adeel Imtiaz

syntax difference between create index and create clustered index

Posted: 21 May 2013 04:50 AM PDT

Is there any difference if I say Create index and create nonclustred index? or both means same?ex:[i]CREATE INDEX[/i] IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID); GO[i]CREATE NONCLUSTERED INDEX[/i] IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID); GO

For your amusement

Posted: 21 May 2013 05:21 AM PDT

[code]USE [collect2000]GO/****** Object: UserDefinedFunction [dbo].[tvf_ScrubData] Script Date: 05/21/2013 12:54:28 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_ScrubData]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[tvf_ScrubData]GOUSE [collect2000]GO/****** Object: UserDefinedFunction [dbo].[tvf_ScrubData] Script Date: 05/21/2013 12:54:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[tvf_ScrubData] ( -- Add the parameters for the function here @data varchar(8000), @type varchar(20))RETURNS TABLE ASRETURN ( select result from (select LTRIM(rtrim(@data)) as data) a outer apply ( SELECT case when @type = 'posint' then case when data like '%[^0123456789]%' then null else data end when @type = 'int' then case when substring(data,1,1) like '%[^-0123456789]%' then null else case when data like '%[^0123456789]%' then null else data end end when @type = 'decimal' then case when substring(data,1,1)like '%[^-0123456789.]%' then null else case when data = '' then null when data is null then null when data like '%[^-0123456789.]%' then null else case when charindex('.',data) > 0 and charindex('.',data,charindex('.',data)) > 0 then null else data end end end when @type = 'posdecimal' then case when data = '' then null when data is null then null when data like '%[^-0123456789.]%' then null else case when charindex('.',data) > 0 and charindex('.',data,charindex('.',data)) > 0 then null else data end end when @type = 'date' then case when data = '' then null when data is null then null when len(data)<> 10 then null when data like '[0123456789][0123456789][/.-][0123456789][0123456789][/.-][12][0123456789][0123456789][0123456789]' then data else null end when @type = 'time' then case when data = '' then null when data is null then null when data like '[0123456789][0123456789][:.][0123456789][0123456789]' or data like '[0123456789][0123456789][:.][0123456789][0123456789][:.][0123456789][0123456789]' or data like '[0123456789][0123456789][:.][0123456789][0123456789][:.][0123456789][0123456789][.][0123456789][0123456789][0123456789][0123456789]' then data else null end when @type = 'datetime' then case when data = '' then null when data is null then null when len(data)<> 10 then null when data like '[0123456789][0123456789][/.-][0123456789][0123456789][/.-][12][0123456789][0123456789][0123456789] [0123456789][0123456789][:.][0123456789][0123456789][:.][0123456789][0123456789][.][0123456789][0123456789][0123456789][0123456789]' then data else null end end as result ) b )GO[/code]

Why is my subtree cost so high?!

Posted: 21 May 2013 07:38 AM PDT

I really need help with optimizing this code. The subtree cost is really high, but it may just be a bug? Plan attached...[code="sql"] DECLARE @filename1 VARCHAR(255); DECLARE @filename2 VARCHAR(255); SELECT @filename1 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xel', '*xel') FROM sys.server_event_sessions es INNER JOIN sys.server_event_session_fields esf ON es.event_session_id = esf.event_session_id WHERE es.name = 'Monitor_Deprecated_Discontinued_features' AND esf.name = 'filename'; SELECT @filename2 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xem', '*xem') FROM sys.server_event_sessions es INNER JOIN sys.server_event_session_fields esf ON es.event_session_id = esf.event_session_id WHERE es.name = 'Monitor_Deprecated_Discontinued_features' AND esf.name = 'metadatafile'; SET STATISTICS IO ON SELECT DISTINCT FinalData.R.value ('@name', 'nvarchar(50)') AS EventName, FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') AS Feature, FinalData.R.value ('data(data/value)[2]', 'nvarchar(500)') AS MESSAGE, FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID, FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText FROM ( SELECT CONVERT(XML, event_data) AS xmldata FROM sys.fn_xe_file_target_read_file(@filename1, @filename2, null, null)) AsyncFileData CROSS APPLY xmldata.nodes ('//event') AS FinalData (R) WHERE FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') <> 'Deprecated encryption algorithm' SET STATISTICS IO OFF[/code][code="plain"](16 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 87955, physical reads 0, read-ahead reads 0, lob logical reads 122773, lob physical reads 0, lob read-ahead reads 0.[/code]

Count Words in Text Field and/or Column (frequency of use)

Posted: 03 Dec 2011 10:04 AM PST

I searched the archives for this and could not find a solution.I have a comment table and would like to get a word count.So for example, if comment #1 was red dress and comment #2 was purple dress, the result would be:1 red1 purple2 dressPerformance is not a concern. Thanks.

SQL service account is sysamdin or not?

Posted: 06 Jun 2012 03:36 AM PDT

A question I have not got a clear answer:When installing SQL server 2008 or 2008 r2, we choose to use a domain account as the service account, for example I will call it mydomain\mysqlsvc. I didn't specifically grant the account as sysadmin.So my question is:Is the SQL service account mydoman\mysqlsvc by default a sysadmin of SQL server or not?Thanks

SQL Server Corporate Standards - Automated Compliance Checking

Posted: 21 May 2013 08:43 AM PDT

Does anyone know of a flexible automated SQL Syntax checker?I want to be able to analyse scripts before they are deployed to our test systems to check for certain things such as the fact that each statement has an appropriate error check after it.I want to make sure each statement is commented.I want to spot syntax that would be legal in an on premise SQL Server but not in Azure.I am expecting to configure my own rules.Does such a thing exist? I have started to write one but clearly its complicated because I am effectively having to start to simulate SQLs own parser so I can break down and analyse statements.Any feedback will be much appreciated (positive or negative).ThanksTim

Tempdb file sizing

Posted: 21 May 2013 05:28 AM PDT

Need some recommendation on sizing of tempdb files for the following specs: • Two SAN drives - 400gb each have been assigned for the tempdb files.• 8 Intel XEON core process – E7- 8870• 10 databases• Big database size is approximately 581 gb.

Dynamic Pivot Using sp_executeSQL with Parameters

Posted: 21 May 2013 05:16 AM PDT

Hello, I am having an issue with a dynamic pivot query(SQL 2008). I have read that using sp_executesql with parameters allows the optimizer to reuse query plans which can obviously improve performance. However I am not able to use the parameters because I keep receiving a syntax error. Here is the data I am working with:[code="sql"]CREATE TABLE [dbo].[tblRating]( [pkRating_ID] [tinyint] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [RatingName] [varchar](50) NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT(GetDate()))CREATE TABLE [dbo].[tblRating2ContentGroup]( [pkRating2ContentGroup_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [ContentGroupId] [int] NOT NULL, [fkRating_ID] [tinyint] NOT NULL, [RatingValue] [tinyint] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT(GetDate()),)Insert Into tblRating(RatingName)Values('Originality'),('Creativity'),('Quality')Insert Into tblRating2ContentGroup(ContentGroupId, fkRating_ID, RatingValue)Values(3398, 1, 37),(3398, 2, 34),(3398, 3, 18),(1, 1, 48) ,(1, 2, 71) ,(1, 3, 28) ,(59, 1, 24) ,(59, 2, 38) ,(59, 3, 36) ,(118, 1, 56) ,(118, 2, 49) ,(118, 3, 11) ,(3169, 1, 12),(3169, 2, 18),(3169, 3, 39),(37, 1, 64) ,(37, 2, 100) ,(37, 3, 41) ,(3187, 1, 41),(3187, 2, 84),(3187, 3, 43),(3188, 1, 26),(3188, 2, 34),(3188, 3, 56),(3189, 1, 26),(3189, 2, 16),(3189, 3, 100)[/code]And here is the query I am trying to run:[code="sql"]Declare @RatingCategories nvarchar(2000), @GroupingCategories nvarchar(2000), @Sql nvarchar(max), @SqlParams nvarchar(500) = N'@RatingCategoriesSql nvarchar(2000), @GroupingCategoriesSql nvarchar(2000)' Select @RatingCategories = ISNULL(@RatingCategories + ',', '') + '[' + RatingName + ']', @GroupingCategories = ISNULL(@GroupingCategories + ',', '') + 'MIN(' + RatingName + ') as ' + RatingNameFrom tblRatingSET @Sql = N'With PivotTemp As ( SELECT ContentGroupId, @RatingCategoriesSql FROM ( Select ContentGroupId,fkRating_ID,RatingValue From tblRating2ContentGroup )a JOIN ( Select pkRating_ID,RatingName From tblRating )b on a.fkRating_ID = b.pkRating_ID PIVOT ( AVG(a.RatingValue) FOR b.RatingName IN(@RatingCategoriesSql) )pt ) SELECT ContentGroupId, @GroupingCategoriesSql FROM PivotTemp GROUP BY ContentGroupId' exec sp_executesql @Sql, @SqlParams, @RatingCategoriesSql = @RatingCategories, @GroupingCategoriesSql = @GroupingCategories;[/code]I keep receiving "Incorrect syntax near '@RatingCategoriesSql'". The problem is occurring on this line: AVG(a.RatingValue) FOR b.RatingName IN(@RatingCategoriesSql). If I change this query to remove the use of parameters, everything works as expected:[code="sql"]Declare @RatingCategories nvarchar(2000), @GroupingCategories nvarchar(2000), @Sql nvarchar(max)Select @RatingCategories = ISNULL(@RatingCategories + ',', '') + '[' + RatingName + ']', @GroupingCategories = ISNULL(@GroupingCategories + ',', '') + 'MIN(' + RatingName + ') as ' + RatingNameFrom tblRatingSET @Sql = N' With PivotTemp As ( SELECT ContentGroupId,' + @RatingCategories + ' FROM ( Select ContentGroupId,fkRating_ID,RatingValue From tblRating2ContentGroup )a JOIN ( Select pkRating_ID,RatingName From tblRating )b on a.fkRating_ID = b.pkRating_ID PIVOT ( AVG(a.RatingValue) FOR b.RatingName IN(' + @RatingCategories + ') )pt ) SELECT ContentGroupId,' + @GroupingCategories + ' FROM PivotTemp GROUP BY ContentGroupId'exec sp_executesql @Sql[/code]I suspect it has something to do with the brackets I am trying to use in the Pivot query, but unfortunately I am not able to see the query that sp_executesql builds. What am I doing wrong? Is it possible to do this using parameters for sp_executesql? Thank you in advance.Bob Pinella

SQL Server Link to Oracle Inconsistent metadata problem

Posted: 03 Dec 2012 12:35 AM PST

HiI have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can query the Oracle database using OPENQUERY but when I try a simple SELECT COUNT(*) from LK_ORA..USR.TAB1 I get an error:Msg 7356, Level 16, State 1, Line 1The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LK_ORA" supplied inconsistent metadata for a column. The column "COL_CODE" (compile-time ordinal 1) of object ""USR"."TAB1"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.I've found an article that says when SQL Server retrieves metadata from Oracle in order to compile the query, Oracle reports the data type to be varchar(255), but when it actually returns the data, the data type is in fact nvarchar(255). (But it may not be called nvarchar in Oracle.)Anyone know how to get around this? I've tried various options on my linked server connection in SQL with no luck so farMany Thanks

Report not working on Report Server

Posted: 21 May 2013 05:48 AM PDT

I'm not sure if this is the right place to post this one, but maybe someone can help.I have a report that I built in Report Builder and it works perfect. I have uploaded it to the Report Server, but when I try to view it, I get an error with the query:An error occurred during client rendering.An error has occurred during report processing.Query execution failed for dataset 'DataSet1'.For more information about this error navigate to the report server on the local server machine, or enable remote errors Here is the query that the report is running:select HourOfTheDay, sum(case when TimeReceived between @startdate and @enddate then NumberOfOrdersInBatch else 0 end) as NumberOfOrders, AVG(case when TimeReceived between @startdate and @enddate then TimeToProcess else 0 end) as AverageTime, sum(case when TimeReceived between @startdate2 and @enddate2 then NumberOfOrdersInBatch else 0 end) as PrvWkNumberOfOrders, AVG(case when TimeReceived between @startdate2 and @enddate2 then TimeToProcess else 0 end) as PrvWkAverageTimefrom info.batchstats with (nolock)where TimeReceived between @startdate and @enddate or TimeReceived between @startdate2 and @enddate2group by HourOfTheDayorder by hourofthedayAny help is appreciated.

Convert data to HL7 message

Posted: 21 May 2013 05:37 AM PDT

Hello everyone. I have be trying to figure out how to convert patient records into HL7 format for about a week now with no success. Is this possible within SQL Server? Is it even worth doing or should I look to a third party solution. If this can be done in SQL Server, can someone please refer me to any resources or point me in the right direction.Thanks.

Settings of Windows Server for SQL

Posted: 18 May 2013 05:35 AM PDT

Hello, Do you have any tips for settings of WS? Roles, memory, etc.. ? it can be different betwen performance? any link or references for interesting reading?thank you for response

How To DISTINCT COUNT with Windows Functions (i.e. OVER and PARTITION BY)

Posted: 21 May 2013 04:09 AM PDT

SQL 2012 Reference = http://msdn.microsoft.com/en-us/library/ms175997(v=sql.110).aspxI'm trying to get a unique count of rows in a child table in a multi-table join just like the example shown below from the above Microsoft article on COUNT(). I'm using the Windows functions (as opposed to old fashion aggregation with GROUP BY) because I need both aggregate data and non-aggregated data to be included in the results.In the below Example (from example C in the above Microsoft Document on T-SQL) it shows that you can do the following [code="sql"]USE AdventureWorks2012; GOSELECT DISTINCT Name, COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDeptFROM HumanResources.EmployeePayHistory AS ephJOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityIDJOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentIDWHERE edh.EndDate IS NULLORDER BY Name;[/code]This is using the DISTINCT keyword so as to get a distinct count where as without it the Count() coould be over stated by the number of rows retruend by the other tables in the join.Is there a way to do the equivelant in T-SQL for SQL Server 2008R2? I've tried the above and it errors out under SQL 2008R2 and examplce C above is NOT in the same Microsoft article when you change the platform to SQL 2008R2 so I'm assuming this is a feature nehancement in SQL 2012. How then can you do this under SQL 2008R2?Thanks

Shrink helping performance

Posted: 21 May 2013 01:56 AM PDT

I have a situation that i thought was impossible and cant really explain to the users. I was asked to speed up an application. The database have no indexes and no PK/FK but have one maintenance job. Each week a shrink database is executed.When asked, i got told that was to speed up the query. I desactivated that job as soon as i saw it, but after 5-6 days the query were getting slower. I got asked to do a shrink, and i did to show them that it will not help, but the query ran faster.Now i do not ask for a solution, adding indexes and no more shrinking will do the job...but im wondering what is going on and under what condition a shrink can actually help performance.Thank you

Update Statement on specific criteria

Posted: 21 May 2013 01:38 AM PDT

I have an existing table where if I use a Select statement against one of the columns I could get 6 rows of data back.I need to update the table in order to add another row based on a particular value in the column in my select statement.I tried using this update statement:[i]update table_name set column_name = value where other_column_name = value[/i]My results were not as desired as it change all the other values for the column_name to the new value. Here is an example of my select statement on the other_column_name [i]before [/i]my update statement:column_name____other_column_name 0001_______________column50002_______________column50003_______________column50004_______________column50005_______________column50006_______________column5Here is the result of my update statement using a new value for column_name of '0007':column_name____other_column_name 0007_______________column50007_______________column50007_______________column50007_______________column50007_______________column50007_______________column5It makes sense what happened. I'm just not sure how to get the results I want which is:column_name____other_column_name 0001_______________column50002_______________column50003_______________column50004_______________column50005_______________column50006_______________column50007_______________column5Thanks for your help on this.

indexed views, aggregations and poor cardinality estimations

Posted: 21 May 2013 03:19 AM PDT

I've got a large sales table (66m rows) that I need to query in an aggregated way to return a bestseller list for specific dates and stores.I think i'm running into an issue relating to statistics and the distribution of values, but I'm not sure....Here is the structure of the tables involved in the query:tblSales table structure:[date_id] [int] NOT NULL,[store_id] [int] NOT NULL,[product_id] [bigint] NOT NULL,[volume_sold] [int] NOT NULL date_id is an int representing a specific date, store_id is the id of the store that made the sale, product_id is the id of the product that sold, volume_soldis the... volume sold of a specific product on a specific date in a specific store. Pretty straightforward.tblDates:date_id int,calendar_date datetime,period intcalendar_date is the actual date the date_id represents, period is an int that is used to group the date_ids into weeks (week 1 = period 1, week2 = period 2, etc.)[dbo].tblStores[dbo].[tblStores_To_Aggregates][dbo].[tblAggregates]these tables are used to assign groups of stores into aggregates that can then be queried on (ex: west coast aggregate, small stores aggregate, ALL aggregate, etc.)to speed up the query i've created an indexed view on the data like so:[code="sql"]selecttblDates.period,product_id,[tblAggregates].aggregate_id,SUM(volume_sold) as vol_sold,COUNT_BIG(*) AS FREQUENCYfrom[dbo].[tblSales] salesINNER JOIN [dbo].tblStores ON sales.store_id = tblStores.store_idINNER JOIN [dbo].[tblStores_To_Aggregates] ON tblStores.store_id = [tblStores_To_Aggregates].store_idINNER JOIN [dbo].[tblAggregates] ON [tblStores_To_Aggregates].aggregate_id = [tblAggregates].aggregate_idINNER JOIN [dbo].tblDates ON tblDates.date_id = sales.date_idgroup by[tblAggregates].aggregate_id,tblDates.period,product_id[/code]And then a clustered index on the view in the form:[aggregate_id] ASC,[period] ASC,[product_id] ASCAnd here is the query:[code="sql"]select top 1000 sales.product_id, SUM(volume_sold) as vol_sold, RANK() OVER (ORDER BY SUM(volume_sold) DESC) as product_rankFROM [dbo].[tblSales] sales INNER JOIN [dbo].tblStores ON sales.store_id = tblStores.store_id INNER JOIN [dbo].[tblStores_To_Aggregates] ON tblStores.store_id = [tblStores_To_Aggregates].store_id INNER JOIN [dbo].[tblAggregates] ON [tblStores_To_Aggregates].aggregate_id = [tblAggregates].aggregate_id INNER JOIN [dbo].tblDates ON tblDates.date_id = sales.date_id WHERE period >= 460 and period <= 470 and [tblAggregates].aggregate_id = 1 group by product_idorder by product_rank asc[/code]The issue comes when varying the aggregate_id. The aggregate could contain many stores, or only a few: aggregate_id 1 has 1878 stores. 2 has 93.When querying on id 2, the query returns in subsecond and 1 takes 30+ seconds.Looking at the query plan it seems like the issue in caused by a poor estimation of the amount of rows that will come back.The plan for 2 has an estimation that is close. But for 1 it's way off. This is causing it to spill to tempdb and slow down the query.My guess is that the problem is related to the statistics (they are up to date btw) and them giving poor cardinality estimations. I'm kind of at a loss at what to do next. Any advice or suggestions would be appreciated.

BIDS 2008 - unable to rename dtsx package

Posted: 21 May 2013 03:15 AM PDT

This strange thing is happening: I am creating a new package, always has to happen by starting from an existing one (because they all run in a framework and there are a lot of variables defined). So I import an existing package (from SQL storage on the production server), and when I try to rename it, it says "The process cannot access the file because it is being used by another process". I tried finding the file, anyhow, I finally rebooted. Now it displays that the new dtsx name "already exists in this project", and on subsequent attempts, it went back to "file is being used by another process". I tried closing everything, delete my projects, delete the backup folders in VS2008, reset all settings in VS2008. Finally, I went on a test server, and added the package there to a new project, and when I try to rename it, it does the same!! makes me believe there is something in MSDB on the production server, but I can't find anything (in sysssispackages)... any ideas? thanks!

recording changes to a table without creating multiple history tables

Posted: 21 May 2013 12:45 AM PDT

I'm curious if there is a built in way or a best practice that allows for recording changes in tables without having to create a history table for every table involved.I've seen several systems where there is one table that has columns for tablename, columnname, oldvalue, newvalue, transactiondate that are populated before update using a trigger. This way seems the way to go. Wondering if this is a handcoded thing for each system, or a common best practice that I could look at.

ORM Queries Prone to Excessive Blocking

Posted: 21 May 2013 02:01 AM PDT

Our application is using nHibernate as our ORM, and during some small load tests we've run I am noticing that the load test script is bogging down badly due to long waits and blocking. It mostly is affecting two tables (Alert and VehicleOfInterest - see create scripts below). These are the two queries. The INSERT into alert is grabbing a MISCELLANEOUS lock and is blocking the second statement below (a SELECT grabbing a shared lock):[code="sql"]INSERT INTO [Alert] (ModifiedDate, Type, CreatedDate, CreatedBy, SalespersonName, DupeDetectionRuleCriteria, OpportunityType, CustomerMatches, RemainingUsers, OrganizationId, AssignedToId, CustomerId, OpportunityId, SalesLeadId, VehicleOfInterestId, ActionId, NoteId, WantListId, ServiceLeadId, PartsLeadId, VehicleId, CompanyId, OrganizationTemplateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22); select SCOPE_IDENTITY()[/code] [code="sql"](@p0 bigint)select vehicleofi0_.Id as Id252_, vehicleofi0_.ModifiedDate as Modified2_252_, vehicleofi0_.Year as Year252_, vehicleofi0_.IsPrimary as IsPrimary252_, vehicleofi0_.Make as Make252_, vehicleofi0_.Model as Model252_, vehicleofi0_.Source as Source252_, vehicleofi0_.Vin as Vin252_, vehicleofi0_.Status as Status252_, vehicleofi0_.Type as Type252_, vehicleofi0_.OrganizationId as Organiz11_252_, vehicleofi0_.VehicleId as VehicleId252_, vehicleofi0_.SalesLeadId as SalesLe13_252_ from [VehicleOfInterest] vehicleofi0_ inner join [SalesLead] saleslead1_ on vehicleofi0_.SalesLeadId=saleslead1_.Id where saleslead1_.IsActive=1 and (saleslead1_.ClosedDate is null) and vehicleofi0_.VehicleId=@p0[/code] As a DBA, I have concerns with how nHibernate is hydrating the alert object with an Id (SCOPE_IDENTITY). However, it seems this is an extremely common method for populating an Id into a model. I've voiced my concerns with using SCOPE_IDENTITY, and we've found some other better options (high-low algorithm for instance), but these cannot be deployed immediately, as they are large refactors. I've created all necessary indexes, and the blocking is still prominent at low load on the hardware. Create Statement For Alert:[code="sql"]CREATE TABLE [dbo].[Alert]([Id] [bigint] NOT NULL IDENTITY(1, 1),[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[CreatedDate] [datetime] NOT NULL,[CreatedBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[SalespersonName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DupeDetectionRuleCriteria] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OpportunityType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CustomerMatches] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OrganizationId] [bigint] NOT NULL,[AssignedToId] [bigint] NOT NULL,[CustomerId] [bigint] NULL,[OpportunityId] [bigint] NULL,[SalesLeadId] [bigint] NULL,[VehicleOfInterestId] [bigint] NULL,[ActionId] [bigint] NULL,[NoteId] [bigint] NULL,[WantListId] [bigint] NULL,[ServiceLeadId] [bigint] NULL,[VehicleId] [bigint] NULL,[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ModifiedDate] [datetime] NULL CONSTRAINT [DF_Alert_ModifiedDate] DEFAULT (getutcdate()),[CompanyId] [bigint] NULL,[RemainingUsers] [int] NULL,[OrganizationTemplateId] [bigint] NULL,[PartsLeadId] [bigint] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [PK__Alert__3214EC071BFD2C07] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_OrganizationId_AssignedToId_Type] ON [dbo].[Alert] ([OrganizationId], [AssignedToId], [Type]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_AssignedToId] ON [dbo].[Alert] ([AssignedToId]) INCLUDE ([OrganizationId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_ActionId] ON [dbo].[Alert] ([ActionId]) INCLUDE ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_CustomerId] ON [dbo].[Alert] ([CustomerId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [OpportunityId], [SalesLeadId], [VehicleOfInterestId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Alert_VehicleOfInterestId] ON [dbo].[Alert] ([VehicleOfInterestId]) INCLUDE ([Id], [Type], [CreatedDate], [CreatedBy], [SalespersonName], [DupeDetectionRuleCriteria], [OpportunityType], [CustomerMatches], [OrganizationId], [AssignedToId], [CustomerId], [OpportunityId], [SalesLeadId], [ActionId], [NoteId], [WantListId], [ServiceLeadId], [VehicleId], [ModifiedDate], [CompanyId], [RemainingUsers], [OrganizationTemplateId], [PartsLeadId]) ON [PRIMARY]GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Action_ActionId_OrganizationEntityBaseId] FOREIGN KEY ([ActionId]) REFERENCES [dbo].[Action] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Company_CompanyId_OrganizationEntityBaseId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Customer_CustomerId_OrganizationEntityBaseId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Note_NoteId_OrganizationEntityBaseId] FOREIGN KEY ([NoteId]) REFERENCES [dbo].[Note] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Opportunity_OpportunityId_OrganizationEntityBaseId] FOREIGN KEY ([OpportunityId]) REFERENCES [dbo].[Opportunity] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_OrganizationTemplate_OrganizationTemplateId_OrganizationEntityBaseId] FOREIGN KEY ([OrganizationTemplateId]) REFERENCES [dbo].[OrganizationTemplate] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_PartsLead_PartsLeadId_LeadId] FOREIGN KEY ([PartsLeadId]) REFERENCES [dbo].[PartsLead] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Person_AssignedToId_OrganizationEntityBaseId] FOREIGN KEY ([AssignedToId]) REFERENCES [dbo].[Person] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_ServiceLead_ServiceLeadId_LeadId] FOREIGN KEY ([ServiceLeadId]) REFERENCES [dbo].[ServiceLead] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[Alert] WITH NOCHECK ADD CONSTRAINT [FK_Alert_VehicleOfInterest_VehicleOfInterestId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleOfInterestId]) REFERENCES [dbo].[VehicleOfInterest] ([Id])GOALTER TABLE [dbo].[Alert] ADD CONSTRAINT [FK_Alert_WantList_WantListId_OrganizationEntityBaseId] FOREIGN KEY ([WantListId]) REFERENCES [dbo].[WantList] ([Id])GO[/code]Create Script for Voi:[code="sql"]CREATE TABLE [dbo].[VehicleOfInterest]([Id] [bigint] NOT NULL IDENTITY(1, 1),[Year] [int] NULL,[IsPrimary] [bit] NOT NULL,[Make] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Model] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Source] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Status] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[OrganizationId] [bigint] NOT NULL,[VehicleId] [bigint] NULL,[SalesLeadId] [bigint] NOT NULL,[ExternalSourceId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ModifiedDate] [datetime] NULL CONSTRAINT [DF_VehicleOfInterest_ModifiedDate] DEFAULT (getutcdate()),[Vin] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [PK__VehicleO__3214EC07592635D8] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_IsPrimary] ON [dbo].[VehicleOfInterest] ([IsPrimary]) INCLUDE ([Year], [Make], [Model], [VehicleId], [SalesLeadId]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_SalesLeadId,IsPrimary_VehicleId] ON [dbo].[VehicleOfInterest] ([SalesLeadId], [IsPrimary], [VehicleId]) INCLUDE ([Year], [Make], [Model]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_VehicleOfInterest_VehicleId] ON [dbo].[VehicleOfInterest] ([VehicleId]) ON [PRIMARY]GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Organization_OrganizationId_EntityBaseId] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_SalesLead_SalesLeadId_LeadId] FOREIGN KEY ([SalesLeadId]) REFERENCES [dbo].[SalesLead] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] WITH NOCHECK ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_EntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GOALTER TABLE [dbo].[VehicleOfInterest] ADD CONSTRAINT [FK_VehicleOfInterest_Vehicle_VehicleId_OrganizationEntityBaseId] FOREIGN KEY ([VehicleId]) REFERENCES [dbo].[Vehicle] ([Id])GO[/code]Does anybody see any glaring issues here?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog