Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NET
web application. The web appliation uses SQL Authentication to connect to the
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?
Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NET
web application. The web appliation uses SQL Authentication to connect to the
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NE
T
web application. The web appliation uses SQL Authentication to connect to th
e
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mining Model?

Hi,

I'm working on a project to create a mining model.I have one "flattened" table with a productID (key) and related attributes. Example columns are weight, color, price, units sold last year, product category, product rating (top seller, etc) and similar columns, about 40 in total. There are about 15,000 products and same number of rows in the table.

The objective is to pass a ProductID (that also exists in the table) and get back the top N products that most resemble the source product based on all the attributes.

Any advice on which models I should test and how to set up the models would be much appreciated. Also if there is a similar example/sample out there, please let me know. I downloaded the MovieClick sample, but that doesn't work in my case as I only have the equivalent of the Customers table.

Thanks in Advance.

This is actually a K-Nearest Neighbor problem, for which we don't ship an implementation for SQL Server Analysis Services (although someone may have written a plug in). I could imagine that you could use the fuzzy matching transform in Integration Services to do this, however. To do so you would just use your product table as the reference table, send in the fields of the product of interest, then sort the results by the match confidence.

HTH

-Jamie

|||

Thanks Jamie, I'll try fuzzy matching in SSIS. I'm also thinking of writing my own code to calculate based on KNN in TSQL. I'm not sure if the performance will be acceptable though especially as I need to pass in up to 5 products, evaluate based on their combined attributes and get a resultset back with top N matches.

Is there a third party software out there that can do this?

|||When using the fuzzy components in SSIS, do be aware that they are string matching components, evaluating the edit distance between two string. So, for example, "911" will be pretty much as close a match to "999" as "199" - so for any columns where you need to evaluate similarity of values you may need to use other components such as the Derived Column to perform the calculation.|||

Hi Donald,

Can you explain what you mean by Derived column. How would I make it work to calculate distances for numeric columns/attributes.

Thanks.

|||I don't know of a 3rd party KNN, but you would likely get better performance implementing your own algorithm using our plug-in interfaces in C#.|||

Jamie/Donald

Thanks for your responses. My C# is not that strong. For now I'll write something in SQL and if I can create a model that works, I'll convert that into a plug-in.

This is the plan. Normalize all attributes using mean and std dev. Apply the nearest neighbour algorithm using Euclidean distance. Also experiment with arbitrary weights for attributes or maybe use correlation to filter out some of the weaker attributes, not sure how that will work though at this point.

Any suggestions are welcome. Also, Is there a sample plug-in in C# out there I can use as template. Is there any way I can mold this table to use Association Rules or any other built-in algorithm.

Regards,

Asim.

|||

There are tutorials here for writing managed plug-in algorithms: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

You may need to register (free!) to get access to the links.

Minimizing Locking Question

Hello all,
I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue.
Any ideas?
ThanksWhat else do you need other than NOLOCK with linked tables?

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.

Min not working

Hi all:
Because me not this functioning the following one query.

simply me not the function respects min, because?, that I am doing badly?

Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime

Thanks.:confused:the problem is because you have c.Link4 in the GROUP BY but not in the SELECT

also, please note: DISTINCT is not a function

try this --select Min(c.gentime) as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
group
by a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
order
by a.CardNumber
, c.recvtime|||thanks by answering, the problem is, that the inner join does not respect me the rank dated, I need a value by each employee, by each I gave of the rank.

Thanks.:confused:|||okay, try this --select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
and c.gentime =
( select min(c.gentime)
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10 )
order
by a.CardNumber
, c.recvtime|||thanks but the following error marks me:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

:eek:|||i'm going to move this query to the SQL Server forum

i don't understand the error message, maybe someone else does|||Not completely sure what is going on here, but trying moving the subquery to the JOIN clause:select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
inner
join --subquery
(select link3,
min(c.gentime) as gentime
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10
group by link3) subquery
on subquery.link3 = b.recordid
and subquery.gentime = c.gentime
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
order
by a.CardNumber
, c.recvtime

Migration to SQL Server 2k5

Hi all,

I have script written in SQL Server 2000.

When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".

But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.

1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'

"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"

2.Moreover none of the tables had been created in "Database->Tables->SystemTable".

In both XP and 2003 Server, While installing, i have choosen same options.

Please anybody could help me to resolve this issue.

Thanks and Regards,

Nilavu

You should try a different forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

-Jamie

Migration to SQL Server 2k5

Hi all,

I have script written in SQL Server 2000.

When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".

But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.

1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'

"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"

2.Moreover none of the tables had been created in "Database->Tables->SystemTable".

In both XP and 2003 Server, While installing, i have choosen same options.

Please anybody could help me to resolve this issue.

Thanks and Regards,

Nilavu

Can you give detailed steps on what exactly you're doing, it's unclear to me what you mean by "migrate". Are you running setup/upgrade, which is failing?|||

Hi,

First i have to thank you for the valuable efforts.

Problem is not with upgrade/Setup of SQL Server 2005, With scripts. Whether the scripts written in SQL Server 2000 will run in SQL Server 2005 without doing any modification .

Here is the scenario.

Actually i have scripts that are written for SQL Server 2000.

Now my clients are using SQL Server 2005.

So i have to test whether my scripts could run in SQL Server 2005 without any modification.

When i tried to run the scripts in SQL Server 2005 installed in Windows XP OS, it is giving error.

At the same time there are no tables created in "Database->Tables->SystemTable".

But Same script is executed properly in SQL Server 2005 installed in Windows 2003 Server.

In this case tables like "sysobjects" etc are created in "Database->Tables->SystemTable".

Still you are not able to understand, Please send me mail.

Please suggest me whether i have to do any changes in script level or in settings of SQL Server 2005.

Moreover Being installed SQL Server 2005, Why tables like "Sysobjects" have been created in 2003 Server OS while not in XP.

Kindly clarify my doubts.

Thanks and Regards,

Nilavu

|||I don't think this has anything to do with sql server on a given OS. If you look at the error message closely, it says the alter table drop column failed because a check constraint exists. You have to drop the check constraint first before you can drop the column.|||

Hi,

Yes Greg, you are right, i'm getting the same error message.

But the problem is, the same scripts works fine in ,

1.Windows XP, SQL Server 2000

2.Windows 2003 Server, SQL Server 2005

But i'm getting error in Windows XP, SQL Server 2005.

Thats what i could not find the solution.

I would like to mention one more different behaviour in SQL Server 2005,

In Windows 2003 Server, SQL Server 2005, "Tables->SystemTables" system tables like "sysobjects" are created.

But in Windows XP, SQL Server 2005, There are no such tables in "Tables->SystemTables".

Could you explain me why it is behaving in two different ways?

Thanks and Regards,

Nilavu.

|||

Look at the error message closely, it says a check constraint exists. You cannot drop a column if a check constraint exists on that column. Please check to see if this check constraint exists in both databases, as well as whether your script is explicitly dropping this constraint. If your script is explicitly dropping this constriant, maybe the script is failing beforehand. If your script is not dropping this constraint, then your databases are not exactly in sync, which is why it is failing on one db and not the other.

|||

Hi,

Thanks Greg.

Before dropping the column, code which is trying to find check constraint fails.

The "info" column in the "sysobjects" table contains only zero values for all objects in case of SQL Server 2005.

But in case of SQL Server 2000, there are certain values have entered.

Could you tell me, Where are the "info" column's values are mapped? Which table and column i have to refer instead of "sysobjects->info".

Here is my code,

IF ((SELECT COUNT(sysobjectsconstraint.id) FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem') = 1)

BEGIN

SET @.sqlcommand = (

SELECT 'ALTER TABLE ' +

QUOTENAME(USER_NAME(sysobjectstable.uid)) + '.' +

QUOTENAME(sysobjectstable.name) +

' DROP CONSTRAINT ' +

QUOTENAME(sysobjectsconstraint.name)

FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem'

)

The "If" condition fails in case of SQLServer 2005, whereas in case of SQLServer 2000, i'm getting value of 1.

Thanks and Regards,

Nilavu.

|||Moving to TSQL forum, someone more familiar with metadata and system tables can offer a better answer.|||The issue is that you are using columns reserved for internal use in your query. So any assumptions made as to what values it can contain will be wrong in SQL Server 2005. It might even break in SQL Server 2000 with a future service pack for example. You can determine CHECK constraints on a table by using INFORMATION_SCHEMA.CHECK_CONSTRAINTS view along with INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view. Or just the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view with OBJECTPROPERTYEX metadata function to filter check constraints. This will work the same in SQL Server 2000/2005. Or you could use sp_helpconstraint system SP for example. It is best to use the documented objects so that your code works consistently and you don't rely on undocumented interfaces that can be modified anytime by us.|||

Hi,

Thank you for your timely help Umachandar.

Its working fine now.

Regards,

Nilavu.

Migration from MS Access 2003

Hello,

I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.

Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.

Can someone please assist me with this?

Thank you.

Here you go:

http://support.microsoft.com/default.aspx/kb/237980

Buck Woody

Migration from MS Access 2003

Hello,

I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.

Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.

Can someone please assist me with this?

Thank you.

Here you go:

http://support.microsoft.com/default.aspx/kb/237980

Buck Woody

migrating with from access with VB scripts

The company i am working for has been using an Access database for purchasing and inventory. this has become to small/dangerous due to the number of records and frequent 'database needs repair' errors. I am trying to migrate the database to a sql server w/ access front end and am runing into problems with most of the VB proceedures that are built into the database. the most common at the moment is a 'Write Conflict' error saying someone else changed the record i am editing. i know this can't be the case becasue i am working on this in an isolated environment. I have traced it to a VB script that changes a field from 'Entering' to 'Pending' (text field) on the click of the 'new record' button. any ideas?

Access 2000/2003, SQL server 2000If you have more than one channel open to the database server (a second "session", basically) then that may well appear to be "another user."|||doesn't seem to be, in my tracing, the issue seems to be that VB doesn't know that I'm the person makeing the changes. It looks like the VB script starts when the form opens, checks the contents of the record to be worked on and when the button is clicked, finds the contents have changed.|||Please post the code involved.

Migrating to Sql25k

We’ve got hundreds of branch which are working with MSDE. As we are moving sql25k, what the ideal version would be adviceable to install there? Sql Express?

How to handle this? I have a fuss on my head because of there are many vesions available.

Thanks in advance for any comment or further information,

Yes, SQLExpress is the succesor to MSDE however "Express" is a much better core database engine as it support more hardware, less restrictions (including the fabled 5 connection limit of MSDE) etc.

Here is a link that lists out the differences between versions:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

And as far as the actual upgrade process you could perform either direct "inplace" upgrades or using named instances perform a slower migration. Either way backup everything!

Derek

|||Thank you|||

enric, could you please mark an answer. And your welcome.

thanks,

Derek

Migrating to SQL Server 2000 Standard to 2005 Express

I am working for a small company. We have an in-house web application that
was purely used by internal employees. At the time, it was cheaper to get a
per user license. Now we have application that is open to the public and so
we need to change the licensing mode to per processor mode. Right now, we
have a tight budget, so we are thinking about getting SQL Server 2005
Express. If performance is an issue, we may upgrade to other version later.
Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 2000
standard to 2005 express upgrade is not supported. Does that mean that if
I get a copy of backup of 2000 standard database and restore it to 2005
express, it will not work? If the upgrade works, will I able to use Upgrade
Advisor?
ThanksAFAIK, you *should* be able to restore your database to SQL Express.
I'd recommend doing a test migration onto a development server or
workstation and trying it out. You're going to want to migrate, instead
of upgrading, though...
JJ wrote:
> I am working for a small company. We have an in-house web application tha
t
> was purely used by internal employees. At the time, it was cheaper to get
a
> per user license. Now we have application that is open to the public and
so
> we need to change the licensing mode to per processor mode. Right now, we
> have a tight budget, so we are thinking about getting SQL Server 2005
> Express. If performance is an issue, we may upgrade to other version late
r.
> Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 200
0
> standard to 2005 express upgrade is not supported. Does that mean that i
f
> I get a copy of backup of 2000 standard database and restore it to 2005
> express, it will not work? If the upgrade works, will I able to use Upgra
de
> Advisor?
> Thanks
>
-Dave Markle
http://www.markleconsulting.com/blog|||The only thing I can think of that would prevent this is a database bigger
than 4GB. There might also be some performance differences because Express
won't use as many system resources as standard.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:OX3yXomSHHA.4744@.TK2MSFTNGP02.phx.gbl...
> AFAIK, you *should* be able to restore your database to SQL Express. I'd
> recommend doing a test migration onto a development server or workstation
> and trying it out. You're going to want to migrate, instead of upgrading,
> though...
> JJ wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Migrating to SQL Server 2000 Standard to 2005 Express

I am working for a small company. We have an in-house web application that
was purely used by internal employees. At the time, it was cheaper to get a
per user license. Now we have application that is open to the public and so
we need to change the licensing mode to per processor mode. Right now, we
have a tight budget, so we are thinking about getting SQL Server 2005
Express. If performance is an issue, we may upgrade to other version later.
Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 2000
standard to 2005 express upgrade is not supported. Does that mean that if
I get a copy of backup of 2000 standard database and restore it to 2005
express, it will not work? If the upgrade works, will I able to use Upgrade
Advisor?
Thanks
AFAIK, you *should* be able to restore your database to SQL Express.
I'd recommend doing a test migration onto a development server or
workstation and trying it out. You're going to want to migrate, instead
of upgrading, though...
JJ wrote:
> I am working for a small company. We have an in-house web application that
> was purely used by internal employees. At the time, it was cheaper to get a
> per user license. Now we have application that is open to the public and so
> we need to change the licensing mode to per processor mode. Right now, we
> have a tight budget, so we are thinking about getting SQL Server 2005
> Express. If performance is an issue, we may upgrade to other version later.
> Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 2000
> standard to 2005 express upgrade is not supported. Does that mean that if
> I get a copy of backup of 2000 standard database and restore it to 2005
> express, it will not work? If the upgrade works, will I able to use Upgrade
> Advisor?
> Thanks
>
-Dave Markle
http://www.markleconsulting.com/blog
|||The only thing I can think of that would prevent this is a database bigger
than 4GB. There might also be some performance differences because Express
won't use as many system resources as standard.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:OX3yXomSHHA.4744@.TK2MSFTNGP02.phx.gbl...
> AFAIK, you *should* be able to restore your database to SQL Express. I'd
> recommend doing a test migration onto a development server or workstation
> and trying it out. You're going to want to migrate, instead of upgrading,
> though...
> JJ wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Migrating to SQL Server 2000 Standard to 2005 Express

I am working for a small company. We have an in-house web application that
was purely used by internal employees. At the time, it was cheaper to get a
per user license. Now we have application that is open to the public and so
we need to change the licensing mode to per processor mode. Right now, we
have a tight budget, so we are thinking about getting SQL Server 2005
Express. If performance is an issue, we may upgrade to other version later.
Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 2000
standard to 2005 express upgrade is not supported. Does that mean that if
I get a copy of backup of 2000 standard database and restore it to 2005
express, it will not work? If the upgrade works, will I able to use Upgrade
Advisor?
ThanksAFAIK, you *should* be able to restore your database to SQL Express.
I'd recommend doing a test migration onto a development server or
workstation and trying it out. You're going to want to migrate, instead
of upgrading, though...
JJ wrote:
> I am working for a small company. We have an in-house web application that
> was purely used by internal employees. At the time, it was cheaper to get a
> per user license. Now we have application that is open to the public and so
> we need to change the licensing mode to per processor mode. Right now, we
> have a tight budget, so we are thinking about getting SQL Server 2005
> Express. If performance is an issue, we may upgrade to other version later.
> Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx, 2000
> standard to 2005 express upgrade is not supported. Does that mean that if
> I get a copy of backup of 2000 standard database and restore it to 2005
> express, it will not work? If the upgrade works, will I able to use Upgrade
> Advisor?
> Thanks
>
-Dave Markle
http://www.markleconsulting.com/blog|||The only thing I can think of that would prevent this is a database bigger
than 4GB. There might also be some performance differences because Express
won't use as many system resources as standard.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:OX3yXomSHHA.4744@.TK2MSFTNGP02.phx.gbl...
> AFAIK, you *should* be able to restore your database to SQL Express. I'd
> recommend doing a test migration onto a development server or workstation
> and trying it out. You're going to want to migrate, instead of upgrading,
> though...
> JJ wrote:
>> I am working for a small company. We have an in-house web application
>> that was purely used by internal employees. At the time, it was cheaper
>> to get a per user license. Now we have application that is open to the
>> public and so we need to change the licensing mode to per processor mode.
>> Right now, we have a tight budget, so we are thinking about getting SQL
>> Server 2005 Express. If performance is an issue, we may upgrade to other
>> version later.
>> Looking at the http://msdn2.microsoft.com/en-us/library/ms143393.aspx,
>> 2000 standard to 2005 express upgrade is not supported. Does that mean
>> that if I get a copy of backup of 2000 standard database and restore it
>> to 2005 express, it will not work? If the upgrade works, will I able to
>> use Upgrade Advisor?
>> Thanks
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog