db datatypes: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?
Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>
|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:
>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribió en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>>Hi all:
>>I have following problem.
>>When I try to run an INSERT from Query Analyser to a table of a linked
>>server I get
>>a bug about a TEXT field of table:
>>INSERT is like this one:
>>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>>Bug is like this one:
>>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>>OLE DB [OLE/DB Provider 'SQLOLEDB'
>>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>>STATUS=DBSTATUS_S_DEFAULT],
>>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>>value.
>>Running same instruction locally there isn't any problem.
>>How can I solve this problem ?
>>
>>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribió en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
>> When I try to run an INSERT from Query Analyser to a table of a linked
>> server I get
>> a bug about a TEXT field of table:
>> INSERT is like this one:
>> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>> Bug is like this one:
>> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>> OLE DB [OLE/DB Provider 'SQLOLEDB'
>> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK],
>> [COLUMN_NAME=familia
>> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>> STATUS=DBSTATUS_S_DEFAULT],
>> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>> value.
>> Running same instruction locally there isn't any problem.
>> How can I solve this problem ?
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion
)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observa
ciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f
amilia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAUL
T],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observacio
nes'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcio
n)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observ
aciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=
familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAU
LT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaci
ones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripci
on)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'obser
vaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME
=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observac
iones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

I have created a linked server on which following query works fine.

EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE]

but the same statement executed with openquery

select * from openquery([remote],'select top 10 * from dummyObjects') returns following error.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Hi ck!

Could you remove all columns except dummyObjectID and run this again?

If this reproes, could you reply with a sequence of "CREATE TABLE" and "INSERT" statements that will allow me to repro this on my machine?

Wednesday, March 28, 2012

Problem with Large data amounts

I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

Unfortunately, Reporting Services has some memory limitations in the current (SQL 2K and SQL 2K5). Because we support things like dataset aggregates (you can have sum(x) on the first page of your report), we materialize the entire dataset. Some things you might try:

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Is there any way to be sure to dispose of the memory being allocated by the reportviewer? I can generate one report, close it, wait, open another, and the memory allocation just keeps growing and growing.

Any help is appreciated.

|||

I seem to be having a similar problem with a Xeon and 4Gig of memory. In my case, after the out of memory error happens, remote connections stop working stating that there is insufficient memory. In other words I can't even run a select statement in Management Studio after it hits the memory error. I have to restart the SQL Service.

HELP!!!

|||

First off. A human does not look at 300,000 records. Most of the time I have seen this it has been to export to Excel. When exporting to Excel export as CSV ASCII (although I don't think Excel can handle more that 64,000 records).

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

Is this still a problem with Reporting Services? We are having a similar problem with .net choking on large data sets and were considering using Reporting Services as an alternative. Is Microsoft an enterprise player or not?

We are required to periodically return several hundred thousand rows from SQL Server. I'm tired of being told that is a design problem. Unfortunately when our client says he needs to print a detail report from his general ledger for his auditor we don't have the luxury of telling him Microsoft doesn't think he needs that large of a report. If he can't get the data he needs from us, he'll be happy to go down the street to get it (read Oracle).

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with Large data amounts

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Any help is appreciated.

|||

HELP!!!

|||

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

sql

Problem with Large data amounts

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Any help is appreciated.

|||

HELP!!!

|||

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with Large data amounts

Do you need the entire data set displayed in the report or could you do some grouping / aggregation in the query?|||

I am having a similar problem with large data amounts. My reporting server is a Dual Xeon 3GHz machine with 2.5GB RAM and still has issues.

What I find is that the reportviewer object is not releasing memory after the reports are generated, viewed and closed. It recycles the application several times if I try to do an export to excel.

Any help is appreciated.

|||

HELP!!!

|||

Note that this is with records returned, not records in the base table. I go against tables with 150 million records but I only return the data needed.

I suggest looking at design drill through reports to limit the data.

|||

If I can't serialize it through .net or get it out of Reporting Services, how am I supposed to extract large data sets from SQL Server?

Will 64 bit processors and multi-GB ram implementations help?

Thanks.

Problem with JOIN

I'm learning aspx. I have the following code in my aspx:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
This code returns the correct data set. However, when I use this code:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
notes, pubdate FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
the returned data set does not contain the MyTable data.
What is Wrong?
"RuffAroundTheEdges" schrieb:
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
You are not asking for data from MyTable. All the fields in the select list
are fields from 'titles', aren't they?
|||If you place the star (*) in the selection list without any prefix for any
table, even all joined tables are used for selecting the columns, If you are
specifying the columns only these are selected.
So in your second example you should go for that statement if you want to
get those columns in addition:

> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate,
myTable.*
FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"RuffAroundTheEdges" <RuffAroundTheEdges@.discussions.microsoft.com> wrote in
message news:AC882F99-C17E-43BF-843E-0EF0D0B4ADC1@.microsoft.com...
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
|||The example shown in the FROM document page at;
http://msdn.microsoft.com/library/de..._pubs_2v8l.asp
has:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
|||I had to do more reading and found that the SELECT statement works
differently in a JOIN:
SELECT Titles.title.id, ..., MyTable.stuff FROM Titles JOIN MyTable ON
(MyTable.title_id = Titles.title_id)

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?
I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa
|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prtint
prot_nr_anosmallint
diasint
dh_recebimetovarchar(10)
uaie_ds_siglavarchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>
sql

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (
Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) C
ursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript fro
m
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT t
he
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
RafaÂ®I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa®|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:
> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "RafaÂ®" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> > I have several jobs that run simple T-SQL scripts...
> > Most of them, fail, with the following problem:
> >
> > "Executed as user: dba. The conversion of a char data type to a datetime
> > data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
> (Error
> > 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
> Cursor
> > is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> >
> > Some parts are specific from a example job I'll post here, but the "char
> to
> > datetime conversion" error, appears in all of them.. There is transcript
> from
> > the Job that raised that error:
> >
> > /***********/
> >
> > DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> > varchar (10), @.IP varchar (50), @.cmd varchar (500)
> > IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> > BEGIN
> > DECLARE cur_opv CURSOR FOR
> > select prot_nr_prt
> > ,prot_nr_ano
> > ,dias
> > ,dh_recebimeto
> > ,uaie_ds_sigla
> > from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> > OPEN cur_opv
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providÃªncias hÃ¡ ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> >
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > END
> > CLOSE cur_opv
> > DEALLOCATE cur_opv
> > END
> >
> > /***********/
> >
> > It's important to note that, the ONLY time when this script deal with some
> > datetime type, is on the DATEPART function on the If. This If is just to
> see
> > if it is 10 o'clock... It wasn't me who wrote this script, because it's
> > stupid to control the date by the script and not by the job schedule..
> > Anyways, I prefered to keep this way... But I don't think that those line
> of
> > code could be the problem...
> >
> > These job errors are making me crazy! If someone know something about, plz
> > help me, I can send some other samples of jobs returning errors!
> >
> > Thanks!!!
> > RafaÂ®
>
>

Problem with JDBC Driver for MS SQL Server

Dear All,

I developed an web application which use MS SQL Server 2000. I
encounter the following SQLException "[Microsoft][SQLServer 2000
Driver for JDBC]Error setting up static cursor cache". Did anyone
encounter this problem before? What does this exception mean and how
to solve this problem?

[Remarks: The web application work properly on development machine and
this machine but encounter this exception when move to another
machine, so is this exception related to the machine?]

Thank you for your attention.

Yours faithfully,
Benny
Benny wrote:

> Dear All,
> I developed an web application which use MS SQL Server 2000. I
> encounter the following SQLException "[Microsoft][SQLServer 2000
> Driver for JDBC]Error setting up static cursor cache". Did anyone
> encounter this problem before? What does this exception mean and how
> to solve this problem?

I think it has to do with OS user file permissions in temp directories.
Joe Weinstein at BEA

> [Remarks: The web application work properly on development machine and
> this machine but encounter this exception when move to another
> machine, so is this exception related to the machine?]
> Thank you for your attention.
> Yours faithfully,
> Benny|||I have been investigating the same thing. I have added the C:\Documents and Settings\Administrator\Local Settings\Temp to every policy file that I could find (both the java.policy and catalina.policy files). This does not seem to help. Hmmmmm.... I will keep working on it, but if you have figured it out, I would really appreciate you letting me know how to resolve this.

This is a win2k environ w sqlserver 2000.

Monday, March 26, 2012

Problem with installation of 'Adventureworks' sample db provided by Microsoft

I was trying to install the 'Adventureworks' sample db to the following
path: D:\MSSQL\Data. After I run the installer, I tried to run the following
query at the Sql Server Management Studio as stated in the documentation:
exec sp_attach_db @.dbname=N'AdventureWorks',
@.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
@.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
However, when I tried to execute this script, I got the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Any idea how can I resolve this?Did you have 'Adventureworks' install on the server already?
Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
BOL?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67m2u$4no$1@.mawar.singnet.com.sg...
>I was trying to install the 'Adventureworks' sample db to the following
>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>following query at the Sql Server Management Studio as stated in the
>documentation:
> exec sp_attach_db @.dbname=N'AdventureWorks',
> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>
> However, when I tried to execute this script, I got the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 9, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> Any idea how can I resolve this?
>|||Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
DATABASE ... FOR ATTACH option and what's a BOL?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Did you have 'Adventureworks' install on the server already?
> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
> BOL?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index ID
>> 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>|||I meant I have installed the 'Adventureworks' db (the installation runs
smoothly so I assumed that). However, it does not appear in the Sql Server
Management Studio. I referred to the documentation and it says that I need
to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
that, I got the error.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67qus$585$1@.mawar.singnet.com.sg...
> Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
> DATABASE ... FOR ATTACH option and what's a BOL?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
>> BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>|||BOL -is Books On Line
Copied from BOL
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67rjb$58l$1@.mawar.singnet.com.sg...
>I meant I have installed the 'Adventureworks' db (the installation runs
>smoothly so I assumed that). However, it does not appear in the Sql Server
>Management Studio. I referred to the documentation and it says that I need
>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
>that, I got the error.
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>|||Hi Uri,
I tried to execute the script that you gave and got the following error:
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'Archive' does not exist. Use sp_helpdb to show available
databases.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'master.sys.master_files'.
Any idea what went wrong?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
> BOL -is Books On Line
> Copied from BOL
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql Server
>>Management Studio. I referred to the documentation and it says that I need
>>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I
>>did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>>
>|||Hi
Sorry, my mistake. I did not tell you changde the name of database. It was
taken from BOL, so please modify it for your needs.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f687ge$5q0$1@.mawar.singnet.com.sg...
> Hi Uri,
> I tried to execute the script that you gave and got the following error:
> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
> The database 'Archive' does not exist. Use sp_helpdb to show available
> databases.
> Msg 208, Level 16, State 1, Line 3
> Invalid object name 'master.sys.master_files'.
> Any idea what went wrong?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the
>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>run the following query at the Sql Server Management Studio as stated
>>in the documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1,
>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>>
>>
>|||Well, I did change the db name but the error is still there. Anyway, here's
my script.
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
FOR ATTACH');
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi
> Sorry, my mistake. I did not tell you changde the name of database. It was
> taken from BOL, so please modify it for your needs.
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>>run the following query at the Sql Server Management Studio as stated
>>>in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>
>>
>>
>>
>>
>|||Hi
Do you have SQL Server 2000 installed on thr machine? Check out that you do
not connect to SQL Server 2000 when you run the script
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f688kd$5si$1@.mawar.singnet.com.sg...
> Well, I did change the db name but the error is still there. Anyway,
> here's my script.
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
> FOR ATTACH');
> GO
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>>run the following query at the Sql Server Management Studio as
>>>stated in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I tried
to stop the Sql Server Service Manager, but it stopped both versions of the
sql server instead).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
> Hi
> Do you have SQL Server 2000 installed on thr machine? Check out that you
> do not connect to SQL Server 2000 when you run the script
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation
>>runs smoothly so I assumed that). However, it does not appear in the
>>Sql Server Management Studio. I referred to the documentation and it
>>says that I need to run the "exec sp_attach_db
>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>to run the following query at the Sql Server Management Studio as
>>>stated in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but I
am still getting the same error. What else am I missing?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f689ud$m6m$1@.reader01.singnet.com.sg...
> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
> tried to stop the Sql Server Service Manager, but it stopped both versions
> of the sql server instead).
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that you
>> do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in the
>>>Sql Server Management Studio. I referred to the documentation and it
>>>says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>>to run the following query at the Sql Server Management Studio as
>>>>stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the following
>>>> error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>>> aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi
Now, you connect to SSMS and make sure that you are connected to SQL Server
2005 instance. Run the script again
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f68b6b$m7s$1@.reader01.singnet.com.sg...
> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but
> I am still getting the same error. What else am I missing?
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that you
>> do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show
>> available databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in the
>>>Sql Server Management Studio. I referred to the documentation and it
>>>says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>>to run the following query at the Sql Server Management Studio as
>>>>stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>>> aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Nope, it didn't work. Still got the same error
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hi
> Now, you connect to SSMS and make sure that you are connected to SQL
> Server 2005 instance. Run the script again
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but
>> I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database.
>> It was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in
>>>the Sql Server Management Studio. I referred to the documentation
>>>and it says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the
>>>error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>tried to run the following query at the Sql Server Management
>>>>Studio as stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>> is aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID
>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi
Hmmm, I have no idea any more. The last one is permission issue. Do you
have full permissions on the folders where database files are located?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f6a03o$nd0$1@.reader01.singnet.com.sg...
> Nope, it didn't work. Still got the same error
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Now, you connect to SSMS and make sure that you are connected to SQL
>> Server 2005 instance. Run the script again
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager
>> but I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>>> Hi
>>> Sorry, my mistake. I did not tell you changde the name of database.
>>> It was taken from BOL, so please modify it for your needs.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>>I meant I have installed the 'Adventureworks' db (the installation
>>>>runs smoothly so I assumed that). However, it does not appear in
>>>>the Sql Server Management Studio. I referred to the documentation
>>>>and it says that I need to run the "exec sp_attach_db
>>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the
>>>>error.
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>tried to run the following query at the Sql Server Management
>>>>Studio as stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>> is aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID
>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Yup, I do have the full permision for the folders where the database files
are kept. Think I'll just uninstall the database and install it again with
its default path to see if it works. Anyway, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eoF$JqHvHHA.3748@.TK2MSFTNGP04.phx.gbl...
> Hi
> Hmmm, I have no idea any more. The last one is permission issue. Do you
> have full permissions on the folders where database files are located?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f6a03o$nd0$1@.reader01.singnet.com.sg...
>> Nope, it didn't work. Still got the same error
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Now, you connect to SSMS and make sure that you are connected to SQL
>> Server 2005 instance. Run the script again
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager
>> but I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>>> Well, I did change the db name but the error is still there. Anyway,
>>> here's my script.
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>>> Hi
>>> Sorry, my mistake. I did not tell you changde the name of database.
>>> It was taken from BOL, so please modify it for your needs.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>>> BOL -is Books On Line
>>>> Copied from BOL
>>>>
>>>> USE master;
>>>> GO
>>>> sp_detach_db Archive;
>>>> GO
>>>> -- Get the SQL Server data path
>>>> DECLARE @.data_path nvarchar(256);
>>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>>> FROM master.sys.master_files
>>>> WHERE database_id = 1 AND file_id = 1);
>>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>>> EXEC ('CREATE DATABASE Archive
>>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>>> FOR ATTACH');
>>>> GO
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>>I meant I have installed the 'Adventureworks' db (the
>>>>installation runs smoothly so I assumed that). However, it does
>>>>not appear in the Sql Server Management Studio. I referred to the
>>>>documentation and it says that I need to run the "exec
>>>>sp_attach_db @.dbname=N'AdventureWorks'" script. When I did that,
>>>>I got the error.
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>>tried to run the following query at the Sql Server Management
>>>>>Studio as stated in the documentation:
>>>>>
>>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>>
>>>>>
>>>>> However, when I tried to execute this script, I got the
>>>>> following error:
>>>>>
>>>>>
>>>>> Msg 1813, Level 16, State 2, Line 1
>>>>>
>>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>>> is aborted.
>>>>>
>>>>> Msg 602, Level 21, State 50, Line 1
>>>>>
>>>>> Could not find row in sysindexes for database ID 9, object ID
>>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>>
>>>>>
>>>>>
>>>>> Any idea how can I resolve this?
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Friday, March 30, 2012

Problem with Linked Server and Text fields

Problem with Linked Server and Text fields

Problem with Linked Server and Text fields

Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

Wednesday, March 28, 2012

Problem with Large data amounts

Problem with Large data amounts

Problem with Large data amounts

Problem with Large data amounts

Problem with JOIN

Problem with Jobs... Plz help!

Problem with Jobs... Plz help!

Problem with Jobs... Plz help!

Problem with JDBC Driver for MS SQL Server

Monday, March 26, 2012

Problem with installation of 'Adventureworks' sample db provided by Microsoft

db datatypes

Blog Archive

About Me