Thursday, June 27, 2013

[SQL Server 2008 issues] SQL SERVER 2008 64 bit installation on Windows 2008 - Error code 32

[SQL Server 2008 issues] SQL SERVER 2008 64 bit installation on Windows 2008 - Error code 32

SQL SERVER 2008 64 bit installation on Windows 2008 - Error code 32

Posted: 26 Jun 2013 07:50 AM PDT

Any advices or idea how to fix it. I am in the middle of installation of it and It is stuck on error code 32 with sqlEngineConfigAction_install_confignonrc_Cpu64. I have admin rights. It is brand new box.

Cannot generate SSPI context

Posted: 26 Jun 2013 03:07 PM PDT

Hi,We have SQL server 2008 Development edition installed on one of our server. When I connect it with IP address using SSMS it gets connected but when I connect it using server name it gives below error:Cannot generate SSPI context.Kindly help.

SQL query help

Posted: 26 Jun 2013 02:04 AM PDT

Hey guys i need to create a query were i can find out the number of learners there are with a model of 21 and another model eglearner model mamzy 20mamzy 21mamzy 30jack 21jack 99greg 99greg 30harper 20RESULTLEARNER modelmamzy 20mamzy 21mamzy 30jack 21 jack 99please helpive tried select learner, modelfrom table namewhere model = 21 and model = 20 or model = 30 or model = 99group by learner, modelbut i get a long list as the first execution please help!!!!!!!!

Global temporary tables as MYSQL named locks

Posted: 20 Jun 2013 01:10 AM PDT

Hello!I was wondering if it is a good idea to use global temporary tables as MySQL named locks?In certain situations I have to "lock" a record, so only one user can modify it. And by modification I mean here the whole process, not just the update command, which finally writes the new data into the table. So I thought that by creating a global temporary table with a well defined rule based name before starting the editing of the record, I could check, if a specific record of a table is being currently edited by someone. The global temporary table would be dropped after editing the record, or in case of an application, or some other crash, it would be dropped automatically by the SQL server. And one more thing, the global temporary table would contain only a few (2-5) fields, and a single record.So, what do you think? Is it a good idea, or it could lead to performance problems having such temp tables?Thanks!

join tabel

Posted: 26 Jun 2013 04:00 PM PDT

hello all masters plese help me to join this query SELECTrj_poliklinik.nmpoli as namapoli,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,instansi.nminstansi as namainstansi,sum (rj_transdt.komptr1) as karcisFROM rj_regJOIN rj_transdt on rj_reg.noreg = rj_transdt.noregJOIN rj_poliklinik on rj_reg.kdpoli = rj_poliklinik.kdpoliJOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansiJOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayanWHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' AND (Tarif_Pelayanan.groupkd = '1') and rj_reg.batal='false'group by rj_reg.kdpoli,rj_reg.kdperusahaan,rj_poliklinik.nmpoli,instansi.nminstansi order by namapoli descSELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,sum (rj_transdt.komptr1) as tindakanFROM rj_transdtJOIN rj_reg on rj_transdt.noreg=rj_reg.noregJOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansiJOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayanWHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '2'group by rj_reg.kdperusahaan,instansi.nminstansiSELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,sum (rj_transdt.komptr1) as alkesFROM rj_transdtJOIN rj_reg on rj_transdt.noreg=rj_reg.noregJOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansiJOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayanWHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '3'group by rj_reg.kdperusahaan,instansi.nminstansi

SQLServer Replication The process could not connect to Distributor .

Posted: 26 Jun 2013 06:31 PM PDT

Good Day. We configured Pull Replication and I checked the number of rows being stored in the replicated tables and they are the numbers match. But when I look at the properties of the subscriptions , the abovementioned message is dispalyed. We use SQL Server Security . I was even able to sign onto the Distributor database using the same account. The account has all the required permissions and there is no entry in SQL's log that there are failed logins . Any ideas ?

Trying to remove similar records

Posted: 26 Jun 2013 12:07 PM PDT

create table dbo.tableinfo(list1 varchar(10),list2 varchar(10),Num int)insert into dbo.tableinfo values('A','A',5),('A','B',2),('A','C',7),('A','D',2),('B','A',2),('B','B',4),('C','A',7),('C','B',6),('C','C',7),('C','D',8),('C','E',3),('D','A',2),('D','B',5),('D','C',8)Expected out put is as following:A B 2A C 7A D 2C B 6C D 8C E 3D B 5Condition1, is do not display same same in list1 and list2, eg A A 5condition2, is do not display "A B 2" and "B A 2" twice, show any one only once.

How to insert the results of a powershell command into a table?

Posted: 04 Jan 2012 12:33 PM PST

Hi,I've got a PS command and a table on an SQL server below. How do I insert the output of the command into the table?Thanks.$Item = @("DeviceId", "MediaType", "Size", "FreeSpace")Get-WmiObject -query "Select * from Win32_logicaldisk" | Format-Table $item -autoCREATE TABLE [dbo].[DiskSpace]( [Drive] [char](2) NOT NULL, [MediaType] [smallint] NOT NULL, [Size] [bigint] NULL, [FreeSpace] [bigint] NULL)

Database Mail not sending mail if number of recipients are more

Posted: 26 Jun 2013 12:21 PM PDT

Hi All,In one of the servers, database mail is not sending mails if the To address is more than 4 and CC is more than 2. I am getting the below error[b]"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2013-06-26T18:11:38). Exception Message: Cannot send mails to mail server. (The operation has timed out.)."[/b]There is no issues with SMTP server. It was working last month but stopped working this month. Running out of clues.. Thanks for your help!!!

Data movement from one server to another

Posted: 25 Jun 2013 11:56 PM PDT

Dear All,How can i move data(only data) from one db server to another dbserver(both are on different machine). Both data have same structure, size of source database is 8 GB.on another database there is some mismatch in data so how can i make them in sink.

Logshipping Alerts

Posted: 25 Jun 2013 10:49 PM PDT

Hi all,Previous DBA has deleted the databases with out disabling the log shipping configuration. so when i am trying to see reports and log shipping status i am seeing alerts for log shipping for which even the databases are not exist in the instance. i can see some kind of alerts in error log as well. how to disable these alerts.Thanks

64 bit linked server to a folder of text files?

Posted: 17 Nov 2011 03:27 AM PST

Does anyone have a working linked server to a folder of text files in 64 bit yet?I know the Jet driver was supposedly replaced with the new ACE driver for Office 64 bit;However, I don't seem to be able to use it to replace some previous functionalities...With the Jet driver, you could set up a Linked server to a folder full of text files [url=http://msdn.microsoft.com/en-us/library/ms190479.aspx]Microsoft BOL Linked Servers[/url], which i had done lots of times previously, and posted lots of forum examples here to boot.now, with my 64 bit 2008, after installing the 64 bit AccessDatabaseEngine_x64.exe drivers, i cannot do the same;i'm getting this error:[quote][color="#FF0000"]Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TxtSvr" reported an error. Provider caused a server fault in an external process.Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TxtSvr". The provider supports the interface, but returns a failure code when it is used.[/color][/quote]for reference, here is the exact code that i'm using to create my linked server to the folder c:\Data:[code]--#################################################################################################--Linked server Syntax for Folder Full Of Text Files--#################################################################################################--add a folder as a linked server to access all .txt and .csv files in the folderDECLARE @server sysname, @srvproduct nvarchar(256), @provider nvarchar(256), @datasrc nvarchar(100), @location nvarchar(100), @provstr nvarchar(100), @catalog sysname, @sql varchar(1000)SET @server = N'TxtSvr'SET @srvproduct = N'OLE DB Provider for ACE'SET @provider = N'Microsoft.ACE.OLEDB.12.0'SET @datasrc = N'C:\Data'set @provstr = 'Text'EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr--===== Create a linked server to the drive and path you desire. --EXEC dbo.sp_AddLinkedServer TxtSvr, -- 'MSDASQL', -- 'Microsoft.ACE.OLEDB.12.0', -- 'C:\', -- NULL, -- 'Text'GO--===== Set up login mappings.EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULLGO--===== List the tables in the linked server which is really a list of -- file names in the directory. Note that the "#" sign in the -- Table_Name is where the period in the filename actually goes. EXEC dbo.sp_Tables_Ex TxtSvrGO--===== Query one of the files by using a four-part name. SELECT * FROM TxtSvr...[xmlmap#txt]--===== Drop the text server EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'GO[/code]

