SQLServerCentral / SQL Server 2005 / SQL Server 2005 General Discussion

Showing all the months in a year

Sachin 80451 — Wed, 23 May 2012 05:03:14 GMT

I have the following scripts which gets me all the data from a table. But I want to shows months such as December and January which currently dont have any data against them but want to show them in a report.SELECT statecodename [Status], count(statecodename) [Count], CASE WHEN tpl_renewalmonthname IS NULL THEN 'Unknown' ELSE tpl_renewalmonthname END as [Month], CASE WHEN tpl_renewalmonth IS NULL THEN '13' ELSE tpl_renewalmonth END as [Monthnumber]FROMCRMV2_MSCRM.dbo.FilteredOpportunityGROUP BY statecodename,tpl_renewalmonthname,tpl_renewalmonthORDER BY 4 ascThis give me the following results:Status Count Month MonthnumberOpen 1 February 2Open 7 March 3Won 6 March 3Won 7 April 4Open 6 April 4Open 12 May 5Won 5 May 5Won 3 June 6Lost 1 June 6Open 10 June 6Open 8 July 7Won 2 July 7Won 5 August 8Open 5 August 8Lost 1 August 8Open 4 September 9Won 1 September 9Won 1 October 10Open 2 October 10Open 1 November 11Won 3 Unknown 13Lost 1 Unknown 13

SQL Server Failed to Listen on IP Any

kennethigiri — Wed, 23 May 2012 14:48:46 GMT

We had the following issue today:Application faile dto connect to the DB. An attempt was made to restart the SQL Server Service but failed.The service was eventually started using the Local System account then the original service account was added to the administrators group and used to start te service.On examining the logs, we found the following ke errors:[font="Courier New"]05/23/2012 13:26:56,Server,Unknown,SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.,05/23/2012 13:26:56,Server,Unknown,Could not start the network library because of an internal error in the network library. To determine the cause<c/> review the errors immediately preceding this one in the error log.,05/23/2012 13:26:56,Server,Unknown,TDSSNIClient initialization failed with error 0x2747<c/> status code 0xa.,05/23/2012 13:26:56,Server,Unknown,TDSSNIClient initialization failed with error 0x2747<c/> status code 0x1.,05/23/2012 13:26:56,Server,Unknown,Server failed to listen on 'any' <ipv4> ****. Error: 0x2747. To proceed<c/> notify your system administrator.,[/font]Does anyone know what this all means. My search is pointing to sometin having to do with Named Pipes but it is just not clear.

Cant see the back up file when restoring the database

srik.kotte — Wed, 23 May 2012 14:03:55 GMT

Hello All,I am trying to restore the database. I have saved the back up file in different server.When I am trying to browse to the folder where tha back up file is during restore, I could see the folder where I saved it but when I expand the node of the folder I couldn't see the back up file.The folder I saved the back up file is a shared one and service account has full permissions to the folder.I am not getting what the issue is. Please help.I am working 2005 version.Thanks

IF EXISTS with LINKED SERVER failures.

drgn38 — Tue, 08 May 2012 12:47:28 GMT

Hello All,I have taken it upon my self to build a client configuration database for my company. This is meant to be an automated discover process where all you need to do is create a linked server and this database and jobs will poll the linked server to import any relevant databases along with various information about the database and client. 1 particular piece of information I am trying to capture from each database is the version of the application. I am running into an error with the query below as the table does not exist but it seems SQL is still trying to validate the query even though the table doesn't exist. If I remove the query and place a print statement I get the else print statement of "Doesn't Exist".IF EXISTS (SELECT 1 FROM MSVSQL04.M_Test_DB.sys.Objects WHERE Name = 'DBREGISTRY' and type = 'U') BEGIN SELECT LEFT(Value, CHARINDEX(' (',Value)-1) AS [Version] FROM MSVSQL04.M_Test_DB.dbo.DBRegistry WHERE Label = 'Version' ENDELSE PRINT 'Doesnt Exist'Here is the error I am getting. The OLE DB provider "SQLNCLI10" for linked server "MSVSQL04" does not contain the table ""M_Test_DB"."dbo"."DBRegistry"". The table either does not exist or the current user does not have permissions on that table.Well its exactly right the table does not exist my only guess is SQL is still trying to validate but not execute. Has anyone run into this before?Any help on this would be greatly appreciated.Thank you,Jeremy

Error while checking the back up file corrupted or not

srik.kotte — Wed, 23 May 2012 11:05:02 GMT

