Tuesday, June 4, 2013

[SQL Server 2008 issues] Join Problem

[SQL Server 2008 issues] Join Problem

Join Problem

Posted: 02 Jun 2013 09:27 PM PDT

my table is like this for attendancecreate table attendance(CID int,BID int,date datetime,eid int,timein datetime,timeout datetime,)and my shift table is like thisCREATE TABLE SHIFT(CompanyID int,BranchID int,ShiftID int,ShiftName varchar(50),LongName varchar(50),SType varchar(50),TimeIn datetime,TimeOutdatetime,LTime datetime,HDTime datetime,Night int,TotalTime datetime,)and this is my data of shift TableCompanyID-----------1BranchID------------1ShiftID---------------1ShiftName-------------GLongName------------------GENERAL SHIFTStype-------------------------------nullshifttimein------------------------------1/23/2013 9:00:00 AMshiftTimeout---------------------------1/23/2013 6:00:00 PMLtime--------------------------------1/23/2013 9:16:00 AMHdTime----------------------------1/23/2013 2:00:00 PMNight---------------------------------0TotalTime-------------------------6/3/2013 9:00:00 AMmy query is like thisselectdistinct Date,[Time in],[Time out],CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,CAST (ABS (540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )CAST (ABS (540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= 540 THEN 'Excess' ELSE 'Short' END ExcessShortFROM trans tLeft outer join shift s on t.bid = s.bid and t.cid = s.cid where employeecode = 26446i want to to add Totaltime column in replace of 540becouse shift timing is not 9 hours some shift are 8 hours and some are 10 hoursplease help me out

system databases crruption

Posted: 03 Jun 2013 05:58 PM PDT

Hi all, If system databases got corrupted and we don't have backups of the system databases... how to handle this situation

SID and PID

Posted: 03 Jun 2013 04:22 PM PDT

Hi can any one explain what these SID and PID . what is the use of them..

Find the error column and the row

Posted: 03 Jun 2013 05:58 PM PDT

Hi All,I am facing a problem with finding the exact error row and column where the error occured.Scenario :1. TableA has 300+ columns all Varchar datatype 2. TableB has exact number of columns as da TableA but different datatypesTableB is to be populated from the TableA .While including a single insert statement, converting accordingly, it error out with DataConversion error. I am having a trouble finding the exact record and column for the error description.I have figure out a way to do that , i.e. Adding an Identity column in the TableB ; after rollback, Identity would still be bound to the table. So, the next insert would give me the point of failure.But, Is there any way to do this without altering table ?here is sample code to work it out .[code="sql"]create table varchartest(a varchar(30),b varchar(30))create table varchartest_1(a varchar(30),b int)insert into varchartest values ('xyz','1'),('abc','2'),('bcd','3'),('efg','4'),('erer','5'),('rerer','x'),('rerer','7')-- alter table varchartest_1 add c int identity-- insert into varchartest_1(a,b) select top 1 a,b from varchartestselect * from varchartest_1 [/code]Thanks

Kitchens Suppliers Manchester

Posted: 03 Jun 2013 05:01 PM PDT

Kitchens Suppliers Manchester. http://www.solidwoodkitchen.co.uk U.K manufactured kitchens with appliances only £595. TEL- 01616-694785. Kitchens Suppliers Manchester

What is the uses of Environment variable explain give me some example.

Posted: 03 Jun 2013 05:23 PM PDT

Hi ,i am new to ssis ,but in my package used Environment variable .Plaese explain give me with some example.Thanks,bal85

Database mail sending fails with SP_Send_DBmail.

Posted: 03 Jun 2013 05:27 PM PDT

Hi,I am stuck in below problem.I have two servers; ServerA and ServerB. I have linked serverA to serverB bysp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB', @srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.The Query is : EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',@subject = 'MySubject',@body = 'This Is Test.',@body_format = 'Text', @profile_name = 'Profile1',@query = 'set nocount onSELECT * FROM [ServerB].[Database].dbo.myTable AS MTWHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',@execute_query_database = 'master',@query_result_separator = ' ',@attach_query_result_as_file = 1,@query_attachment_filename = 'myFile.csv'I have created an proc on serverA's master database and calling this proc into a job.This job throws an error:" Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "Can Anybody help in solving this??It would be a great help.Thanks and Regards,Jignesh

configuring alerts

Posted: 03 Jun 2013 05:12 PM PDT

i would like to configure alerts in all the sql servers to the below 1. disk space on the server 2. log space used of all the databases 3. alert when the job is failed 4. server shut down \up.replies are appreciated .

how to assign additional SQL Server administrators

Posted: 03 Jun 2013 12:26 AM PDT

When I ran SQLEXPRADV_x86_ENU.exe (run under Windows' Administrator user account) to install SQL Server 2008 Express with Advanced Services, when it got to the Database Engine Configuration step, under "Specify SQL Server administrators" I clicked on "Add Current User" to assign NULL\Administrator as a SQL Server administrator.How can I now add my other Windows user accounts (both administrator level and reduced-privilege accounts, if possible) as SQL Server administrators?

Why is CDC returning Insert/Delete when I UPDATE a row in a table?

Posted: 15 Mar 2013 06:11 AM PDT

This is my second post asking this question. Hopefully someone will respond this time. I am experimenting with using CDC to track user changes in our application database. So far I've done the following:[code="sql"]-- ENABLE CDC ON DV_WRP_TESTUSE dv_wrp_test GO EXEC sys.sp_cdc_enable_db GO -- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TESTUSE dv_wrp_test GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AVA', @role_name = NULL GO-- UPDATE A ROW IN THE AVA TABLE FROM SSMSUPDATE AVA SET AvaDesc = 'Test3' WHERE AvaKey = 119-- GET CDC RESULTS FOR CHANGES TO AVA TABLEUSE dv_wrp_test GO SELECT * FROM cdc.dbo_AVA_CT GO--RESULTS SHOW OPERATION 3 (BEFORE UPDATE) AND 4 (AFTER UPDATE) CORRECTLY--__$start_lsn __$end_lsn __$seqval __$operation __$update_mask AvaKey AvaDesc AvaArrKey AvaSAPAppellationID--0x0031E84F000000740008 NULL 0x0031E84F000000740002 3 0x02 119 Test2 6 NULL--0x0031E84F000000740008 NULL 0x0031E84F000000740002 4 0x02 119 Test3 6 NULL[/code]The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4) drop procedure dbo.spdv_AVAUpdateGOCREATE PROCEDURE [dbo].[spdv_AVAUpdate] @AVA AS tvpAVA READONLY -- table valued parameterASDECLARE @ErrLogID AS INTEGERBEGIN TRY SET NOCOUNT ON BEGIN TRANSACTION UPDATE AVA SET AVA.AvaDesc = TVP.AvaDesc, AVA.AvaArrKey = TVP.AvaArrKey FROM @AVA TVP INNER JOIN AVA AVA ON (AVA.AvaKey = TVP.AvaKey) -- Commit the transaction COMMIT TRANSACTION -- Return '' for success SELECT '' AS ErrMessageEND TRY BEGIN CATCH -- Roll back any active or uncommittable transactions IF XACT_STATE() <> 0 BEGIN ROLLBACK TRANSACTION END -- Log the error into the ErrorLog table EXECUTE dbo.uspLogError @ErrLogID = @ErrLogID OUTPUT -- Retrieve logged error information. SELECT dbo.ErrorLog.* FROM dbo.ErrorLog WHERE ErrLogID = @ErrLogID END CATCH GOGRANT EXECUTE on dbo.spdv_AVAUpdate TO publicGO[/code]When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:[code="sql"]-- GET CDC RESULTS FOR CHANGES TO AVA TABLEUSE dv_wrp_test GO SELECT * FROM cdc.dbo_AVA_CT GO-- RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4--__$start_lsn __$end_lsn __$seqval __$operation __$update_mask AvaKey AvaDesc AvaArrKey AvaSAPAppellationID--0x0031E84F000000740008 NULL 0x0031E84F000000740002 3 0x02 119 Test2 6 NULL--0x0031E84F000000740008 NULL 0x0031E84F000000740002 4 0x02 119 Test3 6 NULL--0x0031E84F00000098000A NULL 0x0031E84F000000980003 1 0x0F 119 Test3 6 NULL--0x0031E84F00000098000A NULL 0x0031E84F000000980004 2 0x0F 119 Test4 6 NULL[/code]Does anyone know why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?Thanks,Gina

HOW TO EXECUTE 2-3 SQL STATMENTS ONE AFTER ANOTHER BASED ON THE RESULT OF PREVIOUS QUERY?

Posted: 03 Jun 2013 07:12 AM PDT

Hi,Ho I can run 2-3 sql statments in one go, which are based on above's query value.for an example.first statment copy the data from linked server into new table in local server.second statement copy few fields from the table , just created in local server to another table in the local server.and third statment apply the merge query into this table , by considering it as source to the another destination tablenow , I want to know how can i achive this in one go? either via stored procdure, cursor or smonthing elese.?please help.Thanks.Pratik.

Is there a way to read execution statistics for stored procedures that have "with recompile"

Posted: 03 Jun 2013 02:26 AM PDT

Is there a way to read execution statistics for stored procedures that have "with recompile"I've tried the following with no joy[code="sql"]USE tempdbgoCREATE PROCEDURE usp_testwithoutrecompileASSELECT TOP 1 * FROM sys.objectsgo CREATE PROCEDURE usp_testwithrecompileWITH RECOMPILE ASSELECT TOP 1 * FROM sys.objectsgo EXEC usp_testwithoutrecompile;EXEC usp_testwithrecompile;SELECT OBJECT_NAME(object_id),* FROM sys.dm_exec_procedure_statsWHERE OBJECT_NAME(object_id) = 'usp_testwithoutrecompile';SELECT OBJECT_NAME(object_id),* FROM sys.dm_exec_procedure_statsWHERE OBJECT_NAME(object_id) = 'usp_testwithrecompile';DROP PROCEDURE usp_testwithoutrecompile;DROP PROCEDURE usp_testwithrecompile;[/code]or maybe you know a better way for me to work.I am using the execution stats [max_elapsed_time] and ([Total_elapsed_time]/[Execution_count]) to compare sp performance between our prod & QA servers.Now we have a number of procs that use with recompile and I can not judge the impact of the change.(I'm using SQL 2008 R2 Enterprise 64 bit)Many thanks in advanceIan

SQL Server - Stored Procedure

Posted: 03 Jun 2013 06:06 AM PDT

While passing parameters in exec statement can we use CAST or not.I am getting error while using CAST to pass the parameters.Example:EXEC dbo.abc CAST(0 as bit), nullNOTE: I need to pass just two parameters..Thanks in advance.

SSIS package execution remote vs / local

Posted: 02 Jun 2013 07:56 PM PDT

Hi all. Hope I'm posting in the right forum.We currently schedule SSIS packages using the Windows task scheduler, but we want to use the SQL agent for this. The most important reasons are.- Easier maintenance of jobs by the DBA instead of the system administrators who administer scheduled tasks- Easier monitoring of jobs via job history- Easier to define flows in SQL agent job steps for dependent packages. First execute package X and then Y etc.In our configuration we have 2 separate servers.- DB server. Purpose: only for serving data (not in DMZ for security reasons. This is a strict security policy which we can not change)- JOB server. Purpose: application server. Contains scheduled tasks and serves SSIS packages (in DMZ)[b]Problem[/b]SSIS Service is installed on the job server but not on the database node. First I tested scheduling an agent job on the DB server which calls a package hosted by the JOB server. This works, but the SSIS package is acutally run on the DB server instead of remotely on the JOB server. This is a problem because the DB server is not in the DMZ and can not access external sites. So our packages can not be run correctly.Workarounds for remote scheduling via sp_startjob are complex and have several additional problems.[b]Solution?[/b]A solution is just to stick with old scheduling and run the packages directly via dtexec. However we then loose al of the SQL agent job advantages.Another solution is to install another SQL instance on the JOB server itself just for scheduling the SSIS packages. This is in my opinion the best solution, but it will costs us an extra SQL license!It also violates the SQL instances not in DMZ policy. However because we don't store any data (besides master databases) here the risk is decreased.[b]Advice?[/b]I really need some good advice from the experts on this ;)

Calling Batch file using SQL Script

Posted: 03 Jun 2013 12:03 AM PDT

Hi Team,Am calling a batch file from sql script, passing parameters to batch file and trying to print the parameters in batch file, [b]declare @bat varchar(200) set @bat = 'E:\Test_batch\Test.bat'+ ' ' + 'UPDATE' + ' ' + 'DELETE' exec master..xp_cmdshell @bat[/b]Parameters are printing, but throwing below error msg [b]UPDATE' is not recognized as an internal or external command,operable program or batch file.'DELETE' is not recognized as an internal or external command,operable program or batch file.[/b]Please help me.

How to find SQL Server account name ?

Posted: 03 Jun 2013 04:57 AM PDT

Hi,I need to find account name that SQL Server instance is installed. Is it possible ?Thanks

issues with setting up Publisher in SQL2008

Posted: 30 May 2013 11:51 PM PDT

I have a two server configuration where the distribution db is on another server and the publisher is on a second server. I have setup the distribution db just fine (SQL2008R2 SP2) and when I configure the Publisher on SQL2008 SP3 it works but when the replication starts it gets a failed login.....The login is right and the password is correct. I don't understand why this thinks the password is incorrect. In the Publication I chose the option of RUn under SQL Server Agent Service Account so I didn't have to supply the password.... MessageReplication-Replication Snapshot Subsystem: agent failed. The replication agent had encountered an exception.Source: ReplicationException Type: Microsoft.SqlServer.Replication.ConnectionFailureExceptionException Message: Login failed for userMessage Code: 18456Replication-Replication Snapshot Subsystem: agent failed. Unable to start execution of step 2 (reason: Error authenticating proxy system error: Logon failure: unknown user name or bad password.). The step failed.

facing issue in looping through in SOL to XML

Posted: 30 May 2013 06:56 PM PDT

We are trying to display the xml code using following query but facing some issue as follows. We have created the temp using various tables in the database and trying to create xml ouput from the same. CREATE TABLE #crossTable11( EMPID int INT , SN Int INT , EmpName VARCHAR(150) , EventName VARCHAR(150) , BookingDate smalldatetime SMALLDATETIME , PositionName VARCHAR(150) , DepartmentName VARCHAR(150) )INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES (1, 6, 'N1', '99hh', '2013-05-20 00:00:00', 'Manager', 'Admin')INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES (2, 6, 'N1', 'MS Excel', '', '', '')INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES values (13, 6, 'N1', 'MS Office', '', '', '')INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES (4, 2, 'Pkar', '99hh', '2013-05-20 00:00:00', 'Manager', 'Admin')INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES (5, 2, 'Pkar', 'MS Excel', '2013-05-10 00:00:00', 'Supplier', 'Accounts')INSERT INTO #crossTable11 (EMPID, SN, EmpName, EventName, BookingDate, PositionName, DepartmentName)VALUES values (6, 2, 'Pkar', 'MS Office', '2013-08-01 00:00:00', 'Supplier', 'Accounts') SELECT 'Site of Person Involved' AS '@caption', 'SitePI' AS '@fieldName', ( select EmpName as 'emp/@ID',PositionName as 'emp/@Position',DepartmentName as 'emp/@Department', ( select EventName as 'event/@ID',BookingDate as 'event/@Date' from #crossTable11 L where L.EMPID = p.EMPID order by EventName for XML path ('event'),TYPE ) from #crossTable11 p group by p.SN,p.EmpName,p.PositionName,p.DepartmentName,p.EMPID for xml PATH(''), TYPE )for xml PATH('r'), TYPEOutput is as follows which shows the duplicate names. <r caption="Site of Person Involved" fieldName="SitePI"> <emp ID="Pkar" Position="" Department="" /> <event> <event ID="MS Office" Date="1900-01-01T00:00:00" /> </event> <emp ID="Pkar" Position="Supervisor" Department="Admin" /> <event> <event ID="99hh" Date="2013-05-20T00:00:00" /> </event> <emp ID="Pkar" Position="Supervisor" Department="Admin" /> <event> <event ID="MS Excel" Date="2013-05-10T00:00:00" /> </event> <emp ID="N1" Position="" Department="" /> <event> <event ID="99hh" Date="2013-05-20T00:00:00" /> </event> <emp ID="N1" Position="" Department="" /> <event> <event ID="MS Office" Date="1900-01-01T00:00:00" /> </event> <emp ID="N1" Position="Manager" Department="Account" /> <event> <event ID="MS Excel" Date="1900-01-01T00:00:00" /> </event></r>Required output is: We would like to avoid the duplicates and combine the output for them <r caption="Site of Person Involved" fieldName="SitePI"> <emp ID="Pkar" Position="Supervisor" Department="Admin" /> <event> <event ID="99hh" Date="2013-05-20T00:00:00" /> <event ID="MS Office" Date="1900-01-01T00:00:00" /> <event ID="MS Excel" Date="2013-05-10T00:00:00" /> </event> <emp ID="N1" Position="Manager" Department="Account" /> <event> <event ID="99hh" Date="2013-05-20T00:00:00" /> <event ID="MS Office" Date="1900-01-01T00:00:00" /> <event ID="MS Excel" Date="1900-01-01T00:00:00" /> </event>

sp_updatestats job - very slow

Posted: 03 Jun 2013 03:33 AM PDT

Hello,Sp_updatestats on one of my DBs took over 2 hours. The job runs daily at 5:30am and it usually takes no longer than 45 seconds.Any suggestion is greatly appreciatedTHX

creating logon & logoff triggers in MSSQL

Posted: 03 Jun 2013 01:02 AM PDT

We have several database that we track user logons for, until now all these databases resided in Oracle. For Oracle I created a sys trigger than logs [i]os user, database user, program used[/i], etc., as well as logon time & logoff time that writes to a table I called "audlog" then I query the table every monthThis is what I created in Oracle:[code="sql"]CREATE TABLE audlog.session_audit (user_name VARCHAR2(30), log_date DATE, session_id VARCHAR2(30), ip_addr VARCHAR2(30), hostname VARCHAR2(30), auth_type VARCHAR2(30), os_user VARCHAR2(30), event VARCHAR2(8), program VARCHAR2(30)) tablespace users;Create Or Replace Trigger Trg_Logon_Info After Logon On DatabaseDeclareProgram Varchar2(30);BeginSelect program into program From v$session Where sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG ON' -- Event , Program );End;/Create Or Replace Trigger Trg_Logoff_Infobefore Logoff On DatabaseDeclareProgram Varchar2(30);BeginSelect Program Into Program From V$Session Where Sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG OFF' -- Event , Program );End;[/code]I would like to create something in MSSQL that basically shows the same information, so I can keep the reports consistent looking. Can I duplicate this trigger in MSSQL?

