Viewing 15 posts - 14,731 through 14,745 (of 14,953 total)
You can also find missing dates with a Numbers table more easily than is outlined in the article.
To set up the test:
create table MissingDates (
Date datetime primary key)
go
insert into dbo.missingdates(date)
select...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2008 at 7:53 am
Yet again, I have to say this is clever, but a Numbers table is better.
Method in the article, run against a table with 9989 rows:
----------------
SQL Server parse and compile time:...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2008 at 7:01 am
I'm assuming all of this is to create a logging function. You'll run it on a schedule, and thus have a sort of picture of the state of the...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 2:11 pm
If that does what you need, you might want to try replacing the exec command with sp_executeSQL. It works better, and can take input parameters and such. Books...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 2:04 pm
It's been so long since I used it, I forgot that exec doesn't like string functions. Try this:
declare @date datetime, @cmd varchar(1000)
set @date = getdate()
set @cmd = 'SELECT *...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 2:01 pm
Please copy and paste your code into the forum, and the error message you get.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:40 pm
The foreign key enforces the data at a level below the proc.
This means that, if someone tries to delete data from the primary table, without a foreign key constraint, it...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:38 pm
Use:
declare @date datetime
set @date = getdate()
exec ('SELECT * INTO [table_Backup_'+cast(@date as varchar(100))+'] FROM table' )
First, you need to declare @date as datetime, not timestamp. (Look up timestamp in...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:02 pm
create table #Temp (
ID int identity primary key,
FromID int,
ToID int)
insert into #temp (fromid, toid)
select *
from
(select 1, 1000000 union all
select 1000001, 2000000 union all
select 2000001, 3000000 union all) -- however many...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:50 pm
The only way I know to do what you're asking is to move the second table variable to a separate proc, and call that proc with input parameters from the...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:38 pm
There was a very good article recently on the front page of this site about running totals, and being able to split them into running sub-totals. Check that. ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:35 pm
Truncating will also help prevent excessive log growth.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:30 pm
Not sure why it wouldn't appear there on its own.
To fix it, you can right-click it in Program Files, select "Create Shortcut", then drag the shortcut to your start menu.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:25 pm
;with
Rep1 (Prod, Acct, TC, SubmitRep1, Percent1, ID, Seq) as
(select Prod, Acct, TC, SubmitRep1, Percent1,
row_number() over (order by Prod, Acct, TC, SubmitRep1, Percent1, 1)
from dbo.tbl_CYProcessedSales T1
WHERE
Proj IN (100,200)
AND Percent1...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:22 pm
Does it need to be columns, or would a list (with commas or some such) work?
Is there a reason to have SQL do this, and not to have the front...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 11:59 am
Viewing 15 posts - 14,731 through 14,745 (of 14,953 total)