connect from ASP app: tables

Showing posts with label tables. Show all posts

Thursday, March 22, 2012

cant figure out how to write query..

Hi,
I have 3 tables, a person table, a timeRecords table, and a
RegionPersonHistory table.
The timeRecords table holds how many days were worked for a particualr date,
and the RegionPersonHistory keeps track of the persons Region. People can be
allocated to work on different regions so they might be working in the US fo
r
3 days then the following 4 days are in Europe etc.
I am trying to figure out how to write a query that will calulate how many
days were worked in any one region of a any specific date period. The tricky
thing is that the RegionPersonHistory table only holds records for a person
if they change from their default region (their default region is held in th
e
Person table). It doesnt always hold records for the person.
Have a look at the sql below which sets up the tables and see if you
understand my problem.
Here is the sql for the tables and some sample data...
CREATE TABLE [SYSDBA].[RegionPersonHistory] (
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[enddate] [datetime] NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
CREATE TABLE [SYSDBA].[TimeRecords] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[days] [integer] NOT NULL,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SYSDBA].[Person] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-01',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-02',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-03',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-04',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-05',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-06',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-07',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-08',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-09',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-10',1
go
insert [SYSDBA].[Person]
select 'smithd','US'
go
insert [SYSDBA].[RegionPersonHistory]
select 'smithd','Europe','2006-01-04','2006-01-08'Can you give a sample result that you might need from the inputs?
"NH" wrote:

> Hi,
> I have 3 tables, a person table, a timeRecords table, and a
> RegionPersonHistory table.
> The timeRecords table holds how many days were worked for a particualr dat
e,
> and the RegionPersonHistory keeps track of the persons Region. People can
be
> allocated to work on different regions so they might be working in the US
for
> 3 days then the following 4 days are in Europe etc.
> I am trying to figure out how to write a query that will calulate how many
> days were worked in any one region of a any specific date period. The tric
ky
> thing is that the RegionPersonHistory table only holds records for a perso
n
> if they change from their default region (their default region is held in
the
> Person table). It doesnt always hold records for the person.
> Have a look at the sql below which sets up the tables and see if you
> understand my problem.
> Here is the sql for the tables and some sample data...
> CREATE TABLE [SYSDBA].[RegionPersonHistory] (
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [enddate] [datetime] NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [SYSDBA].[TimeRecords] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [days] [integer] NOT NULL,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [SYSDBA].[Person] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-01',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-02',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-03',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-04',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-05',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-06',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-07',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-08',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-09',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-10',1
> go
> insert [SYSDBA].[Person]
> select 'smithd','US'
> go
> insert [SYSDBA].[RegionPersonHistory]
> select 'smithd','Europe','2006-01-04','2006-01-08'|||Hello again Omnibuzz,
well, if I was to try to calculate the sum(days) that were worked in the US
region between 2006-01-01 and 2006-01-10 I would find that difficult because
theres a few days there that person 'smithd' logged while he was allocated t
o
the 'Europe' region. His default region is the US but the query needs to
check to see if any of the days logged between thoese dates were actually
part of a different region.
Those this make sense?
"Omnibuzz" wrote:
> Can you give a sample result that you might need from the inputs?
> "NH" wrote:
>|||Try this and let me know if this was what you wanted.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-04'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
group by a.persid,coalesce(b.region,c.region)|||Thats not quite right, that returns 8 days when it should be only 4.
Then also the query needs to take in a thrid paramter to filter for a
particualr region.
Maybe this is just a bit too messy...
"Omnibuzz" wrote:

> Try this and let me know if this was what you wanted.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-04'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> group by a.persid,coalesce(b.region,c.region)|||It worked fine for the data you gave.
Can you give the data for which the error and tell me what are the filters
and what is the expected result
"NH" wrote:
> Thats not quite right, that returns 8 days when it should be only 4.
> Then also the query needs to take in a thrid paramter to filter for a
> particualr region.
> Maybe this is just a bit too messy...
> "Omnibuzz" wrote:
>|||sorry your query does return the same value I get.
I gave you a mistake in the source data, can you run this...
delete from RegionPersonHistory
insert [SYSDBA].[RegionPersonHistory]
select 'Europe','smithd','2006-01-04','2006-01-08'
I have this modified query now...
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid, count(a.days)
from Person c, TimeRecords a
left join RegionPersonHistory b on (a.startdate between b.startdate and
b.enddate
and b.region='us')
where a.startdate between @.a and @.b
and a.persid = c.persid
and c.region='us'
group by a.persid
I am trying to only return days worked in the US... but cant figure it out..
.
"Omnibuzz" wrote:
> It worked fine for the data you gave.
> Can you give the data for which the error and tell me what are the filters
> and what is the expected result
> "NH" wrote:
>|||Okay, this works for me. You added the filter wrong.
I have changed my first query. And I saw the mitake in the insert and I
changed it :)
Check this and let me know if this works.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
and coalesce(b.region,c.region) = 'us'
group by a.persid,coalesce(b.region,c.region)|||Thanks Omnibuzz, it looks like this is working.
I appreciate your help once again.
NH
"Omnibuzz" wrote:

> Okay, this works for me. You added the filter wrong.
> I have changed my first query. And I saw the mitake in the insert and I
> changed it :)
> Check this and let me know if this works.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-05'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> and coalesce(b.region,c.region) = 'us'
> group by a.persid,coalesce(b.region,c.region)
>

Sunday, March 11, 2012

Can't delete Web Assistant task

I used the Web Assistant tool to monitor a couple of tables. When I was fin
ished with the monitoring after a couple of weeks, I didn't want to monitor
the tables anymore. I deleted the proceedure, and now when I add data to th
e tables, I get an error me
ssage:
Failed@.CDBBaseGroupObj::dbwrite attribute xref.Database error:[Microsoft][ODBC SQL Serv
er Driver][SQL Server] SQL Web Assistant: Could not execute the SQL statemen
t.
I click "OK" on the error message and proceed as normal.
Can anyone point me in the right direction of getting rid of this error mess
age popping up?
thanks,
SKClarkWhen you select the option to monitor tables in the Web Assistant, it
also creates triggers. If all you did was manually drop the stored
procedure, then the triggers likley still exist. At this point you'll
need to delete them manually. In future, use sp_dropwebtask, which
will clean up all of the objects.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Tue, 10 Feb 2004 08:51:08 -0800, "SKClark"
<anonymous@.discussions.microsoft.com> wrote:

>I used the Web Assistant tool to monitor a couple of tables. When I was finished w
ith the monitoring after a couple of weeks, I didn't want to monitor the tables anym
ore. I deleted the proceedure, and now when I add data to the tables, I get an erro
r m
essage:
>Failed@.CDBBaseGroupObj::dbwrite attribute xref.Database error:[Microsoft][ODBC SQL Ser
ver Driver][SQL Server] SQL Web Assistant: Could not execute the SQL stateme
nt.
>I click "OK" on the error message and proceed as normal.
>Can anyone point me in the right direction of getting rid of this error mes
sage popping up?
>thanks,
>SKClark|||cool...I found 3 triggers attached to a table. I deleted them and now the
error message doesn't show up anymore.
Thanks for your help!!! :-)
-- Mary Chipman wrote: --
When you select the option to monitor tables in the Web Assistant, it
also creates triggers. If all you did was manually drop the stored
procedure, then the triggers likley still exist. At this point you'll
need to delete them manually. In future, use sp_dropwebtask, which
will clean up all of the objects.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Tue, 10 Feb 2004 08:51:08 -0800, "SKClark"
<anonymous@.discussions.microsoft.com> wrote:

>I used the Web Assistant tool to monitor a couple of tables. When I was finished w
ith the monitoring after a couple of weeks, I didn't want to monitor the tables anym
ore. I deleted the proceedure, and now when I add data to the tables, I get an er[/
color]
ror message:
>thanks,
>SKClark

Cant delete user table

I have 4 user tables that show up in Enterprise Manager as SYSTEM tables.
The type and xtype in sysobjects are both U. They show up in object browser
under user tables. When I try to delete them I get the following message:
You tried to delete one or more system objects. They were not deleted.
Anyone have any ideas as to what is going on here and how I can delete these?Can you mention the name of these tables?
Thanks
GYK
"Lyle" wrote:
> I have 4 user tables that show up in Enterprise Manager as SYSTEM tables.
> The type and xtype in sysobjects are both U. They show up in object browser
> under user tables. When I try to delete them I get the following message:
> You tried to delete one or more system objects. They were not deleted.
> Anyone have any ideas as to what is going on here and how I can delete these?|||Yes, the table names are:
conflict_pubInvHist_Head_Det_Invoice_History_Detail
conflict_pubInvHist_Head_Det_Invoice_History_Header
conflict_PubInvoice_Head_Det_Invoice_History_Detail
conflict_PubInvoice_Head_Det_Invoice_History_Header
Lyle
"GYK" wrote:
> Can you mention the name of these tables?
> Thanks
> GYK
> "Lyle" wrote:
> > I have 4 user tables that show up in Enterprise Manager as SYSTEM tables.
> > The type and xtype in sysobjects are both U. They show up in object browser
> > under user tables. When I try to delete them I get the following message:
> >
> > You tried to delete one or more system objects. They were not deleted.
> >
> > Anyone have any ideas as to what is going on here and how I can delete these?|||Yes, the table names are:
conflict_pubInvHist_Head_Det_Invoice_History_Detail
conflict_pubInvHist_Head_Det_Invoice_History_Header
conflict_PubInvoice_Head_Det_Invoice_History_Detail
conflict_PubInvoice_Head_Det_Invoice_History_Header
Lyle
"GYK" wrote:
> Can you mention the name of these tables?
> Thanks
> GYK
> "Lyle" wrote:
> > I have 4 user tables that show up in Enterprise Manager as SYSTEM tables.
> > The type and xtype in sysobjects are both U. They show up in object browser
> > under user tables. When I try to delete them I get the following message:
> >
> > You tried to delete one or more system objects. They were not deleted.
> >
> > Anyone have any ideas as to what is going on here and how I can delete these?|||Yes, the table names are:
conflict_pubInvHist_Head_Det_Invoice_History_Detail
conflict_pubInvHist_Head_Det_Invoice_History_Header
conflict_PubInvoice_Head_Det_Invoice_History_Detail
conflict_PubInvoice_Head_Det_Invoice_History_Header
Lyle
"GYK" wrote:
> Can you mention the name of these tables?
> Thanks
> GYK
> "Lyle" wrote:
> > I have 4 user tables that show up in Enterprise Manager as SYSTEM tables.
> > The type and xtype in sysobjects are both U. They show up in object browser
> > under user tables. When I try to delete them I get the following message:
> >
> > You tried to delete one or more system objects. They were not deleted.
> >
> > Anyone have any ideas as to what is going on here and how I can delete these?

Cant delete user table

Thursday, March 8, 2012

can't delete a full text catalog

Hello
I've defined a full text catalog that indexes several columns from two tables.
Now I want to erase it and it simply don't let me... using the console it
reports that my catalog has been lost and to use sp_fulltext_catalog to
repopulate or rebuild it...
I tryied so and the error was the same...
i need to erase that useless catalog because it don't work anymore and i
need to do another one with the same name...
how can i get it done?!?!
best regards
Jorge Ribeiro
Can you try to right click on your table in EM, and select Full Text Index
Table, and then select edit. Click through the dialogs until you get the new
catalog creation dialog. Create a new catalog.
This will clear some of this class of errors. If this won't work you will
probably have to use some of the full text procedures and possibly manually
remove some rows from the system tables. Post back here and I'll try to
help you further with this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:F60177EB-6333-4755-A035-202EC62FB27F@.microsoft.com...
> Hello
> I've defined a full text catalog that indexes several columns from two
tables.
> Now I want to erase it and it simply don't let me... using the console it
> reports that my catalog has been lost and to use sp_fulltext_catalog to
> repopulate or rebuild it...
> I tryied so and the error was the same...
> i need to erase that useless catalog because it don't work anymore and i
> need to do another one with the same name...
> how can i get it done?!?!
> best regards
> Jorge Ribeiro
|||Hi
I've done what you sujested (create a new catalog) and the result was the
same... the catalog has been lost and so on...
it didn't create a new catalog and the old one still persists
what can I do now?!?!
thanx
Jorge Ribeiro
"Hilary Cotter" wrote:

> Can you try to right click on your table in EM, and select Full Text Index
> Table, and then select edit. Click through the dialogs until you get the new
> catalog creation dialog. Create a new catalog.
> This will clear some of this class of errors. If this won't work you will
> probably have to use some of the full text procedures and possibly manually
> remove some rows from the system tables. Post back here and I'll try to
> help you further with this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
> news:F60177EB-6333-4755-A035-202EC62FB27F@.microsoft.com...
> tables.
>
>
|||drop the catalog using sp_fulltext_catalog 'catalogname', 'drop'
After you have completed doing this, delete the rows from
sysfulltextcatalogs
Then try to build your catalogs again.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:C159A27C-6C4F-4743-B532-1FF11E754DA4@.microsoft.com...[vbcol=seagreen]
> Hi
> I've done what you sujested (create a new catalog) and the result was the
> same... the catalog has been lost and so on...
> it didn't create a new catalog and the old one still persists
> what can I do now?!?!
> thanx
> Jorge Ribeiro
>
> "Hilary Cotter" wrote:
Index[vbcol=seagreen]
new[vbcol=seagreen]
will[vbcol=seagreen]
manually[vbcol=seagreen]
message[vbcol=seagreen]
it[vbcol=seagreen]
to[vbcol=seagreen]
i[vbcol=seagreen]
|||i've already done that
i've got an error message
Cannot drop full-text catalog 'cat_intranet_teste_paginas' because it
contains a full-text index.
can i still delete the catalog row from sysfulltextcatalogs?!
will it work?!?!
"Hilary Cotter" wrote:

> drop the catalog using sp_fulltext_catalog 'catalogname', 'drop'
> After you have completed doing this, delete the rows from
> sysfulltextcatalogs
> Then try to build your catalogs again.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
> news:C159A27C-6C4F-4743-B532-1FF11E754DA4@.microsoft.com...
> Index
> new
> will
> manually
> message
> it
> to
> i
>
>
|||back up your database. Then delete the rows.
Then try to create another catalog, and go back and edit the tables to move
them to the new catalog.
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:096B13D9-C590-4B33-A2BF-C076FED64EDC@.microsoft.com...[vbcol=seagreen]
> i've already done that
> i've got an error message
> Cannot drop full-text catalog 'cat_intranet_teste_paginas' because it
> contains a full-text index.
> can i still delete the catalog row from sysfulltextcatalogs?!
> will it work?!?!
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
try to[vbcol=seagreen]
two[vbcol=seagreen]
console[vbcol=seagreen]
sp_fulltext_catalog[vbcol=seagreen]
and[vbcol=seagreen]

Wednesday, March 7, 2012

Can't create partitioned table in Developer Edition?

I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
fails saying that partitioned tables may only be created in Enterprise
Edition. What gives, DE is supposed to have all the capabilities of EE?
Scott
On Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
> fails saying that partitioned tables may only be created in Enterprise
> Edition. What gives, DE is supposed to have all the capabilities of EE?
> Scott
I have no problem on a developer edition
What does this return?
select serverproperty('Edition')
I have no problem running the following on a developer edition
CREATE PARTITION FUNCTION pf_Range (int)
AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
GO
CREATE TABLE TestPartition (id int not null)
INSERT TestPartition VALUES(1)
INSERT TestPartition VALUES(2)
INSERT TestPartition VALUES(3)
INSERT TestPartition VALUES(10)
INSERT TestPartition VALUES(11)
INSERT TestPartition VALUES(17)
INSERT TestPartition VALUES(18)
INSERT TestPartition VALUES(19)
INSERT TestPartition VALUES(21)
INSERT TestPartition VALUES(25)
INSERT TestPartition VALUES(22)
INSERT TestPartition VALUES(23)
INSERT TestPartition VALUES(0)
INSERT TestPartition VALUES(5)
GO
CREATE PARTITION SCHEME ps_Range
AS PARTITION pf_Range
ALL TO ([PRIMARY])
GO
CREATE CLUSTERED INDEX IX_TestPartition_ID
ON TestPartition(ID) ON ps_Range(ID)
SELECT $PARTITION.pf_Range(ID) AS Partition,*
FROM TestPartition
ORDER BY Partition
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||That's interesting, it returns 'Standard Edition'. I thought I downloaded DE
with my MDSN subscription. I'll go back through the process to see where I
went wrong.
Thanks.
"SQL Menace" wrote:

> On Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> I have no problem on a developer edition
> What does this return?
> select serverproperty('Edition')
> I have no problem running the following on a developer edition
> CREATE PARTITION FUNCTION pf_Range (int)
> AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
> GO
>
> CREATE TABLE TestPartition (id int not null)
> INSERT TestPartition VALUES(1)
> INSERT TestPartition VALUES(2)
> INSERT TestPartition VALUES(3)
> INSERT TestPartition VALUES(10)
> INSERT TestPartition VALUES(11)
> INSERT TestPartition VALUES(17)
> INSERT TestPartition VALUES(18)
> INSERT TestPartition VALUES(19)
> INSERT TestPartition VALUES(21)
> INSERT TestPartition VALUES(25)
> INSERT TestPartition VALUES(22)
> INSERT TestPartition VALUES(23)
> INSERT TestPartition VALUES(0)
> INSERT TestPartition VALUES(5)
> GO
>
> CREATE PARTITION SCHEME ps_Range
> AS PARTITION pf_Range
> ALL TO ([PRIMARY])
> GO
>
> CREATE CLUSTERED INDEX IX_TestPartition_ID
> ON TestPartition(ID) ON ps_Range(ID)
>
> SELECT $PARTITION.pf_Range(ID) AS Partition,*
> FROM TestPartition
> ORDER BY Partition
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Can't create partitioned table in Developer Edition?

I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
fails saying that partitioned tables may only be created in Enterprise
Edition. What gives, DE is supposed to have all the capabilities of EE?
ScottOn Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
> fails saying that partitioned tables may only be created in Enterprise
> Edition. What gives, DE is supposed to have all the capabilities of EE?
> Scott
I have no problem on a developer edition
What does this return?
select serverproperty('Edition')
I have no problem running the following on a developer edition
CREATE PARTITION FUNCTION pf_Range (int)
AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
GO
CREATE TABLE TestPartition (id int not null)
INSERT TestPartition VALUES(1)
INSERT TestPartition VALUES(2)
INSERT TestPartition VALUES(3)
INSERT TestPartition VALUES(10)
INSERT TestPartition VALUES(11)
INSERT TestPartition VALUES(17)
INSERT TestPartition VALUES(18)
INSERT TestPartition VALUES(19)
INSERT TestPartition VALUES(21)
INSERT TestPartition VALUES(25)
INSERT TestPartition VALUES(22)
INSERT TestPartition VALUES(23)
INSERT TestPartition VALUES(0)
INSERT TestPartition VALUES(5)
GO
CREATE PARTITION SCHEME ps_Range
AS PARTITION pf_Range
ALL TO ([PRIMARY])
GO
CREATE CLUSTERED INDEX IX_TestPartition_ID
ON TestPartition(ID) ON ps_Range(ID)
SELECT $PARTITION.pf_Range(ID) AS Partition,*
FROM TestPartition
ORDER BY Partition
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||That's interesting, it returns 'Standard Edition'. I thought I downloaded DE
with my MDSN subscription. I'll go back through the process to see where I
went wrong.
Thanks.
"SQL Menace" wrote:
> On Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> > I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
> > fails saying that partitioned tables may only be created in Enterprise
> > Edition. What gives, DE is supposed to have all the capabilities of EE?
> >
> > Scott
> I have no problem on a developer edition
> What does this return?
> select serverproperty('Edition')
> I have no problem running the following on a developer edition
> CREATE PARTITION FUNCTION pf_Range (int)
> AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
> GO
>
> CREATE TABLE TestPartition (id int not null)
> INSERT TestPartition VALUES(1)
> INSERT TestPartition VALUES(2)
> INSERT TestPartition VALUES(3)
> INSERT TestPartition VALUES(10)
> INSERT TestPartition VALUES(11)
> INSERT TestPartition VALUES(17)
> INSERT TestPartition VALUES(18)
> INSERT TestPartition VALUES(19)
> INSERT TestPartition VALUES(21)
> INSERT TestPartition VALUES(25)
> INSERT TestPartition VALUES(22)
> INSERT TestPartition VALUES(23)
> INSERT TestPartition VALUES(0)
> INSERT TestPartition VALUES(5)
> GO
>
> CREATE PARTITION SCHEME ps_Range
> AS PARTITION pf_Range
> ALL TO ([PRIMARY])
> GO
>
> CREATE CLUSTERED INDEX IX_TestPartition_ID
> ON TestPartition(ID) ON ps_Range(ID)
>
> SELECT $PARTITION.pf_Range(ID) AS Partition,*
> FROM TestPartition
> ORDER BY Partition
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Can't create partitioned table in Developer Edition?

I'm trying to create a partitioned table in SQL2K5 Developer Edition and it
fails saying that partitioned tables may only be created in Enterprise
Edition. What gives, DE is supposed to have all the capabilities of EE?
ScottOn Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> I'm trying to create a partitioned table in SQL2K5 Developer Edition and i
t
> fails saying that partitioned tables may only be created in Enterprise
> Edition. What gives, DE is supposed to have all the capabilities of EE?
> Scott
I have no problem on a developer edition
What does this return?
select serverproperty('Edition')
I have no problem running the following on a developer edition
CREATE PARTITION FUNCTION pf_Range (int)
AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
GO
CREATE TABLE TestPartition (id int not null)
INSERT TestPartition VALUES(1)
INSERT TestPartition VALUES(2)
INSERT TestPartition VALUES(3)
INSERT TestPartition VALUES(10)
INSERT TestPartition VALUES(11)
INSERT TestPartition VALUES(17)
INSERT TestPartition VALUES(18)
INSERT TestPartition VALUES(19)
INSERT TestPartition VALUES(21)
INSERT TestPartition VALUES(25)
INSERT TestPartition VALUES(22)
INSERT TestPartition VALUES(23)
INSERT TestPartition VALUES(0)
INSERT TestPartition VALUES(5)
GO
CREATE PARTITION SCHEME ps_Range
AS PARTITION pf_Range
ALL TO ([PRIMARY])
GO
CREATE CLUSTERED INDEX IX_TestPartition_ID
ON TestPartition(ID) ON ps_Range(ID)
SELECT $PARTITION.pf_Range(ID) AS Partition,*
FROM TestPartition
ORDER BY Partition
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||That's interesting, it returns 'Standard Edition'. I thought I downloaded DE
with my MDSN subscription. I'll go back through the process to see where I
went wrong.
Thanks.
"SQL Menace" wrote:

> On Apr 10, 9:46 am, Scott <S...@.discussions.microsoft.com> wrote:
> I have no problem on a developer edition
> What does this return?
> select serverproperty('Edition')
> I have no problem running the following on a developer edition
> CREATE PARTITION FUNCTION pf_Range (int)
> AS RANGE RIGHT FOR VALUES(5,10,15,20,25)
> GO
>
> CREATE TABLE TestPartition (id int not null)
> INSERT TestPartition VALUES(1)
> INSERT TestPartition VALUES(2)
> INSERT TestPartition VALUES(3)
> INSERT TestPartition VALUES(10)
> INSERT TestPartition VALUES(11)
> INSERT TestPartition VALUES(17)
> INSERT TestPartition VALUES(18)
> INSERT TestPartition VALUES(19)
> INSERT TestPartition VALUES(21)
> INSERT TestPartition VALUES(25)
> INSERT TestPartition VALUES(22)
> INSERT TestPartition VALUES(23)
> INSERT TestPartition VALUES(0)
> INSERT TestPartition VALUES(5)
> GO
>
> CREATE PARTITION SCHEME ps_Range
> AS PARTITION pf_Range
> ALL TO ([PRIMARY])
> GO
>
> CREATE CLUSTERED INDEX IX_TestPartition_ID
> ON TestPartition(ID) ON ps_Range(ID)
>
> SELECT $PARTITION.pf_Range(ID) AS Partition,*
> FROM TestPartition
> ORDER BY Partition
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Can't create database diagrams

Hello, I'm using C# express with SQL express.

I can create a database, create tables and I can even delete the database but I cannot create database digrams. I get an error that says I don't have a valid dbo user or permissions to impersonate. It asks me if I would like to impersonate but it just fails. Any Ideas? Thanks

In this case the first course is to consider that the error might be telling you exactly what the problem is. Does the account you're logging into the server with have dbo rights?

Another idea may be that your database doesn't have a valid owner, check out the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120849&SiteID=1 for a discussion about this issue.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

Can't Create cube

Is there is a way to create cube against database which tables get new records every few seconds?

I get these errors

Thanks in advance

Warning -2128674815 : Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Log, Column: InsertDate, Value: 2/23/2007 9:22:32 AM.

Thanks in advance

Hello. I have not build anything like this but my guess is that you will have to use ROLAP for dimensions and Measure Groups.

The error you see probably depends on that you have a MOLAP cube where the dimensions have not been processed before the partitions/measure groups.

Keep your datamart in the relational engine is my recommendation. Five seconds is a very short batch window. I suspect that you have a cube directly on top of an OLTP-system.

Maybe Notification Services is a better choise than SSAS2005?

Regards

Thomas Ivarsson

|||

You'll need to update the time dimension and your data incrementally and "lock" the database updates while the update is being performed (to maintain consistency). This'll take some careful consideration of your partitioning scheme, to make sure you maintain a reponsive system.

How often do you need to update the cube? Could you consider doing the following steps:

a) materials the dimension information into a "Time" table

b) use a view "dbo_Log_view", that joins to the "Time" table to only return records that exist in the "Time" table. (Alternatively, you could do the same on the partition SQL statement, but I recommend abstracting it to the view.)

c) Process the cube / partition incrementally?

Note: You could set this up in a SQL Agent job, to run as a two step process automatically.

|||

Thank you guys.

I will have to carefully review all the options. Thank you for all suggestions.

Best Regards,

|||I think you need Integration Services for this. I haven't figured it out myself yet, but I need to make this too, and I came across functions in SSIS wich seemed to be usefull to accomplish this.

Regards,

Eyso

Cant create an index on my view

G'day all.

I am trying to create an index on a view that joins two tables.

I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'

Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.

Code is below and any help greatly appreciated.

CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECT t_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)

GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)BOL:

The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
...
Outer or self joins
...

Saturday, February 25, 2012

Can't copy/export a database with identity NEWBIE

I have a database where some tables have the IDENTITY = YES.
If I copy or export from ServerA to ServerB, the IDENTITY gets set to No.
This has to be a simple problem.
How is it fixed?
If you are simply doing an export/import you need to create the table with
the desired format on the subscriber/destination/target side - i.e. with the
identity property on the column. Then before you do your import set
identity_insert on for that table, i.e.
SET IDENTITY_INSERT MyTableName ONIf you are using bcp use the -E parameter.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeffrey Brandt" <jdbrandt@.verizon.net> wrote in message
news:lO7fi.268$XH5.206@.trndny02...
>I have a database where some tables have the IDENTITY = YES.
> If I copy or export from ServerA to ServerB, the IDENTITY gets set to No.
> This has to be a simple problem.
> How is it fixed?
>
|||You were SOO right.
I ended up using snapshot replication, and pushed it to the other machine.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBM0x%23YtHHA.1728@.TK2MSFTNGP06.phx.gbl...
> You can use queued updating subscribers or merge replication both with
> automatic identity range management.
> HTH,
> Paul Ibison
>

Friday, February 24, 2012

Cant connect with full access!

i have a database that i am trying 2 convert from access to SQL Server...

but when i try to read the SQL Server tables through MSAccess i can't enter or edit data... the database properties, and the user permissions are NOT readonly in both access and SQL Server - so whycan't i write data?

Thanks KrisWhat was the error and ensure the login used to connect from MS Access to SQL server does have required privileges.|||no error i just can't add or edit data ...

special privalages?? - i dun no exactly what u mean but i dun think there should be|||ata bare minimum the user account that you are using should have read select update insert and delete permission on the tables.

are you using a sql login to connect to the sql server or are you using a trusted operating system account?

Can''t Connect to SQl via Windows NT Network

Help!

I'm writing some VB.net 2005 windows applications which connect to various tables within SQL Server 2005.

If my programs are running from C drive, I have no problems connecting to my database.

but if i run my exe file from a network drive, then the SQL server doesnt recognise the user's pc, and wont allow my program to access any data.

Can anyone help me with this problem?

What is the exact error message you came across?

Can you answer the question according to the following guidance?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Good Luck!

Ming.

|||I guess you are receiving exception due to security restrictions, right ? This is caused by the CAS defined for network locations making it not possible to use all applications / assemblies from network ressources.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The problem was solved by adding a strong name when publishing the program.

Thursday, March 22, 2012

cant figure out how to write query..

Sunday, March 11, 2012

Can't delete Web Assistant task

Cant delete user table

Cant delete user table

Cant delete user table

Thursday, March 8, 2012

can't delete a full text catalog

Wednesday, March 7, 2012

Can't create partitioned table in Developer Edition?

Can't create partitioned table in Developer Edition?

Can't create partitioned table in Developer Edition?

Can't create database diagrams

Can't Create cube

Cant create an index on my view

Saturday, February 25, 2012

Can't copy/export a database with identity NEWBIE

Friday, February 24, 2012

Cant connect with full access!

Can''t Connect to SQl via Windows NT Network

connect from ASP app

Blog Archive

About Me