[Articles] Who Wants to Be Rich? |
| Posted: 14 Aug 2013 11:00 PM PDT With all the concern over the economy lately, Steve Jones talks about about finances and tries to bring perspective to those of us in the real world. |
| You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[MS SQL Server] How to migrate DTS packages from SQL 2000 to SQL 2008R2 |
| How to migrate DTS packages from SQL 2000 to SQL 2008R2 Posted: 15 Aug 2013 03:33 AM PDT How to migrate DTS packages from SQL 2000 to SQL 2008R2.Or atleast let me know how to migrate DTS packages in SQL 2000 to SSIS packages SQL 2008R2 |
| System-health extended-event session does not capture latest deadlocks Posted: 20 Dec 2012 08:04 PM PST While running the following query to capture the latest deadlocks recorded in the default system-health extended-event session, I noticed that the latest deadlock captured was 3 days ago. However, in the SQL ERRORLOG I see that several deadlocks have occurred as recently as today.Has anyone else noticed this issue?[code="sql"]--http://www.quest.com/whitepaper/how-to-use-sql-servers-extended-events-and-notifications816315.aspx;WITH SystemHealthAS ( SELECT CAST ( target_data AS xml ) AS SessionXML FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.event_session_address WHERE name = 'system_health')SELECT Deadlock.value ( '@timestamp', 'datetime' ) AS DeadlockDateTime, CAST ( Deadlock.value ( '(data/value)[1]', 'Nvarchar(max)' ) AS XML ) AS DeadlockGraphFROM SystemHealth sCROSS APPLY SessionXML.nodes ( '//RingBufferTarget/event' ) AS t (Deadlock)WHERE Deadlock.value ( '@name', 'nvarchar(128)' ) = N'xml_deadlock_report'ORDER BY Deadlock.value ( '@timestamp', 'datetime' );[/code] |
| How can I change a user created without login to a user with login Posted: 14 Aug 2013 07:56 PM PDT We lost a raid controller (supposedly) on a brand new database server Wednesday at about 3:45pm. Just before our 4:00 backup. I'm trying to attach our main database and get it running at that closest point.We have reinstalled SQL Server 2008 R2 (long story talk to DELL)I am able to attach the 3:45 mdf and ldf files but the users are without logins.How can I get the db users to have a login and password again?Before 6:00am this morning ..... central time USA.its 3:55am now.This is an emergency and any help is sincerely appreciated.tia,Todd |
| Emailing from SQL 2008 using 'EX:\O' type addresses Posted: 14 Aug 2013 09:44 PM PDT I'm trying to do the simple task of sending emails to end-users when their case is closed on the database.Normally I simply use DBMail and sp_send_mail; no problems.But the only email addresses available are of the form 'EX:/O=xxx/OU=site/cn=Recipients/cn=nnn.mmmm' which I am given to understand arise from Exchange. Anyway I can't get them to work, and so far haven't found anything about it on the net.Anyone have an idea? All offers gratefully accepted.thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[SQL 2012] Sp to create SQL job |
| Posted: 15 Aug 2013 12:52 AM PDT After my failed attempt last time, I thought of a new way to get my scheduled backups in place.I have basically created an SP and put it in model, this creates the folder structure for the backups, deletes old backups then takes a full backup of the database. To automate this, I am created another SP, which I will run, feeding in the database name of which I Want it to schedule backups for.create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as declare @JobCode varchar(max)declare @thisserver nvarchar(100)select @thisserver = (select @@SERVERNAME)set @JobCode = 'begin transactionDECLARE @jobId BINARY(16)EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @category_name=N''[Uncategorized (Local)]'', @owner_login_name=N''sa'', @job_id = @jobId OUTPUTselect @jobIdGOEXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserverGOUSE [msdb]GOEXEC msdb.dbo.sp_add_jobstep @job_name=N''' + @DatabasenameInput + '- backup Job'', @step_name=N''' + @DatabasenameInput + ' Backup'', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N''TSQL'', @command=N''exec dbo.backupdatabase'', @database_name=@databasenameinput, @flags=0GOUSE [msdb]GOEXEC msdb.dbo.sp_update_job @job_name=N''' + @DatabasenameInput + '- backup Job'', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N''[Uncategorized (Local)]'', @owner_login_name=N''sa'', @notify_email_operator_name=N'', @notify_netsend_operator_name=N'', @notify_page_operator_name=N''GOUSE [msdb]GODECLARE @schedule_id int[color=#ff0000]EXEC msdb.dbo.sp_add_jobschedule @job_name=N''' + @DatabasenameInput + 'backup Job'', @name=N''10pm''',[/color] @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20130815, @active_end_date=99991231, @active_start_time=220000, @active_end_time=235959, @schedule_id = @schedule_id OUTPUTselect @schedule_idGO )exec @jobcodeI am getting a syntax error on the highlighted row? please help :D |
| always on sql server 2012 Hybrid Storage Posted: 14 Aug 2013 06:25 AM PDT Good afternoon,What are the implications for the always on, have the primary with disk SSD and secondary with HDD SAS?thank you very muchMichael Antunes |
| Posted: 14 Aug 2013 06:02 PM PDT Hi everyone,We have several big jobs which execute a lot of SQL. These jobs execute every day during the night and take some time to finish.I suppose that all those queries are then stored in SQL Server cache right?Also, we have a website which uses this SQL Server database.Possible problem is, queries in jobs and website queries are different. So, agent jobs are used in back end to set up products, categories, brands etc. Web pages are using other procedures and other queries on front end to present products, enable online shopping etc.I am thinking, maybe it would be good to clear cache after jobs are finished? Maybe by using:[code="sql"]DBCC FREEPROCCACHE WITH NO_INFOMSGS;[/code]I am worrying that cache is not used on best way after jobs are finished. During the day, I need it optimized only for website queries. I suppose that jobs queries take some resources in cache. As a consequence, website would run slower?So, idea is, during the night, after jobs are finished, add as a last step clearing of cache. Then, website queries will build cache again and website will work as fast as possible during the work hours. On this way jobs queries don't affect cache. All cache belongs to website queries.Please let me know your thoughts :-)Regards |
| Posted: 14 Aug 2013 07:13 AM PDT Hi,The system is SQL Server 2012 SP1 Enterprise Ed 64-bit 11.0.3349.0Integrity Check has been failing on random user databases and on master and msdb, the error is:[font="Courier New"]Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.[/font]The above error is generated for Integrity checks that were ran from a scheduled job or directly in Management Studio, and also when dbcc checkdb is ran WITH NO_INFOMSGS or WITH TABLOCK.None of these messages are logged in SQL Server Error Log, and I did not find any other messages occurring at the same time in the log or in Event Viewer.Tried creating a snapshot but failed, error message:[font="Courier New"]Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 5119, Level 16, State 1, Line 1Cannot make the file "F:\MSSQL\Data\ReportServer_0814.ss" a sparse file. Make sure the file system supports sparse files.[/font]If this is a permission issue, how come the integrity check is successful for some of the databases?Any suggestions?thank you! |
| Run a script file against Multiple databases Posted: 14 Aug 2013 05:18 AM PDT I have a script file in C:\Script.sql that I need to run against multiple databases.I can run on one database as follows :r c:\Script.sql But my challenge is I need to run on all the databases returned by the folowing query. The script file creates abount 50 objects .SELECT db_name from Table1 where dbID > 4 Thanks |
| Restoring a database and simultaneously increasing it's file size Posted: 14 Aug 2013 03:43 AM PDT Hey guys, At my organization, we have just built a new SQL Server machine to which we would like to migrate existing databases from other servers. For one of the databases, we'd like the MDF and LDF to have a new initial file size once it is moved. What is the best way to accomplish this? Is there a way to increase the file sizes during restoration from a backup? Or will we have to restore and than alter the file size through T-SQL (or SSMS)? The reason we are asking, is because we want to ensure that the MDF and LDF files on the new server are contiguous in the file system, and we are worried that increasing the file size after restoration will lead to having non-contiguous files in the file system. If there is more detail I can provide, just let me know.Thanks, |
| SAS disk partitioning for SQL Server Posted: 14 Aug 2013 06:31 AM PDT Good afternoon,I have 6 SAS HDD with 900GB capacity each. What better way to split it to SQL Server, considering LDF, MDF and TempDB. For four database of volumetric total of 180GBthank you very muchMichael Antunes |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[T-SQL] Moving the decimal |
| Posted: 14 Aug 2013 08:13 AM PDT Hi,I have a decimal field (10,4) that has values in it like:0.01230.00630.0595I need a way in my select statement to make them look like:1.230.635.95I've tried this but it's not correct:SELECTCAST([Overtime Actual Percentage].[Actual Overtime Percentage]*100 AS DECIMAL(20)) AS 'Actual Overtime Percentage', |
| Help with Retrieving 1 Fax number Posted: 14 Aug 2013 06:17 AM PDT Hello all, I was wondering if someone can help me with an issue that I'm having. I'm trying to retrieve 1 fax number on a join. Here's the scenerio.1. I have a Vendors table that has the Vendor ID in it.2. I have a Address_Phone table that contains phone numbers with phone types such as FAX, BUS, etc. In this table it also has a POS (position) column that tells what position that phone number is in. A FAX number can be in any position 1,2,3.. So, in my scenerio I can have 3 record where I have a BUS phone number in POS 1 (First Record) then a FAX number in POS 2 (Second Record) and another FAX number in POS 3 (Third Record). I'm doing a left outer join because a Vendor may not have a fax number.SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM FROM VENDOR V LEFT OUTER JOIN ADDRESS_PHONE AP ONV.VENDOR_ID = AP.VENDOR_ID WHERE ADDRESS_PHONE_TYPE = 'FAX'The problem is that if the VENDOR ID is in ADDRESS_PHONE table more than once (which it is) with more than 1 fax number it brings me back both fax numbers. I only want one; but I'm don't know what position this fax number will be in because it can be in any position if one exists.Any thoughts? |
| Posted: 14 Aug 2013 01:11 PM PDT This is more of a question to people who read code and understand exactly what it does. Like someone posts a bunch of code.. and there are people answering the questions related to it.I would like to know if you have a process or method to analyze the code in your head.. or do you type up a query to see what it does. I have seen people look at a stored procedure that runs into a 2 page printout and know what it does.Most of it is probably experience,knowledge, etc.. but there must an underlying technique to break it down into "human"... |
| Posted: 30 Jun 2013 02:52 AM PDT Dear Friend,Am I able to capture the query inside the trigger that I was executed..?For example, I have instead of delete trigger for a table say SampleTable.[code="sql"] create table sampletable (Id int ,name varchar(800)) create trigger sampletriger on sampletable instead of delete as Print 'Desired Query' insert into sampletable values(1, 'heavenguy') insert into sampletable values(2, 'tristan') insert into sampletable values(3, 'Jack') [/code]Now i'm trying to delete the sampletable...[code="sql"]delete from sampletable where id = 1[/code]I wanted the trigger to return the query that I executed...output:-delete from sampletable where id = 1 |
| TSQL For Xml Path query to get nested data Posted: 14 Aug 2013 02:53 AM PDT I have one xml file with nested data when the xml is loaded with SSIS; it produces 3 tables. I created tables in SQL Server. The 3 tables are:1. Customer (95 columns) 2. Booking - (46 columns) 3. Customer_Booking_ID - (2 columns) is empty in the xml before loading the data but it populated after loading the data which provide the ID's to link the table (not sure how it done to recreate in my own xml)I now have to recreate the same xml format but with more data but I'm currently having problems producing a nested XML format like below:[code="other"]<?xml version="1.0"?><Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Customers.xsd"> <Customer> <AddressID>12132434454349</AddressID> <AddressOptin>1</AddressOptin> <CustomerID>12132434454349</CustomerID> <Customer_Booking_ID> <Booking> <BookingTypeCode>LastBooked</BookingTypeCode> <Complained>0</Complained> </Booking> <Booking> <BookingTypeCode>LastReturned</BookingTypeCode> <BalanceOutstanding>2333.12</BalanceOutstanding> <BookingReference>ref1232343454</BookingReference> <EngagementChannelCode>CC</EngagementChannelCode> <EnquiryReference>ref1232343454</EnquiryReference> <Complained>0</Complained> </Booking> </Customer_Booking_ID> <LastBrochureRequestDate>2013-07-24</LastBrochureRequestDate> <LastComplaintIssuedDate>2013-07-26</LastComplaintIssuedDate> </CustomerContact> </Customers>[/code][code="sql"]Create Table dbo.Customers ( AddressID Varchar(50) ,AddressOptin int ,CustomerID int ,LastBrochureRequestDate INT ,LastComplaintIssuedDate INT ,Complained Varchar(50))Create Table dbo.Customer_Booking_ID(customerID int /** not in the xml file create during loading data **/ ,bookingID int) /** not in the xml file create during loading data **/ Create Table Booking ( BookingTypeCode Varchar(50) ,BalanceOutstanding int ,BookingReference Varchar(50) ,EngagementChannelCode Varchar(50) ,EnquiryReference Varchar(50) ,BookingID int) **** *** bookingID is also in the Booking Table but is not in the xml file but it created during ssis loading into the tables. INSERT into Customer_Booking_ID ([customerID],[bookingID])Values(1, 1), (2, 2)INSERT INTO Customers (AddressID, AddressOptin,CustomerID,LastBrochureRequestDate,LastComplaintIssuedDate,Complained) VALUES ('1111', 1,1111,'2013-12-12','2013-12-12',0), ('2222', 1,2222,'2013-12-12','2013-12-12',0) INSERT INTO dbo.Booking (BookingTypeCode,BalanceOutstanding,BookingReference,EngagementChannelCode,EnquiryReference,BookingID) VALUES ('LastReturned', 222.13,'BookingREF11','CC','EEREF11',1), ('LastReturned', 222.13,'BookingREF12','CC','EEREF12',2)[/code] |
| Posted: 01 Aug 2013 02:49 AM PDT How do I nested common table expression? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
