Problems inserting records into non dbo schema

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?This turned out to be a conflict between using Nvarchar(max) and Nvarchar(255). Even though the data would fit ssis didnt seem to like it.|||

Of course not. they're two different data types!

If I understand correctly varchar(max) is not the same as an infinitely long "normal" varchar. Or it helps to not think about it that way anyway.

I'm sure you'd get a better answer on the T-SQL forum

-Jamie

sql

Problems inserting into table

I am trying to write an insert statement. I insert into 1 table test with th
e
statements below and what I want is I want to use this table to insert value
s
in
table test1 but I want be able to insert everything from the keyword product
so the new table description is always beginning from Product......
Since the length of month varies before the keyword I cannot use a static
substring or trim functions.
My insert will be something like this in test1.
id desc
-- --
1 Product dispatch to website...(individual number)
2 Product dispatch to website...(individual number)
Can anyone help me .
create table test(
Sno Int,
description varchar(2000),
cyats datetime default getdate() )
create table test1(
id int,
description varchar(2000),
cyats datetime default getdate())
insert into test(sno,description)
values(1,'Integrated Release August 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is :...')
insert into test(sno,description)
values(1,'Integrated Release December 2004. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')
insert into test(sno,description)
values(1,'Integrated Release June 2005. Product despatched to website.
Website address for tracking consignment is http://www.track.com,your number
is ...')First suggestion clean this data before getting into SQL Server using
whatever tool you have (like DTS or whatever.) as it will be far more
natural. However, a possible way is to reformat as a select in whatever
code you are using to build this data:
SELECT 1,substring('Integrated Release August 2005. Product despatched to
website.
Website address for tracking consignment is http://www.track.com,your
number
is :...', charindex('product', 'Integrated Release August 2005. Product
despatched to website.
Website address for tracking consignment is http://www.track.com,your
number
is :...'), 4000)
You could change the string to a variable, but I wouldn't unless you are
hand typing this stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8B17F575-0318-4317-A318-2E7F543D1A31@.microsoft.com...
>I am trying to write an insert statement. I insert into 1 table test with
>the
> statements below and what I want is I want to use this table to insert
> values
> in
> table test1 but I want be able to insert everything from the keyword
> product
> so the new table description is always beginning from Product......
> Since the length of month varies before the keyword I cannot use a static
> substring or trim functions.
> My insert will be something like this in test1.
> id desc
> -- --
> 1 Product dispatch to website...(individual number)
> 2 Product dispatch to website...(individual number)
> Can anyone help me .
> create table test(
> Sno Int,
> description varchar(2000),
> cyats datetime default getdate() )
> create table test1(
> id int,
> description varchar(2000),
> cyats datetime default getdate())
> insert into test(sno,description)
> values(1,'Integrated Release August 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is :...')
> insert into test(sno,description)
> values(1,'Integrated Release December 2004. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')
> insert into test(sno,description)
> values(1,'Integrated Release June 2005. Product despatched to website.
> Website address for tracking consignment is http://www.track.com,your
> number
> is ...')

Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNameSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so. Still takes a long time. I then notice what is happening. The SQL is taking the SOUNDEX of the LastName of the first record, apply it to ALL the records, then taking the SOUNDEX of the LastName of the second records, then updating it to ALL the records, etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
>
How did you notice this? This is really strange, I've never heard of
something like this.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!

quote:

---

> It takes forever. I start tweeking the SQL to commit every 500 records or

---

so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.

quote:

---

>

---

How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems in selecting record.

I have a table A which has the following structure:
Column Name(Type)
-- --
ID(int)
Repair Code(varchar)
Damage Code(varchar)
Location Code (varchar)
I also have a the master code table
Column Name(Type)
-- --
Code Type (int)
Code Name (varchar)
Code Description ( varchar)
and have a stored procedure called GetCodeDescription which input the Code
Type and Code Name , display the Code Description found in the code master
table.
I want to select the record from TabelA by inputting a ID , then select the
record which have the follwong structure
Column Name(Type)
--
ID (int)
Repair Code (varchar)
Repair Code Description (varchar)
Damage Code (varchar)
Damage Code Description (varchar)
Location Code (varchar)
Location Code Description (varchar)
How can I do it by using the GetCodeDescription stored procedure?
Thnak you very much !Trying to use the stored procedure is only going to make things much,
much more complicated than necessary. How about just doing a query?
SELECT A.ID,
A.RepairCode, B.COdeDescription as RepairDescription,
A.DamagerCode, C.COdeDescription as DamageDescription,
A.LocationCode, D.COdeDescription as LocationDescription
FROM TableA as A
JOIN MasterCodes as B
ON A.RepairCode = B.CodeName
AND B.CodeType = 1
JOIN MasterCodes as C
ON A.DamageCode = C.CodeName
AND C.CodeType = 2
JOIN MasterCodes as D
ON A.LocationrCode = D.CodeName
AND D.CodeType = 3
Roy
On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
<BallBall@.discussions.microsoft.com> wrote:

>I have a table A which has the following structure:
>Column Name(Type)
>-- --
>ID(int)
>Repair Code(varchar)
>Damage Code(varchar)
>Location Code (varchar)
>I also have a the master code table
>Column Name(Type)
>-- --
>Code Type (int)
>Code Name (varchar)
>Code Description ( varchar)
>and have a stored procedure called GetCodeDescription which input the Code
>Type and Code Name , display the Code Description found in the code maste
r
>table.
>I want to select the record from TabelA by inputting a ID , then select the
>record which have the follwong structure
>Column Name(Type)
>--
>ID (int)
>Repair Code (varchar)
>Repair Code Description (varchar)
>Damage Code (varchar)
>Damage Code Description (varchar)
>Location Code (varchar)
>Location Code Description (varchar)
>How can I do it by using the GetCodeDescription stored procedure?
>Thnak you very much !
>|||Thank for the answer , but because my code table container about 12000
records, if i join many times , i think the performance will be affected
"Roy Harvey" wrote:

> Trying to use the stored procedure is only going to make things much,
> much more complicated than necessary. How about just doing a query?
> SELECT A.ID,
> A.RepairCode, B.COdeDescription as RepairDescription,
> A.DamagerCode, C.COdeDescription as DamageDescription,
> A.LocationCode, D.COdeDescription as LocationDescription
> FROM TableA as A
> JOIN MasterCodes as B
> ON A.RepairCode = B.CodeName
> AND B.CodeType = 1
> JOIN MasterCodes as C
> ON A.DamageCode = C.CodeName
> AND C.CodeType = 2
> JOIN MasterCodes as D
> ON A.LocationrCode = D.CodeName
> AND D.CodeType = 3
> Roy
>
> On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
> <BallBall@.discussions.microsoft.com> wrote:
>
>|||Try the join first. You will probably find the performance is just fine,
and simpler to manage.
"BallBall" <BallBall@.discussions.microsoft.com> wrote in message
news:36B2D0B0-6201-42BE-933B-079D4FCE44DE@.microsoft.com...
> Thank for the answer , but because my code table container about 12000
> records, if i join many times , i think the performance will be affected
> "Roy Harvey" wrote:
>
Code
master
the|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative and pseudo-code are useless.
That would be a horrible design error! Go back to the foundations; a
table is made up of one and only one kind of entity. There is no such
thing as "master code table" in a valid schema.
Next, a data element can be a type or a code but not a "type_code";
read ISO-11179. Next you confuse rows with records and columns with
fields and do not bother to give us table names.
Finally, how do you get enough codes into a VARCHAR(1) column? In a
good design, codes are fixed length and have a validation rule. There
is no such thing as a magical universal "id" in RDBMS; you are not
actually using IDENTITY in an RDBMS, are you'
I have to make a wild guess about the key
CREATE TABLE RepairRequests
(repair_nbr INTEGER NOT NULL,
repair_code INTEGER NOT NULL
REFERENCES Repairs (repair_code)
ON UPDATE CASCADE,
PRIMARY KEY (repair_nbr, repair_code),
damage_code INTEGER NOT NULL
REFERENCES Damages(damage_code)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations (location_code)
ON UPDATE CASCADE);
CREATE TABLE Repairs
(repair_code INTEGER NOT NULL PRIMARY KEY,
repair_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Damages
(damage_code INTEGER NOT NULL PRIMARY KEY,
damage_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Locations
(location_code INTEGER NOT NULL PRIMARY KEY,
location_description VARCHAR(20) NOT NULL,
.) ;
I have no idea; where is the code for this stored procedure?
I see that you also do not understand what a repeated group is and how
to program in a tiered architecture. This should be done as a simple
query. But assuming that you really do hate RDBMS, how do you handle
a repair request with more or less than two damages on it?|||>Thank for the answer , but because my code table container about 12000
>records, if i join many times , i think the performance will be affected
If performance is bad the table is not indexed correctly. Also it is
not necessary to guess what performance will be, all you have to do is
run the queries in Query Analyzer to see what it is. Easy, quick, and
you can learn a lot.
Twelve thousand rows is really all that large, by the way. With
proper indexing performance should be fine with ten or a hundred times
as many rows.
Roy

Problems importing data into sql server

I have a table for authors (for our bookstore) that has several fields
(firstname, lastname, etc.) and an author_id field (set as identity)

I'm trying to import a spreadsheet into this table, but keep getting
error messages that say I can't import data into the author_id field
(the identityf field).

Can someone suggest what I can do to overcome this?

Thanks,

Bill1) Import the data into a new flat table (ie: without indexes).
2) Run a report on the key field (identityf) -- there must be no duplicates.

select indentityf, count(*)
from newtable
group by identityf
having count(*)>1

should yield no results.

3) Correct the spreadsheet.
4) reimport the spreadsheet.

"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
> Thanks,
> Bill|||"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
<snip
Use the DTS. On the screen that allows you to select the tables, click the
Transform button. On this screem, you'll see 'Allow Identity Insert'.

BV
www.iheartmypond.comsql

Problems importing data from an ODBC source

I'm having an odd issue importing data from an ODBC source using a DataReader Source.

There's data in the source table, and if I use a non-SSIS tool to query the source, all the data is visible. However, if I use the SSIS DataReader source, It returns no rows, and throws no error.

Any ideas for figuring this one out? This is affecting about 10% of the tables from this data source, and it's our ERP application, so there's limits on what I can do to the source schemas.

Thanks!

What types of colums are contained in these tables?

Problems formatting my table...

I have a table that looks like this...

City State Server Type
Chicago IL Svr1 Data
Chicago IL Svr2 Data
Chicago IL Svr3 Backup
Chicago IL Svr4 Backup
Atlanta GA Svr1 Data
Atlanta GA Svr2 Data

I already have a function to convert the server rows into a comma delimited string...
ALTER function dbo.fnGetServers (@.City varchar(25), @.State varchar(25), @.Type varchar(25), @.Tree varchar(25))
returns varchar(1000)
as
begin
declare @.NewSvrCol varchar(1000)
select @.NewSvrCol = ''
select @.NewSvrCol = @.NewSvrCol + Server + ', ' from serverops.dbo.v_userviews where city = @.City and State = @.State and Type = @.Type and Tree = @.Tree
select @.NewSvrCol = left(@.NewSvrCol, len(@.NewSvrCol)-1)
return(@.NewSvrCol)
end

Any suggestions on how to display the table in the following format?

City State DataSvrs BackupSvrs
Chicago IL Svr1,Svr2 Svr3,Svr4
Atlanta GA Svr1,Svr2 NULLread the article titled Cross-Tab Reports in Books online and the read about CASE statements.|||read the article titled Cross-Tab Reports in Books online and the read about CASE statements.

Thanks a lot, I've made some progress but am still having a slight problem...
I am using this query:

ALTER VIEW v_USAtlasAuthServers
as
select distinct u.City, u.State,
CASE u.Type WHEN 'Data' then dbo.fnGetServers(City, State, Type) ELSE null END as Data_Servers,
CASE u.Type WHEN 'Backup' then dbo.fnGetServers(City, State, Type) ELSE null END as Backup_Servers
from serverops.dbo.v_userviews u

where u.tree='tree1' and u.country='united states' and u.type in ('Data Server','Backup Server')
group by u.city, u.state, u.type

which gives me the following table:

City State Data_Servers Backup_Servers
Chicago IL Svr1,Svr2 NULL
Chicago IL NULL Svr3,Svr4
Atlanta GA Svr1,Svr2 NULL

Which is not right as I want Chicago, IL to be in one row...any idea on how to fix this?sql

Problems exporting to Excel

I am having a problem exporting a linked SQL server table from Access 2003
to
Excel. The file is about 2,000 records with several columns. The problem
is that when the table is imported into excel some of the records are not
coming across with the correct data and when numeric fields are summed the
totals are wrong.
When I export directly to Excel from the SQL server using DTS, I don't have
this problem. Has anyone else had this problem? Anyone have ideas of how to
fix it? I need to be able to allow the users to create queries in access
and export the data they need, so setting up a DTS as a solution is not
really workable (or at least I don't think it is).Hi Jim,
Being that this works fine from SQL Server but you are
having problems from MS Access, it's likely an MS Access
issue that you may want to post in one of the MS Access
newsgroups. Maybe try the following group:
microsoft.public.access.externaldata
-Sue
On Mon, 21 Aug 2006 14:52:39 GMT, "JIM" <jcrisp1@.kc.rr.com>
wrote:

>I am having a problem exporting a linked SQL server table from Access 2003
>to
>Excel. The file is about 2,000 records with several columns. The problem
>is that when the table is imported into excel some of the records are not
>coming across with the correct data and when numeric fields are summed the
>totals are wrong.
>When I export directly to Excel from the SQL server using DTS, I don't have
>this problem. Has anyone else had this problem? Anyone have ideas of how t
o
>fix it? I need to be able to allow the users to create queries in access
>and export the data they need, so setting up a DTS as a solution is not
>really workable (or at least I don't think it is).
>

Problems exporting from reporting services 2005 to excel with a lot of rows

Hello,

When I export to excel a report made in Reporting Services 2005, and it has a lot of rows in a table (in the detail section), I have to wait a lot of time, and when it finish the size of the file is bigger than the excel file exported from the old reports in crystal report 6. When I open this exported documents, I have to wait more time until I can see them.

The document have 3 sheets, the first have the document map, the second has a report header, and the last one the page header and the body section (with the table). We thought that it could be the document map because it has hyperlinks to the third sheet, but I have tried to delete this sheet in the excel document and the size is more or less the same.

I tried also exporting to CSV but the results aren't what we need.

What could I do?

Anybody can help me? :/|||

Hi ,

I suggest you to align your report items perfectly to the standards of reporting services

what can you notice when the Excel is opened.if you can see unnecessary columns with zero width.then my expectation is right.

if you can see the dots on report layout tab,when you design the report

,make sure that a report item should not end between these two dots

(size should be the multiple of 0.125 inch)

Follow the same for each Report item in the Report and let me know what did you end up with ?

Thank you,

Raj Deep>A

|||

Hi Raj Deep,

Thanks for your response, but my report items are not between any dots, and the size of every textbox is 0.5. The problem must be because there are too many data in the table, I think, but it wasn′t a matter for crystal reports 8. Any other idea? :)

Thank you,

Pablo

Problems deleting a column

Hello,
I'm trying to delete an existing varchar(10) column called "abc1" o in a
table on a SQL 2000 database and I'm getting the following error message in
Enterprise Manager.:
'myTable' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near 'abc1'.
I tried to delete it from SQL Analyzer directly from the server's console,
but I'm getting a similar error: "Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'abc1'."
I tried to rename, change data type and size of the data type of the column,
but still could not delete it.
Any thoughts?
ThanksOn Tue, 10 Aug 2004 15:25:01 -0700, Vi wrote:
>Hello,
>I'm trying to delete an existing varchar(10) column called "abc1" o in a
>table on a SQL 2000 database and I'm getting the following error message in
>Enterprise Manager.:
>'myTable' table
>- Unable to modify table.
>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
>syntax near 'abc1'.
>
>I tried to delete it from SQL Analyzer directly from the server's console,
>but I'm getting a similar error: "Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'abc1'."
>I tried to rename, change data type and size of the data type of the column,
>but still could not delete it.
>Any thoughts?
>Thanks
Hi Vi,
You forgot to include the code you executed in query analyzer when you got
this error message.
Dropping the column abc1 from table myTable should be possible with the
following statement:
ALTER TABLE myTable
DROP COLUMN abc1
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The code is:
--
USE myDatabase
go
ALTER TABLE dbo.myTable DROP COLUMN abc1
go
--
"Hugo Kornelis" wrote:
> On Tue, 10 Aug 2004 15:25:01 -0700, Vi wrote:
> >Hello,
> >I'm trying to delete an existing varchar(10) column called "abc1" o in a
> >table on a SQL 2000 database and I'm getting the following error message in
> >Enterprise Manager.:
> >
> >'myTable' table
> >- Unable to modify table.
> >ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> >syntax near 'abc1'.
> >
> >
> >I tried to delete it from SQL Analyzer directly from the server's console,
> >but I'm getting a similar error: "Server: Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'abc1'."
> >
> >I tried to rename, change data type and size of the data type of the column,
> >but still could not delete it.
> >
> >Any thoughts?
> >Thanks
> Hi Vi,
> You forgot to include the code you executed in query analyzer when you got
> this error message.
> Dropping the column abc1 from table myTable should be possible with the
> following statement:
> ALTER TABLE myTable
> DROP COLUMN abc1
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Perhaps your database is set to a pre 70 compatibility level. You can check
this with:
EXEC sp_dbcmptlevel 'myDatabase'
And set it with:
EXEC sp_dbcmptlevel 'myDatabase', 80
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:EA353C88-D7EC-46BB-A31A-F748F87B40FE@.microsoft.com...
> The code is:
> --
> USE myDatabase
> go
> ALTER TABLE dbo.myTable DROP COLUMN abc1
> go
> --
> "Hugo Kornelis" wrote:
> > On Tue, 10 Aug 2004 15:25:01 -0700, Vi wrote:
> >
> > >Hello,
> > >I'm trying to delete an existing varchar(10) column called "abc1" o in
a
> > >table on a SQL 2000 database and I'm getting the following error
message in
> > >Enterprise Manager.:
> > >
> > >'myTable' table
> > >- Unable to modify table.
> > >ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2:
Incorrect
> > >syntax near 'abc1'.
> > >
> > >
> > >I tried to delete it from SQL Analyzer directly from the server's
console,
> > >but I'm getting a similar error: "Server: Msg 170, Level 15, State 1,
Line 1
> > >Line 1: Incorrect syntax near 'abc1'."
> > >
> > >I tried to rename, change data type and size of the data type of the
column,
> > >but still could not delete it.
> > >
> > >Any thoughts?
> > >Thanks
> >
> > Hi Vi,
> >
> > You forgot to include the code you executed in query analyzer when you
got
> > this error message.
> >
> > Dropping the column abc1 from table myTable should be possible with the
> > following statement:
> >
> > ALTER TABLE myTable
> > DROP COLUMN abc1
> > (untested)
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
> >|||What do you mean that you tried to change the data type and size of the
column? Were you able to do that? Or did you get error messages for those
operations also.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:9F0BD776-CA70-42C4-92D6-0338228817EA@.microsoft.com...
> Hello,
> I'm trying to delete an existing varchar(10) column called "abc1" o in a
> table on a SQL 2000 database and I'm getting the following error message
in
> Enterprise Manager.:
> 'myTable' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2:
Incorrect
> syntax near 'abc1'.
>
> I tried to delete it from SQL Analyzer directly from the server's console,
> but I'm getting a similar error: "Server: Msg 170, Level 15, State 1, Line
1
> Line 1: Incorrect syntax near 'abc1'."
> I tried to rename, change data type and size of the data type of the
column,
> but still could not delete it.
> Any thoughts?
> Thanks

Problems creating second relationship to the same table

I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:

- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.

Hi. Maybe the SecondCategoryId field has data that not exists in the categories table|||

mariop77 is probably right.

Try doing a SELECT DISTINCT SecondCategoryID FROM ADS

Make sure all FKs really do exist.

|||It has Nulls someties. How can I allow nulls? The reason is sometimes there will be no second category...|||

Hi,

Actually, null is allowed here. But you need to make sure that Allow Null has been checked in the design view of the table.

|||Yes, it is allowed on the column level of course, yet can not create relationship...|||

Seems to me like this is a limitation not allowing more than one join to the same table... Do I need to create alias for the existing table?

1 Category (alias 1) many Ads on Ads.CategoryID

1 Category (Alias 2) many Ads on Ads.SecondCategoryID

I used to be able to do this in MS Access, but maybe SQL does not allow...

sql

Problems changing data in a table

Some data was erroneouely changed in a table earlier this month. The
table used to track documents that have been released for consumption.
The field I wasnt to change is called modified_date and of course
contains dates. Whenever I change the field manually and move to the
next record, the value updates to todays date. If I try an update
statement, no changes are made. I was advised this may be a trigger but
I am unable to remove the trigger due to "cannot alter the table
<table_name> because it is being published for replication". Im fairly
new to SQL Server 2000 and it feels as though I am at a dead end. The
publication is a snapshot.
My questions is, is there an easy way to change the values in the
field?
Thanks
You could drop the subscription(s), drop the article , alter the table to
disable the trigger then do the reverse, eg:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees disable trigger triggername
update tEmployees .....then the reverse (add the article and add the
subscription)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, thanks for your reply. I just browsed across your artcle on
SQLServerCentral. Can I get clarification on a couple of items?
@.publication is the name of the publication
@.article is the name of the table
@.subscriber - not sure what this is
@.destination_db is pretty much self explanitory
Also Im not sure how to get the name of the trigger. If I go to enable
the trigger, can iI use the all parameter to re-enable it?
Thanks
Paul Ibison wrote:
> You could drop the subscription(s), drop the article , alter the table to
> disable the trigger then do the reverse, eg:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> alter table tEmployees disable trigger triggername
> update tEmployees .....then the reverse (add the article and add the
> subscription)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||sp_helptrigger will give you the trigger name. You could use sp_helptext to
check it is the one you thought.
@.subscriber is just the name of the subscriber, as it appears in the
distributor properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Problems calling a stored procedures depending on parameters

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.

I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)
The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).

On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:

ALTERPROCEDURE [dbo].[S_SPROC]
(@.IDint,@.TYPECHAR(10),@.NAMECHAR(20) OUTPUT)
AS

SELECT @.NAME= NAME
FROM SPROCS
WHERE [ID]= @.ID
AND [TYPE]= @.TYPE

Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.
Has anyone done something similar before, or could point me in the right direction to solving this problem.

Thanks
Phil

Since @.NAME is an output parameter, you need to indicate that in your Command object (ParameterDirection.InputOutput or ParameterDirection.Output). That allows the parameter's value to be retrieved after the command has been executed.

Alternatively, you could select the data like you would in a normal data retrieval, and not worry about using an output parameter.

|||

Thanks for your reply Mark, I will try adding the ParameterDirection part.

If I use normal data retrieval how can I select the appropriate stored procedure when I try filling my table adapter from the dataset?

Thanks

|||

I assumed you would be performing an operation to select the stored proc name, then another operation to execute that stored proc.

|||

Yes that is what I am trying to do, but not so sure on how to go about it. Do you have any code examples?

Thanks

|||

hi mate,

Here is a sample

Dim cmd_ObjectpathAsNew SqlCommand("Select * from [" & tabelName &"]", sqlCon)

Dim adapterAsNew SqlDataAdapter(cmd_Objectpath)

Dim resultAsNew DataTable

adapter.Fill(result)

ForEach rowAs DataRowIn result.Rows

////do the process u want

next

|||

The code I have so far is:

Dim IDAs Int32
Dim TypeAsString

PrivateSub SimpleButton1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SimpleButton1.Click

ID =Me.TextBox1.Text
Type =Me.TextBox2.Text

Me.SPROCSTableAdapter.Fill(Me.DataSet1.SPROCS, ID, Type)

GetSprocName("AUMS_VALID")

Try

'Logic is a seperate VB file containing further code
Logic.run_SQL_fill_dataset(Me.sqlDataAdapter1, DataSet1.GEN_VALID)
Catch exAs Exception

EndTry

EndSub

PrivateFunction GetSprocName(ByVal st1)AsString
' Gets the names for the sprocs so each table can be filled with differant data. Using value 1 for param 1 just to test
Me.SQLCommand_GetSprocName.Parameters(1).Value = 1
Me.SQLCommand_GetSprocName.Parameters(2).Value = st1.ToString()
Logic.run_SQL_command(Me.sqlConnection1,Me.SQLCommand_GetSprocName)
' This is is where the app seems to fail
ReturnMe.SQLCommand_GetSprocName.Parameters(3).Value.ToString()

EndFunction

**** Code in Logic File: *****

'Sub to run SQLcommand, checks the connection and haddles errors

PublicSharedSub run_SQL_command(ByVal sqlcon1As SqlClient.SqlConnection,ByVal sqlcom1As SqlClient.SqlCommand)

Try
If sqlcon1.State <> ConnectionState.ClosedThen' connection check
sqlcon1.Close()
EndIf

If sqlcon1.State = ConnectionState.ClosedThen' connection check

sqlcon1.Open()

EndIf

sqlcom1.ExecuteNonQuery()

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Catch exAs Exception

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Error_box(ex,"Error on Running SQL Command")'Can place more better code here later

MsgBox(sqlcom1.CommandText.ToString)

EndTry

EndSub

PublicSharedSub run_SQL_fill_dataset(ByVal sqladapterAs SqlClient.SqlDataAdapter,ByVal datatableAs Data.DataTable)

Try

datatable.Clear()

sqladapter.Fill(datatable)

Catch exAs Exception

Error_box(ex,"Error on fill on dataset.")

EndTry

|||

Hi,

I'm afraid that there's something wrong in your code. What we can provide is a general process of communicating with a stored procedure from a .NET application.

Let's take the stored procedure you provided as the sample.

ALTER PROCEDURE [dbo].[S_SPROC]
( @.ID int, @.TYPE CHAR(10), @.NAME CHAR(20) OUTPUT )
AS

SELECT @.NAME = NAME
FROM SPROCS
WHERE [ID] = @.ID
AND [TYPE] = @.TYPE

In your procs, there are 2 input parameters and an output parameter. Then in your application, you should following the steps below:

1. Create the connection which links to the database.
a) Dim myconn As New SqlConnection(ConnectionString)

2. Create the SqlCommand object which execute the procs.
Dim sc As New SqlCommand()
sc.CommandType = CommandType.StoredProcedure
sc.CommandText = "YourProcsName"
sc.Connection = myconn

3. Setting your parameters and add them to SqlCommand object.

Dim sp1 As New SqlParameter()
sp1.ParameterName = "Parameter1"
sp1.Value = ""

Dim sp2 As New SqlParameter()
sp2.ParameterName = "Parameter2"
sp2.Value = ""

Dim sp3 As New SqlParameter()
sp3.ParameterName = "Parameter3"
sp3.Size = 10
sp3.Direction = ParameterDirection.Output

sc.Parameters.Add(sp1)
sc.Parameters.Add(sp2)
sc.Parameters.Add(sp3)

4. Open the connection, execute the process, and get the output parameter.

myconn.Open()
sc.ExecuteNonQuery()
myconn.Close()
Dim c As String = sp.Value.ToString()

After all, you can get the output parameter from the variable C.

Besides, this is a WebForm support forum, if you are developing WindowForm application, it would be better for you to go to MSDN forum where you can get more help.

Thanks.

|||

Thanks for your reply - it has been a big help.

Phil

Problems adding an article to an existing merge replication if owner is not dbo

Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Just some more info. It seems like the problem occurs when the snapshotagent
generates the bulk copy data for system table
ms_merge_contents_<<newtable>>. It seems like this procedure doesn't use the
right owner to generate the bcp file.
Stefan
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> schreef in bericht
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I created a new table on the publication database the owner of the new
> table is not dbo. We then called sp_addmergearticle to add the article to
> the publication, using the @.source_owner and @.destination_owner parameters
> to specify the owner is different from dbo, and the
> @.force_invalidate_snapshot parameter.
> When we start the snapshot agent, it prepares the newly added table for
> replication, and generates the necessary scripts. At one moment the
> snapshot agent stops with an error stating 'invalid object name' and the
> name of the new table. I think this occurs when the snapshot agent wants
> tot generate the bcp files.
> Inspecting the article's properties dialog, showed 'that source table
> owner' and 'destination table owner' are correct. And except for the
> tablename an article name the properties are no different to the
> previously published articles.
> I retried this for a table that has dbo as owner. This worked without
> problems.
> Does anybody experienced the same problems?
> Best regards,
> Stefan
>
>
|||I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs in merge replication system stored procedures. Try looking for my messages (end of last year) - you should find attached archive with those fixes. if you will not find it, I could re-post it in this thread (I have fixes for SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||OK - I've put them on the downloads section. I can't give you the precise link, other that request that you browse to www.replicationanswers.com, as this website is currently done on the cheap (soon to change providers so I can have absolute urls).
Rgds,
Paul Ibison (SQL Server MVP)
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message news:%23AaQjgHBFHA.2552@.TK2MSFTNGP09.phx.gbl...
Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
That is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>
|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>
|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers[vbcol=seagreen]
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key
> the
>
|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>
|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
--
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Many applications retrieve information from the same table. Each
> application
> > needs to remember the records it has retrieved before, so that it does
not
> > retrieve these records in the following run any more.
> >
> > The current solution is to put the different "flag" field for each
> > application on the table. The flag field for each application will be
> marked
> > by the application after it retrieves the records.
> >
> > The problem is that the table becomes extremely wide and keep widening.
> The
> > table needs to be changed each time a new appliction comes up.
> >
> > I am thinking about keep a list for each application which stores the
key
> > values of the records the application has retrieved, instead of marking
> the
> > original table.
> >
> > Is there any better way to do it? What is the workaround you recommend?
> > Thanks,
> >
> > Lixin
> >
> >
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key[vbcol=seagreen]
> the
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> > treese
> > Do you have appropriate indexes for your table?
> > Using LIKE '%Something' may prevent from query optimizer to use an
index.
> >
> >
> >
> >
> > "treesy" <treesy@.hostran.com.cn> wrote in message
> > news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > > I have a table of 60,000rows. There are 47 fields in the table. 43 of
> them
> > > are nvarchar with length of 100. Two of them are big nvarchar with
> length
> > of
> > > 1024 and 2048. an indexno field is int and the other is date. two
> primary
> > > keys, one of them is indexno;the other is nvarchar.
> > >
> > > Low performance:
> > > 1.using query of 'like', it shows timeout. I have to use indexno to
> > control
> > > the number of the rows i am querying.
> > > 2.When I use application program to query some length of bytes, it
costs
> > > quite a lot of minutes.
> > > Any one can help?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> дÈëÏûÏ¢ÐÂÎÅ
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> д?
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> д?
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
index.
> them
> length
> primary
costs
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> д?
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>