| Effect of indexes in SQL Server Performance Tuning Posted: 07 Jun 2013 03:23 PM PDT I have a couple of questions.a) What is the effect of indexes in SQL Server Performance Tuning?b) I have a SQL statement below, which is running slow. How to tune the query?Select col1, col2 from Table TWhere col2 = 'Sydney'Here col1 is the primary key, col2 is having a non clustered index on it. |
| DEFAULT Constraint syntax Posted: 06 Jan 2010 04:44 AM PST Hi guys,I'm writing few new tables and I'd like to keep all the constraints named according to my standards. I have no problems with indexes, checks etc...but with DEFAULTIn my table definition I haveCreate Table User(...ActiveFromDate DateTime Not Null,...Constraint [DF_CMS_User_ActiveFromDate] Default (GetDate()) For ActiveFromDate,...)Message I get is Msg 102, Level 15, State 1, Line 35Incorrect syntax near 'for'.I have scripted the constraint from previous table and I've got the following:ALTER TABLE [dbo].[CMS_User] ADD DEFAULT (getdate()) FOR [ActiveFromDate]GOThank you :) |
| Error Posted: 07 Jun 2013 04:23 PM PDT i am having this errorMsg 257, Level 16, State 3, Procedure query, Line 21Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.this is the line and red one is the column that i replace from 540CAST (ABS( t4.LTime - DATEDIFF (MINUTE, t.[Timein], t.[Timeout] ) ) % 60 as varchar ) + ' min' as excesshort, -- Minutesthis column t4.LTime datatype is datetimeplease help me out |
| Google Analytics SQL Import Posted: 08 May 2012 09:07 PM PDT Hi AllI was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS?A quick google brought up some C# scripts, a PHP web scraper and using the java client to export to CSV and then import it, but was wondering if anyone has managed to query the API and load it direct in SSIS to the DB?This was a task which was done a couple of years ago and I have been asked to re-ignite the flame to get it moving again and the previous way was to use xp_cmdshell to execute a file which loads it to CSV and imports it, which I have no trouble with, just wondering if anyone has a better solution.Thanks |
| Performace issue while updating records and trigger on table Posted: 04 Jun 2013 03:57 PM PDT Hi All,I am having a performance issue while updating records into sql server table,I have created below trigger to update related tables if the status of this table is updated/Changed.USE [EMEA_SERVICEMAX]GO/****** Object: Trigger [Servicemax].[ERP_ACCOUNT_DATE_UPDATE] Script Date: 06/05/2013 10:19:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER [Servicemax].[ERP_ACCOUNT_DATE_UPDATE]ON [EMEA_SERVICEMAX].[Servicemax].[ERP_ACCOUNT_STG]FOR UPDATEAS BEGINDECLARE @nOldValue varchar(20),@nNewValue varchar(20),@record_dt date,@CUST_NUM varchar(20),@SOURCE_SERVER varchar(15),@COMPANY_NO intSELECT @nOldValue=b.CUSTOMER_STATUS, @nNewValue=a.CUSTOMER_STATUS ,@record_dt=a.record_insert_dt,@CUST_NUM=a.customer_number,@SOURCE_SERVER = a.SOURCE_SERVER,@COMPANY_NO=a.COMPANY_NOFROM inserted a, deleted bIF @nNewValue = @nOldValuereturn else BEGINupdate EMEA_SERVICEMAX.Servicemax.SALES_ORDERS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.INSTALLATION_STGset record_insert_dt=@record_dtwhere LOCATION_CUSTOMER_NO=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.ERP_ACCOUNT_PROJECTS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.SERVICE_ORDER_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOENDENDGOAny help on this may be appreciated.Thanks,Nilesh |
| How to get the list of tables that has specific column name from linked server, available in SQL SERVER 2008 R2 Posted: 07 Jun 2013 01:15 AM PDT Hi,I have an access to oracle linked server called [oracleLS]it is there under the SERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES.I want to find out list of tables from these tables which has specific column called 'business address';I USED FOLLOWING ONE..BUT GIVES ME ERROR OF INVALID OBJECT. SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%PATTERN%';I AM ALSO WONDERING WHERE I HAVE TO SPECIFY THE LINKED SERVER NAME IN ABOVE QUERY!!I would also like to try another option if you have any idea!!Please help me.thanks. |
| Support UTF-8 character encoding Posted: 07 Jun 2013 07:49 AM PDT Does SQL server 2008 R2 support UTF-8 character encoding ?Thanks. |
| TVCs Posted: 07 Jun 2013 06:51 AM PDT Table Value Constructors.... I've been trying to do some research and some tests and I haven't come across anything that tells me why I should use a TVC over anything else I normally use in T-SQL constructs.Can anyone tell me what, if any, benefits or uses TVCs fulfill aside from being able to specify multiple value row constructors for Merge and Insert statements? I mean when would you specifically use a TVC over another SQL object?Erin |
| Querying sys.dm_db_partition_stats instead of sys.sp_spaceused Posted: 04 Jun 2013 10:24 PM PDT We have a daily process that saves into a table the storage info returned by the SP, but it's procedural, slow, and requires manipulating the character output to get just the numbers. After searching your forums, I've found that the SP references the DMV above.For a table with a clustered index and no non-clustered indexes, it's very easy to match the output from each; for a table that has non-clustered index(es), it seems more complicated. Although we currently don't use multiple partitions, I suppose that might be a further complication?Just wondering if anyone has already written a query using the DMV to get the same (single line) output that the SP does (without the "KB" characters)?Thanks,~ Jeff |
| How to output multiple columns one column depending on its values Posted: 07 Jun 2013 05:57 AM PDT Hi, I have a table where I have amount and rank columns.It is basically an approval workflow. there can be several approvers for one area/unit where the level is he rank.I need to know a way of getting one column for each approving level.For example, for the CID 25756Q, I need to display the approvers as shown below.CID ID1 Amt1 ID2 Amt2 ID3 Amt3 ID4 Amt4 ID5 Amt5 ID6 Amt6 ID7 Amt725756Q 12345A 0.1 12345B 0.2 12345C 0.3 12345D 0.4 12345E 0.5 12345F 0.6 12345G 0.7This is the DDLDrop table ApproverTCREATE TABLE [dbo].[ApproverT]( [CID] [varchar](6) NULL, [ID] [char](6) NULL, [Amount] [real] NULL, [Rank] [int] NULL) Insert into ApproverT (CID, ID, Amount, Rank) Select '25756Q', '12345A', 0.10, 1 UNION ALL Select '25756Q', '12345B', 0.20, 2 UNION ALL Select '25756Q', '12345C', 0.30, 3 UNION ALL Select '25756Q', '12345D', 0.40, 4 UNION ALL Select '25756Q', '12345E', 0.50, 5 UNION ALL Select '25756Q', '12345F', 0.60, 6 UNION ALL Select '25756Q', '12345G', 0.70, 7 UNION ALL Select '93512X', '123456', 0.01, 1 UNION ALL Select '91142A', '654321', 0.10, 1 UNION ALL Select '91142A', '963852', 1.00, 2 I will appreciate any help on this.Thanks, |
| Certification Question: You need to ensure the successful recovery of any single database from a catastrophic failure without requiring backup data center in a different location Posted: 06 Jun 2013 10:04 PM PDT You need to ensure the successful recovery of any single database from a catastrophic failure without requiring backup data center in a different location.which three tasks should you include?(each correct answer presents part of the solution. choose three) A. Store all backup media offsiteB. Script SQL Login accounts and credentialsC. Install all SQL Server instances on a failover cluster.D. Maintain one list of all Windows logins and Passwords.E. Document the administrative processes and application access requirements.A, B AND E ARE THE CORRECT ANSWERS. BUT ARE THOSE REALLY THE MOST CORRECT ANSWERS? |
| linked server issue Posted: 07 Jun 2013 03:08 AM PDT Hello, I have setup a linked server between SQL Server 2008 R2 and MAS90 server and I am able to select data and see the list of mas90 tables. I am using a sql agent job to pull data from mas90 to sql server database through the linked server.The issue here is whenever a new account is added to MAS90 database the sql agent job which tries to pull data from MAS90 to SQL Server database gets hung. So the data that I am trying to pull from mas90 to sql database wont appear in the sql server database. Everytime this happens if I restart the sql server services and restart the sql agent job then it works fine. Experts I need your advice on how to fix this as this is happening quite frequently and I cannot afford to restart the sql server services everytime a new account is added. Does it have to do anything with the local DTC property settings on the SQL Server database server side? I have attached the current msdtc settings jpg file with this post. Thanks |
| Service Broker Question - End Conversation command Posted: 06 Jun 2013 10:00 PM PDT Does "END CONVERSATION" send a message back to the actual queues?The reason I'm asking is due to some code I've been looking through. There's doesn't seem to be a normal reply message to send a message back to the initiator, but a stored procedure linked to the initiator queue is definitely being triggered somehow.I've even looked through a trace and the stored procedure linked to the initiator queue is started right after the END CONVERSATION command and the CLOSED Broker:Conversation. |
| Invoke or BeginInvoke cannot be called on a control until the window handle has been created Posted: 04 Nov 2009 06:44 AM PST HiI am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the installer for a standalone installation and this works fine for the setup support rules bit. That passes ok but then I get this error:TITLE: SQL Server Setup failure.------------------------------SQL Server Setup has encountered the following error:Invoke or BeginInvoke cannot be called on a control until the window handle has been created..------------------------------BUTTONS:OK------------------------------How can I resolve this? I am getting 2008 R2 CTP but this is just a CTP. My download is from DreamSpark. |
| SQL Server Transactional Replication Posted: 06 Jun 2013 10:27 PM PDT We did a performance audit on the system and the reports revealed we should change the Clustered index (the primary key of the table) to another index, but kept the primary key on the original column. We ran the snapshot , but we niticed that the tabke of which we changed the primary key , does not get populated , althiugh the snapshot shows that records were bulkcopied into the table . Any ideas ? |
| Using the same partition function and scheme for multiple tables Posted: 04 Jun 2013 02:58 PM PDT I have a database where most of the tables have an integer field say "SetId" denoting a batch of data.If a new SetId is created all these tables will get data related to the new SetId.SetId governs data retention, purging, it also forms part of filtering in many user queries on these tables.I am thinking of partitioning all these tables on the SetID column, which I am sure would be beneficial for performance and data maintenance.My question is, do I use a common partition function and partition scheme to partition all the tables on SetId, or is it more advisable to use separate partition functions and schemes for each tables? |
| Profiler shows long duration for sp but actually it is not Posted: 06 Jun 2013 10:43 PM PDT HiI have a sp which is timing out on my website. When i run a trace on it, duration comes ~ 80000 (ms)So it times out as it is set 30s for site. but when I run the same call after copying from trace in SSMS, it hardly takes 2-3 secs. Why profiler show long durations?I tries same thing for 5-10 calls to be sure that it is not because of cached data. but they are always fast on SSMS.Is duration column in profiler accounts for Network time or any other times in it? |
| prevent simultaneous update Posted: 06 Jun 2013 11:14 PM PDT HiI have a table where multiple Agents can assign tasks to themselves. So I basically pull the minimum TaskId which is not assigned using SELECT statement and then RUN an update to assign that task to Agent.Below is the statementUPDATE TOP(1) TaskTable SET TaskStatus = 2 , TaskOwner = @UserID WHERE TaskID = (SELECT MIN(TaskID) FROM TaskTable WHERE Assigned=0) Now there is possibility that 2 agents clicked simultaneously to assign tasks, both pulled same taskID from SELECT but as updated can't be simultaneous, so update of one agent will be overwritten by other.How to prevent it? |
| Error converting Varchar to Float Posted: 06 Jun 2013 11:38 PM PDT Hi All,I am trying to use the query below but receive the error message "Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float."Query :[b]select w.casecode,w.category,w.desc1,w.weightage,w.WACC,w.Goal_Weighing,f.Factorfrom [dbo].[Final] as w inner join[dbo].[Final01] as fon w.casecode = f.casecode[/b]I am surprised because this query is used to create a view. This view was not giving me any error message since last 1 week but suddenly today I pressed button to see results of this view and received this error message. Please help! |
| how to archive data based on on archive it self? Posted: 28 May 2013 11:36 PM PDT HiI have an archive tabel in Oracle. We copy this to a ms sql 2008 r2 database for datawarehouse purpose.The problem is that it only contains 90 days of records. I now need to archive this archive table. :-DThere is no unique key in the table. It log our load_id but one id may come several times a day even several days.I need to take a copy of the archive as is, and each day make it run thru the new transfer from oracle to figure out what has cahnged and add those lines to my copy.How to solve this the best way?BRDan |
| DROP PK on highly called big table Posted: 02 Jun 2013 08:05 PM PDT HII have a table with 80 million records with PK on an int column. A service selects data from this table multiple times/sec in live environment.I have to change the datetype of this PK column from int to BigInt. So I need to drop the PK constraint. But when I run the ALTER TABLE statement to drop it, it gets blocked by Select statements (they have NOLOCK in them). So i think it will be never ending wait.Is there any other better way to achieve it?Can I put some lock on table while altering it so that Select statements just wait for their turn but son't block the ALTER statement?thanks |
| Transposing a table from multicolumn to key-value Posted: 06 Jun 2013 09:56 PM PDT I have a transactional table with 100+ columns and each row is uniquely identified by an id column. For a data export requirement, we need to make export the table flat - aka, id/column_name/column_value format. the number of columns are dynamic in nature. By this, I mean : there could be 10 additional columns in near future. Some columns might get dropped as well. This is another reason for exporting as a name/value pair.A dynamic pivoting might be the answer - I tried using dynamic pivoting using xmlpath/cross apply and made things complex enough to get a cross joined resultset. Here is the sample schema (input/output) with sample data:http://sqlfiddle.com/#!3/f0efa/7Any help is highly appreciated. |
| Division problem in SQL Posted: 06 Jun 2013 08:38 PM PDT Hello All,Can anyone please explain why do we always get 0 when we divide 1 by any other larger number.select 1/2 gives me 0.Thank you for your help |
