Viewing 15 posts - 5,071 through 5,085 (of 7,613 total)
Another option to generate code to be run on the other server:
DECLARE @view_template varchar(8000)
SET @view_template = '
CREATE VIEW [$schema$].[$table$]
AS
SELECT * FROM [SourceDatabase].[$schema$].[$table$] UNION ALL
SELECT * FROM [ArchiveDatabase].[$schema$].[$table$]
GO
'
SELECT REPLACE(REPLACE(@view_template,
...
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".
May 27, 2015 at 1:09 pm
Create a replacement values table, and use it to do the data replacement.
CREATE TABLE dbo.data_replacements (
column_name varchar(30) NOT NULL,
current_value varchar(100) NULL,
...
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".
May 22, 2015 at 8:25 am
You'd have to generate dynamic SQL to do that. First, get the highest count as it exists ni your current data. Then modify the above overall structure to...
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".
May 21, 2015 at 4:18 pm
How about?:
SELECT p.Warehouse,
CASE WHEN SUM(Quantity) >= 10
THEN CAST(SUM(Quantity) AS varchar(30))
...
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".
May 21, 2015 at 3:46 pm
JediSQL (10/31/2014)
In most conditional statements, like WHERE clauses and IF statements, the conditional expression will return TRUE if and only if SQL Server considers the assertion provably true. When...
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".
May 21, 2015 at 1:20 pm
Here's one quick-and-dirty way to do this for up to 5 instances:
Edit: changed the column names to better match the table defs.
with instanceInfo as
(
select
ins.srvID
,ins.instNetName 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".
May 21, 2015 at 1:01 pm
CORRECTION: Previous code has bug because the tally table does not include a 0.
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
...
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".
May 19, 2015 at 3:52 pm
Select FROM the job table and user LEFT OUTER JOINs on the other two tables.
[This sounds possibly like class work or home work, so I'm just giving a general guideline...
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".
May 19, 2015 at 3:41 pm
Edit: Was able to post an inline CTE this time; guess they fixed the bug at work that was blocking posting any CTEs ... Hallelujah!
DECLARE @Sample TABLE(
...
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".
May 19, 2015 at 3:37 pm
I don't like table overhead when it can be avoided by simple mathematical calcs. Calendar tables have their uses, but to me they can sometimes become the proverbial hammer...
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".
May 19, 2015 at 3:11 pm
You should only rebuild or reorganize an index when it is fragmented enough to be significant. For some tables, this might be every day. For others, only every...
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".
May 19, 2015 at 9:36 am
Presumably some number of UDFs would get updated over time, particularly as new techniques are learned. I believe, for example, that the splitter function has gone thru several iterations,...
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".
May 19, 2015 at 8:28 am
Jason A. Long (5/15/2015)
Just tetested with this...
DECLARE
@BegDate DATE...
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".
May 18, 2015 at 9:56 am
Interesting points. (Although I'd prefer to put the test conditions / "known case" conditions into a table, for the usual reasons of flexibility and maintainability.)
As an aside, the function...
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".
May 18, 2015 at 8:49 am
Jeff Moden (5/15/2015)
Jason A. Long (5/15/2015)
ScottPletcher (5/15/2015)
Never use BETWEEN on dates or datetimes;
Out of curiosity, why do you say this?
I could see it it you were talking about...
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".
May 18, 2015 at 8:26 am
Viewing 15 posts - 5,071 through 5,085 (of 7,613 total)