create table tbl1(
fld1 varchar(10),
fld2 varchar(10))
insert into tbl1 Values('joe','x')
insert into tbl1 Values('joe','y')
insert into tbl1 Values('joe','z')
insert into tbl1 Values('bill','x')
insert into tbl1 Values('sam','z')
insert into tbl1 Values('ted','x')
insert into tbl1 Values('ted','y')
insert into tbl1 Values('ed','x')
insert into tbl1 Values('mary','x')
insert into tbl1 Values('sue','z')
insert into tbl1 Values('tom','x')
insert into tbl1 Values('tom','y')
insert into tbl1 Values('tom','z')
insert into tbl1 Values('frank','x')
insert into tbl1 Values('samir','x')
insert into tbl1 Values('li','x')
insert into tbl1 Values('li','z')
insert into tbl1 Values('cindy','z')
insert into tbl1 Values('bert','x')
insert into tbl1 Values('jon','x')
--the statement below contains duplicate records.
SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 )
--I want to remove/delete the records from tbl1 where fld1 has count(fld1)
> 1 and fld2 = 'z' -- joe, x, joe, y, joe, z| tom, x, tom, y, tom, z| li, x,[/color
]
li, z.
joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld2.
I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
remove rows where count(fld1)>1 and fld2 = 'z'.
--Here is what I tried that did not work:
Delete from tbl1
Where fld2 in
(SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 ))
and fld2 = 'z'
Any suggestions appreciated how this could be accomplished.
Thanks,
RichTry THis:
Delete tbl1
Where fld1 In
(SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING Count(*)>1 )
And Fld2 = 'z'
"Rich" wrote:
> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich|||Thanks very much.
"CBretana" wrote:
> Try THis:
> Delete tbl1
> Where fld1 In
> (SELECT fld1
> FROM tbl1
> GROUP BY fld1
> HAVING Count(*)>1 )
> And Fld2 = 'z'
> "Rich" wrote:
>|||And for the future, use UNIQUE constraints to ensure your data doesn't get
this way in the first place.
-- Alex
"Rich" wrote:
> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
delete data
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?
ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?
ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints
Sunday, March 25, 2012
Delete a user
Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always recevin
g
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always recevin
g
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
Delete a user
Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
Jac
ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
Jac
ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
Delete a user
Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always receving
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>> Hi how we delete a user in a sql table completly
>> I deleted the user but when im trying to create it again im always
>> receving
>> the error that the user already exist
>> Thanks for your help
>> Jac
>>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always receving
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>> Hi how we delete a user in a sql table completly
>> I deleted the user but when im trying to create it again im always
>> receving
>> the error that the user already exist
>> Thanks for your help
>> Jac
>>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>
Thursday, March 22, 2012
Delete & create a partition
Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.
|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>
|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.
|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>
|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
Delete & create a partition
Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
--
Arnold
"Dan Guzman" wrote:
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> > Hello All,
> >
> > I am working on a hugeee table partionned in 20.
> >
> > Working on one partition at one time, I need to drop and re-create a
> > partition before inserting treated data.
> >
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> >
> > Thanks !!
> > Arnold.
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> To effectively truncate a partition, SWITCH the desired partition into a
>> staging table. The staging table needs to be on the same filegroup(s)
>> with
>> like schema and indexes. You can then drop or truncate the staging table
>> to
>> permanently remove the data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "r.no" <rno@.discussions.microsoft.com> wrote in message
>> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>> > Hello All,
>> >
>> > I am working on a hugeee table partionned in 20.
>> >
>> > Working on one partition at one time, I need to drop and re-create a
>> > partition before inserting treated data.
>> >
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> >
>> > Thanks !!
>> > Arnold.
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
--
Arnold
"Dan Guzman" wrote:
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> > Hello All,
> >
> > I am working on a hugeee table partionned in 20.
> >
> > Working on one partition at one time, I need to drop and re-create a
> > partition before inserting treated data.
> >
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> >
> > Thanks !!
> > Arnold.
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> To effectively truncate a partition, SWITCH the desired partition into a
>> staging table. The staging table needs to be on the same filegroup(s)
>> with
>> like schema and indexes. You can then drop or truncate the staging table
>> to
>> permanently remove the data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "r.no" <rno@.discussions.microsoft.com> wrote in message
>> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>> > Hello All,
>> >
>> > I am working on a hugeee table partionned in 20.
>> >
>> > Working on one partition at one time, I need to drop and re-create a
>> > partition before inserting treated data.
>> >
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> >
>> > Thanks !!
>> > Arnold.
Delete & create a partition
Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) wit
h
> like schema and indexes. You can then drop or truncate the staging table
to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) wit
h
> like schema and indexes. You can then drop or truncate the staging table
to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>
Wednesday, March 21, 2012
Delay in running queries
I have a problem in running queries.
I developed an application uses sqlserver 2005 express edition
I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.
what should I do?
It sounds like you are experience 'blocking' and 'locking' behavior. If you post the complete stored procedure code, we 'may' be able to help you.
And you could also use Profiler to determine what procedures and/or statements in the procedures are causing the blocks.
|||my data base has so many procudures and this problem may happend in running each one . one simple of them is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[STPListStock]
AS
SELECT GoodSyscode, Title, Code, BarCode, Type, FstUnit, SecUnit, UnitRate, OrderPoint, SalePrice1, SalePrice2, SalePrice3, SalePrice4, SalePrice5,
VisitorPer, Comment, DiscontPer, UserPrice, GroupName1, GroupName2, Term, Weight, SerialNo, TypeId, GroupId1, GroupId2
FROM VWCompleteGood
|||try this
EXEC sp_dboption '<db_name>', 'autoclose', 'false';
Delay between CREATE DATABASE and ability to connect to that database
I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
Delay between CREATE DATABASE and ability to connect to that database
I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the d
atabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the
database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As so
on as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a l
ittle while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google a
nd you should find some
info, possibly also in Books Online. So it is possible that you can query sy
s.databases (state_desc
column) to see what state the database is in and based on that connect. Stil
l a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google
and you should find some
> info, possibly also in Books Online. So it is possible that you can query
sys.databases (state_desc
> column) to see what state the database is in and based on that connect. St
ill a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the d
atabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the
database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As so
on as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a l
ittle while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google a
nd you should find some
info, possibly also in Books Online. So it is possible that you can query sy
s.databases (state_desc
column) to see what state the database is in and based on that connect. Stil
l a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google
and you should find some
> info, possibly also in Books Online. So it is possible that you can query
sys.databases (state_desc
> column) to see what state the database is in and based on that connect. St
ill a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
Delay between CREATE DATABASE and ability to connect to that database
I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
>> This is probably for the simple reason that it takes a while to create the database.
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
>> This is probably for the simple reason that it takes a while to create the database.
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
Delay between CREATE DATABASE and ability to connect to that database
I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/On 6 Oct 2006 17:36:01 -0700, Jason Kester wrote:
(snip)
Hi Jason,
You posted the same question and youy already received some answers
there.
In the future, please post to a single group only. It prevents people
wasting time on a problm that has already been solved.
--
Hugo Kornelis, SQL Server MVP
Sunday, March 11, 2012
Defrag or not to Defrag
I have been reading many things on the internet and I wanted to create a thread asking my question here. We currently do all the re-indexing and show contig's etc to maintain my sql data and to ensure everything is good to go there.
My question is, what about the physical drive and data. We house our mdf's on a raid 1_0 and our ldfs on raid 5. I am wondering if I need to defrag these drives b/c if not am i impacting my I/O on that box. If so should I stop the sql service so that it does not corrupt SQL data? Any help on this topic would be great.
-patrick
IF the database files are set to 'Autogrow' and 'Autoshrink', you can get substaintial file fragmentation.
If you maintain your databases, and deliberately enlarge the files only rarely, then you may not have much file fragmentation.
If necessary to defrag the drives, I would prefer to stop SQL Server (and have recent backups).
Friday, March 9, 2012
Defining week ending date.
Hello,
I have a w
ending date question. Here is my table (just for demo
purposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
wending a Saturday. Now, my client wants to change the w ending to
Thursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w number if you want to calculate acco
rding to the ISO
standard (where this w is w 38). SQL Server DATEPART considers this we
ek to be w number 39.
If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w. I think ISOWEEK respects thi
s setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w ending date question. Here is my table (just for demo purpo
ses)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the wen
ding a Saturday. Now,
> my client wants to change the w ending to Thursday (or whatever). I ha
ve no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the ws yourself, but
it gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w.
SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>
I have a w
ending date question. Here is my table (just for demopurposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
w
ending a Saturday. Now, my client wants to change the w ending toThursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w
number if you want to calculate according to the ISO
standard (where this w
is w 38). SQL Server DATEPART considers this week to be w
number 39.If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w
. I think ISOWEEK respects this setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w
ending date question. Here is my table (just for demo purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the w
ending a Saturday. Now,
> my client wants to change the w
ending to Thursday (or whatever). I have no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the w
s yourself, butit gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w
ending date question. Here is my table (just for demo> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> w
ending a Saturday. Now, my client wants to change the w ending to> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w
.SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:
> Hello,
> I have a w
ending date question. Here is my table (just for demo> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> w
ending a Saturday. Now, my client wants to change the w ending to> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>
Defining custom column groups on a matrix report in report builder
I have table called Buildings which has market and sq. ft information.
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott
Wednesday, March 7, 2012
Defining a named set
I have built a cube and I want to add a named set. Following dimensions are important: suscriber and handset. The set I want to create should contain only those subscribers for which the handset is different from the handset from the month before.
I tried to use the filter function resulting in the following:
filter([Dim Subscriber].[Subscriber].[Subscriber].members,([Time].[Month],[Dim Handset].[Dim Handset].[Dim Handset])<>([Time].[Month].prevmember,[Dim Handset].[Dim Handset].[Dim Handset]))
But deploying the cube returns the error message that "<>" cannot be used with sets...
Can anybody help me? Thanks in advance...
Regards
Joos
Use MemberValue for handset
(See help at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/f9b2af16-2b81-48e4-ae81-99f64e4bbc98.htm in Books online).
|||
But then I have to define a specific handset member. I want only those subscribers for which the handset in the current month is different from the month before. Maybe I do have to use the measure base, which simply counts all subscribers?
filter([Dim Subscriber].[Subscriber].[Subscriber].members,([Time].[Month],[Dim Handset].[Dim Handset].[Dim Handset],[Measures].[Base])<>([Time].[Month].prevmember,[Dim Handset].[Dim Handset].[Dim Handset],[Measures].[Base]))
But this returns the error: Error 1 The '<>' operator cannot be used with sets.
|||You mentioned that [Dim Subscriber] and [Dim Handset] dimensions are important, but didn't describe the fact table data. Anyway, assuming that there are only fact records in a given month for valid combinations of Subscriber and Handset, it's still not clear how you define the current month. So, assuming that the current month is the last in the [Time].[Month] hierarchy:
>>
Extract(Filter(NonEmpty([Dim Subscriber].[Subscriber].[Subscriber].Members
* [Dim Handset].[Handset].[Handset].Members * Tail([Time].[Month].[Month].Members),
{[Measures].[Base]}),
IsEmpty(([Measures].[Base], [Time].[Month].PrevMember))),
[Dim Subscriber].[Subscriber])
>>
define default for date report parameter / analysis services
I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.
So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.
SELECT measures.turnover ON COLUMNS,
nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS
FROM [Itdev1 Hk]
I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.
Now, how do I get it to default to the last valid member in the list?
I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.
|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||
this is a helpful tip!
|||i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....
SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
and then in alphabetical order when I sort like this
SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
how do I get it to sort by real date?
Saturday, February 25, 2012
Defaulting the User's ID in a column
Hello,
I'm new to SQL Server and I'm wondering how I can default the User's Id in to a column when they create/modify a record.
Better yet, is there a way to get a list of allowable functions for the Default Parm?
thx so much.
Use the system_user.
ALTER TABLE MyTable
ADD ChangeUser varchar(100) DEFAULT system_user
Most of the system functions are available as DEFAULTS. Check Books Online.
|||if you want to know the database username then system_user will not be the one. sometimes, you can have many database user names mapped to single login. try this and understand.
drop table TestuserDefault
create table TestuserDefault (userid int)
ALTER TABLE TestuserDefault
ADD systemuser varchar(100) DEFAULT system_user
ALTER TABLE TestuserDefault
ADD dbuser varchar(100) DEFAULT current_user
ALTER TABLE TestuserDefault
ADD dbuser1 varchar(100) DEFAULT user
ALTER TABLE TestuserDefault
ADD dbuser2 varchar(100) DEFAULT user_name()
insert into TestuserDefault(userid) select 1
select *from TestuserDefault
Madhu
|||
And of course, if you have multiple users mapped to the same login, there is no way that SQL Server can provide you the User identification information -you will have to do that in the application code, that is, if your application even tracks UserID information.
However, in those situations where IntegratedSecurity is being used, SYSTEM_USER will provide the correct UserName.
Friday, February 24, 2012
Default value of stored procedure parameter
Hi,
This works:
CREATE PROCEDURE MyProc
@.Date smalldatetime = '2005-01-01'
AS
...
But this does not
CREATE PROCEDURE MyProc
@.Date smalldatetime = GETDATE()
AS
...
I'm talking about sql2005. Can anyone help how to overcome this?
You'd have to set it inside the proc not at the definition level. Leave the default as NULL. Inside the proc check if the @.Date IS NULL, then assign the Getdate() to it. Let me try to phrase it "You cannot assign non-deterministic value to a parameter in proc definition".
|||Not exactly the same, but usually works:
CREATE PROCEDURE MyProc
@.Date smalldatetime = NULL
AS
IF @.Date IS NULL SET @.Date=GetDate()
|||
ndinakar:
"You cannot assign non-deterministic value to a parameter in proc definition".
I knew about solution you offered, but real answer I was looking for is sentence I quoted.
Thank you.
Subscribe to:
Posts (Atom)