Bulk insert Error

Posted: 03 Jun 2013 12:52 AM PDT

Hello All,Currently trying to insert a data from a file placed in a remote server and using the BULK INSERT command to perform the same, but I am getting the error "Operating system error code 5(Access is denied) when i use the bulk insert command.Login already has bulkadmin privileges and able to access the file in the remote server from my source server.I am able to perform the import operation when i use the import /export wizard.Why is it so?

Trying to use case when to pivot data

Posted: 03 Jun 2013 12:11 AM PDT

I'm trying to pivot this data but, I can't seem to get rid of the "extra zeros".Here's my query.[code="sql"]SELECT provider, month_name, case when WeekNumberOfMonth='1' then ArrivedVisits else 0 end as 'Week1', case when WeekNumberOfMonth='2' then ArrivedVisits else 0 end as 'Week2', case when WeekNumberOfMonth='3' then ArrivedVisits else 0 end as 'Week3', case when WeekNumberOfMonth='4' then ArrivedVisits else 0 end as 'Week4', case when WeekNumberOfMonth='5' then ArrivedVisits else 0 end as 'Week5'from VISITSgroup by provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisitsorder by provider, fiscal_month[/code])[code="sql"]create table VISITS(provider varchar(100),fiscal_month int,month_name varchar(25),WeekNumberOfMonth int,ArrivedVisits int)[/code][code="sql"]insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',1,'October',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',2,'November',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',3,'December',6)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',4,'January',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',5,'February',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',6,'March',6)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABELES M.D.,DEBORAH',7,'April',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',1,'October',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',2,'November',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',3,'December',6)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',4,'January',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',5,'February',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',5)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',6,'March',6)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',1)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',2)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',3)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',4)insert into VISITS(provider, fiscal_month, month_name, WeekNumberOfMonth, ArrivedVisits) values('ABESAMIS M.D.,AILEEN A.',7,'April',5)[/code]

