Can't Sum Table Fields in SQL 2000 Reporting Services Report

I have a report in SQL 2000 Reporting Services with several tables.
The only thing showing in these tables are the group footers because
the detail data is not needed.
I am needing to add a total for a column in each table and then add
those totals together in a text box.
When ever I do this I am getting the following errors:
The value expression for the text box 'textbox58' uses an aggregate
function on a report item. Aggregate functions can be used only on
report items contained in page headers and footers.
The value expression for the text box 'textbox37' uses an aggregate
expression without a scope. A scope is required for all aggregates
used outside of a data region unless the report contains exactly one
data set.
I've tried several different ways and still can't figure it out, PLEASE
HELP!!A few design suggestions. If you don't need the detail then don't return it.
Let SQL Server do the work. But, let's say you continue the way you are
today. What I do sometimes is to add a colum to the result set and make it
calculated, then using the expression editor make it the sum of the two
fields. This makes things a whole lot easier at times.
Once you have done this you can use the expression editor, base it on the
dataset and sum up the column.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jasper.frazee@.gmail.com> wrote in message
news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>I have a report in SQL 2000 Reporting Services with several tables.
> The only thing showing in these tables are the group footers because
> the detail data is not needed.
> I am needing to add a total for a column in each table and then add
> those totals together in a text box.
> When ever I do this I am getting the following errors:
> The value expression for the text box 'textbox58' uses an aggregate
> function on a report item. Aggregate functions can be used only on
> report items contained in page headers and footers.
> The value expression for the text box 'textbox37' uses an aggregate
> expression without a scope. A scope is required for all aggregates
> used outside of a data region unless the report contains exactly one
> data set.
> I've tried several different ways and still can't figure it out, PLEASE
> HELP!!
>|||Thanks for the Responce Bruce!
In my dataset there are 5 columns that will alwayse have the same
result and one column that has detail data that needs to be summed. If
I sum that one column in SQL I have to have the other columns in an
aggregate function as well. In SQL 2000 the "First" function is not
supported. Any ways around that?
Once I get the totals in each table on my report how can I grab those
totals and sum them in a textbox? It keeps telling me that "Aggregate
functions can be used only on
report items contained in page headers and footers."
Thanks A Lot!
Jasper
Bruce L-C [MVP] wrote:
> A few design suggestions. If you don't need the detail then don't return it.
> Let SQL Server do the work. But, let's say you continue the way you are
> today. What I do sometimes is to add a colum to the result set and make it
> calculated, then using the expression editor make it the sum of the two
> fields. This makes things a whole lot easier at times.
> Once you have done this you can use the expression editor, base it on the
> dataset and sum up the column.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <jasper.frazee@.gmail.com> wrote in message
> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
> >I have a report in SQL 2000 Reporting Services with several tables.
> > The only thing showing in these tables are the group footers because
> > the detail data is not needed.
> > I am needing to add a total for a column in each table and then add
> > those totals together in a text box.
> > When ever I do this I am getting the following errors:
> >
> > The value expression for the text box 'textbox58' uses an aggregate
> > function on a report item. Aggregate functions can be used only on
> > report items contained in page headers and footers.
> >
> > The value expression for the text box 'textbox37' uses an aggregate
> > expression without a scope. A scope is required for all aggregates
> > used outside of a data region unless the report contains exactly one
> > data set.
> >
> > I've tried several different ways and still can't figure it out, PLEASE
> > HELP!!
> >|||You can sum the numbers by summing =ReportItems("textbox1").Value +
ReportItems("textbox2").Value etc.
You need to know the names of the textboxes, so you might want to name the
textboxes with something more descriptive than textbox1 etc before you start
summing. :)
(Not sure if it's ReportItems or ReportItem, so you might have to try both.)
Kaisa M. Lindahl Lervik
<jasper.frazee@.gmail.com> wrote in message
news:1162417277.149051.147270@.f16g2000cwb.googlegroups.com...
> Thanks for the Responce Bruce!
> In my dataset there are 5 columns that will alwayse have the same
> result and one column that has detail data that needs to be summed. If
> I sum that one column in SQL I have to have the other columns in an
> aggregate function as well. In SQL 2000 the "First" function is not
> supported. Any ways around that?
> Once I get the totals in each table on my report how can I grab those
> totals and sum them in a textbox? It keeps telling me that "Aggregate
> functions can be used only on
> report items contained in page headers and footers."
> Thanks A Lot!
> Jasper
> Bruce L-C [MVP] wrote:
>> A few design suggestions. If you don't need the detail then don't return
>> it.
>> Let SQL Server do the work. But, let's say you continue the way you are
>> today. What I do sometimes is to add a colum to the result set and make
>> it
>> calculated, then using the expression editor make it the sum of the two
>> fields. This makes things a whole lot easier at times.
>> Once you have done this you can use the expression editor, base it on the
>> dataset and sum up the column.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <jasper.frazee@.gmail.com> wrote in message
>> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>> >I have a report in SQL 2000 Reporting Services with several tables.
>> > The only thing showing in these tables are the group footers because
>> > the detail data is not needed.
>> > I am needing to add a total for a column in each table and then add
>> > those totals together in a text box.
>> > When ever I do this I am getting the following errors:
>> >
>> > The value expression for the text box 'textbox58' uses an aggregate
>> > function on a report item. Aggregate functions can be used only on
>> > report items contained in page headers and footers.
>> >
>> > The value expression for the text box 'textbox37' uses an aggregate
>> > expression without a scope. A scope is required for all aggregates
>> > used outside of a data region unless the report contains exactly one
>> > data set.
>> >
>> > I've tried several different ways and still can't figure it out, PLEASE
>> > HELP!!
>> >
>sql

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0

|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks
|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent
sql

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Can't seem to build simple cube in SQL 2005

I have a relatively simple cube, with the exception that some of the tables in my data source view are named queries from different databases.

Whenever the cube processes, it fails with the following error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported..

When I switch the data source to use the OLEDB for SQL Server instead of the native client, it complains of invalid columns in the query (there are no invalid columns in the query, I can browse the data from the designer and run the query successfully).

Anyone know how to get past this issue?

Hi, Did you find a solution to this, as I'm experiencing the same situation.

Thanks,

Steve

|||Yes, I eventually figured it out. I had to switch every connection to use oledb for it to work, even ones not associated with the query giving the error.|||I hit this same issue as well...does anyone on the SSAS team know more about this? Why doesn't the default provider work? The strange thing is that was JUST working fine and I don't know why I started to receive this error.

Error 1 OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000. 0 0|||Did you find an issue to this problem ? I encounter exactly the same thing.

Thanks|||I have not tried this again since sp2 came out, but I would make sure the service packs are applied. Otherwise, the only workaround is to change ALL the connections to use the OLEDB provider. If any of the connections use the new provider the error will continue to occur.|||Thank you for your reply. All my connections were using OLEDB provider and I still had the problem.

Anyway, by the same time we changed our test server (hosting both the databases and the visual studio solution) and I don't have the problem anymore with this new server. I hope it won't occure again !

Thanks again

Vincent

Cant See Tables in SQL Server 2000

This is a re-post as I didn't get a resolution to the problem
Using MDAC 2.8
I have set up my SQL Server and set SQL SERVER authentication and for some
reason when I connect through ODBC I can't see any of the tables. It logs
into SQL server just fine, I have set the appropriate rights to the tables
etc. If I log on as system admin I can see the tables but can't update any
of the data.
Any ideas?What utility/tool/program are you using? Are you sure you're being connecte
d
to the database that you're expecting?

> Using MDAC 2.8
> I have set up my SQL Server and set SQL SERVER authentication and for some
> reason when I connect through ODBC I can't see any of the tables. It logs
> into SQL server just fine, I have set the appropriate rights to the tables
> etc. If I log on as system admin I can see the tables but can't update an
y
> of the data.
> Any ideas?
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hey Neil,
Thanks for the reply..
I am suing Access 97/2000/2003 to link to the tables. Again it connects
fine, but the tables are not displayed.
Thanks!
Barry
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000061cf.021cee1a@.compuserve.com...
> What utility/tool/program are you using? Are you sure you're being
connected
> to the database that you're expecting?
>
some
logs
tables
any
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Barry - connect using query analyser (ISQLW) and see if you can see the
tables.

> I am suing Access 97/2000/2003 to link to the tables. Again it connects
> fine, but the tables are not displayed.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Can't see or access tables through linked server

Apparently I am doing something wrong and I don't know what!

I am needing to link a remote Oracle Server to SQL Server 2005.

I have (I think) followed the steps precisely to create the linked

server both by code or through the SQL Server Management Studio and I

get the same results eith way. I can see the linked server and there are no apparent errors but I can't see or access the tables from Oracle.

If I try a Select statement on a table from the linked server I get the

error "Msg 208, Level 16, Stat 1, Server {servername}, Line 1 Invalid

object name"

I am assuming this is a permissions issue but I don't see it. I

am an admin on both the SQL Server DB and the Oracle DB with full

access and I am using windows authentication.

I have mapped local logons to the remote logon and tried almost every

possible combination of security contexts and other users on the local

and linked server but I always get the same result. The Oracle server shows it is linked but I can't get to any table on the linked server.

Any help on this would be really appreciated. I have already spent two days on this.

Thanks in advance.

This kb should help.

support.microsoft.com/kb/280106

|||

I don't think this applies. This KB article only covers up to Windows 2000 and Oracle 8.i.

We have either 2003 server or XP (happening on two machines) and Oracle 10g.

Also, I am not getting any error messages as the article discusses, I just can't see the tables.

Thanks for the help.

sql

Can't see data anymore!

I have had an issue when I try to return all rows or do a query on my data
tables. I get the error 8007007F unknown error
I have tried to instal the newest service releases sp3, and have had no
luck. I have upgraded my server to MS Server 2003.
If you have any ideas, I desparately need them!
Thanks
DerekTry re-install MDAC2.8.
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:A58B499A-6AD8-4AA7-9E7B-C8FCD27C0B4F@.microsoft.com...
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Install SP3a. The SQL Server version will be 8.00.760 (SP3).
"dkoz" wrote:
> I have had an issue when I try to return all rows or do a query on my data
> tables. I get the error 8007007F unknown error
> I have tried to instal the newest service releases sp3, and have had no
> luck. I have upgraded my server to MS Server 2003.
> If you have any ideas, I desparately need them!
> Thanks
> Derek|||Still no go. I have installed the sp3a, and have done just about everything
I can think of other then reinstalling the SQL server.
Quite frustrating!
"Richard_SQL" wrote:
> Install SP3a. The SQL Server version will be 8.00.760 (SP3).
>
> "dkoz" wrote:
> > I have had an issue when I try to return all rows or do a query on my data
> > tables. I get the error 8007007F unknown error
> > I have tried to instal the newest service releases sp3, and have had no
> > luck. I have upgraded my server to MS Server 2003.
> >
> > If you have any ideas, I desparately need them!
> > Thanks
> > Derek|||Did you try re-install MDAC2.8?
Ayad
"dkoz" <dkoz@.discussions.microsoft.com> wrote in message
news:5DF77B1A-C076-46CF-B854-B830F5171B9D@.microsoft.com...
> Still no go. I have installed the sp3a, and have done just about
everything
> I can think of other then reinstalling the SQL server.
> Quite frustrating!
> "Richard_SQL" wrote:
> > Install SP3a. The SQL Server version will be 8.00.760 (SP3).
> >
> >
> >
> > "dkoz" wrote:
> >
> > > I have had an issue when I try to return all rows or do a query on my
data
> > > tables. I get the error 8007007F unknown error
> > > I have tried to instal the newest service releases sp3, and have had
no
> > > luck. I have upgraded my server to MS Server 2003.
> > >
> > > If you have any ideas, I desparately need them!
> > > Thanks
> > > Derek

Can't See All Tables in Entities Pane

Why is it that only SOME of the tables in my database show up in the Entities pane?

There are a total of six tables in my database, yet only three of them are available to me in the Entities pane. I have looked at permissions, etc, and I am using the SA account. It seems like I should see either ALL or NONE of the tables, but that is not the case.

What am I missing?

Only tables that you included in the Data Source View when you built the Report Model will show in the list of entities. Did you include all six tables? If not, or if you have added tables since you built the model, you'll need to build the model again.

Can't see a particular table in a database

Hello,
I am trying to get info from a SQL7 database via ODBC. I can connect and
view the vast majority of tables, but one particular table (ceninfo_data)
does not appear.
In SQL Enterprise manager, I have taken a look at the permissions on the
table and public can select from it.
What am I missing?
Cheers
CQMMAN
"See, free nations are peaceful nations. Free nations don't attack each
other. Free nations don't develop weapons of mass destruction." George W
Bush -Milwaukee, Wis., Oct. 3, 2003Doh!
Fixed it. I was replicating a database and had changed the permissions on
the original, but not on the replicated database.
Cheers
"CQMMAN" <cqmman@.yahoo.co.uk> wrote in message
news:bq2jmp$1uhsi3$1@.ID-215193.news.uni-berlin.de...

quote:

---

> Hello,
> I am trying to get info from a SQL7 database via ODBC. I can connect and
> view the vast majority of tables, but one particular table (ceninfo_data)
> does not appear.
> In SQL Enterprise manager, I have taken a look at the permissions on the
> table and public can select from it.
> What am I missing?
> Cheers
> CQMMAN
> "See, free nations are peaceful nations. Free nations don't attack each
> other. Free nations don't develop weapons of mass destruction." George W
> Bush -Milwaukee, Wis., Oct. 3, 2003
>

---

Cant quite figure this one out... any help?

I have 2 tables, one contains the details for all the documents stored on a server such as the documents name and which directory it is stored in, and the other contains the details for each document version (if there are any) these are paired up using the documents id, per example below

Documents
ID, Name, Dir
1, word.doc, /docs
2, excel.xls, /docs
3, webpage.html, /docs

Document Versions
ID
1
1
1
2
2
3

so, as you can see, in the versions table there are 3 versions of 'word.doc' 2 of 'excel.xls' and 1 of 'webpage.html'. i need to write a query that will return all the documents in the versions table who have a matching document in the documents table (that is not the major problem though) the main problem is that i only want to return it if the matching record in the documents table passes certain criteria, this being that it is from a certain site and has a particular type of file name. for example, i would like to return only the document versions whose parent records are '.doc' or '.xls' files, but not html. this would leave me with :

Versions
ID
1
1
1
2
2

leaving the last record out, as it's parent is a html file.

As a bonus, if you could tell me how to append the parents name to each record so i get a table like this :

Results
ID Name
1 'word.doc'
1 'word.doc'
1 'word.doc'
2 'excel.xls'
2 'excel.xls'

that would be great, thank youYou didn't specify database engine you use; but, in Oracle it would be something like this:SELECT d.id, d.name
FROM DOCUMENTS d, VERSIONS v
WHERE v.id = d.id
AND SUBSTR(d.name, INSTR(d.name, '.', 1) + 1, LENGTH(d.name)) <> 'html';
WHERE clause will be different for another requirements, of course.|||hmmm, not sure if that's quite it. and the database engine is SQL server|||As I can't see a generic SQL solution, I'd recommend using:SELECT d.id, d.name
FROM documents AS d
JOIN versions AS v
ON (v.id = d.id)
WHERE d.name NOT LIKE '%.html'-PatP|||Got It! Thanks for your help though. This was the final solution for anyone who's interested

SELECT Docs.Name, DocVersions.TimeCreated, DocVersions.Content
FROM DocVersions INNER JOIN
Docs ON DocVersions.Id = Docs.Id
WHERE (Docs.LeafName LIKE '%.doc' OR
Docs.Name LIKE '%.xls' OR
Docs.Name LIKE '%.ppt' OR
Docs.Name LIKE '%.pdf' OR
Docs.Name LIKE '%.mpp' OR
Docs.Name LIKE ' % .txt ') AND (Docs.DirName LIKE 'sites/archive/%')

I think i confused u all with my talk of leaving out the html document. It wasn't so much i wanted to leave that out as include the others if you know what i mean

Can't paste data

I am trying to migrate an Access database to SQL Express.I created the new database in SQL Express, and added the tables and columns.At this point, I have done so without defining any keys, indexes, or relationships.For most of the tables I was able to copy the data from Access and paste same into the SQL Express tables.

However, I am unable to create the last table, which is a link table to manage the many-to-many relationships.

The table has the following columns:

MediumCode (bigint, null)

ArtistCode (bigint, null)

SongCode (bigint, null)

TrackNumber (int, null)

Here is a sample of the data I am trying to paste:

1,180,204,1

2,2,45,1

3,3,80,1

4,4,30,1

5,5,22,1

6,6,108,1

When I perform the paste there is no error message.The data just doesn’t get added to the table.I don’t understand why the other tables worked and this one does not.I am trying to paste 85 rows. Any ideas?

Also I tried to do this as an INSERT query, but SQL Express does not like the following syntax:

INSERT INTO [Music].[dbo].[tblMediumDetails]

([MediumCode]

,[ArtistCode]

,[SongCode]

,[TrackNumber])

VALUES

(1, 180, 204, 1),

(2, 2, 45, 1),

(3, 3, 80, 1),

(4, 4, 30, 1),

(5, 5, 22, 1),

(6, 6, 108, 1)

Do I have to create an INSERT statement for each row? Is there a bulk load function available in SQL Express, or is that not available with the free version?

Thanks,

Robert

Hi,

you either have to create single insert statements or you define a Insert into statement with a following Select statement like:

INSERT INTO SomeTable
(
Columnlist
)
SELECT Columnlist
From Someothertable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the quick reply. Now that I know what needs to be done, I can stop spinning my wheels.

Thanks,

Robert

can't open tables -> return all rows

Hello Everyone,
I am having trouble with opening any of the tables in any
of my databases in SQL Serve 7.
The error message i get is:
"Unexpectd Error happened during this operation.
[Query] - Query Designer encountered a querey error:
Unspecified error"
Please advise.
Thank you!
MitraHi Mitra. How've you been?
This problem seems to be from a corrupted DLL in EM. You might need to
reinstall it. Good luck to you.
Alex Ivascu
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:046a01c398ee$e384b770$a401280a@.phx.gbl...
> Hello Everyone,
> I am having trouble with opening any of the tables in any
> of my databases in SQL Serve 7.
> The error message i get is:
> "Unexpectd Error happened during this operation.
> [Query] - Query Designer encountered a querey error:
> Unspecified error"
> Please advise.
> Thank you!
> Mitra
>|||Hi Alex,
I can't believe that you replied to my question. It's so
nice to hear from someone i know.
Thanks so much for your comment. I am going to reboot my
system and see if that will help else i have to reinstall
like you had suggested.
Mitra
>--Original Message--
>Hi Mitra. How've you been?
>This problem seems to be from a corrupted DLL in EM. You
might need to
>reinstall it. Good luck to you.
>Alex Ivascu
>
>"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
>news:046a01c398ee$e384b770$a401280a@.phx.gbl...
>> Hello Everyone,
>> I am having trouble with opening any of the tables in
any
>> of my databases in SQL Serve 7.
>> The error message i get is:
>> "Unexpectd Error happened during this operation.
>> [Query] - Query Designer encountered a querey error:
>> Unspecified error"
>> Please advise.
>> Thank you!
>> Mitra
>>
>
>.
>|||> I am having trouble with opening any of the tables in any
> of my databases in SQL Serve 7.
> The error message i get is:
> "Unexpectd Error happened during this operation.
> [Query] - Query Designer encountered a querey error:
> Unspecified error"
You might get more reliable results if you use Query Analyzer to view /
change data.

cant open sql database tables from access adp project

hi there,

we cant open most of the sql server tables from our Access
project any more, which has to be related to collation or
extended property problems.

trying to open any table will result in the information
"The stored procedure has been executed but did not return
any records" after the first try
and later on nothing will happen any more; tables wont
open.

basically this started to happen after recreating the sql
database due to resolution 1 in Microsoft Knowledge Base
Article - 318989:
collation of the server is Latin1_General_CI_AS
collation of the old database was
SQL_Latin1_General_CP1_CI_AS, which led to the error
described in the article.
collation of the new database is set to <Database
Standard>, now i have the problems described above.

anybody some good help on that?

ps: we cant really rebuild the user database all the
time, because we got flight data in about 4 billion
records in there, so we will need anything quick and
easy...
thanks

ronHello there,

Firstly do you have the latest Office service packs installed?

Do you have the latest version of MDAC?

Have you tried creating a new project and linking that to the SQL database?|||i got office xp sp 3 (=latest), mdac 2.8 (=latest), and i tried of course the recreation of a new access project: all failed

still more ideas...??

thanx
ron

Cant login to db after I do a db backup in SQL Express

I have a working dnn site. I've created several modules and corresponding tables in the database and everything works great. Then, I open SQL Server Management Studio Express, attach to the database, backup the database, detach from the database and I can no longer run my site (error shown below which says I cannot login to db). I can open the database from Visual Studio and see my tables, but I cannot run the application. Rebooting doesn't help. I've even tried copying over the database with a prior copy. It still doesn't work. The only way I have found to reconnect to the database is to copy over the entire dotnetnuke install directory with a copy I made prior to the backup. Then it works again.

Anyone else had this behavior? How do I fix it?

Thanks

------ DB ERROR BELOW ------------

DotNetNuke Upgrade Error

The Assembly Version ( [ASSEMBLYVERSION] ) does not match the Database Version ( [DATABASEVERSION] )

ERROR: Could not connect to database.

Cannot open user default database. Login failed. Login failed for user 'PARAPRO1\ASPNET'.

You should post over at the DNN forums onwww.dotnetnuke.com, this isn't an uncommon event. You should always back up your DNN database from DNN, not an outside product. When you did the attach/detach, you changed the database credentials so DNN can't log in. They can be fixed, and I think there was a script posted to do this at the DNN site, but at least you'll get the correct changes there.

Jeff

Cant Keep Primary Key Columns when moving db to SQL 2005 from 2000

there are some primary key columns which are autoincrement in some tables.
for exampl page_id, now we have deleted many pages in old server so there
are many gaps between the page_id
even the it doest start with 1 because during the testing many initial pages
were deleted.

but when we move it to the new server with sql2005 it gives them numbers from 1,2,3 and
so on.

I need to keep existing 2000 keys. How can I acomplish this?

it would have worked if it also updates this number in other tables which
refer to this column
for example page_id is autoincreement in tbl_category_pages ( this table
contains the page information)
and it also appears in tbl_page_content ( this table contains the page's
content)

because it changes this number at one place and not the other place it makes
the database incorrect.

i tried many things ( there is even an option to keep the values of such
columns but doesnt seem to work)

Thanks,

WessTo maintain existing relationships, simply select all tables and copy them to a notepad. From there you just copy and paste them in the query anlyzer.|||If you want tables with relationships and data, take a complete back up of the database. Use that backup file and restore in sql 2005.|||ramasp,

Thanks, I appreciate your help.

I have given this information to the developer I have working on this. He tells me he tried this and that it can not work because the two versions are different.

Please let me know if this should work and the proccess to do so.

Thanks,

Wess

can''t import views and store procedures

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong

awong@.virginiadare.com

Right click the database > Taks > Generate Scripts, this will give you the option to put it in one output-file

Jens K. Suessmeyer

http://www.sqlserver2005.de

Can't import Access tables..?

Hi all,
When I use the import Wizard in SQL-2005 I get the following message:
TITLE: SQL Server Import and Export Wizard
--
Error 0xc002f210: Preparation SQL Task: Executing the query "" failed
with the following error:
"Retrieving the COM class factory for component with CLSID
{7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following
error: 80040154.".
Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
--
BUTTONS:
OK
--
..it's just plain tables.
What can be wrong?
Kent J.I had this with Access tables once and decided to use Microsofts
"Access to SQL Migration tool", not sure if that was the exact name
but... its available for download.. its really cool and fast...
don't even bother trouble shooting this to get the wizard to work; I
spent a long time until I gave in to using the tool.
Erik

Can't import Access tables..?

Hi all,
When I use the import Wizard in SQL-2005 I get the following message:
TITLE: SQL Server Import and Export Wizard
Error 0xc002f210: Preparation SQL Task: Executing the query "" failed
with the following error:
"Retrieving the COM class factory for component with CLSID
{7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following
error: 80040154.".
Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
BUTTONS:
OK
...it's just plain tables.
What can be wrong?
Kent J.
I had this with Access tables once and decided to use Microsofts
"Access to SQL Migration tool", not sure if that was the exact name
but... its available for download.. its really cool and fast...
don't even bother trouble shooting this to get the wizard to work; I
spent a long time until I gave in to using the tool.
Erik