Viewing 15 posts - 5,206 through 5,220 (of 7,619 total)
Detach now (since SQL 2005, IIRC) changes the security on the files -- this is an intentional thing by MS, as a "security feature". But it's been a royal...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2015 at 2:11 pm
You check for a "GLOBAL" cursor but you don't explicitly specify a GLOBAL cursor when you DECLARE it. Whether a cursor is local or global can vary based on...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2015 at 2:07 pm
You should look into RCSI as well. That may solve your issue even more completely and with less effort during coding :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 3:11 pm
CREATE FUNCTION [dbo].[CheckLoginDetails_Username] (
@SuppliedUsername nvarchar(150),
@SuppliedPassword nvarchar(150)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 1:00 pm
Maybe a join to reduce repetition of comparisons:
SELECT bf.BF_ORGN_CD, bf.BF_BDOB_CD, bf.BF_TM_PERD_CD, bf.data
FROM BF_DATA bf
INNER JOIN (
VALUES('A1', 'B1', 'C1'),
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 9:56 am
There used to be both recoverability and performance reasons for the split. Now it's more about recoverability than performance.
If possible, you want the data and logs on separately "fail-able"...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 9:49 am
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 8:09 am
jgale 70688 (4/13/2015)
Both solutions did work. However, the data I'm working with is slightly more complex than my simple example. My main data table is a...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 4:33 pm
Grant Fritchey (4/13/2015)
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 4:32 pm
Eirikur Eiriksson (4/13/2015)
ScottPletcher (4/13/2015)
SELECT...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 3:33 pm
Luis Cazares (4/13/2015)
ScottPletcher (4/13/2015)
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
Why? :unsure:
It's...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 2:58 pm
Here's an alternative approach, also using CROSS APPLY, that can take advantage of the (presumed) clustered index on prefix.PREFIX_VALUE; no guarantee, but it might perform better.
SELECT p_cross_apply.PREFIX_VALUE, SUM(d.DATA_VALUE) AS...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 2:54 pm
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 2:37 pm
In general, since you want to avoid grouping by varchar columns anywhere, as it's not very efficient, delay converting numeric codes until the outer-most query you can.
select DB_NAME(a.database_id) AS [Database...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 2:25 pm
Off the top of my head, I would use a temp table here, something like below. I don't have time to finish all the median values in the SELECT,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2015 at 3:57 pm
Viewing 15 posts - 5,206 through 5,220 (of 7,619 total)