Backup Size Changes - Data and Logs don't?

Posted: 26 Jun 2013 09:09 AM PDT

Hi All, I've never seen this before. I have an 11GB backup file(native SQL compression). I restore it to a test database which has a used portion of the data file of around 50GB(the file is actually 75GB buta thrd unused). The immediately backup the file again and it backs up to a file of around 9GB. How can this be?Some other info:SQL 2008 R2 SP1 - Standard Edition.The database is backed up and restored to the same sql instance.No SQL service packs or patches applied.No Windows patches applied(although the server WAS restarted between the backup and restore, so potentially something was left unapplied)The backup was taken at the start of a failed vendor deployment, and I don't know what else they did.Backup has no filestream files, but filestream is enabled on server.

Dettach and Attach multi databases

Posted: 26 Jun 2013 05:21 AM PDT

Can some one please provide me the script to dettach and attach multiple databases? Please help

Using a Stored Procedure - Best Practice? Detecting Error?

Posted: 26 Jun 2013 06:04 AM PDT

Hello all.I have a SP which inserts data and all works fine. I have also written an ASP.NET (VB) page which passes the data to the SP and the SP inserts the data. All is good.I don't want to assume anything and especially that the SP worked fine without error. I suppose if something goes wrong, I will see some kind of system generate error message on the page but I was hoping to learn how to handle an error more gracefully. How do you more experienced developers do this? Can I get the SP to return a True or "success" kind of value if all goes well and a False or "Error" if something fails? That way, I can detect for it in my ASP.NEt page(s)....

Syntax error in SQL Statement when executing SPROC

Posted: 26 Jun 2013 12:16 AM PDT