I am working on Microsoft sql server 2005 version. I am trying to restore the database from the back up files stored in different server. I am getting error when trying to restore.During debugging process I was thinking to check the back up file whether it is corrupted or not by using RESTORE VERIFYONLY FROM DISK = '\\ServerName\FromINTLQ01\043112\POSTEOM_Service.bak'But I am getting an error as Cannot open backup device 'E:\FromINTLQ01\043112\POSTEOM_Service.bak'. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1VERIFY DATABASE is terminating abnormally. But I can navigate to the path mentioned and see the back up file. I chedcked all the permissions and I am logged in as windows authentication and I have full permissions to the folder mentioned. Please help.

Unable to open management studio 2005

sreedevi_c — Tue, 22 May 2012 23:27:12 GMT

Hi,I am unable to open SQL Server Management Studio.The following error message is encountered..."Package 'Microsoft SQL Management Studio Package' failed to load."--When i login to the server as an administrator and try opening management studio,it works fine but when i login as a normal windows user and try opening management studio ,i get the above error message.Please help ASAP....

Inserting from 1 table to another with no logging

Tom West — Tue, 14 Jul 2009 04:27:24 GMT

Hi,I have a server which has run out of space. Basically one of the tables has new been archived, so is taking up 60% of the drive. What i need to do is to copy out the data from the table into a new table, to allow me to drop the existing table.However, the problem is that there is only enough room on the drive for the data in the new table - which leaves no space for the log to grow (I have already shrunk this to 10 mg and deleted all indexes except 1 that is required).My question is can I do an insert from the existing table to the new one as this has minimal logging. I don't know whether the SELECT * INTO could be used or whether it is possible to do a bulk insert from 1 table to another? Is there a table hint or something similar that I can apply to turn off the logging?Any pointers would be appreciated.Thanks in advance.

SQL 2005 - Encrypting column data while storing in tables

Suresh Kumar-284278 — Mon, 21 May 2012 04:21:00 GMT

Hi All,I have been recently requested to help for encrypting token and payment data in some in way in the payment table (SQL 2005 DB)Is there a default way in SQL Server in which we can configure the database columns as encrypted (or hashed) so that automatically the database encrypts/decrypts (or hashes) it when inserting and/or reading from a table? Thank you in advance.Regards,Suresh

Running Balance

noblepaulaziz — Mon, 21 May 2012 20:08:27 GMT

Hi FolksI have a double entry transaction table named trans which accumulates both debit and credit transactions. The structure of the table is shown below.[accountcredited][accountdbited][amount][dateoccurred]The trans table is linked to accounts table whose structure is shown below[accountno][accountname][balance]; i.e the current balance each account.I am able to calculate the current balance for each account.My problem is how generate running balances for each account like bank transaction showing the transactions that gave each account its balance to date.I cannot figure out how to handle this problem.Any assistance would greatly appreciated.

SSIS Package error

Mark Clarke — Tue, 22 May 2012 05:27:32 GMT

I am troubleshooting a VS 2005 SSIS project and am hitting a connectivity issue.The server is Windows 2003 R2 Standard Edition SP2SQL Server is Standard Edition v9.00.1399.06SSIS is v9.00.1399.00VS is v8.0.50727.42 with .Net Framework v2.0.50727 SP2The problem is the package runs without error in VS when ProtectionLevel is set to EncryptSensitiveWithUserKey.The package fails in VS if the ProtectionLevel is set to DontSaveSensitive with a "Communication Link Failure" and "Shared Memory Provider: No process is on the other end of the pipe"There is a Stored Procedure on the server to execute SSIS file packages and when using this to run the package from SQL Server the package fails with the error "Communication Link Failure" and "Shared Memory Provider: No process is on the other end of the pipe" if the ProtectionLevel is DontSaveSensitive and the package fails with the error "Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state." when the ProtectionLevel is set to EncryptSensitiveWithUserKey. This occurs if the connection to SQL Server is either a SQL Login or the same windows user that has created the package.I am a bit stumped as to how to overcome this issue, any suggestions greatfully received.Thanks

SQL 2005 on vSphere 5.0 drop connections

adamjwchen — Tue, 22 May 2012 03:11:31 GMT

Hi, We just have a new SQL 2005 server setup on vmware 5.0. But we found out the new server dropped connections in random. Is this caused by vmware or any improper configuration?

How to limit the number of processor SQL servur uses.

egpotus — Mon, 30 Mar 2009 03:18:26 GMT

Hi,Is it possible to tell SQL server to use only 4 processors out of 8 ?I would like to dedicate 1 SQL server instance to processor 0 to 3 and a Second SQL server instance to processor 4 to 7, is it possible?Can I use affinity mask Option ?regards

SSIS package hangs connecting to flat file or row count task

j.bluestein — Mon, 21 May 2012 13:28:18 GMT

Hello SQL server colleagues! I have 2 SQL server 2005 SSIS packages that hang intermittently doing the same tasks. When successful, the packages run in 1 minute. There's a flat file connection and a row count task in the data flow.Both packages are known to hang only on Sat. morning at ~1AM. There's nothing in the log to indicate a server issue at that time. We're considering trapping/ending the job when this occurs with an onprogrees event task. I would like your advice: 1) are there any known SSIS task hang problems I should be aware of? 2) do you think trap & error-ing this process when this runs > 10 minutes is feasible? 3) If so, is the onprogress event the best to use? I'm thinking if the package runs > 10 minutes, raiserror. Rerun the job....thanks in advance,

SQL Server fails to start after creating Server Principle Names

rstringer — Thu, 10 May 2012 12:24:52 GMT

Hello,I am trying to implement Kerberos authentication between our BizTalk servers and a 2-node SQL 2005 cluster.1. 8 Server Principle Names (SPN's) are successfully created.2. SQL taken offline in the cluster (one node at a time).3. Attempt to restart SQL from within the cluster. This fails because of Error 18456, Sev. 14, State 16 and sometimes state 11). State 11 means login is valid but server access failed. We get the login failed error for both the account running SQL and a different account running BizTalk.How do I find out the root cause of the server access failure?Also, we verified that the account and passwords are valid by using them to logon to another server.If we reset the passwords, SQL still fails to start.If the SPN's are removed and we wait about an hour, presumably for domain replication to complete, the SQL will finally start normally.Thanks in advance,DetRich

Summing in a table

Sachin 80451 — Thu, 17 May 2012 09:24:34 GMT

I need to work out the percentage of the values from sales. I have split my data into the necessary group like:status Total ALLTOTAL1. On Hold 29 ?2. Attempting to Contact 3 3. In dialogue with customer 1 4. 1st Appointment made 1 9. Quoted 15 Im trying to get the all total. I have basically got the total by counting status. But want to get the ALLTOTAL column to be 49 all the way down. Is there a way of doing this?

TADOQuery Error While Executing The SQL Second Time

vishualsoft — Sat, 05 May 2012 04:45:21 GMT

Hi All, I am getting a strange behavior by TADOQuery component in Delphi7 on Windows XP and Windows7 operating system. Following issue works fine on Windows7 oprating system, but it gives an error on XP operating system. Let me explain the schenario, I have one TADOConnection component and two TADOQuery components. The TADOConnection component is having the value for "ConnectionString" property as follows: ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User ID; Data Source = My Data Source'; I have already applied the role/granted permission to the User ID/Password which I am login to the application. I have connected TADOConnection component to the TADOQuery components using "Connection" property. When I execute the first sql then it works fine without any error, but when I execute the second sql that time I am getting following error on Windows XP oprating system. [Microsoft] [SQL Server Native Client 10.0] [SQL Server] The SELECT permission was denied on the object 'My Table Name', database 'My Database Name', schema 'dbo' The same code if I execute on Windows7 oprating system it works fine without any error. Could anybody put focus what could be missing or what could be the issue ? ------------------------------------------------------------------------------------------------------------------------ I have added "Persist Security Info = False;" to a connection string as follows: ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Persist Security Info = False; Password = My Password; User ID = My User ID; Data Source = My Data Source'; Now, whenever I execute the sql using TADOQuery component, I need to write following statement every time, even if I have already set the "Connection" property of TADOQuery at the beginning, still I need to set this property whenever I execute the sql. ADOQuery1.Connection := ADOConnection1; But as I said, On Windows7 there is no need to change the "ConnectionString" property of TADOConnection component and no need to set the "Connection" property of any TADOQuery component. Could anybody tell what exactly has to be done ? If your application is too hugh and lot of places coding changes are required, would anybody agree with that ? And what about testing efforts ? At all the places testing has to be done, right ? So, I am still in search of proper solution. The question is still unanswered. If anybody is having any clue or hint or proper solution that would be highly appreciated. Thanks In Advance. With Best Regards.

.ndf already in use by tempdb?

acorrei1 — Fri, 18 May 2012 06:43:18 GMT

I'm trying to add a secondary data file to temp to help with performance but it's coming with an error:[b][i]The file 'X:\xxxx\xxxxx.ndf' cannot be overwritten. It is being used by database 'tempdb'.[/i][/b]Is there some cleanup that needs to be done somewhere or do I HAVE to create the .ndf under a different name?

Stored Procedure to change filename

dan_g — Fri, 18 May 2012 10:05:17 GMT

Hi, Newbie here, have 2 questions. I dont want to use C or other programs just stored procedure, in SQL Server 2005...Q1. I want to write a stored procedure to change the filename. The file currently comes to the server that includes the timestamp.For example: The file lands on E:\Folder1\Filename_yyyymmdd_hhmmss.txtand I want it to be renamed to E:\Folder1\Filename_yyyymmdd.txtQ2. The text file has a pipe delimited first row header that is something like "HEADER|yyyymmdd|nnrows"Can I copy the yyyymmdd from the header row to the filename? using the Stored procedureThanksDan

EXECUTE stored procedure question

mldardy — Fri, 18 May 2012 11:25:41 GMT

I want to know if and how I would EXECUTE a stored procedure where one of the parameters I can put in multiple id like below. I want to put multiple id's where it says 'v_intGroupID =' Is that possible to do that where it says 'v_intGroupID = 136064, 122244, 122222', etc.[code="sql"]USE [TESales]GODECLARE @return_value int, @o_bitErrorFound bitEXEC @return_value = [dbo].[prGroup_delGroup] @v_intGroupID = 136064, @v_intChangeUserID = 386631, @v_strChangeRole = N'0A', @v_intChangeGroupID = 24870, @o_bitErrorFound = @o_bitErrorFound OUTPUTSELECT @o_bitErrorFound as N'@o_bitErrorFound'SELECT 'Return Value' = @return_valueGO[/code]

Tempdb Problem

adrian.sudirgo — Thu, 17 May 2012 03:40:06 GMT

Hi,I am using SQL Server 2005 and .NET applications on mys erver which has been running fine for 3 years. Suddenly yesterday I got notification low disk on my primary C drive. It turned out that my tempdb.mdf grew very large up to 10 GB. We did nothing within these few days (meaning we didn't make any changes to the settings, servers, or applciations)To solve this tempdb problem, this is what I have done:1. Restarted the SQL Server (MSSQLSERVER) from Services. After doing so, the tempdb is back to normal which is 200MB. But within 1 hour it grew back to 10 GB!2. I set the Maximum File Size for the tempdb to 3000 MB, then restarted the SQL Server. Well, this time the tempdb.mdf still grew very fast from 200MB to 3000MB and stop. But another problem arises: it causes error which is then logged into ERRORLOG. Thus, this ERRORLOG file grew up to 8 GB within a few hours.Because those 2 attemps failed, I tried to find out what causes the tempdb to behave this way. This is what I did:- I stopped all my .NET windows applications- I stopped all FTP Sites, Application Pools, Web Sites and Web Service Extensions from the Internet Information Services (IIS) Manager- Then I restarted the SQL Server from ServicesBut the thing is, the tempdb.mdf still grows very fast from 200MB to 10 GB!I run a trace in SQL Server Profiler, and it shows that there is no activity at all!So I can conclude that the this tempdb problem is not caused by any of the applications.I tried to run this T-SQL:[code="sql"]SELECT t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handleFROM (Select session_id, request_id, SUM(internal_objects_alloc_page_count) AS task_alloc, SUM (internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS t1, sys.dm_exec_requests AS t2WHERE t1.session_id = t2.session_id AND (t1.request_id = t2.request_id)ORDER BY t1.task_alloc DESC[/code](Sorry, I could not find a way to paste table here, so I just summarized the important information)And from the result returned only 3 of them that has values in task_alloc:session_id 12 has task_alloc 24744session_id 14 has task_alloc 360session_id 20 has task_alloc 112Then I ran this command:[code="sql"]select * from Sys.dm_exec_requests[/code]Then this is what I got for those 3 session ids:session_id: 12status: backgroundcommand: BRKR EVENT HNDLRsql_handle: NULLstatement_start_offset: NULLstatement_end_offset: NULLplan_handle: NULLdatabase_id: 1user_id: 1connection_id: NULLblocking_session_id: 0wait_type: BROKER_EVENTHANDLERwait_time: 1046last_wait_type: BROKER_EVENTHANDLERwait_resource: open_transaction_count: 0open_resultset_count: 1transaction_id: 0context_info: NULLpercent_complete: 0estimated_completion_time: 0cpu_time: 60718total_elapsed_time: 0scheduler_id: 0task_address: 0x006D87A8reads: 32writes: 30424logical_reads: 8433857text_size: 4096language: us_englishdate_format: mdydate_first: 7quoted_identifier: 1arithabort: 0ansi_null_dflt_on: 1ansi_defaults: 0ansi_warnings: 1ansi_padding: 1ansi_nulls: 1concat_null_yields_null: 1transaction_isolation_level: 2lock_timeout: -1deadlock_priority: 0row_count: 0prev_error: 0nest_level: 1granted_query_memory: 0executing_managed_code: 0session_id: 14status: backgroundcommand: BRKR TASKsql_handle: NULLstatement_start_offset: NULLstatement_end_offset: NULLplan_handle: NULLdatabase_id: 1user_id: 1connection_id: NULLblocking_session_id: 0wait_type: NULLwait_time: 0last_wait_type: LATCH_EXwait_resource: SERVICE_BROKER_TRANSMISSION_WORKTABLE (00000000)open_transaction_count: 0open_resultset_count: 1transaction_id: 0context_info: NULLpercent_complete: 0estimated_completion_time: 0cpu_time: 2532total_elapsed_time: 0scheduler_id: 1task_address: 0x008CC988reads: 0writes: 0logical_reads: 52584text_size: 4096language: us_englishdate_format: mdydate_first: 7quoted_identifier: 1arithabort: 0ansi_null_dflt_on: 1ansi_defaults: 0ansi_warnings: 1ansi_padding: 1ansi_nulls: 1concat_null_yields_null: 1transaction_isolation_level: 2lock_timeout: -1deadlock_priority: 0row_count: 0prev_error: 0nest_level: 1granted_query_memory: 0executing_managed_code: 0session_id: 20status: backgroundcommand: BRKR TASKsql_handle: NULLstatement_start_offset: NULLstatement_end_offset: NULLplan_handle: NULLdatabase_id: 5user_id: 1connection_id: NULLblocking_session_id: 14wait_type: LATCH_SHwait_time: 0last_wait_type: LATCH_SHwait_resource: SERVICE_BROKER_TRANSMISSION_WORKTABLE (02A06750)open_transaction_count: 0open_resultset_count: 1transaction_id: 0context_info: NULLpercent_complete: 0estimated_completion_time: 0cpu_time: 266625total_elapsed_time: 0scheduler_id: 0task_address: 0x006D8898reads: 108523writes: 132logical_reads: 10875333text_size: 4096language: us_englishdate_format: mdydate_first: 7quoted_identifier: 1arithabort: 0ansi_null_dflt_on: 1ansi_defaults: 0ansi_warnings: 1ansi_padding: 1ansi_nulls: 1concat_null_yields_null: 1transaction_isolation_level: 2lock_timeout: -1deadlock_priority: 0row_count: 0prev_error: 0nest_level: 1granted_query_memory: 0executing_managed_code: 0I noticed that the cpu time, reads, writes and logical reads are high for those 3 session_ids.But I don't have any ideas what are those 3 transactions. Are those the ones causing my tempdb.mdf to grow large out of control?Have any of you experiencing the same problems? Any suggestions or ideas?Any help is appreciated.Thank you!Adrian

Reg: Migration of Crystal reports to Sql 2005 Reporting Services

pavan.sunkara — Fri, 14 Dec 2007 21:33:39 GMT

Hi,i am working with web based ERP which was developed in .NET, and the reports we generated for our clients are by crystal reports.....Now we want to migrate crystal reports to SQL server 2005 reporting service reports.Right now i am using the Stored Procedures whicch are used in crystal reports for generating reports through Reporting Services.... is there any alternate way to directly migrate reports from Crystal reports to Reporting service reportsKindly help me in this

Management Studio message: "This file has been modified outside of the source editor"

Tracy Romito — Thu, 03 May 2007 14:02:00 GMT

When working in a .sql file inside of the editor for Management Studio, Irepeatedly receive the following message (file resides on a network drivethat only I use):“This file has been modified outside of the source editor. Do you want toreload it and lose the changes made in the source editor?”I cannot find a way to disable this feature. Any assistance would be greatlyappreciated. No one else is using the file.thank you,Tracy

SQL 2005 trigger some time not runing

nguyenhoang 18993 — Thu, 17 May 2012 03:50:43 GMT

Hi all.I'm working with sql 2005, but my trigger some time not work. help me please!My trigger:Create Trigger ChungTuKho_ArchiveLogTrg On ChungTuKhoWith EncryptionAfter Insert,Update,Delete As Declare @v_DuLieu XML; If Exists (Select null From Inserted ChungTuKho Where isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho') Begin Select @v_DuLieu = (Select MaPK,MaLoaiChiPhiKho,KyHieuHoaDon,SoHoaDon,NgayHoaDon,SoChungTu,NgayHachToan From Inserted As ChungTuKho Where isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho' FOR XML AUTO, ELEMENTS); Insert TruyenNhan.dbo.ArchiveLog (MaTruyenNhan,DBname,TenTable,DuLieu) Select A.Ma,DB_NAME(),'ChungTuKho',@v_DuLieu From TruyenNhan.dbo.TruyenNhan A,TruyenNhan.dbo.DanhSachTable B Where A.DatabaseTruyen = DB_NAME() And B.DBName = DB_NAME() And A.InstanceTruyen = 1 And A.Ma = B.MaTruyenNhan And B.TenTable = 'ChungTuKho' And @v_DuLieu is not null; Insert TruyenNhan.dbo.DongBoLog (MaTruyenNhan,DBName,TenTable,MaPKExp) Select B.Ma,'TasecoTH','ChungTuKho',ChungTuKho.MaPK From Inserted ChungTuKho,TruyenNhan.dbo.TruyenNhan B,TruyenNhan.dbo.DanhSachTable C Where B.DatabaseTruyen = 'TasecoTH' And B.InstanceTruyen = 1 And B.Ma = C.MaTruyenNhan And C.DBName = DB_NAME() And isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho' And C.TenTable = 'ChungTuKho' And Not Exists (Select Null From TruyenNhan.dbo.DongBoLog Where TenTable = 'ChungTuKho' And MaPKExp = Cast(ChungTuKho.MaPK as varchar) And MaTruyenNhan = C.MaTruyenNhan And NhanVe = 0 And DBName = DB_NAME()); End; Else If Not Exists (Select Null From Inserted) Begin Update TruyenNhan.dbo.DongBoLog Set Xoa = 1, SoThuTuXoa = isNull(D.SoThuTuXoa,1) From Deleted ChungTuKho, TruyenNhan.dbo.TruyenNhan B, TruyenNhan.dbo.DanhSachTable C Left join (Select isNull(Max(SoThuTuXoa),0) + 1 SoThuTuXoa,MaTruyenNhan From TruyenNhan.dbo.DongBoLog Where DBName = DB_NAME() And Xoa = 1 Group by MaTruyenNhan) D On D.MaTruyenNhan = C.MaTruyenNhan Where TruyenNhan.dbo.DongBoLog.TenTable = 'ChungTuKho' And TruyenNhan.dbo.DongBoLog.MaPKExp = Cast(ChungTuKho.MaPK as varchar) And B.DatabaseTruyen = DB_NAME() And B.InstanceTruyen = 1 And C.MaTruyenNhan = B.Ma And C.TenTable = 'ChungTuKho' And TruyenNhan.dbo.DongBoLog.NhanVe = 0 And C.DBName = DB_NAME() And TruyenNhan.dbo.DongBoLog.MaTruyenNhan = B.Ma; Insert TruyenNhan.dbo.DongBoLog (MaTruyenNhan,DBName,TenTable,MaPKExp,Xoa,SoThuTuXoa) Select B.Ma,'TasecoTH','ChungTuKho',ChungTuKho.MaPK,1,isNull(D.SoThuTuXoa,1) From Deleted ChungTuKho, TruyenNhan.dbo.TruyenNhan B, TruyenNhan.dbo.DanhSachTable C Left join (Select isNull(Max(SoThuTuXoa),0) + 1 SoThuTuXoa,MaTruyenNhan From TruyenNhan.dbo.DongBoLog Where DBName = DB_NAME() And Xoa = 1 Group by MaTruyenNhan) D On D.MaTruyenNhan = C.MaTruyenNhan Where B.DatabaseTruyen = 'TasecoTH' And B.InstanceTruyen = 1 And B.Ma = C.MaTruyenNhan And C.DBName = DB_NAME() And D.MaTruyenNhan = C.MaTruyenNhan And isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho' And C.TenTable = 'ChungTuKho' And Not Exists (Select Null From TruyenNhan.dbo.DongBoLog Where TenTable = 'ChungTuKho' And MaPKExp = Cast(ChungTuKho.MaPK as varchar) And MaTruyenNhan = C.MaTruyenNhan And NhanVe = 0 And DBName = DB_NAME()); End;go

Please i need HELP !

gonfreezc — Wed, 09 May 2012 16:42:16 GMT

please forgive me for my ignorance and rudeness BUT this prob really really annoying me and i need an urgent solution ...1. I DONT KNOW ANYTHING ABOUT SQL ...ANYTHING2. i installed a software for my pharmacy and i supose its using some kind of data stored as SQL server .. it was workin fine ...i just uninstaled it then re installed it in 5 MIN .... and here whats happening ..[img]http://desmond.imageshack.us/Himg690/scaled.php?server=690&filename=13361526.png&res=landing[/img]then [img]http://desmond.imageshack.us/Himg607/scaled.php?server=607&filename=31555764.png&res=landing[/img] then [img]http://desmond.imageshack.us/Himg338/scaled.php?server=338&filename=68341888.png&res=landing[/img]then [img]http://desmond.imageshack.us/Himg252/scaled.php?server=252&filename=80880823.png&res=landing[/img]although i made everything i even re installed my win xp .. same P.S:[img]http://desmond.imageshack.us/Himg109/scaled.php?server=109&filename=97946713.png&res=landing[/img]PLEASE help me

COPY ONLY Backups - What Impact Do They Have On Perfomance

YSLGuru — Wed, 16 May 2012 09:06:08 GMT

I realize this may be to much of a "It Depends" type of question and answer scenario but does anyone know, preferably from experience, if performing a COPY ONLY backup of their DB while its live/In Use has an impact of such that the users work in the DB is affected enough to warrant not doing a COPY ONLY while the DB is in use?We are having some problems (once again) with our Microsoft DPM Backup System and I want to make a COPY ONLY backup right away so that I have a somewhat recent recovery point but I can’t find any documentation on what kind of impact if any a COPY ONLY backup has on the DB if its in use.Thoughts?Thanks

Proxy server for Database mail

mbusindovela — Wed, 16 May 2012 07:11:53 GMT

Hi My internet has a proxy server login as result when I configure my Database Mail it blocks me from sending email. Can you please help me with the settings of Database Mail if I have a proxy server.:(

parameters in sql job

Hollyz — Tue, 15 May 2012 09:18:52 GMT

How to pass parameter to a sql job in 2005?If there is a store proc that executes and needs to pass a parameter of varchar data type to a sql server job, how should it be done?

How to script out in a single .sql file stored procedures containing specific reference on their code

APA0876 — Thu, 10 May 2012 15:13:46 GMT

Hi,How to script out in a single .sql file stored procedures containing specific reference on their code.I have this scenario :I have to change at least 181 stored procedures that reference a name on their code.I was thinking on doing it through Windows Power Shell and SMO but I'm having issues putting the values of the names of the stored procs on a power shell variable.I have the names of thos stored proc by queriing the sys.comments table. What do you think will be the best way, this is a SQL Server 2005.Thanks

Creating linked server over to oracle database

johnnyrmtl — Mon, 14 May 2012 06:25:16 GMT

Hello all,I am trying to create a linked server over to an Oracle database and for this I've installed the oracle client where SQL server 2005 resides , but still don't see the provider xxxoracleOLExxx in the linked server section of SSMS.I even restarted sql services as required...Did i miss something?Much appreciated

Creating xml from a few columns

bopritchard — Wed, 09 May 2012 11:57:20 GMT

Table named Clients with columns of ID,FirstName,LastNameI want a select statement that produces results with 2 columnsID1,<client><firstname>Peter</firstname><lastname>Frampton</lastname></client>Of course my table is a lot bigger, but help with the example I provided will get me going.Thanks

Need help with SQL statement using COUNT function

mldardy — Tue, 08 May 2012 08:52:54 GMT

I am using a Select statement where I get the difference between two columns (ForecastQtyContainers and LastYearQtyActualContainers). I'm able to get that fine but I want take the Difference (using the COUNT function) from those two and divide it by LastYearQtyActualContainers. The problem is the Difference column is not an actual database column but I'm using it as an 'As'. How can I get the Difference column to work I get Invalid column name 'Container Diff 2010 vs. 2011'. error. Here is my select statement:SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast], SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],Count([Container Diff 2010 vs. 2011])* 100/LastYearQtyActualContainers, ActualContainers, Product, ActualQty,Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice,LastYearActualQty, ActualContainers, ForecastPrice, ForecastQtyContainers from TmpLYData group by LastYearActualQty, ActualQty, Product, CouncilName, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers, ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilname

Having a very hard time trying to install some SQL 2005 tools

Rod at work — Wed, 09 May 2012 14:46:28 GMT

I'm trying to get a couple of ASP.NET 2.0 apps installed onto our new web server. I've run into a problem installing it; I can't remember but I think I've posted a message on this list about it. We install both of these apps using a .MSI setup, because we've got to get Crystal Reports XI Release 2 onto the server (we use several reports written in Crystal). The setups fail immediately, before it has a chance to do anything. I finally found a way to get the msiexec to write to a log file, and have looked it over. It appears to me that the problem is it can't find a MSOLAP.DLL file on the server. In researching that I've found that Windows 2008 R2 Web Server already has OLAP services installed, so this installer is looking for something that is no longer relevant. So now I'm trying to find a way to get rid of the dependency upon OLAP. I've looked through all of our code, and there's nothing in there that uses OLAP services, so frankly I'm guessing the culprit is Crystal (the bane of my existence these days). So I found a way of editing the dependencies the Crystal reports in VS 2005. I've spent time doing that and have gotten it down to only what we need. Then I started another build of the .MSI, and at this point I ran into another problem, which I don't understand and has me stumped.For some reason trying to build the new setup requires that it run a SqlRun_Tools.msi file. I've pulled out our SQL Server 2005 DVD's, and found the file there, and tried to install it. It failed. I next went through old MSDN DVD's we've got, found the file on them, and have tried to use them, but that fails again. I've even copied SqlRun_Tools.msi onto my old XP machine (where I've got VS 2005 installed, and where I used to make the .MSI files for installing onto our old web server back years ago), and tried running it there, but it fails. Each and every time it's failed, it gives me an error saying:[quote]The feature you are trying to use is on a CD-ROM or other removable disk that is not available.Insert the ‘Microsoft SQL Server 2005 Tools' disk and click OK.[/quote]Well, I'm using the only media I've got, back when we first installed SQL 2005 on our database server, years ago. I'm really stumped and at a complete loss as to what to do and how to proceed.

Unable to Load Client Print Control on Windows 7 64-bit

Michael Viglas — Wed, 10 Nov 2010 10:46:26 GMT

We have applied SQL 2005 SP3 and I have deployed a new Windows 7 64-bit computer and I am now getting this message on this computer.I have seen all the messages regarding ActiveX and such, but now that I'm on the latest services packs on both SQL 2005 and Windows 7, what could possibly be the problem now?Any help would be great.Thanks,Michael

Converting TEXT columns to VARCHAR(MAX)

Henk Schreij — Mon, 29 Nov 2010 03:05:35 GMT

I have a database designed in SqlServer 7 which uses text columns for the storage of text (size from 0 to ca 15000 characters). Now the text data type will be removed in a future version of Microsoft SQL Server I want to modify the text columns to the varchar(max) data type. It is a database without stored procedures or user defined functions. All updates/inserts/deletes are done from a Windows application connected to the database using ADO.I want to do the conversion in SSMS in the design mode, just by replacing 'text' by 'varchar(max)'. There are only a few tables with Text columns and these tables only contain a few thousend records.Are there any problems to expect and what can I do to avoid them?

I want to script database security SQL SNAPSHOT Question

dthayer 86868 — Mon, 07 May 2012 17:45:18 GMT

I have a snapshot of a mirrored database, and a database that holds the links to the snapshot, which updates every 10 minutes. I want to pull the database roles info from the snapshot to maintain the same security on the Report database. How can I get this out of the snapshot?I think the easiest way is to get it from the original database, but I'm looking for a generic solution that could be used in other similar scenarios...

How to use table column values as such for calculations?

nivecse — Mon, 07 May 2012 15:51:43 GMT

Hi,Am using SQL Server 2005. I have 2 tables. Table A (RuleNo, RuleDesc)Table B (Col1,Col2,Col3).Table C (Col1,Col2)Table A has values as below:RuleNo RuleDesc----------------1 Col1*Col2/Col32 Col2*Col33 Col1*100/Col2Where RuleDesc column contains the rule for calculation based on the values from Table B. Lets assume Table B has values like below:Col1 Col2 Col3 Col4---------------------1 2 2 S12 4 3 S34 1 5 S2Table C has to apply the rule from Table A as below:Col1 Col2-----------S1 1S2 3S3 2Col2 of Table C refers to the RuleNo of Table A. For eg, S1 refers to RuleNo 1 of Table A. When the RuleNo 1 (Col1*Col2/Col3) is applied with the values from table B, S1 will have value as 1. Like wise, my table A has lots of rules to be applied for Table C. How to calculate this value dynamically using the formula from TableA?Is there any possibility of doing this in sql server tables?

SQL Server 2005 not using available memory

Thunk123 — Fri, 06 Mar 2009 02:32:38 GMT

Hi All,We are using SQL Server 2005 Enterprise Edition(64bit) version, which is running on Windows 2003 Enterprise edition(64bit). The total memory of the server is around 32GB. But when I look at the Task Manager > Processes > Memory Usage, the SQL server is only consuming around 145MB. The CPU looks healthy around 10-15%. But the customer some times complaining about performance of the server. My question is why the sql server is not using the memory capacity which is available and only using 145MB. The minimum server memory is set at 0 and the maximum server memory is set at 2147483647. The Boost SQL server priority also enabled.Anyone can enlighten me on this.Best regards,stm

Import and Export Wizard - remove double quotes from csv data

_simon_ — Tue, 08 May 2012 06:16:24 GMT

I am frequently given csv files in following format:[quote]"Id", "Col1", "Col2", "Col3""1", "x1", "y1", "z1""2", "x2", "y2", "z2"[/quote]The column names are in the first row. Two questions: - is there any way to automatically remove double quotes for the names of the columns? I can remove them manualy (in the Advanced module of the Flat File Source, but this is tedious and I have a lot of columns).- how can I remove double quotes for data? I can set column delimiter to "{,}", but this leaves first and last double qoutes in my data, like this:[quote]"1, x1, y1, z1""2, x2, y2, z2"[/quote]Edit - what is the best way to somehow save the settings of the current import so that I can use it in the future also?

interview questions

paul.starr — Fri, 17 Apr 2009 14:43:36 GMT

any info on really good interview questions for a sql server dba?

Start a remote service from SQL??

tysonlibelula — Fri, 04 May 2012 14:11:04 GMT

I wanna start a service on a remote machine. I use exec..xp_cmdshell 'sc \\remotemachine start messenger'From cmd on windows it works, from SQL it doesn't.The SQL account is a member of a domain admin.I tried to create a new SQL user a grant him acces to xp_cmdshell i stil get an error Error 5: Access is Denied.