connect from ASP app: table

Showing posts with label table. Show all posts

Tuesday, March 27, 2012

Can't get MAX DISTINCT to work the way I want it to

I have 3 numeric fields in a table that need to be returned by a query:
SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
SupervisorLineID is the only definite unique value; there are cases where
one supervisor can manage more than one production line, and thus be
represented many times in the table. There are also cases where more than
one supervisor can manage a line, complicating things further.
What I need to return is one unique record for each LineID, with the highest
SupervisorLineID number as the selecting factor, along with the matching
SupervisorID. I tried using MAX DISTINCT on the SupervisorLineID, but oddly
enough it caused the opposite of what I expected: all supervisors were
returned EXCEPT the one represented by the max SupervisorLineID per line!
This caused lines to appear more than once in many cases.
I've tried various approaches and none produce the desired results. I know
this has to be possible-- any ideas?
Thanks,
Randall ArnoldRandall Arnold wrote:

> I've tried various approaches and none produce the desired results.
> I know this has to be possible-- any ideas?
Please provide DDL and some testdata, you are talking about more than 1
table I presume? We can't guess all the tablestructures.
Kind regards,
Stijn Verrept|||Hi
Since you have not posted a table stucture + sample data I did some testing
on Northwind database and Orders table
SELECT * FROM Orders
WHERE OrderDate=(SELECT MAX(OrderDate) from Orders O
WHERE O.CustomerId=Orders.CustomerId)
Getting highest OrderDate for each Customer
"Randall Arnold" <randall.arnold@.nokia.com> wrote in message
news:OwClf.16116$Nb2.285732@.news1.nokia.com...
>I have 3 numeric fields in a table that need to be returned by a query:
>SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
>SupervisorLineID is the only definite unique value; there are cases where
>one supervisor can manage more than one production line, and thus be
>represented many times in the table. There are also cases where more than
>one supervisor can manage a line, complicating things further.
> What I need to return is one unique record for each LineID, with the
> highest SupervisorLineID number as the selecting factor, along with the
> matching SupervisorID. I tried using MAX DISTINCT on the
> SupervisorLineID, but oddly enough it caused the opposite of what I
> expected: all supervisors were returned EXCEPT the one represented by the
> max SupervisorLineID per line! This caused lines to appear more than once
> in many cases.
> I've tried various approaches and none produce the desired results. I
> know this has to be possible-- any ideas?
> Thanks,
> Randall Arnold
>|||Randall wrote on Wed, 07 Dec 2005 14:41:50 GMT:

> I have 3 numeric fields in a table that need to be returned by a query:
> SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
> SupervisorLineID is the only definite unique value; there are cases where
> one supervisor can manage more than one production line, and thus be
> represented many times in the table. There are also cases where more than
> one supervisor can manage a line, complicating things further.
> What I need to return is one unique record for each LineID, with the
> highest SupervisorLineID number as the selecting factor, along with the
> matching SupervisorID. I tried using MAX DISTINCT on the
> SupervisorLineID, but oddly enough it caused the opposite of what I expect
ed:
> all supervisors were returned EXCEPT the one represented by the max
> SupervisorLineID per line! This caused lines to appear more than once in
> many cases.
> I've tried various approaches and none produce the desired results. I
> know this has to be possible-- any ideas?
> Thanks,
> Randall Arnold
DISTINCT applies to an entire row, not a single column.
Without DDL I've had to make some assumptions.
I think this might work ...
SELECT A.LineID, A.SLID, B.SupervisorID FROM
(SELECT LineID, MAX(SupervisorLineID) AS SLID FROM Table GROUP BY LineID) AS
A)
INNER JOIN Table B ON A.SLID = B.SupervisorLineID AND A.LineID = B.LineID
There's probably an easier way to write this.
Dan|||Actually it's only one table, and I provided the structure (field list) in t
he first post. That's really all there is to the table. Sorry if I didn't
make that clear, but I would have listed other tables if any were involved.
Test data would be as follows:
RecentLineSupervisor_View SupervisorID SupervisorLineID LineID
18 9804 1
108 9913 1
128 -->9964 1
7 9715 19
11 9752 19
118 -->9894 19
1 9831 20
24 9688 20
108 -->9927 20
6 9782 22
77 9771 22
78 -->9978 22
As I said, I just want to see each LineID represented once, with the highest
value for SupervisorLineID determining which record is returned. Desired re
cords marked with arrows.
Thanks,
Randall Arnold
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message news:YLWdnelcNs6FZAveRVnyig@.scarlet.
biz...
> Randall Arnold wrote:
>
>
> Please provide DDL and some testdata, you are talking about more than 1
> table I presume? We can't guess all the tablestructures.
>
> --
>
> Kind regards,
>
> Stijn Verrept|||Randall Arnold wrote:
> I have 3 numeric fields in a table that need to be returned by a query:
> SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
> SupervisorLineID is the only definite unique value; there are cases where
> one supervisor can manage more than one production line, and thus be
> represented many times in the table. There are also cases where more than
> one supervisor can manage a line, complicating things further.
>
If those are the only three columns then the combination of
(supervisorid, lineid) should be unique and declared as such -
otherwise your table is full of redundant garbage. Therefore the answer
would be:
SELECT supervisorid, lineid
FROM your_table AS T
WHERE supervisorlineid =
(SELECT MAX(supervisorlineid)
FROM your_table
WHERE lineid = T.lineid) ;
However, you then seem to be breaking the golden rule of using an
IDENTITY column - don't assign any business significance to it. In
future, please post DDL and sample data so that we don't have to guess
your requirements.
David Portas
SQL Server MVP
--|||Please don't assume I designed this table; rest assured I would NOT have
taken this approach! I'm stuck with trying to extract meaningful
information from a previous employee's cowboy database. In fact, the only
reason I have to create this query at all is to select a single
supervisor-to-line record for each line, and that ability *should* have been
designed into the structure. Had the former data manager done his job
properly, I wouldn't be dealing with this.
As for DDL and structure, it seemed to me I posted enough info. As I told
another gentleman, had there been another table involved I would have
indicated so. I also figured that listing the only 3 fields involved and
describing their type and role would cover it; note that everyone who
provided a suggestion based on a single table was on the right track so it
seems to me it wasn't TOO confusing. But I'll try to be more pedantic in
the future <g>.
Anyway, it looks like one of the methods listed here will work. Thanks all,
Randall Arnold
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133968276.805046.224980@.o13g2000cwo.googlegroups.com...
> Randall Arnold wrote:
> If those are the only three columns then the combination of
> (supervisorid, lineid) should be unique and declared as such -
> otherwise your table is full of redundant garbage. Therefore the answer
> would be:
> SELECT supervisorid, lineid
> FROM your_table AS T
> WHERE supervisorlineid =
> (SELECT MAX(supervisorlineid)
> FROM your_table
> WHERE lineid = T.lineid) ;
> However, you then seem to be breaking the golden rule of using an
> IDENTITY column - don't assign any business significance to it. In
> future, please post DDL and sample data so that we don't have to guess
> your requirements.
> --
> David Portas
> SQL Server MVP
> --
>|||Randall Arnold wrote:
> Please don't assume I designed this table; rest assured I would NOT have
> taken this approach! I'm stuck with trying to extract meaningful
> information from a previous employee's cowboy database. In fact, the only
> reason I have to create this query at all is to select a single
> supervisor-to-line record for each line, and that ability *should* have be
en
> designed into the structure. Had the former data manager done his job
> properly, I wouldn't be dealing with this.
> As for DDL and structure, it seemed to me I posted enough info. As I told
> another gentleman, had there been another table involved I would have
> indicated so. I also figured that listing the only 3 fields involved and
> describing their type and role would cover it; note that everyone who
> provided a suggestion based on a single table was on the right track so it
> seems to me it wasn't TOO confusing. But I'll try to be more pedantic in
> the future <g>.
> Anyway, it looks like one of the methods listed here will work. Thanks al
l,
> Randall Arnold
>
In the absence of the DDL it's reasonable to assume that we lack the
information about the alternate key(s) because that information was so
conspicuously absent from your post. I didn't assume you designed it. I
assumed you'd want to fix it.
David Portas
SQL Server MVP
--|||> As for DDL and structure, it seemed to me I posted enough info.
That's all fine and good. However, in order for us to provide you with a
meaningful, accurate and testable solution, instead of guessing, we ask for
a bit more. I don't understand where the vehement objection to providing
proper specs comes from. The prevailing opinion seems to be that we are
lazy and are trying to be a pain in the ass. Nothing could be further from
the truth... please read http://www.aspfaq.com/5006 before assuming that
what you provide should be enough for anyone to solve the problem.|||There was no "vehement objection" to providing proper specs, Aaron. Just a
minor goof IMO on my part based on personal tendencies (ie, omitting
reference to what *isn't* there such as other tables). I don't understand
where this perception of a "vehement objection" comes from. Certainaly not
from anything I've posted! Personally, I'm all too eager to provide as much
detail as possible. Again, I *thought* I had. Mea culpa. But no need for
a hangin', please. The verbal beating has been enough.
This is a silly axle anyway. I gotta unwrap myself and get back to work
<g>.
Randall Arnold
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyRFaW0%23FHA.3676@.tk2msftngp13.phx.gbl...
> That's all fine and good. However, in order for us to provide you with a
> meaningful, accurate and testable solution, instead of guessing, we ask
> for a bit more. I don't understand where the vehement objection to
> providing proper specs comes from. The prevailing opinion seems to be
> that we are lazy and are trying to be a pain in the ass. Nothing could be
> further from the truth... please read http://www.aspfaq.com/5006 before
> assuming that what you provide should be enough for anyone to solve the
> problem.
>

Can't get current value of a field in table header

Ok, I give up (again). I have a report that breaks on company number.
I want to display
="Details for Company " & Fields!Company.Value
in the table header (or in a textbox within the body above the table).
However, I only get the value of the first company number in the dataset,
even when it breaks on company. How is this done?
Thank you very much,
Marc MillerAdd a table group with a grouping expression of =Fields!Company.Value
In the table group header your expression will work ="Details for Company "
& Fields!Company.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:Oa7l51UgEHA.2416@.TK2MSFTNGP10.phx.gbl...
> Ok, I give up (again). I have a report that breaks on company number.
> I want to display
> ="Details for Company " & Fields!Company.Value
> in the table header (or in a textbox within the body above the table).
> However, I only get the value of the first company number in the dataset,
> even when it breaks on company. How is this done?
> Thank you very much,
> Marc Miller
>

Cant get CAST function to work in ASP.NET

I'm trying to do something like this SQL statement. I have a table with a field of date/times.

i.e - 2/27/06 9:55:95 PM Basically there are multiple entries in the table per day.

I want to count the records for particular (hence the CAST) and output the count.

Here it was I had just to see if SQL would work in ASP.NET :

SELECT COUNT(*) AS Expr1
FROM dbo.tblActiveDrums
WHERE [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0' OR
NCTimeStamp - [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0'

It didn't like the AS references?

Any help would be appreciated.

Remove all the brackets and post if there are any error messages. Shouldn't be.|||You want to count the records for a particular what?

Sunday, March 25, 2012

Can't generate report when there are lot of data.

Hi there,
I have a very simple report which includes only one table to reflect
"event" table in database. It works great when there are few data like
1000 rows, but when there are lot of data like 500000 rows, the report
will run about 15 minutes and give me an error "Execution
'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
at the same time one dialogue will show up to let me login to connect
to my machine. Why is that? I google the group, got no answer.
I am sure I didn't delete anything in ReportServer and
ReportServerTempDB, is there any setting I should change?
Thanks in advance.
HenryHi,
Just make sure you have created datasource and that you have given the
userid and password. if you have given, revisit the datasource and save again.
Try using pagination, ie may be you can display about 50 records in a page
so that the fetching of remaining paged records happen in the background.
Amarnath
"fanh@.tycoelectronics.com" wrote:
> Hi there,
> I have a very simple report which includes only one table to reflect
> "event" table in database. It works great when there are few data like
> 1000 rows, but when there are lot of data like 500000 rows, the report
> will run about 15 minutes and give me an error "Execution
> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> at the same time one dialogue will show up to let me login to connect
> to my machine. Why is that? I google the group, got no answer.
> I am sure I didn't delete anything in ReportServer and
> ReportServerTempDB, is there any setting I should change?
> Thanks in advance.
> Henry
>|||Querying 500,000 records is nothing (I query 185 million row tables).
Returning that many can be problematic. I assume you are not looking at that
many records. You should make sure to use query parameters and bring back
only the data you need. Use query parameters instead of filters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> Hi,
> Just make sure you have created datasource and that you have given the
> userid and password. if you have given, revisit the datasource and save
> again.
> Try using pagination, ie may be you can display about 50 records in a page
> so that the fetching of remaining paged records happen in the background.
> Amarnath
>
> "fanh@.tycoelectronics.com" wrote:
>> Hi there,
>> I have a very simple report which includes only one table to reflect
>> "event" table in database. It works great when there are few data like
>> 1000 rows, but when there are lot of data like 500000 rows, the report
>> will run about 15 minutes and give me an error "Execution
>> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
>> at the same time one dialogue will show up to let me login to connect
>> to my machine. Why is that? I google the group, got no answer.
>> I am sure I didn't delete anything in ReportServer and
>> ReportServerTempDB, is there any setting I should change?
>> Thanks in advance.
>> Henry
>>|||I don't use any filter. It is only a simple report with a simple table.
For our system, it is very easy to have million events within a short
time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
limitation is about 230K rows of data, more than that will get the
error. How can I let customer Stop if they want to check more data? Can
I write some function to check rows of data before generating the
report?
Thanks a lot.
Henry
On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Querying 500,000 records is nothing (I query 185 million row tables).
> Returning that many can be problematic. I assume you are not looking at that
> many records. You should make sure to use query parameters and bring back
> only the data you need. Use query parameters instead of filters.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
>
> > Hi,
> > Just make sure you have created datasource and that you have given the
> > userid and password. if you have given, revisit the datasource and save
> > again.
> > Try using pagination, ie may be you can display about 50 records in a page
> > so that the fetching of remaining paged records happen in the background.
> > Amarnath
> > "f...@.tycoelectronics.com" wrote:
> >> Hi there,
> >> I have a very simple report which includes only one table to reflect
> >> "event" table in database. It works great when there are few data like
> >> 1000 rows, but when there are lot of data like 500000 rows, the report
> >> will run about 15 minutes and give me an error "Execution
> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> >> at the same time one dialogue will show up to let me login to connect
> >> to my machine. Why is that? I google the group, got no answer.
> >> I am sure I didn't delete anything in ReportServer and
> >> ReportServerTempDB, is there any setting I should change?
> >> Thanks in advance.
> >> Henry- Hide quoted text -- Show quoted text -|||A human does not look at a million events. You should have query parameters
that allows the user to get to the timeframe or the even they are interested
in. Even if they are bringing it into another program like Excel, Excel has
a limit of 65,000 rows per sheet.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<fanh@.tycoelectronics.com> wrote in message
news:1162220059.488944.185660@.i42g2000cwa.googlegroups.com...
>I don't use any filter. It is only a simple report with a simple table.
> For our system, it is very easy to have million events within a short
> time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> limitation is about 230K rows of data, more than that will get the
> error. How can I let customer Stop if they want to check more data? Can
> I write some function to check rows of data before generating the
> report?
> Thanks a lot.
> Henry
> On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Querying 500,000 records is nothing (I query 185 million row tables).
>> Returning that many can be problematic. I assume you are not looking at
>> that
>> many records. You should make sure to use query parameters and bring back
>> only the data you need. Use query parameters instead of filters.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in
>> messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
>>
>> > Hi,
>> > Just make sure you have created datasource and that you have given the
>> > userid and password. if you have given, revisit the datasource and save
>> > again.
>> > Try using pagination, ie may be you can display about 50 records in a
>> > page
>> > so that the fetching of remaining paged records happen in the
>> > background.
>> > Amarnath
>> > "f...@.tycoelectronics.com" wrote:
>> >> Hi there,
>> >> I have a very simple report which includes only one table to reflect
>> >> "event" table in database. It works great when there are few data like
>> >> 1000 rows, but when there are lot of data like 500000 rows, the report
>> >> will run about 15 minutes and give me an error "Execution
>> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
>> >> at the same time one dialogue will show up to let me login to connect
>> >> to my machine. Why is that? I google the group, got no answer.
>> >> I am sure I didn't delete anything in ReportServer and
>> >> ReportServerTempDB, is there any setting I should change?
>> >> Thanks in advance.
>> >> Henry- Hide quoted text -- Show quoted text -
>|||Henry,
what do you think you are going to do with 500,000 rows in a
report...?
I once had a customer (1993) who said the product we were selling was
no good because she could not print a list of all the customers her
company had...it was a bank...and they had 3,000,000
customers.....she could not get to grips with the idea that there is
actually nothing useful you can do with a printout of 3,000,000
customers...
Do do something useful with a report, like make a decision that makes
your company money, it generally needs to be more focused than 'all the
transactions we have had recently'...
Best Regards
Peter
fanh@.tycoelectronics.com wrote:
> I don't use any filter. It is only a simple report with a simple table.
> For our system, it is very easy to have million events within a short
> time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> limitation is about 230K rows of data, more than that will get the
> error. How can I let customer Stop if they want to check more data? Can
> I write some function to check rows of data before generating the
> report?
> Thanks a lot.
> Henry
> On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
> > Querying 500,000 records is nothing (I query 185 million row tables).
> > Returning that many can be problematic. I assume you are not looking at that
> > many records. You should make sure to use query parameters and bring back
> > only the data you need. Use query parameters instead of filters.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> >
> >
> >
> > > Hi,
> > > Just make sure you have created datasource and that you have given the
> > > userid and password. if you have given, revisit the datasource and save
> > > again.
> >
> > > Try using pagination, ie may be you can display about 50 records in a page
> > > so that the fetching of remaining paged records happen in the background.
> >
> > > Amarnath
> >
> > > "f...@.tycoelectronics.com" wrote:
> >
> > >> Hi there,
> >
> > >> I have a very simple report which includes only one table to reflect
> > >> "event" table in database. It works great when there are few data like
> > >> 1000 rows, but when there are lot of data like 500000 rows, the report
> > >> will run about 15 minutes and give me an error "Execution
> > >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> > >> at the same time one dialogue will show up to let me login to connect
> > >> to my machine. Why is that? I google the group, got no answer.
> > >> I am sure I didn't delete anything in ReportServer and
> > >> ReportServerTempDB, is there any setting I should change?
> > >> Thanks in advance.
> >
> > >> Henry- Hide quoted text -- Show quoted text -|||I agree with you, I already use a lot of parameters to limit the
report, otherwise it will be huge.
Now the question is how I can limit customers to generate a big report?
Can I write some function to count the data rows first? If more than a
specific number, then show customer a dialog? I don't know I can do
this in RS or not.
Thanks.
Henry
On Oct 30, 12:36 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> A human does not look at a million events. You should have query parameters
> that allows the user to get to the timeframe or the even they are interested
> in. Even if they are bringing it into another program like Excel, Excel has
> a limit of 65,000 rows per sheet.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <f...@.tycoelectronics.com> wrote in messagenews:1162220059.488944.185660@.i42g2000cwa.googlegroups.com...
>
> >I don't use any filter. It is only a simple report with a simple table.
> > For our system, it is very easy to have million events within a short
> > time(2 days or so). The machine has 1G RAM and 1.5G PF, the report
> > limitation is about 230K rows of data, more than that will get the
> > error. How can I let customer Stop if they want to check more data? Can
> > I write some function to check rows of data before generating the
> > report?
> > Thanks a lot.
> > Henry
> > On Oct 26, 8:36 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> Querying 500,000 records is nothing (I query 185 million row tables).
> >> Returning that many can be problematic. I assume you are not looking at
> >> that
> >> many records. You should make sure to use query parameters and bring back
> >> only the data you need. Use query parameters instead of filters.
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Amarnath" <Amarn...@.discussions.microsoft.com> wrote in
> >> messagenews:B5F31853-0A41-4AAE-947C-0BF5B1565BBA@.microsoft.com...
> >> > Hi,
> >> > Just make sure you have created datasource and that you have given the
> >> > userid and password. if you have given, revisit the datasource and save
> >> > again.
> >> > Try using pagination, ie may be you can display about 50 records in a
> >> > page
> >> > so that the fetching of remaining paged records happen in the
> >> > background.
> >> > Amarnath
> >> > "f...@.tycoelectronics.com" wrote:
> >> >> Hi there,
> >> >> I have a very simple report which includes only one table to reflect
> >> >> "event" table in database. It works great when there are few data like
> >> >> 1000 rows, but when there are lot of data like 500000 rows, the report
> >> >> will run about 15 minutes and give me an error "Execution
> >> >> 'uwug2g55hbfrtu55pqh4a1bl' cannot be found (rsExecutionNotFound)", and
> >> >> at the same time one dialogue will show up to let me login to connect
> >> >> to my machine. Why is that? I google the group, got no answer.
> >> >> I am sure I didn't delete anything in ReportServer and
> >> >> ReportServerTempDB, is there any setting I should change?
> >> >> Thanks in advance.
> >> >> Henry- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

can't force index

Hi,
I have a table called "users"
basically, it has a column called "user_id",
and there is a clustered index called "user_id_index",
so everytimes I try to execute
select * from users
I look at the execution plan, it's always used user_id_index, which is
fine.
however, I added another index called "user_name_index"
then I execute:
select * from users (index=user_name_index)
now I look at the execution plan, it is still using "user_id_index" ,
obviously,
it didn't force the index. what happen? why sql server ignore my index
hint?
how do you solve this problem?Recall that the clustered index holds the data as well as the key
columns. Unless the index "user_name_index" contains all the columns of
the table the server still has to read the clustered index to retrieve
the data. You should see a bookmark lookup on the cluster key.
Why do you see this as a problem? Why are you attempting to force an
index hint? Why are you using SELECT *, which potentially hinders index
optimization and shouldn't be used at all in production code.
David Portas
SQL Server MVP
--|||Hi
An not supplying a WHERE clause results in SQL server doing a table scan so
indexes may not be used (why use an index when you are returning all the
data?).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||user_name_index is used on "user_name" column,
but even when I execute
select user_name from users (index=user_name_index)
where user_name='Joe'
I still see the execution plan is using clustered index "user_id_index".
don't you think it's weird?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||> don't you think it's weird?
No. Did you read the replies from Mike and myself?
If you explain what you want to achieve maybe we can help you better.
If you just want to understand indexes and hints then I recommend Kalen
Delaney's book "Inside SQL Server". Hints are an advanced feature and
should be used only when essential and with an understanding of their
effects on the query plan.
David Portas
SQL Server MVP
--|||> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index
"user_id_index".
Maybe you could post some runnable code to reproduce that behaviour
(CREATE..., INSERT..., SELECT...). I don't see that myself. I get
user_name_index used with or without the hint. Also tell us your
edition, version and SP level.
David Portas
SQL Server MVP
--|||1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
table use? You can check this with "sp_spaceused".
2) What version of SQL-Server are you using
3) Please post (simplified) DDL and some sample rows, and preferably a
script to reproduce the behavior.
Gert-Jan
Britney wrote:
> user_name_index is used on "user_name" column,
> but even when I execute
> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index "user_id_index".
> don't you think it's weird?|||Oh my god, sorry guys.
I was wrong about it, "users" is not a table, but it's a view.
I just found out.
In case you ask me why i'm doing this stupid view:
the reason we create a view for this is because I want to do snapshot
isolation for read and write.
if there are data coming in to [2users] table, then I alter view to use
[1users] table. So users table have 2 tables:
Read and write. This way I don't worry about locking.
CREATE VIEW users
AS
select * from [2users]
--
Now We know what is happening...
IF I select from actual table ,
select user_name from [2users] (index=user_name_index)
where user_name='Joe'
I see that it 's using forced index.
I guess view doesn't work correctly for some reason.
---
sp_spaceused [2users]
result:
name rows reserved data index_size unused
[2users] 41892 13952 KB 6616 KB 7144 KB 192 KB
---
select @.@.version
result:
Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
----

> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
>
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42166EA6.EA6900C3@.toomuchspamalready.nl...
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
> 2) What version of SQL-Server are you using
> 3) Please post (simplified) DDL and some sample rows, and preferably a
> script to reproduce the behavior.
> Gert-Jan
>
> Britney wrote:

Thursday, March 22, 2012

Can't find NOT FOR REPLICATION option

» Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.
Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison
|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison
|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison
|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison
|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Cant figure out this query

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.This works...

SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:

select a.sk, max(a.mk) as MK from yourtable a
group by a.sk

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)
>

Can't figure out how to write query

I have a table TABLE1. My company has 2 sites. This table contains employees
with the amount of hours they worked on which project at which sites.

code:

CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')

I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a
project that ended in a 5. If employees worked on projects that did not end
in 5 I need the totals for their site only. A parameter of site will be
passed to the stored procedure.
So for example: If site parameter of 1 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
Any help would be greatly appreciated,
Thanks,
ninel
Message posted via http://www.webservertalk.comThanks for posting DDL and sample data.
declare @.site varchar(1)
set @.site = '1'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
set @.site = '2'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
"ninel g via webservertalk.com" wrote:

>
I have a table TABLE1. My company has 2 sites. This table contains employe
es
>
with the amount of hours they worked on which project at which sites.
>
>

code:

>
 CREATE TABLE #TABLE1 (
>
 Calldate varchar(10) NULL,
>
 Employee varchar(10) NULL,
>
 Project varchar(10) NULL,
>
 Hours decimal(10,4) NULL,
>
 Site varchar(1) NULL)
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '246', 'EACQ5', 3, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ5', 2, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 5, '2')
>

>
>
I need to figure out the following:
>
I need the total hours of employees from both sites ONLY if they worked on
a
>
project that ended in a 5. If employees worked on projects that did not en
d
>
in 5 I need the totals for their site only. A parameter of site will be
>
passed to the stored procedure.
>
>
So for example: If site parameter of 1 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 1.5
>
>
If site parameter of 2 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 5
>
>
Any help would be greatly appreciated,
>
>
Thanks,
>
ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thnak you so much for teh quick response.
Mark Williams wrote:
>Thanks for posting DDL and sample data.
>declare @.site varchar(1)
>set @.site = '1'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>set @.site = '2'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>[quoted text clipped - 49 lines]
Message posted via http://www.webservertalk.comsql

Tuesday, March 20, 2012

cant enter data in field

I have a field in my table that is varchar:1500:null, but I cannot enter any more that 994 characters. any ideas why?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Quotes]
GO

CREATE TABLE [dbo].[Quotes] (
[QuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[DateAdded] [datetime] NULL ,
[CustomerID] [int] NOT NULL ,
[ProductName] [varchar] (100) NULL ,
[RepID] [int] NULL ,
[QuoteNumber] [varchar] (30) NULL ,
[QuoteDate] [datetime] NULL ,
[QuoteTerm] [varchar] (10) NULL ,
[QuoteFOB] [varchar] (15) NULL ,
[QuoteNAIRep] [varchar] (30) NULL ,
[QuoteExpiration] [datetime] NULL ,
[Note] [varchar] (700) NULL ,
[Comment] [varchar] (1500) NULL ,
[OrderRequirement] [varchar] (1000) NULL ,
[Status] [varchar] (1) NULL ,
[DateClosed] [datetime] NULL ,
[ProductType] [varchar] (30) NULL ,
[ImageID] [int] NULL ,
[CloseMonth] [int] NULL ,
[CloseYear] [int] NULL ,
[ClosePercent] [int] NULL ,
[Segment] [varchar] (50) NULL ,
[AccountID] [uniqueidentifier] NULL ,
[ReplacedQuoteID] [int] NULL ,
[Lead] [varchar] (80) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Quotes] ADD
CONSTRAINT [DF_Quotes_Closed] DEFAULT ('N') FOR [Status]
GO

CREATE INDEX [idx_RepID] ON [dbo].[Quotes]([RepID]) ON [PRIMARY]
GO

CREATE INDEX [indx_CustomerID] ON [dbo].[Quotes]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [indx_QuoteID] ON [dbo].[Quotes]([QuoteID]) ON [PRIMARY]
GO

CREATE INDEX [indx_Status] ON [dbo].[Quotes]([Status]) ON [PRIMARY]
GO

[Comment] [varchar] (1500) NULL , is where I can't enter more than 994 characters.

note - I know very little about SQL, I just had the responsibility placed on me at my job. If this is not enough information to go on to throw an idea at me please let me know what else you need.

Thanks in advanceNo such issue i just ran your script and inserted 1500 characters into the comments column....also when you define 1500 it will give you a capacity of 1500

are you using the insert statement or just entering data through enterprise manager?? cuz insert statement worked fine for me...
insert into quotes (Customerid, comment)
values (3,'Your comment goes here')

Also try to find the length of your largest column by using the following script

select len(comment)
from quotes|||i am entering the text manually. Don't know how to run an insert script.

I ran the sel script and the longest entry is 1000 chars.

would you mind telling me how to run the insert script to apply the comment to quote number 070530JB185438?|||I was able to insert the text I need using the insert script you gave, I just had to add more fields to make it apply to the correct quote...

INSERT INTO Quotes

(QuoteID, CustomerID, QuoteNumber, Comment)

VALUES ('35845', '5695', '070530jb185438', 'comment')

Thanks for the help, it is greatly appreciated.:beer:|||since quoteid is generated automatically you dont need to insert a value there...... according to your table design then customerid is the only non nullable field which needs to be present...

hence for testing purpose if you provide a query as
INSERT INTO Quotes
(CustomerID, Comment)
VALUES ('5695', 'comment')
it would still work...since all other columns are nullable......

Also to test your case you can insert a very long string in place of 'comment'... and inside single quotes of course...

other thing what you can do to test the case is use the update statement
update quotes
set comment = 'You comment goes here'
where customerid = '5695'

this will update the record which has customerid = '5695'
i.e. the record which you have just inserted in the previous post|||Here's a function to insert a 1500 character string of x's into your comment field.

INSERT INTO Quotes
(customerid, comment) VALUES ('1234', REPLACE(SPACE(1500), ' ', 'x'))

then check the length with the Len function

Can't Edit Table

Hello, I'm an undergrad who is using SQL Server 2005 for the first time. I have a table Components where there are three columns, ID, Name and Value, where ID and name are the composite key.

I have entered some data into it from the user interface and it is entered just fine. But when I try to edit data in Value column it can't be done. This error message comes...

"No Row was updated
The Data in Row 7 was not committed
Error source: Microsoft.VisualStudio.DataTools
Error Message: The Row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3 rows).
Correct the errors and retry or press Esc to cancel change(s)."

The way I get it, does it say that it is being bound to VS.NET environment? I use it in my VB . NET programme. But I close every time that I open a connection. I'm positive about that. Then I tried, restarting the application, nothing happened! And then I closed Visual Studio .Net and even then nothing happened. And I'm getting very frustrated....

The funny thing is that, when I write a query using the SQL Pane of the SQL Server 2005 then, everything works perfectly. And I don't understand what this is...

I really appreciate if anyone can shed some light into me.
Thank You,
Prasad.

hi,

It seems that you are violation a primary key constraints,

If your using visual studio.net 2003 and later
a schema of the table is stored in the dataset for 2003
or other sqlclient objects of vsnet 2005.

A primary key constraints can be implemeted on these data layers(dataset.. and the likes)

And on the database.

what you can do.

1. Refresh the dataset schema (datatables.. etc) and other ado.net implementation

2. verify that your data is not violting pk on the database tables

hope it helps

|||

I am also getting the same message. I have been using 2000 for some time and this error does not make sense.

My table has no primary key or any relationship to another table. I created the table in the SSMSE and added records using ADO in VB6. I can not delete records and get the following message: "A Problem occurred attempting to delete row X. Error Source: Microsoft.VisualStudio.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(Y rows). Correct the errors and attempt to delete the row again or press ESC to cancel the changes(s)."

SQL will give me a very similar message if I try to edit a field in any of these rows.

Some rows SQL will allow me to edit or delete.

Any Ideas?

Peter

|||

Apparently this only effects SSMSE as a DELETE query issued from inside VB6 using an ODBC connection works flawlessly. This is the same DELETE query I issued using the Query designer in SSMSE.

Seems like a bug in SSMSE.

Peter

|||Suppose you have two identical rows in your table, where column X has the value 7. The query DELETE FROM yourTable WHERE X = 7 will delete both of these rows, and there will be no error. Now suppose you open the table in SSMS and try to delete just one of these rows (or edit just one of them). You will get an error, because there no DELETE (or update) statement is possible to modify just one of two identical rows.

Even without identical rows you may get this error when there is no primary key on the table. The position of a row in the visual editor is not an intrinsic property of the data in the table, and the only modifications you can make to a table are those that can be written as queries with conditions that depend on the column values in the table. A task like "delete the third row" does not correspond to a delete query unless some set of column values unambiguously describes the third row (in other words, unless there is a key). The row number as the table appears in the visual editor is not a table column, so it can't be the sole property used to identify the row to be deleted.

Steve Kass
Drew University|||

I did write a query:

DELETE FROM SalesData WHERE Sequence = 5;

This should have deleted any rows where the Sequence Column is equal to 5. This query did not work in SSMSE when the rows where the Sequence was equal to 5 are identical. My understanding is any rows with a Sequence of 5 should be deleted. This works from VB6 and will not work in the query designer under SSMSE.

Maybe there is some interpretation going on when I select the 7th row in the SSMSE GUI and it tries to delete the rows one at a time and cannot do this since the rows are not unique. The SSMSE query designer should work the same as a Query from VB6 shouldn't it?

SalesData:
Seq Name ID
5 Tom A
5 Tom A
6 Bob B
6 Bob C

These do not work from within SSMSE using wither the GUI or the Query designer and DO work from VB6:

DELETE FROM SalesData WHERE Seq = 5;
DELETE FROM SalesData WHERE Seq = 5 AND ID = 'A';
DELETE FROM SalesData;

These work as expected:

DELETE FROM SalesData WHERE Seq = 6;
DELETE FROM SalesData WHERE Seq = 6 AND ID = 'B';

Peter House

|||

Hi there

got the same problem. Did you manage to solve it?

regards

Michael J

|||

Michael,

I did solve the problem - sort of. Apparently this is an issue with the SQL Management Studio environment. I could do the same exact query from within a VB program or MS Access Query and they would work correctly while the query would not work in the Management Studio.

I have not experienced this problem in a while and use the Management Studio Interface a lot with both the Express and Standard versions.

Peter

|||

Peter

I first experienced it now and I have had ss05 for 5 month and used the feature a lot. But as you say. One can do without it and hope that it resolves.....

Thanks for your reply!

Michael

sql

Can't Edit Table

Hello, I'm an undergrad who is using SQL Server 2005 for the first time. I have a table Components where there are three columns, ID, Name and Value, where ID and name are the composite key.

I have entered some data into it from the user interface and it is entered just fine. But when I try to edit data in Value column it can't be done. This error message comes...

The funny thing is that, when I write a query using the SQL Pane of the SQL Server 2005 then, everything works perfectly. And I don't understand what this is...

I really appreciate if anyone can shed some light into me.
Thank You,
Prasad.

hi,

It seems that you are violation a primary key constraints,

If your using visual studio.net 2003 and later

a schema of the table is stored in the dataset for 2003

or other sqlclient objects of vsnet 2005.

A primary key constraints can be implemeted on these data layers(dataset.. and the likes)

And on the database.

what you can do.

1. Refresh the dataset schema (datatables.. etc) and other ado.net implementation

2. verify that your data is not violting pk on the database tables

hope it helps

|||

I am also getting the same message. I have been using 2000 for some time and this error does not make sense.

SQL will give me a very similar message if I try to edit a field in any of these rows.

Some rows SQL will allow me to edit or delete.

Any Ideas?

Peter

|||

Apparently this only effects SSMSE as a DELETE query issued from inside VB6 using an ODBC connection works flawlessly. This is the same DELETE query I issued using the Query designer in SSMSE.

Seems like a bug in SSMSE.

Peter

Steve Kass
Drew University
|||

I did write a query:

DELETE FROM SalesData WHERE Sequence = 5;

SalesData:
Seq Name ID
5 Tom A
5 Tom A
6 Bob B
6 Bob C

These do not work from within SSMSE using wither the GUI or the Query designer and DO work from VB6:

DELETE FROM SalesData WHERE Seq = 5;
DELETE FROM SalesData WHERE Seq = 5 AND ID = 'A';
DELETE FROM SalesData;

These work as expected:

DELETE FROM SalesData WHERE Seq = 6;
DELETE FROM SalesData WHERE Seq = 6 AND ID = 'B';

Peter House

|||

Hi there

got the same problem. Did you manage to solve it?

regards

Michael J

|||

Michael,

I have not experienced this problem in a while and use the Management Studio Interface a lot with both the Express and Standard versions.

Peter

|||

Peter

I first experienced it now and I have had ss05 for 5 month and used the feature a lot. But as you say. One can do without it and hope that it resolves.....

Thanks for your reply!

Michael

Can't Drop table in Replication

Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.|||

Greg Y wrote:
The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.

I done like that.But I got a problem .I am using backup from replicated database .This backup only i restored on my publisher & subscriber.When I am subscribing three system stored procedure should be generate na for Del,Ins.Upd operations .This is not happening.I tried to delete this system generated procedures in the restored database .But it's not allowing.Then I applied replication Pubs database which is coming along with SQL Server .Then what I seen I published all Tables(10).But Stored procedure only for 5 tables is generated .in these tables whatever changes i am making it's affecting through Replication,Example publisher table in pubs database is not getting any stored procedure after subscription.So it's not getting any change through replication.Then I am getting error message'procedure Sp_MsIns_Publisher not found'.So please tell me what to do remove all old Stored procedures for replication in restored database and how to create the stored procedures for all the tables published

Thanks in Advance

Filson

|||

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

|||

Greg Y wrote:

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

Greg thanks for suggestion.Previously itself I tried these ways to remove replication components.But still System stored procedures for table Ins,Del,Upd is not dropping.I want a specific way to drop it out.I think i mentioned this in earlier post.So kindly suggest me a better way for that

Filson

|||Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.|||

Greg Y wrote:
Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.

I am describing my problem below .I taken a backup of replicated database.That I restored on another server.I made that one as a publisher.Another instance I made as a subscriber.I restored same database on this subscriber.Then I tried for transactional replication.While synchronizing I got error on my subscriber 'Sp_MsIns_AgentCode is not found'.This I got while Inserting records to AgentCode Table in publisher.Then I come to know for each table article published in Transactional replication will have three system generated stored procedures 1)insertion 2)updation 3)Deletion.This is not generating when i subscribing to my publisher..Here when I am pulling the subscription I am specifying 'No Shema & data transfer'.So I am not able to transport this System generated Procedures.If I am selecting 'Schema & data Tansfer'.It won't able to initialize in subscriber due to Foreign Key criterias. after completing Subscription through Wizard,Can I trnsfer UPD,INS,DEL Procedure Schema through any Sp_procedure call? Give me some better way

Filson

|||

I mentioned above to run stored procedure sp_scriptpublicationcustomprocs at the subscriber database after applying the snapshot, did you do that? That proc is supposed to generate the missing sp_MSins/upd/del procs that the distribution agent is trying to execute.

Can't drop subscription

Either through EM or using sp_dropsubscription. Whole system locks up. I've
even tried to drop it on a per article basis. First few table/articles went
fine, but when went to drop an article for a very large table, whole system
locked up again.
--Thanks,
Kristy
try sp_dropsubscription from QA. You might want to stop your distribution,
merge, log reader agents to be able to do this. Then start them up one by
one.
How many subscribers do you have? are they push or pull? Are you running
them continuously or staggering them. If you have a large number >50 (or
perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
11 minutes.
Hilary Cotter
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Either through EM or using sp_dropsubscription. Whole system locks up.
I've
> even tried to drop it on a per article basis. First few table/articles
went
> fine, but when went to drop an article for a very large table, whole
system
> locked up again.
> --Thanks,
> Kristy
>
|||I have run the sp_dropsubscriptions from QA and it locks up. I have also run
it from QA and have been trying to select on a pure article basis. The first
15 articles went fine, but the 16th locked everything up again. This
particular table is massive, so I didn't know if this had anything to do
with it.
We have 1 publisher and 1 subscriber. distributor is currently on publisher.
THe subscriber is really just a hot backup for the publisher since we don't
have clustering. If something were to happen to pub and we couldn't bring
back up then we would switch to sub.
It is currently a push subscription, but we will be using the pull with a
new subscription. the immediate_sync is 1 (which I think means immediate)
and sync_method = 3 (which I have no idea what that means.) The agents have
not been running for 2 days as we are dropping this subscription and
starting over with a remote distributor and pull subscription. I have even
backed up the distribution database, truncated the ms_repltransactions and
ms_replcommands tables and then shrink the distribution database files.
Previously the dist db had grown to about 60 GB with the combined file size.
Now its about 4 MBs.
I have searched all over online and through the repl book and can't find
anything. I don't know what else to do!!!!
Many thanks for your help,
Kristy
we are using tran repl and I have stopped the agents 2 days ago.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
> try sp_dropsubscription from QA. You might want to stop your distribution,
> merge, log reader agents to be able to do this. Then start them up one by
> one.
> How many subscribers do you have? are they push or pull? Are you running
> them continuously or staggering them. If you have a large number >50 (or
> perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
> 11 minutes.
> --
> Hilary Cotter
> 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
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> I've
> went
> system
>
|||I figured it out. The previous DBA had a job that put an UPDLOCK, HOLDLOCK
on the table I was trying to drop from replication. I can probably do a drop
all now instead of a per article basis.
--K
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%23a98n9IMFHA.3988@.tk2msftngp13.phx.gbl...
> I have run the sp_dropsubscriptions from QA and it locks up. I have also
run
> it from QA and have been trying to select on a pure article basis. The
first
> 15 articles went fine, but the 16th locked everything up again. This
> particular table is massive, so I didn't know if this had anything to do
> with it.
> We have 1 publisher and 1 subscriber. distributor is currently on
publisher.
> THe subscriber is really just a hot backup for the publisher since we
don't
> have clustering. If something were to happen to pub and we couldn't bring
> back up then we would switch to sub.
> It is currently a push subscription, but we will be using the pull with a
> new subscription. the immediate_sync is 1 (which I think means immediate)
> and sync_method = 3 (which I have no idea what that means.) The agents
have
> not been running for 2 days as we are dropping this subscription and
> starting over with a remote distributor and pull subscription. I have even
> backed up the distribution database, truncated the ms_repltransactions and
> ms_replcommands tables and then shrink the distribution database files.
> Previously the dist db had grown to about 60 GB with the combined file
size.[vbcol=seagreen]
> Now its about 4 MBs.
> I have searched all over online and through the repl book and can't find
> anything. I don't know what else to do!!!!
> Many thanks for your help,
> Kristy
> we are using tran repl and I have stopped the agents 2 days ago.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
distribution,[vbcol=seagreen]
by[vbcol=seagreen]
or
>

cant drop rowguid or msrepl_tran_version

sql2k sp3
Ive got these columns on a previously replicated table and
I cant get rid of them. I went so far as to disable
Publisheing from the box and still no luck. I also removed
the default values and the indexes from them. I also tried
sp_repldropcolumn. Any ideas?
TIA, ChrisR
Chris,
there is a stored procedure to do this called sp_MSunmarkreplinfo which
takes a tablename as a parameter. Alternatively, setting replinfo to 0 in
sysobjects for the particular table should do it. Finally, running
sp_removedbreplication can be used to remove all traces of replication in
the subscriber database, but obviously must only be done if this database is
not also configured as a publisher.
HTH,
Paul Ibison
|||I appreciate the help. I had already run the
sp_MSunmarkreplinfo. I have dropped the Subscription db's.
And I ran sp_removedbreplication on the Publisher. I still
cant drop them. Any other ideas?

>--Original Message--
>Chris,
>there is a stored procedure to do this called
sp_MSunmarkreplinfo which
>takes a tablename as a parameter. Alternatively, setting
replinfo to 0 in
>sysobjects for the particular table should do it.
Finally, running
>sp_removedbreplication can be used to remove all traces
of replication in
>the subscriber database, but obviously must only be done
if this database is
>not also configured as a publisher.
>HTH,
>Paul Ibison
>
>.
>

Can't drop constraint

Hello,
I am using SS7.
I have a table with a column "yn_LockOut" with a default set to 0. I tried
to delete the column and got the following:
'tblGuidelineResponseOwner' table
- Error modifying column properties for 'yn_LockOut'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'DF_tblGuideLineResponse_yn_LockOut' is not a constraint.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop constraint.
See previous errors.
When I ran exec sp_helpconstraint 'tblGuidelineResponseOwner', I got:
constraint_type constraint_name
DEFAULT on column yn_LockOut DF_tblGuideLineResponse_yn_LockOut
I ran:
alter table tblGuidelineResponseOwner DROP CONSTRAINT
DF_tblGuideLineResponse_yn_LockOut
and got the error message:
'DF_tblGuideLineResponse_yn_LockOut' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 5
Could not drop constraint. See previous errors.
Any help with this would be appreciated.
--
Thanks in advance,
StevenSteven,
Check sp_unbindefault in BOL.
-Mark
This positing is as is
>--Original Message--
>Hello,
>I am using SS7.
>I have a table with a column "yn_LockOut" with a default
set to 0. I tried
>to delete the column and got the following:
>'tblGuidelineResponseOwner' table
>- Error modifying column properties for 'yn_LockOut'.
>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
>Server]'DF_tblGuideLineResponse_yn_LockOut' is not a
constraint.
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
drop constraint.
>See previous errors.
>
>When I ran exec
sp_helpconstraint 'tblGuidelineResponseOwner', I got:
>constraint_type
constraint_name
>DEFAULT on column yn_LockOut
DF_tblGuideLineResponse_yn_LockOut
>
>I ran:
>alter table tblGuidelineResponseOwner DROP CONSTRAINT
>DF_tblGuideLineResponse_yn_LockOut
>and got the error message:
>'DF_tblGuideLineResponse_yn_LockOut' is not a constraint.
>Server: Msg 3727, Level 16, State 1, Line 5
>Could not drop constraint. See previous errors.
>Any help with this would be appreciated.
>--
>Thanks in advance,
>Steven
>
>.
>sql

Monday, March 19, 2012

Can't drop column

I have a couple of questions relating to a merge
replication database.
1.I was unable to add a column to a table being
replicated using EM, but succeeded using
sp_repladdcolumn. When I tried to drop another column
using sp_repldropcolumn, I get the following
message: "ALTER TABLE DROP COLUMN failed
because 'FieldName' is currently replicated.
2.If I am unable to use EM to drop/add columns,
does this mean that my db is corrupt? How can I check if
my db is corrupt and which tools can I use?
3.The transaction log for a replicated db keeps
growing. The database is about 500 MB and the transaction
log is almost 4GB. I performed a complete backup and even
tried to shrink the log manually but the size did not
change.
I will appreciate any help I can get in resolving these
problems.
Thanks
Emma
is this column a pk, or part of a pk? are there and contraints on this
column?
It is unlikely your database is corrupt. Database base corrpuption errors
normally show up when you query a page telling you a page of the table or
index is inaccessible, your database is inaccessible. To check this run dbcc
checkdb
Regarding your ever expanding database, run dbcc open tran and see if there
are any old open transactions. If so figure out what they are doing and
evaluate killing them. The consider switching to the simple recovery model
and trying to shrink the database again several times. This will cause
locking so it is best to do this off hours. After you do this run a backup,
and then switch back to the full model.
"Emma" <eeemore@.hotmail.com> wrote in message
news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
> I have a couple of questions relating to a merge
> replication database.
> 1. I was unable to add a column to a table being
> replicated using EM, but succeeded using
> sp_repladdcolumn. When I tried to drop another column
> using sp_repldropcolumn, I get the following
> message: "ALTER TABLE DROP COLUMN failed
> because 'FieldName' is currently replicated.
> 2. If I am unable to use EM to drop/add columns,
> does this mean that my db is corrupt? How can I check if
> my db is corrupt and which tools can I use?
> 3. The transaction log for a replicated db keeps
> growing. The database is about 500 MB and the transaction
> log is almost 4GB. I performed a complete backup and even
> tried to shrink the log manually but the size did not
> change.
> I will appreciate any help I can get in resolving these
> problems.
> Thanks
> Emma
>
|||Hilary,
Thanks for your response. dbcc checkdb returned no error.
dbbc opentran returned the following and I don't know
what to do with it.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>
|||Hilary,
In response to your first question, the column that I
can't drop is not a pk or part of a pk and there are no
constraints. There is a relationship between this table
and another table on another column.
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>

Tuesday, March 27, 2012

Can't get MAX DISTINCT to work the way I want it to

Can't get current value of a field in table header

Cant get CAST function to work in ASP.NET

Sunday, March 25, 2012

Can't generate report when there are lot of data.

can't force index

Thursday, March 22, 2012

Can't find NOT FOR REPLICATION option

Can't find NOT FOR REPLICATION option

Can't find NOT FOR REPLICATION option

Cant figure out this query

cant figure out how to write query..

Can't figure out how to write query

Tuesday, March 20, 2012

cant enter data in field

Can't Edit Table

Can't Edit Table

Can't Drop table in Replication

Can't drop subscription

cant drop rowguid or msrepl_tran_version

Can't drop constraint

Monday, March 19, 2012

Can't drop column

connect from ASP app

Blog Archive

About Me