We have a SPROC that when i try to execute i receive the following error message of "Syntax error line 7 at or after token <AS>. [10179] " Below is the actual code of the SPROC. Any suggestions?USE [ConstarOLAP_PROPHIX_FactDb]GO/****** Object: StoredProcedure [dbo].[ActualPurchasePriceExport] Script Date: 06/26/2013 08:37:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =======================================================-- Author: Tom Stagliano, Constar-- Create date: June 25, 2013-- Description: Prophix Actual Purchase Price Export SPROC-- =======================================================ALTER PROCEDURE [dbo].[ActualPurchasePriceExport](@Start_Date DATETIME = NULL,@Part_Type_MP VARCHAR(1000) = '')ASSET @Part_Type_MP = ',' + @Part_Type_MP + ','SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))DECLARE @sqlQuery NVARCHAR(MAX),@finalQuery NVARCHAR(MAX),@q CHAR(1) = ''''CREATE TABLE #POLINEKEY(PART_NO VARCHAR(100),LINE_ITEM_KEY INT)SET @sqlQuery = 'SELECT PLK.Part_no,PLK.line_item_keyFROM(SELECT p.part_no, MAX(PLI.Line_Item_Key) FROM Purchasing _v_Line_Item_e AS PLI JOIN Part_v_Part_e AS P ON p.plexus_customer_no = pli.plexus_customer_no AND p.part_key = pli.part_key WHERE pli.add_date <= @Start_Date AND (@Part_Type_MP = ,, OR (CHARINDEX(, + CAST(P.Part_Type as VARCHAR(50)) + ,, @Part_Type_MP) >0)) GROUP by p.part_no ) AS "PLK"'SET @finalQuery = 'SELECTp.part_no AS "Part",pli.unit_price AS "ActualCost"FROM OPENQUERY (PLEXREPORTSERVER, ' + @q + @SQLQuery + @q + ')'INSERT INTO #POLINEKEY( PART_NO, LINE_ITEM_KEY)EXEC(@finalquery)EXEC sp_executesql @finalqueryINSERT INTO dbo.tblActualPriceselect * from #POLINEKEY

how to substring chunks of a string starting at different positions?

Posted: 26 Jun 2013 06:36 AM PDT

i am spinning my wheels....i need to extract a distinct string from the first character up to the position where there is a number and then what ever, if anything follows the number, but cannot seem to get to it. Even though there are two Abilifys and six ACETAMINOPHENs, i only need it once without the dosages or strengths, but i need the type in a new column, so if its a TABLET or CAPLET, that should also be extracted, but placed in a new column. Further, idk if ACETAZOLAMID is a misspelling of ACETAZOLAMIDE, but i am guessing distinct will return them both.use tempdbcreate table #drugnames(drugname varchar(50))insert #drugnames select'10 SERIES BP MIS MONITOR' UNION ALLselect'ABILIFY 20 MG TABLET' UNION ALLselect'ABILIFY 5 MG TABLET' UNION ALLselect'ACARBOSE TAB 50MG' UNION ALLselect'ACCOLATE 20 MG TABLET' UNION ALLselect'ACCUNEB 0.63 MG/3 ML INH SOLN' UNION ALLselect'ACCUSURE INSULIN SYRN 0.5 ML' UNION ALLselect'ACE ELASTIC BANDAGE 3"' UNION ALLselect'ACETAMIN SUP 325MG' UNION ALLselect'ACETAMINOPHEN 100 MG/ML DROP' UNION ALLselect'ACETAMINOPHEN 120 MG SUPPOS' UNION ALLselect'ACETAMINOPHEN 160 MG/5 ML ELIX' UNION ALLselect'ACETAMINOPHEN 325 MG TABLET' UNION ALLselect'ACETAMINOPHEN 500 MG CAPLET' UNION ALLselect'ACETAMINOPHEN 650 MG/20.3 ML' UNION ALLselect'ACETAMINOPHEN/COD #3 TABLET' UNION ALLselect'ACETAZOLAMID TAB 250MG' UNION ALLselect'ACETAZOLAMIDE 250 MG TABLET' UNION ALLselect'ACETIC ACID 0.25% IRRIG SOLN' UNION ALLselect'ACID CONTROL 75 MG TABLET'--select drugname from #drugnamesso for example, my limits are reached here...select distinct substring(drugname, 1, charindex(' ',drugname)-1) from #drugnamesdrop table #drugnamesbut that doesnt get it because first of all it returns only ACE, and i need ACE ELASTIC BANDAGE...but in the case of ACETAMINOPHEN i need ACETAMINOPHEN DROP, ACETAMINOPHEN SUPPOS, ACETAMINOPHEN TABLET, ACETAMINOPHEN CAPLET, ACETAMINOPHEN ELIX and ACETAMINOPHEN ML, but i only need those suffixes once.how can i get from here to there?thanks very much

Seeking Feedback/Recomendations on Auditing, both Simple and Detailed

Posted: 11 Jun 2013 09:45 AM PDT

[b]SQL PLATFORM:[/b] 2008r2 Enterprise 64Bit[b]SIMPLE AUDITING =[/b] Capture the name and date/time for each row that is inserted or updated in a table. This is just INSERT & UPDATE since a DELETE action would leave no row to store the info.[b]DETAIL AUDITING = [/b]Capturing the Name of the user, the date/Time of the command (UPDATE, INSERT and DELETE)As of current I can implement Simple Auditing and Detail Auditing via the use of table triggers and additional audit tables (tables that store info from the DELETED table anytime an UPDATE or DELETE DML statement is executed). However I'm looking for a better, more robust solution, ideally one that's native to SQL Server if 2008R2 has such a thing.My company has been using SQL Server 2000 and more recently 2005 for management of our primary accounting database which is form a third party vendor that specializes in vertical accounting markets. We recently moved over to 2008R2 and I'm hoping that this will open up some options for us like better auditing.Does anyone know if SQL 2008R2 natively has table level auditing that stores the info either in the table where the change has been made (SIMPLE AUDITING ) or in a backup or secondary audit table when you want to capture not only who and when but the "what changed" as well? From what I've read about SQL SERVER AUDIT you don't have the option to save this info to a table and that's important for us. In fact I don't know why someone would prefer to use windows logs except for when it comes to performance and trying to avoid adding to the DB Server workload and or size by storing the audit info within the DB. I had hoped that SQL 2008 R2 might natively offer a new data type that when you set a column to it, the thing would auto-record the login info of the user account/login that the DML statement is being executed under. Then again maybe I'm the odd one for thinking windows logs are not the best pace to store audit info. I just seems logical to me that you'd want to store that info a normalized fashion so you can use T-SQL to qeury/mine the audit info.In any event, if you know of a third party product (perhaps something by Red-Gate) that can do this please share that in a post.Thanks

Get min and max dates based on end flag

Posted: 26 Jun 2013 04:52 AM PDT

HiI have a result set which produces EndFlag for an activity.The resultset is like below.CustNo, ActivityStart, ActivityEndDate, ActivityEndActivityEnd is derived based on current ActivityEndDate and next row's ActivityStartDate is > 7 hours ActivityEnd = 1Now, I need to generate final resultset with CustNo, MIN(ActivityStartDate), MAX(ActivityEndDate) for each ActivityEnd = 1Means if the ActivityEnd = 1 then we need to MIN(ActivityStart) where ActivityStartDate <= ActivityEndDate of ActivityEnd = 1I am struggling to get this done.Can anyone give an exampleSample Resultset and expected output as below.CustNo ActivityStartDate ActivityEndDate DiffHours ActivityEnd42 12/21/2006 11:35 12/21/2006 13:40 1 042 12/21/2006 14:10 12/21/2006 16:30 1 042 12/21/2006 17:00 12/21/2006 18:15 0 042 12/21/2006 18:45 12/21/2006 20:00 33 142 12/23/2006 07:00 12/23/2006 10:00 0 042 12/23/2006 10:30 12/23/2006 13:35 15 142 12/24/2006 07:00 12/24/2006 10:00 0 042 12/24/2006 10:30 12/24/2006 13:35 1 042 12/24/2006 14:30 12/24/2006 19:30 5 042 12/25/2006 00:00 12/26/2006 00:00 0 1Need output asCustNo ActivityStartDate ActivityEndDate42 12/21/2006 11:35 12/21/2006 20:0042 12/23/2006 07:00 12/23/2006 13:3542 12/24/2006 07:00 12/26/2006 00:00

A way to shred an XML Deadlock report for easier reading...

Posted: 26 Jun 2013 02:46 AM PDT

I'm not taking credit for this, I just took some code WayneS posted a couple years back here: [url=http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx]http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx[/url] and updated it / modified it some to work with the XML that SQL2008 spits out when you query the Extended Events...[code="sql"]declare @deadlock xmlset @deadlock = 'put your deadlock graph here'select [PagelockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [KeyLockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@objectname', 'varchar(200)'), [KeyLockIndex] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@indexname', 'varchar(200)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock[1]/victim-list[1]/victimProcess[1]/@id', 'varchar(50)') then 1 else 0 end, [ProcessID] = Deadlock.Process.value('@id', 'varchar(50)'), [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(5)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), --[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [BatchTime] = Deadlock.Process.value('@lastbatchstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') from @deadlock.nodes('/deadlock/process-list/process') as Deadlock(Process)[/code]Just had to change some little things, SQL no longer wraps the XML in "<deadlock-list>" blocks, and the victim information is now a couple more layers deep.And yes, I'm working with a dev right now trying to troubleshoot deadlocks in an application...Thanks WayneS!

granting insert access for everyone to a table

Posted: 26 Jun 2013 01:45 AM PDT

Ok I created a database AuditDB, and in it one table called ServerLogonHistory. Then I create a logon trigger like so:[code="sql"]use mastergoCREATE TRIGGER [Tr_ServerLogon]ON ALL SERVER FOR LOGONASBEGINif( (ORIGINAL_LOGIN() <> 'sa')and APP_NAME() not like 'SQLAgent%'and APP_NAME() not like '%IntelliSense')INSERT INTO AuditDb.dbo.ServerLogonHistorySELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()ENDGO[/code]Now how can I grant every user access to this table so they can logon? Is there a grant insert on <tablename> to public in MSSQL? If not, how is it best done?thanks

Mirror Database - Cannot create snapshot

Posted: 11 Aug 2010 07:30 PM PDT

Hi,I have a mirrored database configuration, with two SQL Server 2008 Enterprise boxes, and SQL Server 2008 Express as a witness.I have configured the mirroring, and reviewed the mirroring state via a select on sys.database_mirroring. All seems correct, and I now want to take a snapshot of the mirror database (in restoring state) to validate that the data changes made on the principal are in fact being persisted on the mirror.I am attempting to create the snapshot using the below script:-CREATE DATABASE MyDatabase_DD_MM_YYYY_Snapshot ON(NAME = N'MyDatabase', FILENAME = N'C:\TestSnapshots\MyDatabase_DD_MM_YYYY_Snapshot.ss') AS SNAPSHOT OF MyDatabase;The problem I am having is that when I attempt to create the snapshot I am receiving the below error:-Msg 5014, Level 16, State 3, Line 1The file 'MyDatabase' does not exist in database 'MyDatabase'I don't understand this, as my data file is named 'MyDatabase'.Any advice would be greatly appreciated.Thanks

Foreign Keys WITH CHECK and NOT FOR REPLICATION

Posted: 25 Jun 2013 09:28 PM PDT

Howdy,I've been checking the foreign keys in my system for whether they are trusted in order to leverage the optimiser being able to effectively ignore joins if no column is return from the referenced table.However, I can't seem to get them to work if the foreign key is replicated. If I run the below command, the foreign key is successfully created:-[code="sql"]CREATE TABLE [dbo].[zUser]( [UserId] [int] IDENTITY(1,1) NOT NULL, [Username] [varchar](50) NOT NULL, [Firstname] [varchar](50) NOT NULL, [Surname] [varchar](50) NOT NULL CONSTRAINT [pk_zUser] PRIMARY KEY CLUSTERED ([UserId] ASC))CREATE TABLE [dbo].[Debtor]( [DebtorId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [CreatedBy] [int] NOT NULL CONSTRAINT [pk_Debtor] PRIMARY KEY CLUSTERED ([DebtorId] ASC) ) ALTER TABLE [dbo].[Debtor] WITH CHECK ADD CONSTRAINT [FK_Debtor_zUserCreatedBy] FOREIGN KEY([CreatedBy])REFERENCES [dbo].[zUser] ([UserId])NOT FOR REPLICATION GOALTER TABLE [dbo].[Debtor] WITH CHECK CHECK CONSTRAINT [FK_Debtor_zUserCreatedBy]GO[/code]However, checking the sys.foreign_keys table shows that the foreign key is not trusted. In this instance I need to replicate the Debtor table to a read only server, but the zUser table is superfluous to requirements on that server. Ideally I don't want to bother replicating it at all, but I'm worried that I now have a foreign key that isn't being checked.I also can't run the Foreign Key creation without the NOT FOR REPLICATION option because it then screws up the replication. If I drop the article for the table, recreate the foreign key WITH CHECK and without the NOT FOR REPLICATION, then recreate the article and don't include foreign key constraints it seems to work. I'd prefer not to do this unless I absolutely have to though, due to the downtime this will cause.I've found [url=http://support.microsoft.com/kb/246323]this article[/url] stating it was a bug way back when, but has since been fixed. Is this maybe not true?Cheers,Matthew

implicit transaction with a SQL SERVER Agent job

Posted: 25 Jun 2013 08:51 PM PDT

Good Morning.Is possible to change the isolation level for a job, or to do that the Agent not initialize a transaction for a job?Thanks.

Backup Size.

Posted: 25 Jun 2013 11:00 PM PDT

Hi Team,Today's Physical backup size in D: Drive is showing 21GB.but when i run the below query, it is showing 43260149.000000 KB means 41GB.Query....SELECT physical_device_name, backup_start_date, backup_finish_date, backup_size/1024.0 AS [Backup Size (KB)]FROM msdb.dbo.backupset bJOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_idWHERE database_name = 'TRACK'ORDER BY backup_finish_date DESCPlease suggest

What is the best way to configure default and named instance to work with DNS aliases?

Posted: 25 Jun 2013 11:25 PM PDT

I have seen a number of people asking how to set up multiple instances using same port, same ip, etc. Basically, how to do what they want to do. What I want to know is, what is the BEST or ACCEPTED way to set up 2 instances and have them both aliased? Should they have static ports and use port forwarding somewhere? Use browser and dynamic ports? Although it is interesting to hear what "YOU" have done, I would like to see some support in a white paper, blog, or somewhere else for any suggestions so that I can provide support to my boss. Not that I don't trust the experience here, but someone's word (unless it is someone well known in the industry) is not good enough to propose a change in existing configuration. Any help is appreciated.

Renaming a DB that is on Availability Group

Posted: 25 Jun 2013 11:04 PM PDT

Friends,We have few Databases that are added in Availability Group under Availability Databases. We are planning to rename these databases. Would the Database name change automatically under the Availability Group or should we change the same manually. ThanksMurali
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog