Thursday, June 27, 2013

[SQL Server] Explanation of LIKE '%[0-9]%'?

[SQL Server] Explanation of LIKE '%[0-9]%'?

Explanation of LIKE '%[0-9]%'?

Posted: 27 Jun 2013 01:06 AM PDT

From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).

Issue with triggers following migration

Posted: 26 Jun 2013 07:35 PM PDT

Ugh, sorry for all the questions. I've inherited this system I know very little about, and everybody else has quit, so I'm having to try and work everything out backwards, without any documentation or history.We have one database system (a) on which when a new record is entered, a trigger (actually several) is meant to fire and update another database (b) on the same instance of SQL Server 2008 R2 (SP2). It was all working fine until we migrated (backup & restore) the databases to new VMs. Database b is no longer being updated when a new record is inserted on database a (via the front-end). I've run a trace, and the trigger does seem to be firing (which it should, because I checked that they weren't disabled). If the update's not happening does this suggest a permissions issue or something? What might be preventing one database interacting with another in this case (bearing in mind that they're on the same default instance).Does anything spring to mind? Thanks.
Posted by at No comments:

[Articles] Better Technical Interviews

[Articles] Better Technical Interviews

Better Technical Interviews

Posted: 26 Jun 2013 11:00 PM PDT

Technical interviews don't work great, but are they dead? Is there a better way? Steve Jones comments today.
Posted by at No comments:

[MS SQL Server] Data copy

[MS SQL Server] Data copy

Data copy

Posted: 26 Jun 2013 05:48 PM PDT

Hi,I have been looking around bu tnot found what I am looking for as yet...I have a production system that has the underlying database built by the application. All thables, indexes, constraints etc are all there. My porblem is that the currnet db seems to be dodgy and I need to get all the data out and into a vanilla database with all the pre built stuff - any ideas as how to get around all the constraints - pks and fks etc ?Appreciate any and all ideas.

datatype doubts

Posted: 26 Jun 2013 07:18 PM PDT

Hi,In sqlserver ,If a column is declared as TIMESTAMP, can we specify to capture the date / time as Universal Coordinated Time (GMT) ?Are there automatic routines that run to synchronize the Server's Clockto the Naval Observatory's master clock? For TIMESTAMP datatypes, is the TIME ZONE automatically included with the date / time or must this be specified manually?Thanks

money datatype

Posted: 26 Jun 2013 08:01 PM PDT

we have an online payment applicationExample: A customer in India pays in Rupees on one transaction,another customer in the US pays in Dollers.For CLOUD implemented databases where a server can exist anywhere in theworld, and transactions can come in from anywhere, can the database be set upto store money in currencies specific to each transaction?

Moving from Standalone to clustered SQL

Posted: 10 May 2013 06:44 AM PDT

Hi -We have a standalone SQL box that we need to migrate all the DBs to a new clusted environment that we will be building. The storage team with me detaching the existing storage desks from the standalone to the clusered servers so we do not move databases, just attach them back to the new clustered servers. The question I have is howto handle the system databases. From some links I see that others recommend restoring system databases, and then attaching the databases. Can you please tell me what is the best way is to handle the master database? Do you restore the master first, then do msdb and model DBs afterwards? The two new nodes we have need to be clustered? Is it better to first make the two nodes set as clustered and then have the storage team do the actual attachments to disks the day we go live?! What is the best way to set the clustered environment?

DBCC UPDATEUSAGE

Posted: 26 Jun 2013 07:08 AM PDT

When a database is upgraded from a SQL Server 2000 instance to a 2005+, if its compatibility level is raised to 90+, can the page and row counts still become inaccurate (after DBCC UPDATEUSAGE) since it came from SQL Server 2000?I understand the counts can be off a little bit for any compatibility level if a database is subject to many DDL changes, but I'm interested particularly in whether it is worse for databases from 2000.That's the impression I get, but can't seem to find a definite answer.Thanks!
Posted by at No comments:

[SQL 2012] calculated fields in SSRS report

[SQL 2012] calculated fields in SSRS report

calculated fields in SSRS report

Posted: 26 Jun 2013 07:20 PM PDT

Hello all i want to add a calculated field in my report this is what i want to put in the expression of my calculated fieldIF UNITE_COUT_MATERIEL = Unité THEN (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * QUANTITE_MATERIELIF UNITE_COUT_MATERIEL = h THEN (SUPERFICIE_INTER_CULTURE / SUPERFICIE_INTER) * DUREE_UTILISATION_MATERIELi put it like this : =Switch(Fields!UNITE_COUT_MATERIEL.Value = "Unité","(Fields!SUPERFICIE_INTER_CULTURE.Value/ Fields!SUPERFICIE_INTER.Value) * Fields!QUANTITE_MATERIEL.Value",Fields!UNITE_COUT_MATERIEL.Value = "h","(Fields!SUPERFICIE_INTER_CULTURE.Value / Fields!SUPERFICIE_INTER.Value) * Fields!DUREE_UTILISATION_MATERIEL.Value")when a execute my report i have just the expression in the report any idea please ?
Posted by at No comments:

[T-SQL] "Running totals" query

[T-SQL] "Running totals" query

"Running totals" query

Posted: 26 Jun 2013 02:22 AM PDT

I need to create a table of running totals. Suppose initially we start with $50, then write a check for $3 (leaving $47), then write another check for $7 (leaving $40), and finally a check for $9 (leaving $31). Here's the end table.check # check_amount Available_Funds----------------------------------------------0000 0 500001 3 470002 7 400003 9 31Other than using a cursor to populate the Available_funds column how could you generate those values using t-sql?TIA,BD

Need help ANSI-92'ing a subquery join

Posted: 26 Jun 2013 01:04 PM PDT

I'm working on converting some old Sybase DBs to SQL, and am stuck on one query. Here's the original:/* originalSELECT A.recid, A.id, A.procid, A.pstatus, B.pname, B.pdesc, LEN(pname) AS plen, CASE WHEN C.seq IS NULL THEN 0 ELSE 1 END AS DISABLEDFROM lprocs A, pmaster B, pmap CWHERE A.procid = B.procid AND A.id = @id AND C.procid =* A.procid AND C.typeid =* ( SELECT D.typeid FROM loan D WHERE D.id = A.id )ORDER BY pname*/And here's my (weak) attempt /* my attempt - obviously produces a different query planSELECT A.recid, A.id, A.procid, A.pstatus, B.pname, B.pdesc, LEN(pname) AS plen, CASE WHEN C.seq IS NULL THEN 0 ELSE 1 END AS DISABLEDFROM lprocs A JOIN pmaster B ON A.procid = B.procid LEFT OUTER JOIN pmap C ON C.procid = A.procidWHERE A.id = @id AND C.typeid in ( SELECT D.typeid FROM loan D WHERE D.id = A.id )ORDER BY pname*/Please help, thanks!

Query showing tables with a specific column in them

Posted: 27 Jun 2013 12:48 AM PDT

I must be doing this wrong..becouse I get columns with no matching table.Here is the query I created to show tables that have a column with the name "solutionscenariocode" as the column name.USE [asccdb]select col.[name] as Column_Name, tbl.[name] as Table_Name from sys.columns colinner join sys.tables tblon tbl.[object_id] = col.[object_id]where col.[name] like 'solutionscenariocode'Checking query should be empty, but it is notUSE [asccdb]select col.[name] as Column_Name, tbl.[name] as Table_Name from sys.columns colleft outer join sys.tables tblon tbl.[object_id] = col.[object_id]where col.[name] like 'solutionscenariocode' and tbl.[object_id] is NULL

Date Query Issue

Posted: 26 Jun 2013 07:32 AM PDT

Hello Everyone - this seams easier than it has been.Here's the sample data:[code="sql"]DECLARE @tblJobHist_source TABLE( SeqNo int UNIQUE NOT NULL ,startYear int NULL ,endYear int NULL ,number int NOT NULL ,name varchar(255) NOT NULL);DECLARE @tblJobHist_resultSet TABLE( SeqNo int UNIQUE NOT NULL ,startYear int NULL ,endYear int NULL ,number int NOT NULL ,name varchar(255) NOT NULL);INSERT INTO @tblJobHist_source SELECT 5,1986,1988,1,'C & B LLC (US)' UNION ALL SELECT 10,1991,1992,1, 'Government of the Iceland' UNION ALL SELECT 15,1992,1996,1, 'O''Martin LLP' UNION ALL SELECT 20,1996,1998,1, 'WorldCo, Inc.' UNION ALL SELECT 25,1998,2000,1, 'WorldCo, Inc.' UNION ALL SELECT 30,2000,2002,1, 'XXX, Inc.' UNION ALL SELECT 35,2002,2003,1, 'WorldCo, Inc.' UNION ALL SELECT 40,2004,2006,1, 'Government of the Iceland' UNION ALL SELECT 45,2006,2010,1, 'Government of the Iceland' UNION ALL SELECT 50,2010,NULL,1, 'Pauls Equity Partners LLC' UNION ALL SELECT 55,NULL,NULL,1, 'ACME Consulting, Inc.';INSERT INTO @tblJobHist_resultSet SELECT 55,NULL,NULL,1,'ACME Consulting, Inc.' UNION ALL SELECT 50,2010,NULL,1,'Pauls Equity Partners LLC' UNION ALL SELECT 45,2004,2010,2,'Government of the Iceland' UNION ALL SELECT 40,2004,2010,2,'Government of the Iceland' UNION ALL SELECT 35,2002,2003,1,'WorldCom, Inc.' UNION ALL SELECT 30,2000,2002,1,'XXX, Inc.' UNION ALL SELECT 20,1996,2000,2,'WorldCo, Inc.' UNION ALL SELECT 25,1996,2000,2,'WorldCo, Inc.' UNION ALL SELECT 15,1992,1996,1,'O''Martin LLP' UNION ALL SELECT 10,1991,1992,1,'Government of the Iceland' UNION ALL SELECT 5,1986,1988,1,'C & B LLC (US)';-- WHAT I HAVE NOW...SELECT * FROM @tblJobHist_source;-- WHAT I NEED...SELECT * FROM @tblJobHist_resultSet ORDER BY SeqNo DESC; [/code]What I have now: [code="plain"]SeqNo startYear endYear number name5 1986 1988 1 C & B LLC (US)10 1991 1992 1 Government of the Iceland15 1992 1996 1 O'Martin LLP20 1996 1998 1 WorldCo, Inc.25 1998 2000 1 WorldCo, Inc.30 2000 2002 1 XXX, Inc.35 2002 2003 1 WorldCo, Inc.40 2004 2006 1 Government of the Iceland45 2006 2010 1 Government of the Iceland50 2010 NULL 1 Pauls Equity Partners LLC55 NULL NULL 1 ACME Consulting, Inc.[/code]What I need:[code="plain"]SeqNo startYear endYear number name55 NULL NULL 1 ACME Consulting, Inc.50 2010 NULL 1 Pauls Equity Partners LLC45 2004 2010 2 Government of the Iceland40 2004 2010 2 Government of the Iceland35 2002 2003 1 WorldCom, Inc.30 2000 2002 1 XXX, Inc.25 1996 2000 2 WorldCo, Inc.20 1996 2000 2 WorldCo, Inc.15 1992 1996 1 O'Martin LLP10 1991 1992 1 Government of the Iceland5 1986 1988 1 C & B LLC (US)[/code]As you can see there are a couple companies that have connecting startYear & endYear... For those the I need to merge the dates; in other words, I need to change this:20 1996 1998 1 WorldCo, Inc.25 1998 2000 1 WorldCo, Inc.into this:25 1996 2000 2 WorldCo, Inc.20 1996 2000 2 WorldCo, Inc.Let me know if you need more clarity. Order is not important. Thanks a ton!

Logic for a complex query which involves grouping and average in SQL

Posted: 26 Jun 2013 07:09 AM PDT

I have 2 tables. Below are the steps I need to follow to get my desired output. I could follow upto Step 3 .Kindly help me as it is a little complex and Im unable to understand how to proceed further. Table 1 [code="sql"] Site Code FailFlag Comments ModifiedDate ModifiedBy ABT A01 F Dfasdf 10/11/2011 Anna ABT A01 F dsfsdf 15/12/2012 Mand ABT A01 Rds 30/03/2011 Tim ABT A01 GHDs 02/12/2012 Andy ABT A02 F dfd 09/05/2012 Anna ABT A02 sdada 11/02/2013 Kathy ABT A02 Dfg 15/05/2011 Rob AFL A02 F asda 13/02/2011 Dan AFL A02 dsaa 24/12/2010 Ryan TRG A01 sdasd 16/04/2010 Richard TRG K05 jksdh 23/04/2012 Mark KLD K05 F sd 18/05/2013 Jim KLD K05 dsfsd 10/03/2012 James KLD K05 sdsd 12/05/2011 Luther KTY K05 F saq 09/09/2012 Ryan KTY K05 asd 04/04/2010 Kathy KMD C02 F nas 29/02/2012 Rob KMD C02 asda 11/11/2011 Andy[/code][code="sql"]CREATE TABLE Table1 ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Site VARCHAR(5), Code VARCHAR(5), FailFlag CHAR(1), Comments VARCHAR(100), ModifiedDate DATETIME, ModifiedBy VARCHAR(50) )[/code][code="sql"]INSERT INTO Table1 (Site, Code, FailFlag, Comments, ModifiedDate, ModifiedBy)SELECT 'ABT', 'A01', 'F', 'Dfasdf', '10/11/2011', 'Anna' UNION ALLSELECT 'ABT', 'A01', 'F', 'dsfsdf', '15/12/2012', 'Mand' UNION ALLSELECT 'ABT', 'A01', NULL, 'Rds', '30/03/2011', 'Tim' UNION ALLSELECT 'ABT', 'A01', NULL, 'GHDs', '02/12/2012', 'Andy' UNION ALLSELECT 'ABT', 'A02', 'F', 'dfd', '09/05/2012', 'Anna' UNION ALLSELECT 'ABT', 'A02', NULL , 'sdada', '11/02/2013', 'Kathy' UNION ALLSELECT 'ABT', 'A02', NULL, 'Dfg', '15/05/2011', 'Rob' UNION ALLSELECT 'AFL', 'A02', 'F', 'asda', '13/02/2011', 'Dan' UNION ALLSELECT 'AFL', 'A02', NULL, 'dsaa', '24/12/2010', 'Ryan' UNION ALLSELECT 'TRG', 'A01', NULL, 'sdasd', '16/04/2010', 'Richard' UNION ALLSELECT 'TRG', 'K05', NULL, 'jksdh', '23/04/2012', 'Mark' UNION ALLSELECT 'KLD', 'K05', 'F', 'sd', '18/05/2013', 'Jim' UNION ALLSELECT 'KLD', 'K05', NULL, 'dsfsd', '10/03/2012', 'James' UNION ALLSELECT 'KLD', 'K05', NULL, 'sdsd', '12/05/2011', 'Luther' UNION ALLSELECT 'KTY', 'K05', 'F', 'saq', '09/09/2012', 'Ryan' UNION ALLSELECT 'KTY', 'K05', NULL, 'asd', '04/04/2010', 'Kathy' UNION ALLSELECT 'KMD', 'C02', 'F', 'nas', '29/02/2012', 'Rob' UNION ALLSELECT 'KMD', 'C02', NULL, 'asda', '11/11/2011', 'Andy' [/code]Table 2 :[code="sql"] Site Code Freq StartDate EndDate ABT A01 43 01/01/2011 01/02/2012 ABT A02 254 01/01/2011 19/02/2011 ABT A02 109 20/02/2011 01/01/2012 ABT A02 12 02/01/2012 01/01/2013 AFL A02 13 01/01/2011 01/02/2012 TRG A01 122 01/01/2011 01/02/2012 TRG K05 61 01/01/2011 01/02/2012 KLD KO5 33 01/01/2011 15/05/2012 KLD K05 79 16/05/2012 01/01/2013 KTY K05 52 01/01/2011 01/02/2012 KMD C02 78 01/01/2011 01/02/2012 ZYT G01 11 01/01/2011 01/02/2012 PYN A01 15 01/01/2011 01/02/2012 DYN F08 122 01/01/2011 01/02/2012[/code][code="sql"]CREATE TABLE Table2 ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Site VARCHAR(5), Code VARCHAR(5), Freq int, StartDate DATETIME, EndDate DATETIME )[/code][code="sql"] INSERT INTO Table2 (Site, Code, Freq, StartDate, EndDate) SELECT 'ABT', 'A01', 43, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'ABT', 'A02', 254, ,'01/01/2011', '19/02/2011' UNION ALL SELECT 'ABT', 'A02', 109, ,'20/02/2011', '01/01/2012' UNION ALL SELECT 'ABT', 'A02', 12, ,'02/01/2012', '01/01/2013' UNION ALL SELECT 'AFL', 'A02', 13, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'TRG', 'A01', 122, ,'01/01/2011', ,01/02/2012' UNION ALL SELECT 'TRG', 'K05', 61, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'KLD', 'KO5', 33, ,'01/01/2011', '15/05/2012' UNION ALL SELECT 'KLD', 'K05', 79, ,'16/05/2012', '01/01/2013' UNION ALL SELECT 'KTY',' 'K05', 52, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'KMD', 'C02', 78, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'ZYT', 'G01', 11, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'PYN', 'A01', 15, ,'01/01/2011', '01/02/2012' UNION ALL SELECT 'DYN', 'F08', 122, ,'01/01/2011', '01/02/2012' [/code]Steps : 1. The combination of two columns 'Site' and 'Code' in Table 1 are looked upon the combination of the columns 'Site' and 'Code' in Tabel2. 2. Filter the same on the 'Failure' column and find out the number of failures Below is the query and the output: [code="plain"]SELECT Site,Code,COUNT(*) as [Count], FailFlagCount= SUM(CASE WHEN F = 'F' THEN 1 ELSE 0 END) FROM Table1 GROUP BY Site,Det[/code] [code="sql"] Site Code Count FailFlagCount ABT A01 4 2 ABT A02 3 1 AFL A02 2 1 TRG A01 1 0 TRG K05 1 0 KLD KO5 3 1 KTY K05 2 1 KMD C02 2 1[/code]3.We check for the same combination in Table 2. i.e., the Site and Code of the step 2 output are looked into Table 2 to get its Frequency 4.Calculations : a. CC % = [1-(FailCount / Count)]*100 = [1-(2/4)]*100 b. B.P.O % = [1-(FailCount / Freq)]*100 = [1-(2/43)]*100 c.Forecast% = Let us assume the current month is March.Calculate the Number of failures over the last 3 years and find the average, and let's say as 'X'.Calculate the failure for remaining months, 'Y' = (X * Remaining months/12)Total failure over 12 months = Current Failure + Y Hence Forecast% = [1-(Total failure over 12 months/ Freq)]*100. For our example, Let us assume the value of X = 2 Hence Y = (2 * 9/12) Forecast % = [1-(1+1.5)]*100 5.The above calculations would be done for all the possible combinations of Site and Code. [code="sql"] Site Code CC B.P.O Forecast StartDate EndDate ABT A01 01/01/2011 01/02/2012 ABT A02 01/01/2011 19/02/2011 ABT A02 20/02/2011 01/01/2012 ABT A02 02/01/2012 01/01/2013 AFL A02 01/01/2011 01/02/2012 TRG A01 01/01/2011 01/02/2012 TRG K05 01/01/2011 01/02/2012 KLD K05 01/01/2011 15/05/2012 KLD K05 16/05/2012 01/01/2013 KTY K05 01/01/2011 01/02/2012 KMD C02 01/01/2011 01/02/2012[/code] 6.The grouping of the above table is done on Site type i.e. first letter of Site.Then the average of the all the calculations( cc,BPO,Forecast) are done after GroupingFor example: - 'A' for 'ABT', 'T' for 'TRG'. (I am assuming that we either create multiple tables for multiple types and then do a union to get the below query) Examples :- [code="sql"] Site Code CC B.P.O Forecast A A01 A A02 [i.e.,Avg value of (ABT and A02) and (AFL and A02)] T A01 T K05 avg [i.e., Avg value of (KLD and K05) and (KTY and K05)] K K05 K C02 [/code] This should be my final output. Kindly help.

Using Pivot When There are two sum(upload),sum(pending)

Posted: 26 Jun 2013 05:08 PM PDT

i used pivot when there was 1 sum(count) and it is working fine, but now the requirement is to do more than 1 sum. below are the required details.My sample data (Sql server 2005)region branch trdate reportcnt pendingBangalore BG40-BELGAUM 06/05/2013 0 1Bangalore BG40-BELGAUM 08/05/2013 2 0Bangalore BG40-BELGAUM 09/05/2013 2 0Bangalore BG40-BELGAUM 14/05/2013 4 1Bangalore BN03-BANGALORE 06/05/2013 6 2Required out putRegion Branch 03/06/2013 04/06/2013 Report Pending Report Pending Bangalore BG40-BELGAUM 1000 20 1 200 30plz help me out

Split total into specified intervals.

Posted: 26 Jun 2013 02:24 PM PDT

Hello,I need to distribute total usage into specified intervals. Please find below an exampleTotal Usage: 1,000Specified Intervals: Block 1 - 0 To 100 ---->100 (First 100 units will be allocated into block #1)Block 2 - 101 To 250 ---->150 (Next 150 units allocated into block #2) etcBlock 3 - 251 To 800 ---->550Block 4 - 900 To 1,000 -->200Block 5 - More than 1,000100+150+550+200= 1,000Blocks intervals is stored in a table and it can change based on geography.Appreciate if anyone can please send me function or SQL code which can do the above.Thanks

Removing similar data

Posted: 26 Jun 2013 12:04 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.
Posted by at No comments:

[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:

Wednesday, June 26, 2013

[SQL Server] General Question

[SQL Server] General Question

General Question

Posted: 26 Jun 2013 02:56 PM PDT

Hi Every One, Can we implement/run an application with huge data without using any database like oracle,ms sql etc..is there any way to run an application like CDSS(CLinical Decision support system) what i heard they never used any database...any one has any idea????Thanks..

SSRS / IsNothing problems

Posted: 10 Jun 2013 08:57 AM PDT

I'm trying to write an expression in SSRS to allow for different fields to display based on the result set. This example is for the Title line, and obviously it would be adjusted for the row containing the data. [u]Objective:[/u]if SectionCategory is null and SectionType is null, then nothing;if SectionCategory is null and SectionType is not null, then "Type"'if SectionCategory is not null, then "Category"[u]Expression:[/u]=switch( Fields!SectionCategory.Value is nothing and Fields!SectionType.Value is nothing, nothing, iif (Fields!SectionCategory.Value is nothing and (IsNothing(Fields!SectionType.Value)="No"), "Type", nothing), iif ((IsNothing(Fields!SectionCategory.Value)="No"), "Category", nothing) )Is it possible to nest IIF statements in a Switch statement? I've experimented several different ways and I'm getting "#Error" in Preview.

Return sepcifc data on query

Posted: 26 Jun 2013 02:37 AM PDT

If I run a query to return an email address from an emails replied to, I get some returnes with multiple email addresses with the email address I want at the end. Example - Service, Customer Support, Customer123@provider.comHow do I remove the info I don't want in these rows but not affect the other rows that are correct?

ERD Lookup

Posted: 26 Jun 2013 07:01 AM PDT

Designing the ERD ..I am trying to create a lookup entity for my Header(Entity) which has Producttype as one of the attributes.Header_---- Tablename(Entity)--------producttype ---- columnname(attribute)---------ProductLookup------Tablename(Entity)Producttype PK (Varchar)columnname(attribute)Is this a good approach ?

trigger causes job to fail

Posted: 26 Jun 2013 05:31 AM PDT

Sorry, please disregard this post.

Help - Need to parse out some values in the a long text field

Posted: 26 Jun 2013 02:40 AM PDT

Hello all, i have the following example(s) of values in a varchar field, but need to parse out specific attributes for analysis.Here are two examples of the data;12/15/2012 19:41:05 : CustList : ReportSubmit : my region code = US, my segment number = 3, my main terr number = 53 - John Smith (802313)12/15/2012 19:45:58 : Summary : my region code = US, my segment number = 0, my main terr number = 0 - Scooter Brown(98513) - (Impersonated, by: Casper Ghost (883599))In the first, i need to parse the date "12/15/2012", then "CustList" between the colons, and last "John Smith(802313)"In the second example, same attributes "12/15/2012", then "Summary" between the colons, then "Scooter Brown(98513)", but need to add "(Impersonated, by: Casper Ghost (883599))"any way this can be done? The dataset contains these two examples throughout, i could get by with running two sql statements, one for the true user (first example), then run a second to get the additional field for impersonatiton.Any help would be greatly appreciated.
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog