Attach database to SQL2005 from old MSDE database

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which the company says works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get an error:

Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

Or... is there a way to attach the databases to SQLExpress manually?

Help pls?The instance of SQL Express needs to be run as "Local system" which is not the default. Once this is changed, the SQL Express database attaches to the old MSDE SQL 2000 database just fine, and the program appears to run without problems.

Thanks to anyone looking at my post, and hopefully, this will help others.

David|||1> use master
2> go

** close the database johandb

1>exec sp_dboption N'johandb',N'autoclose',N'false'
2>go

1>checkpoint
2>go

** detach database johan
1> exec sp_detach_db johandb, true
2> go

** attach with files

1> exec sp_attach_db @.dbname = 'johandb',
2> @.filename1='d:\mssql7\data\johandb_data.mdf',
3> @.filename2='d:\mssql7\data\johandb_log.ldf'
4> go

Regards,
Johan|||Does anyone use Lytec and SOAPware together?

MD in Texassql

ASYNC_NETWORK_IO issue

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.

Thanks
-Ashis

Hi, Ashis,

Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.

Thanks,

Zhiqiang Feng

Assistance with a query

I have 2 tables POheader and POline. I am running the following SQL
statement but am getting 2 records where as I should only be getting one.
Could someone please assist.
s_commar@.hotmail.com
select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
POline.Ponbr and POline.ponbr
= 16916;
I should just be getting one record but I get 2 records. Could someone
advise as to what I am doing wrong.
Thanks
s_commar@.hotmail.comIts hard to comment without seeing DDL+Sample data. My best
guess is that your POline table has 2 matching rows. If you dont want to
reference any columns from the POnline table in the final output,
you can probably rewrite your query like
Select POheader.ponbr from POheader
WHERE EXISTS(
SELECT 1 FROM POline WHERE POheader.Ponbr = POline.Ponbr
AND POline.ponbr = 16916)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"s commar" <scommar@.verizon.net> wrote in message
news:36iJe.1262$D4.785@.trndny07...
>I have 2 tables POheader and POline. I am running the following SQL
>statement but am getting 2 records where as I should only be getting one.
>Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>|||"s commar" schrieb:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
select POheader.ponbr
from POheader
inner join POline
on POheader.Ponbr = POline.Ponbr
WHERE POline.ponbr = 16916|||Since u hv 2 matching rows in the POLine table against the row in the
POHeader table, u r getting 2 records. Try below query
select DISTINCT POheader.ponbr from POheader inner join POline on
POheader.Ponbr = POline.Ponbr and POline.ponbr= 16916;
Rakesh
"s commar" wrote:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>
>

assigning sequence numbers to records

hey all,
i have a table with about 1300 rows
the records are grouped by a type and i want to
assign each type a numbering sequence. what's the best way to do this?
For instance,
Rec1 Type1 Seq1
Rec2 Type1 Seq2
Rec3 Type2 Seq1
Rec4 Type2 Seq2
Rec5 Type2 Seq3
The seq columns is what i'm trying to incorporate.
thanks,
rodcharI assume from your post that the records are already sequenced globally...
Then you can do this for output only with a query
Select RecSeq, Type,
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq) TypeSeq
From TableName T
Or if you have another column in the table that you want to actually
populate with the value
Then
Update T Set TypeSeq =
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq)
From TableName T
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||We need to know if there is another column we can using to uniquely identity
a row in a group.
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||I don't think I'm doing something right here cause it's not working.
Please let me explain a different way to make sure:
Rec1 Type Seq#
-- -- --
1 A
3 B
2 A
4 A
5 B
After should look like the following:
Rec1 Type Seq#
-- -- --
1 A 1
2 A 2
4 A 3
3 B 1
5 B 2
The Seq# field is a new field that I need numbered sequentially by Types
So you're saying that the update statement in the above reply should do this
?
thanks,
rodchar
"CBretana" wrote:
> I assume from your post that the records are already sequenced globally...
> Then you can do this for output only with a query
> Select RecSeq, Type,
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq) TypeSeq
> From TableName T
> Or if you have another column in the table that you want to actually
> populate with the value
> Then
> Update T Set TypeSeq =
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq)
> From TableName T
>
> "rodchar" wrote:
>|||The "new" field that you want populated must already exist in the Table
first. Have you added it? If you have named it "SeqNo" then
Update T Set SeqNo=
(Select Count(*) From TableName
Where Type = T.Type
And Rec1 <= T.Rec1)
From TableName T
What error are you getting ?
"rodchar" wrote:
> I don't think I'm doing something right here cause it's not working.
> Please let me explain a different way to make sure:
> Rec1 Type Seq#
> -- -- --
> 1 A
> 3 B
> 2 A
> 4 A
> 5 B
> After should look like the following:
> Rec1 Type Seq#
> -- -- --
> 1 A 1
> 2 A 2
> 4 A 3
> 3 B 1
> 5 B 2
> The Seq# field is a new field that I need numbered sequentially by Types
> So you're saying that the update statement in the above reply should do th
is?
> thanks,
> rodchar
> "CBretana" wrote:
>|||I'm not getting an error message however the value for the new field contain
s
that maximum number (1372) for all the records:
Here's my actual statement:
UPDATE Records
SET SeqNo =
(SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department AND
RecID <= Records.RecID)
FROM Records
So, here we have
RecID (like Rec1 is just an autonumber field)
Department is the type
"CBretana" wrote:
> The "new" field that you want populated must already exist in the Table
> first. Have you added it? If you have named it "SeqNo" then
> Update T Set SeqNo=
> (Select Count(*) From TableName
> Where Type = T.Type
> And Rec1 <= T.Rec1)
> From TableName T
>
> What error are you getting ?
>
> "rodchar" wrote:
>|||On Tue, 5 Apr 2005 11:55:03 -0700, rodchar wrote:

>I'm not getting an error message however the value for the new field contai
ns
>that maximum number (1372) for all the records:
>Here's my actual statement:
>UPDATE Records
>SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
>RecID <= Records.RecID)
>FROM Records
(snip)
You omitted the table alias from CBretana's solution. Try this one
instead (not exactly the same as CBretana's suggestion - I changed it to
use ANSI-standard instead of proprietary Transact-SQL syntax):
UPDATE Records
SET SeqNo = (SELECT COUNT(*)
FROM Records AS R
WHERE R.Department = Records.Department
AND R.RecID <= Records.RecID)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Because you're not using an alias for the tablename in the outer query... Th
e
way you wrote it,
UPDATE Records SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department
AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
FROM Records
The token <Records> in the Last part of the subquery's where clause refers
to the Records table IN the subquery, so you're saying Where RecID in The
Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s to
ask
" Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That's
why you need to use Table ALias
UPDATE R SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = R.Department
AND RecID <= R.RecID)
FROM Records As R
"rodchar" wrote:
> I'm not getting an error message however the value for the new field conta
ins
> that maximum number (1372) for all the records:
> Here's my actual statement:
> UPDATE Records
> SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
> RecID <= Records.RecID)
> FROM Records
> So, here we have
> RecID (like Rec1 is just an autonumber field)
> Department is the type
>
> "CBretana" wrote:
>|||thank you all, I give a try and let you know.
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>|||That's awesome, hey any recommendations on a good book or how to get up to
speed in knowing how to write statements like these?
thanks so much for everyone's help here,
rodchar
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>

Assign Sequential Numbers

I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp) documentation.

-PatP|||Hey...

SQLTeam still down?

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO

INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...

SQLTeam still down? Nah, at least I can see it from here.

-PatP

Assign records to Analysts

Here is my issue. I have a list of merchants (generated daily) and I
need to assign them eually to a set of analysts. Both the merchant
list and analyst list can change daily. I want to assign each merchant
with an analyst daily and need help to write out a stored procedure
for this (do not want to use VB).
Here is relevant code for the tables:
CREATE TABLE MerchList (
MerchListID int IDENTITY (1, 1) NOT NULL ,
FileDate datetime NOT NULL ,
MerchID int NOT NULL ,
AnalystID int NOT NULL ,
)

CREATE TABLE tblAnalyst (
AnalystID int IDENTITY (1, 1) NOT NULL ,
AnalystName varchar(100) NOT NULL
)

there will be about 10000 records in table MerchList and around 25
records in table tblAnalyst.

This will be used to assign work to analysts on a daily basis.

Thanks for all your help!

Vishal"Vishal Sinha" <vsinha73@.yahoo.com> wrote in message
news:2f4d7b0a.0401121129.40ee0b82@.posting.google.c om...
> Here is my issue. I have a list of merchants (generated daily) and I
> need to assign them eually to a set of analysts. Both the merchant
> list and analyst list can change daily. I want to assign each merchant
> with an analyst daily and need help to write out a stored procedure
> for this (do not want to use VB).
> Here is relevant code for the tables:
> CREATE TABLE MerchList (
> MerchListID int IDENTITY (1, 1) NOT NULL ,
> FileDate datetime NOT NULL ,
> MerchID int NOT NULL ,
> AnalystID int NOT NULL ,
> )
> CREATE TABLE tblAnalyst (
> AnalystID int IDENTITY (1, 1) NOT NULL ,
> AnalystName varchar(100) NOT NULL
> )
> there will be about 10000 records in table MerchList and around 25
> records in table tblAnalyst.
> This will be used to assign work to analysts on a daily basis.
> Thanks for all your help!
> Vishal

Your requirements aren't entirely clear (at least to me), and the tables
don't seem to have enough information (keys and constraints) to give a good
answer. Is the MerchList table repopulated every day, or are today's
merchants identified by FileDate? Can the same MerchID appear multiple times
in one day (if that is what FileDate is for)? Is the tblAnalyst table
repopulated every day? If so, then how do you track AnalystIDs over time? If
it is not, then how do you mark analysts as sick or unavailable (there seems
to be no column for that)?

Your basic requirement seems to be assigning merchants to analysts, but it
isn't clear what you mean by "equally". What happens if some analysts can
process more merchants than others, due to greater experience, better
resources or whatever? In that case, you would want to weight the
assignment, so they get more merchants assigned to them. Or what happens if
the numbers simply don't divide into each other cleanly - who gets the
'extra' merchants?

And do you have to ensure that merchants are 'rotated' among analysts, or is
it acceptable for the same merchant to be assigned to the same analyst every
day indefinitely? If you need rotation, should it be random or periodic? Are
there rules about merchants which can never be assigned to certain
analysts - a blacklist or Chinese wall?

I don't want to sound unhelpful, but I suspect you may be oversimplifying
your requirements, and if you do need to consider some of the issues I've
suggested, then it's likely that you'll end up with something too detailed
to discuss in a newsgroup.

But if it is a simple case, then I think you'll get more help by providing
some sample data, preferably for at least a couple of different days. That
should help clarify what you expect to achieve.

Simon|||Thanks for the quick response,
Please see my replies below:

Re: Assign records to Analysts
From: Simon Hayes
Date Posted: 1/12/2004 1:43:00 PM

Your requirements aren't entirely clear (at least to me), and the tables
don't seem to have enough information (keys and constraints) to give a
good answer. Is the MerchList table repopulated every day, or are
today's merchants identified by FileDate? Can the same MerchID appear
multiple times in one day (if that is what FileDate is for)? Is the
tblAnalyst table repopulated every day? If so, then how do you track
AnalystIDs over time? If
it is not, then how do you mark analysts as sick or unavailable (there
seems to be no column for that)?

Vishal>> There is one record per mechant and the list is created on a
daily basis. so the table would have data for multiple dates where there
can be only one record for a merchant for any given date.

Your basic requirement seems to be assigning merchants to analysts, but
it isn't clear what you mean by "equally". What happens if some analysts
can process more merchants than others, due to greater experience,
better
resources or whatever? In that case, you would want to weight the
assignment, so they get more merchants assigned to them. Or what happens
if the numbers simply don't divide into each other cleanly - who gets
the
'extra' merchants?

Vishal>> I would need to assign equal number of merchants to analysts.
If the numbers do not divide equally then the first few get the extra
(can be order by ID - does not matter)

And do you have to ensure that merchants are 'rotated' among analysts,
or is it acceptable for the same merchant to be assigned to the same
analyst every day indefinitely? If you need rotation, should it be
random or periodic? Are
there rules about merchants which can never be assigned to certain
analysts - a blacklist or Chinese wall?

Vishal>> No rotation required

I don't want to sound unhelpful, but I suspect you may be
oversimplifying
your requirements, and if you do need to consider some of the issues
I've
suggested, then it's likely that you'll end up with something too
detailed
to discuss in a newsgroup.

But if it is a simple case, then I think you'll get more help by
providing
some sample data, preferably for at least a couple of different days.
That
should help clarify what you expect to achieve.

Simon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Vishal Sinha (vsinha73@.yahoo.com) writes:
> Here is my issue. I have a list of merchants (generated daily) and I
> need to assign them eually to a set of analysts. Both the merchant
> list and analyst list can change daily. I want to assign each merchant
> with an analyst daily and need help to write out a stored procedure
> for this (do not want to use VB).
> Here is relevant code for the tables:
> CREATE TABLE MerchList (
> MerchListID int IDENTITY (1, 1) NOT NULL ,
> FileDate datetime NOT NULL ,
> MerchID int NOT NULL ,
> AnalystID int NOT NULL ,
> )
> CREATE TABLE tblAnalyst (
> AnalystID int IDENTITY (1, 1) NOT NULL ,
> AnalystName varchar(100) NOT NULL
> )
> there will be about 10000 records in table MerchList and around 25
> records in table tblAnalyst.

If I understood the requirements from your supplemental posting
correctly, this could be a solution:

UPDATE MerchList
SET AnalystID = MerchListID %
(SELECT COUNT(*) FROM tblAnalyst) + 1

This presumes that the AnalystIDs goes from 1 to 25 (or whatever)
without gaps. If there are gaps, or the id:s do not start at 1,
you could do:

CREATE TABLE #analystmap (id int IDENTITY(0, 1),
analystid int NOT NULL

INSERT #analystmap (analystid)
SELECT AnalystID FROM tblAnalyst ORDER BY newid()

UPDATE MerchList
SET AnalystID = a.analystid
FROM MerchList m
JOIN #analystmap a ON
m.MerchList % (SELECT COUNT(*) FROM #analystmap) = a.id

The ORDER BY newid() adds a certain randomness into the process, but
still merchants that are 25 ids apart will get the same analyst.

Disclaimer: as there was not test data included, I have not tested this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Your schema has no keys, no DRI and no constraints. You will also
have no data integrity. Let's fix it up:

CREATE TABLE MerchAnalysts
(file_date DATETIME NOT NULL,
REFERENCES Analysts (analyst_id),
merch_id INTEGER NOT NULL
REFERENCES Merchants (merch_id),
analyst_id INTEGER NOT NULL,
REFERENCES Analysts (analyst_id),
..
PRIMARY KEY (file_date, analyst_id, merch_id));

Since I do not know what the **real keys** for merchants and analysts
would be (SSN? Tax number, DUN number?), I have left them as
INTEGERs; fix this omission immediately! IDENTITY is never a key. I
also shortened the names to a length that will not collect garbage
strings or be so long they cannot display easily, etc.

CREATE TABLE Analysts
(analyst_id INTEGER NOT NULL PRIMARY KEY,
analyst_name VARCHAR(35) NOT NULL,
..);

CREATE TABLE Merchants
(merch_id INTEGER NOT NULL PRIMARY KEY,
analyst_name VARCHAR(35) NOT NULL,
..);

>> I want to assign each merchant with an analyst daily and need help
to write out a stored procedure for this <<

What is the rule for the assignments? To write a procedure, we need
to have a spec. Do you want to divide them up evenly each day? Are
certain analysts assigned to certain merchants, but have an
alternative analyst if the primary one is not at work? Are the rules
more complex than that either of those possibilities?|||Thanks for the Suggestions - it is EXACTLY what I needed. I did post a
rather simplified version of table design for illustration purposes.

Thanks everyone!

Vishal

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946EF0EF2F3F4Yazorman@.127.0.0.1>...
> Vishal Sinha (vsinha73@.yahoo.com) writes:
> > Here is my issue. I have a list of merchants (generated daily) and I
> > need to assign them eually to a set of analysts. Both the merchant
> > list and analyst list can change daily. I want to assign each merchant
> > with an analyst daily and need help to write out a stored procedure
> > for this (do not want to use VB).
> > Here is relevant code for the tables:
> > CREATE TABLE MerchList (
> > MerchListID int IDENTITY (1, 1) NOT NULL ,
> > FileDate datetime NOT NULL ,
> > MerchID int NOT NULL ,
> > AnalystID int NOT NULL ,
> > )
> > CREATE TABLE tblAnalyst (
> > AnalystID int IDENTITY (1, 1) NOT NULL ,
> > AnalystName varchar(100) NOT NULL
> > )
> > there will be about 10000 records in table MerchList and around 25
> > records in table tblAnalyst.
> If I understood the requirements from your supplemental posting
> correctly, this could be a solution:
> UPDATE MerchList
> SET AnalystID = MerchListID %
> (SELECT COUNT(*) FROM tblAnalyst) + 1
> This presumes that the AnalystIDs goes from 1 to 25 (or whatever)
> without gaps. If there are gaps, or the id:s do not start at 1,
> you could do:
> CREATE TABLE #analystmap (id int IDENTITY(0, 1),
> analystid int NOT NULL
> INSERT #analystmap (analystid)
> SELECT AnalystID FROM tblAnalyst ORDER BY newid()
> UPDATE MerchList
> SET AnalystID = a.analystid
> FROM MerchList m
> JOIN #analystmap a ON
> m.MerchList % (SELECT COUNT(*) FROM #analystmap) = a.id
> The ORDER BY newid() adds a certain randomness into the process, but
> still merchants that are 25 ids apart will get the same analyst.
> Disclaimer: as there was not test data included, I have not tested this.

asp.net datareader+sql server+null fields=error

Following problem:
I try to select records where a field is null.
i.e.:
MyCommandGeneric.CommandText = "select A, b from object where C is null";
MyDataReader = MyCommandGeneric.ExecuteReader();
If I try now to access the values (string bla = MyDataReader["A"]), I am
getting the error, that datareader can't read, when there is no data.
But there is data!
I tried to query the sql server directly with the above query, and it gives
a record.I think you have to execute read method before accessing the data.
SqlDataReader Class
http://msdn.microsoft.com/library/d...opi
c.asp
AMB
"the friendly display name" wrote:

> Following problem:
> I try to select records where a field is null.
> i.e.:
> MyCommandGeneric.CommandText = "select A, b from object where C is null";
> MyDataReader = MyCommandGeneric.ExecuteReader();
> If I try now to access the values (string bla = MyDataReader["A"]), I am
> getting the error, that datareader can't read, when there is no data.
> But there is data!
> I tried to query the sql server directly with the above query, and it give
s
> a record.|||Oh, good god.
Of course! Now it works.
"Alejandro Mesa" wrote:
> I think you have to execute read method before accessing the data.
> SqlDataReader Class
> http://msdn.microsoft.com/library/d...o
pic.asp
>
> AMB
> "the friendly display name" wrote:
>