Process For Data Migration

Posted: 02 Jun 2013 10:17 PM PDT

Hi All, I have a requirement to migrate a database but, with the migrated version to have column name changes that must be reflected in all of the code base. I am basically perfoming a translation on an existing database. Now, I could set these up as SSIS packages or individual scripts and searching all the Sprocs/views, functions..etc, but I hate re-inventing the wheel and was wondering if anyone knew of any software or another processes that could aid this? I have the DBA toolbelt from redgate but I cannot see anything that will help (so far, apart from the SQLSearch helping to identify the locations of the references)Thanks in advanceElliot

2 transaction log files -- which one is in use?

Posted: 02 Jun 2013 09:23 PM PDT

Hi ,Today we discovered a database that has 2 log files (reason unknown; they're on the same disk). This database is in SIMPLE mode. I'm wondering which view will identify which of the two are active. I've looked at ones that seemed the most likely from the autocomplete but haven't been able to find it (sys.fn_dblog provided nothing while sys.dm_audit_actions knows that the action was logged but doesn't say where to).sys.dm_io_virtual_file_stats says the number of reads/writes (duration unknown, assume since startup) but it doesn't have a date field. The idea would be to delete the currently inactive log file before the database switches to it again.Mike

Locale issues when importing flat file data (UK/US date problem)

Posted: 03 Jun 2013 12:32 AM PDT

I've raised this issue before but I've now been allowed to spend time on site to review the problem further. Unfortunately, the servers and the desktops are on different domains from mine (and each other I believe), quite locked down and administered by another organisation.We have a server set up with SQL Server 200R2 Installed on it.The location and all the regional settings on the server are all set to United Kingdom.I'm connecting to the server via a Win 7 desktops with SSMS installed (SQL 200R2) The location and the regional settings on the desktops are also set to United Kingdom.If importing flat file pipe delimited data via SSIS into a database on the server from the desktop it thinks the locale is United States and screws the dates up. Whilst I can create a package and view the properties, change and save it, I cannot run the package due to permissions, nor will the relevant permissions be given. Plus the nature of the data being provided means consistency is very irregular.If I run a file in on the server itself it correctly loads it up as United Kingdom and the dates are correct. It also correctly imports the data if I log in via another server.I've created a new instance on the desktops, ensured that everything is set for UK and tried to load the data there. This also gives me the US date problem.In all the tests we have carried out it does seem that the desktops are at fault. I've checked with the sys admins and they say nothing is being blocked (or shown in the logs as being blocked). Unfortunately, they are not open to the problem or there being a problem for them to fix.Whilst I can log into and load the data via the server direct, this is not really acceptable and because of the nature of the project, may not be allowed due to risks involved.Has anyone come across this issue before and if so what was the solution? Is this a problem with ports and if so which ones? Or is this a problem with GP?Many thanks

option (maxdop 1)

Posted: 09 Aug 2010 08:27 PM PDT

I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and Exe's. Anyone know what this is? Is it stats / indexes being rebuilt?insert [dbo].[CLIENT] select * from [dbo].[CLIENT] option (maxdop 1)

help with Arithmetic overflow error; Calculating Lunar Phases.

Posted: 02 Jun 2013 11:12 PM PDT

ok a few years ago, I was poking around with calculating Lunar Phases,which I adapted off of a math web site: and with a bit of help from the community in this thread, we came up with a better ITVF version for it in this thread:[b][url=http://www.sqlservercentral.com/Forums/Topic899188-391-1.aspx#bm899574]mis-appying a cross apply table value function?[/url][/b]so today I decided to apply that ITVF apply against a larger tally table than before; basically 1101 years, from 1900-01-01 to 3000-0101.that ITVF returns an error when it starts calculating against any date greater than [b]2808-07-13 00:00:00.000[/b] and i sure as heck can't see the forest thru the trees, and see where the calculation is hitting a data type limit;[quote][color="#FF0000"]Msg 8115, Level 16, State 8, Line 4Arithmetic overflow error converting numeric to data type numeric.[/color][/quote]here's a test harness that allows you to recreate the issue; can you help me figure out where i'm overflowing?[code]--this TallyCalendar table goes from year 0 (1900-01-01 to year 3000-01-01--calculation crashes on dates greater than '2808-07-11 00:00:00.000' --initialize our vars With TallyC AS (SELECT convert(datetime,RW) AS TheDate FROM ( SELECT TOP ( datediff(dd,0, dateadd(year,1101,0)) ) ROW_NUMBER() OVER (ORDER BY sc1.id) -1 AS RW FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2 ) X), cteDtSplit AS ( SELECT TheDate, YEAR(TheDate) AS TheYear, MONTH(TheDate) AS TheMonth, DAY(TheDate) AS TheDay FROM TallyC ), cteDates AS ( SELECT TheDate, TheYear - FLOOR( ( 12 - TheMonth ) / 10 ) AS yy, CASE WHEN (TheMonth + 9) >= 12 THEN (TheMonth + 9) - 12 ELSE TheMonth + 9 END AS mm, TheDay AS dd FROM cteDtSplit ), ctePre AS ( SELECT TheDate, dd, FLOOR( 365.25 * ( yy + 4712 ) ) AS k1, FLOOR( 30.6 * mm + 0.5 ) AS k2, FLOOR( FLOOR( ( yy / 100 ) + 49 ) * 0.75 ) - 38 AS k3 FROM cteDates ), cteAdj AS ( SELECT TheDate, CASE WHEN (k1 + k2 + dd + 59) > 2299160 THEN (k1 + k2 + dd + 59) - k3 ELSE k1 + k2 + dd + 59 END AS jd -- % for dates in Julian calendar FROM ctePre ), cteFin AS ( SELECT TheDate, ((( jd - 2451550.1 ) / 29.530588853) - CAST((FLOOR( ( jd - 2451550.1 ) / 29.530588853 )) AS DECIMAL(20,16))) * 29.53 AS AG FROM cteAdj ) SELECT TheDate,CASE WHEN ag < 1.84566 THEN 'New Moon' WHEN ag < 5.53699 THEN 'Waxing crescent' WHEN ag < 9.22831 THEN 'First quarter' WHEN ag < 12.91963 THEN 'Waxing near full moon' -- the web calls this "Gibbous ", WTH is that? WHEN ag < 16.61096 THEN 'Full Moon ' WHEN ag < 20.30228 THEN 'Waning near full moon' -- the web calls this "Gibbous ", WTH is that? WHEN ag < 23.99361 THEN 'Last quarter' WHEN ag < 27.68493 THEN 'Waning crescent' ELSE 'New Moon' END AS Phase FROM cteFin[/code]

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

Query Tuning

Posted: 02 Jun 2013 08:40 PM PDT

Hi all,How to do query tuning. Is there any methods for query tuning.how to find Query cost .What is the importance of this in tuning.

tempdb keeps growing

Posted: 02 Jun 2013 09:08 PM PDT

I have configured my tempdb as follows:8 data files with an initial size of 14GB each. This was based on 2 weeks of monitoring the DB and seeing to what size it grows.I have set the autogrow to 10%.The thing is, the data files have now grown to 22 GB each - making the overall size just over 183 GB. The tempdb is on its own drive and the capacity of this drive is 200 GB.I am afraid that I might be running out of space....I know restarting the SQL service will shrink the files back to its initial size of 14 GB each, but this is a production server and a restart is not possible.Any possible solutions?Will the tempdb not release unused space?

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

SQL Server only using 4Gb of 8Gb RAM

Posted: 29 May 2013 08:14 PM PDT

Hi all,I'm slightly befuddled and was wondering if anyone could shed some light on why my test box running a SQL Server 2008 R2 instance is only using 4Gb of the available 8Gb RAM? I've checked the properties of the instance and memory is showing as 4095 MB on the properties 'General' node but when I look at the 'Memory' node the 'Maximum server memory' is set to 8192 MB. When I look at Start > Computer > Properties this shows 8.00 Gb RAM and I'm using Windows Server Standard SP2 (32 Bit). Is it because the OS is set to use the other 4Gb and if so can I change it to 2Gb for OS and 6Gb for SQL?I feel I'm missing something blindingly obvious, could someone put me out of my misery please? :-)Thanks. M

Risks of not upgrading from SQL 2000, SQL 2005

Posted: 28 May 2013 02:36 AM PDT

Hello - I am inheriting 5 servers to administer from a jack-of-all-trades DBA/developer in another department.2 of the instances are SQL 2000 standard and are worried that SQL 2000 is out of support.The jack-of-all-trades DBA/developer will still be involved but has no desire to upgrade the SQL 2000 instances. (DTS packages that would need to be rewritten and he is understaffed. Plus, its not broke so don't fix it.)I was told by my boss that we need to inform them of the risks of running an out of support version.I had a conversation with the developer and he said "if the servers are behind a firewall, then there are no security risks"Can anyone offer an educated response to the firewall statement? Any links or insights would be appreciated. ThanksDave
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog