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

Can't see Designer view in Asp.net 2005

Hi All,

I am using VS2005 Trial version, In my website project i can't able to see Designer View of all .aspx files but i can able to view code part. Earlier days i can able to see Designer page but now i can't do why? This problem occurs even new website project also.

Can any one help me?

Thanks in advance

Regards

Nat Raja

This forum is dedicated to SQL/e, not to ASP.Net. Please post to relevant forum or news group instead:

http://forums.asp.net/

http://support.microsoft.com/newsgroups/default.aspx

Closing as off topic.

Can't see database in enterprise manager

When connecting to server using enterprise manager it
shows tree view but won't allow me to see datbases? I f I
stop and start the service it says something lik ean error
1056(an instance of service already running) can't find a
solution anywhere getting desperate. pleas ehelp A.S.A.P.Have you logged on using sa ? Is is the same kinda view when you open
Enterprise Manager from other clients that have connectivity to the server ?
What about from Query Analyser ? Can you connect and do you see all
databases ?
"johnboy" <anonymous@.discussions.microsoft.com> wrote in message
news:12c901c4a2e9$ba258fc0$7d02280a@.phx.gbl...
> When connecting to server using enterprise manager it
> shows tree view but won't allow me to see datbases? I f I
> stop and start the service it says something lik ean error
> 1056(an instance of service already running) can't find a
> solution anywhere getting desperate. pleas ehelp A.S.A.P.|||"johnboy" <anonymous@.discussions.microsoft.com> wrote in message
news:12c901c4a2e9$ba258fc0$7d02280a@.phx.gbl...
> When connecting to server using enterprise manager it
> shows tree view but won't allow me to see datbases? I f I
> stop and start the service it says something lik ean error
> 1056(an instance of service already running) can't find a
> solution anywhere getting desperate. pleas ehelp A.S.A.P.
Log in as SA user and check read permissions in sysdatabase table in master
database.
Dra

Can't see database in enterprise manager

When connecting to server using enterprise manager it
shows tree view but won't allow me to see datbases? I f I
stop and start the service it says something lik ean error
1056(an instance of service already running) can't find a
solution anywhere getting desperate. pleas ehelp A.S.A.P.
Have you logged on using sa ? Is is the same kinda view when you open
Enterprise Manager from other clients that have connectivity to the server ?
What about from Query Analyser ? Can you connect and do you see all
databases ?
"johnboy" <anonymous@.discussions.microsoft.com> wrote in message
news:12c901c4a2e9$ba258fc0$7d02280a@.phx.gbl...
> When connecting to server using enterprise manager it
> shows tree view but won't allow me to see datbases? I f I
> stop and start the service it says something lik ean error
> 1056(an instance of service already running) can't find a
> solution anywhere getting desperate. pleas ehelp A.S.A.P.
|||"johnboy" <anonymous@.discussions.microsoft.com> wrote in message
news:12c901c4a2e9$ba258fc0$7d02280a@.phx.gbl...
> When connecting to server using enterprise manager it
> shows tree view but won't allow me to see datbases? I f I
> stop and start the service it says something lik ean error
> 1056(an instance of service already running) can't find a
> solution anywhere getting desperate. pleas ehelp A.S.A.P.
Log in as SA user and check read permissions in sysdatabase table in master
database.
Dra

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQ
L
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh
=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdk
I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk
|||SELECT dbo.Hornshj$Item.No_ AS [No], dbo.Hornshj
$Item.Description, dbo.Hornshj$Item.[Unit Price], SUM(dbo.[Hornshj
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornshj$Item INNER JOIN
dbo.[Hornshj$Item Ledger Entry] ON dbo.Hornshj
$Item.No_ = dbo.[Hornshj$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornshj$Item.No_, dbo.Hornshj$Item.Description,
dbo.Hornshj$Item.[Unit Price]
|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)

Cant SAVE a view

HI,
Whats wring with the followng views? I can view the results if i run the
view without saving; but when try to save the view, I get error .
View1:
SELECT COUNT(*)
FROM dbo.Cingular_AllMin MINUS
SELECT MDN
FROM Testallmatches
VIEW2:
SELECT COUNT(*)
FROM dbo.Cingular_AllMin MINUS
SELECT mdn
FROM Testallmatches INNER JOIN
Cingular_AllMin ON
Cingular_AllMin.previousmin = Testallmatches.mdn OR
Cingular_AllMin.currentmin = Testallmatches.mdn
Error:
ODBC error: [Microdoft][odbc sql server driver][sql server] Incorrect syntax
near keyword 'SELECT'.
Please help.
Thanks
pmudMINUS is not valid T-SQL.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:29AC1DA5-6153-4F9E-A053-330DEFA9B583@.microsoft.com...
> HI,
> Whats wring with the followng views? I can view the results if i run the
> view without saving; but when try to save the view, I get error .
> View1:
> SELECT COUNT(*)
> FROM dbo.Cingular_AllMin MINUS
> SELECT MDN
> FROM Testallmatches
> VIEW2:
> SELECT COUNT(*)
> FROM dbo.Cingular_AllMin MINUS
> SELECT mdn
> FROM Testallmatches INNER JOIN
> Cingular_AllMin ON
> Cingular_AllMin.previousmin = Testallmatches.mdn OR
> Cingular_AllMin.currentmin = Testallmatches.mdn
> Error:
> ODBC error: [Microdoft][odbc sql server driver][sql server] Incorrect
> syntax
> near keyword 'SELECT'.
> Please help.
> Thanks
> --
> pmud|||Hi Aaron,
Is there a way I can get around this problem?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> MINUS is not valid T-SQL.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:29AC1DA5-6153-4F9E-A053-330DEFA9B583@.microsoft.com...
>
>|||Can you show table structure, sample data, and desired results, so we can
understand what "this problem" is?
http://www.aspfaq.com/5006
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:82899AA1-B1BC-4B46-8D7B-10D9583614EB@.microsoft.com...
> Hi Aaron,
> Is there a way I can get around this problem?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Cant PREVIEW SAMPLE REPORTS

Hi,
I tried to view the sample reports provided by microsoft, but I cant even
preview them...I get the error::
AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE CONNCTION TO
THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
DENIED...
But the wierd thing is that I CAN PREVIEW THE FOODMART SALES REPORT!... Why
is that?
Also, when I tried to create a subscription as in their walkthrough I get
the error:
"The dataset cannot be generated. An error occurred while connecting to a
data source, or the query is not valid for the data source.
(rsCannotPrepareQuery)
SQL Server does not exist or access denied. A valid query must be
specified. "
But the query is simply Select * from table...
Any idea why these things are happening? Also, the reports are already
successfully deployed to the server...
Plz help...Thanks
--
pmudAs part of setup did you choose to install the AdventureWorks2000 database?
If so did you make sure the AdventureWorks shared datasource was pointed to
wherever that database was installed?
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
> Hi,
> I tried to view the sample reports provided by microsoft, but I cant even
> preview them...I get the error::
> AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE CONNCTION TO
> THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
> DENIED...
> But the wierd thing is that I CAN PREVIEW THE FOODMART SALES REPORT!...
> Why
> is that?
> Also, when I tried to create a subscription as in their walkthrough I get
> the error:
> "The dataset cannot be generated. An error occurred while connecting to a
> data source, or the query is not valid for the data source.
> (rsCannotPrepareQuery)
> SQL Server does not exist or access denied. A valid query must be
> specified. "
> But the query is simply Select * from table...
> Any idea why these things are happening? Also, the reports are already
> successfully deployed to the server...
> Plz help...Thanks
> --
> pmud|||Hi Douglas,
the adventure works database is installed bcoz the forrdmart sales report is
using it & i can preview that sample report... But how to make sure that
adventuer works databse was pointed to '
Thanks
"Douglas McDowell" wrote:
> As part of setup did you choose to install the AdventureWorks2000 database?
> If so did you make sure the AdventureWorks shared datasource was pointed to
> wherever that database was installed?
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
> > Hi,
> >
> > I tried to view the sample reports provided by microsoft, but I cant even
> > preview them...I get the error::
> >
> > AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE CONNCTION TO
> > THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
> > DENIED...
> >
> > But the wierd thing is that I CAN PREVIEW THE FOODMART SALES REPORT!...
> > Why
> > is that?
> >
> > Also, when I tried to create a subscription as in their walkthrough I get
> > the error:
> >
> > "The dataset cannot be generated. An error occurred while connecting to a
> > data source, or the query is not valid for the data source.
> > (rsCannotPrepareQuery)
> > SQL Server does not exist or access denied. A valid query must be
> > specified. "
> >
> > But the query is simply Select * from table...
> >
> > Any idea why these things are happening? Also, the reports are already
> > successfully deployed to the server...
> >
> > Plz help...Thanks
> > --
> > pmud
>
>|||Foodmart Sales is the only report that does not use AdventureWorks2000, it
uses FoodMart, the default Analysis Services OLAP database. Open Enterprise
manager on the SQL Server instance that you are pointing your shared
datasource to and see if it has an AdventureWorks2000 database.
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:4B67C061-122A-42F6-BF3F-58DBC3539326@.microsoft.com...
> Hi Douglas,
> the adventure works database is installed bcoz the forrdmart sales report
> is
> using it & i can preview that sample report... But how to make sure that
> adventuer works databse was pointed to '
> Thanks
> "Douglas McDowell" wrote:
>> As part of setup did you choose to install the AdventureWorks2000
>> database?
>> If so did you make sure the AdventureWorks shared datasource was pointed
>> to
>> wherever that database was installed?
>> --
>> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>>
>> "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
>> > Hi,
>> >
>> > I tried to view the sample reports provided by microsoft, but I cant
>> > even
>> > preview them...I get the error::
>> >
>> > AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE CONNCTION
>> > TO
>> > THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
>> > DENIED...
>> >
>> > But the wierd thing is that I CAN PREVIEW THE FOODMART SALES
>> > REPORT!...
>> > Why
>> > is that?
>> >
>> > Also, when I tried to create a subscription as in their walkthrough I
>> > get
>> > the error:
>> >
>> > "The dataset cannot be generated. An error occurred while connecting to
>> > a
>> > data source, or the query is not valid for the data source.
>> > (rsCannotPrepareQuery)
>> > SQL Server does not exist or access denied. A valid query must be
>> > specified. "
>> >
>> > But the query is simply Select * from table...
>> >
>> > Any idea why these things are happening? Also, the reports are already
>> > successfully deployed to the server...
>> >
>> > Plz help...Thanks
>> > --
>> > pmud
>>|||Hi Douglas,
I checked the enterprise manager, it has the Adventure works database...
Also, I faced a similar problem just now.. I think it has smthg to do with
analysis manager & enterprise manager... I bought the book by
Microsoft..."SQL service sreporting services 2000 Step by Step" & installed
the samples from CD... It had a sample database called rs2000DW which got
restored to the ANALYSIS MANAGER...
Now I tried to create a report using this data source.... But i get the
error :: Connection open...SQL server doesnt exist or access denied... In
the server name i had typed localhost....I even tried typing the IP address
there & the Computer name... But it gives the same error msg...
Can it be bcoz In my enterprise manager... the SQL server instance name is
DEVELOPMENT\DEV & in the ANALYSIS MANAGER its called DEVELOPMENT '
It doesnt see the data source from ANALYSIS Mgr . but sees from enterprise
manager when i use the server name as DEVELPOMENT\DEV..'
Whats wrong? Plz help...
Thanks
"Douglas McDowell" wrote:
> Foodmart Sales is the only report that does not use AdventureWorks2000, it
> uses FoodMart, the default Analysis Services OLAP database. Open Enterprise
> manager on the SQL Server instance that you are pointing your shared
> datasource to and see if it has an AdventureWorks2000 database.
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:4B67C061-122A-42F6-BF3F-58DBC3539326@.microsoft.com...
> > Hi Douglas,
> >
> > the adventure works database is installed bcoz the forrdmart sales report
> > is
> > using it & i can preview that sample report... But how to make sure that
> > adventuer works databse was pointed to '
> >
> > Thanks
> >
> > "Douglas McDowell" wrote:
> >
> >> As part of setup did you choose to install the AdventureWorks2000
> >> database?
> >> If so did you make sure the AdventureWorks shared datasource was pointed
> >> to
> >> wherever that database was installed?
> >> --
> >> Douglas McDowell douglas@.nospam.solidqualitylearning.com
> >>
> >>
> >> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> >> news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
> >> > Hi,
> >> >
> >> > I tried to view the sample reports provided by microsoft, but I cant
> >> > even
> >> > preview them...I get the error::
> >> >
> >> > AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE CONNCTION
> >> > TO
> >> > THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
> >> > DENIED...
> >> >
> >> > But the wierd thing is that I CAN PREVIEW THE FOODMART SALES
> >> > REPORT!...
> >> > Why
> >> > is that?
> >> >
> >> > Also, when I tried to create a subscription as in their walkthrough I
> >> > get
> >> > the error:
> >> >
> >> > "The dataset cannot be generated. An error occurred while connecting to
> >> > a
> >> > data source, or the query is not valid for the data source.
> >> > (rsCannotPrepareQuery)
> >> > SQL Server does not exist or access denied. A valid query must be
> >> > specified. "
> >> >
> >> > But the query is simply Select * from table...
> >> >
> >> > Any idea why these things are happening? Also, the reports are already
> >> > successfully deployed to the server...
> >> >
> >> > Plz help...Thanks
> >> > --
> >> > pmud
> >>
> >>
> >>
>
>|||Looks like you have a named instance of SQL Server, so in your SQL Server
connection string for your datasource you need to specify DEVELOPMENT\DEV
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:7E49FB2F-C9D3-49F8-A3CD-FA4CF586C2DE@.microsoft.com...
> Hi Douglas,
> I checked the enterprise manager, it has the Adventure works database...
> Also, I faced a similar problem just now.. I think it has smthg to do with
> analysis manager & enterprise manager... I bought the book by
> Microsoft..."SQL service sreporting services 2000 Step by Step" &
> installed
> the samples from CD... It had a sample database called rs2000DW which got
> restored to the ANALYSIS MANAGER...
> Now I tried to create a report using this data source.... But i get the
> error :: Connection open...SQL server doesnt exist or access denied... In
> the server name i had typed localhost....I even tried typing the IP
> address
> there & the Computer name... But it gives the same error msg...
> Can it be bcoz In my enterprise manager... the SQL server instance name is
> DEVELOPMENT\DEV & in the ANALYSIS MANAGER its called DEVELOPMENT '
> It doesnt see the data source from ANALYSIS Mgr . but sees from enterprise
> manager when i use the server name as DEVELPOMENT\DEV..'
> Whats wrong? Plz help...
> Thanks
> "Douglas McDowell" wrote:
>> Foodmart Sales is the only report that does not use AdventureWorks2000,
>> it
>> uses FoodMart, the default Analysis Services OLAP database. Open
>> Enterprise
>> manager on the SQL Server instance that you are pointing your shared
>> datasource to and see if it has an AdventureWorks2000 database.
>> --
>> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>>
>> "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> news:4B67C061-122A-42F6-BF3F-58DBC3539326@.microsoft.com...
>> > Hi Douglas,
>> >
>> > the adventure works database is installed bcoz the forrdmart sales
>> > report
>> > is
>> > using it & i can preview that sample report... But how to make sure
>> > that
>> > adventuer works databse was pointed to '
>> >
>> > Thanks
>> >
>> > "Douglas McDowell" wrote:
>> >
>> >> As part of setup did you choose to install the AdventureWorks2000
>> >> database?
>> >> If so did you make sure the AdventureWorks shared datasource was
>> >> pointed
>> >> to
>> >> wherever that database was installed?
>> >> --
>> >> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>> >>
>> >>
>> >> "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> >> news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I tried to view the sample reports provided by microsoft, but I cant
>> >> > even
>> >> > preview them...I get the error::
>> >> >
>> >> > AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE
>> >> > CONNCTION
>> >> > TO
>> >> > THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
>> >> > DENIED...
>> >> >
>> >> > But the wierd thing is that I CAN PREVIEW THE FOODMART SALES
>> >> > REPORT!...
>> >> > Why
>> >> > is that?
>> >> >
>> >> > Also, when I tried to create a subscription as in their walkthrough
>> >> > I
>> >> > get
>> >> > the error:
>> >> >
>> >> > "The dataset cannot be generated. An error occurred while connecting
>> >> > to
>> >> > a
>> >> > data source, or the query is not valid for the data source.
>> >> > (rsCannotPrepareQuery)
>> >> > SQL Server does not exist or access denied. A valid query must be
>> >> > specified. "
>> >> >
>> >> > But the query is simply Select * from table...
>> >> >
>> >> > Any idea why these things are happening? Also, the reports are
>> >> > already
>> >> > successfully deployed to the server...
>> >> >
>> >> > Plz help...Thanks
>> >> > --
>> >> > pmud
>> >>
>> >>
>> >>
>>|||Thanks Douglas , that solves one of my problems. I can preview th sample
reports now... Thanks... I guess my other doubts should be aske din the next
question...
Thanks again...
"Douglas McDowell" wrote:
> Looks like you have a named instance of SQL Server, so in your SQL Server
> connection string for your datasource you need to specify DEVELOPMENT\DEV
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:7E49FB2F-C9D3-49F8-A3CD-FA4CF586C2DE@.microsoft.com...
> > Hi Douglas,
> >
> > I checked the enterprise manager, it has the Adventure works database...
> >
> > Also, I faced a similar problem just now.. I think it has smthg to do with
> > analysis manager & enterprise manager... I bought the book by
> > Microsoft..."SQL service sreporting services 2000 Step by Step" &
> > installed
> > the samples from CD... It had a sample database called rs2000DW which got
> > restored to the ANALYSIS MANAGER...
> >
> > Now I tried to create a report using this data source.... But i get the
> > error :: Connection open...SQL server doesnt exist or access denied... In
> > the server name i had typed localhost....I even tried typing the IP
> > address
> > there & the Computer name... But it gives the same error msg...
> >
> > Can it be bcoz In my enterprise manager... the SQL server instance name is
> > DEVELOPMENT\DEV & in the ANALYSIS MANAGER its called DEVELOPMENT '
> >
> > It doesnt see the data source from ANALYSIS Mgr . but sees from enterprise
> > manager when i use the server name as DEVELPOMENT\DEV..'
> >
> > Whats wrong? Plz help...
> >
> > Thanks
> > "Douglas McDowell" wrote:
> >
> >> Foodmart Sales is the only report that does not use AdventureWorks2000,
> >> it
> >> uses FoodMart, the default Analysis Services OLAP database. Open
> >> Enterprise
> >> manager on the SQL Server instance that you are pointing your shared
> >> datasource to and see if it has an AdventureWorks2000 database.
> >> --
> >> Douglas McDowell douglas@.nospam.solidqualitylearning.com
> >>
> >>
> >> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> >> news:4B67C061-122A-42F6-BF3F-58DBC3539326@.microsoft.com...
> >> > Hi Douglas,
> >> >
> >> > the adventure works database is installed bcoz the forrdmart sales
> >> > report
> >> > is
> >> > using it & i can preview that sample report... But how to make sure
> >> > that
> >> > adventuer works databse was pointed to '
> >> >
> >> > Thanks
> >> >
> >> > "Douglas McDowell" wrote:
> >> >
> >> >> As part of setup did you choose to install the AdventureWorks2000
> >> >> database?
> >> >> If so did you make sure the AdventureWorks shared datasource was
> >> >> pointed
> >> >> to
> >> >> wherever that database was installed?
> >> >> --
> >> >> Douglas McDowell douglas@.nospam.solidqualitylearning.com
> >> >>
> >> >>
> >> >> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> >> >> news:0EA50AAF-11C8-4CAE-AD1C-E3E03522115F@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I tried to view the sample reports provided by microsoft, but I cant
> >> >> > even
> >> >> > preview them...I get the error::
> >> >> >
> >> >> > AN ERROR HAS OCCURED DURING REPORT PROCESSING. CANNOY CREATE
> >> >> > CONNCTION
> >> >> > TO
> >> >> > THE DATA SOURCE 'ADVENTURE WORKS'. SQL SERVER DOESNT EXIST or ACCESS
> >> >> > DENIED...
> >> >> >
> >> >> > But the wierd thing is that I CAN PREVIEW THE FOODMART SALES
> >> >> > REPORT!...
> >> >> > Why
> >> >> > is that?
> >> >> >
> >> >> > Also, when I tried to create a subscription as in their walkthrough
> >> >> > I
> >> >> > get
> >> >> > the error:
> >> >> >
> >> >> > "The dataset cannot be generated. An error occurred while connecting
> >> >> > to
> >> >> > a
> >> >> > data source, or the query is not valid for the data source.
> >> >> > (rsCannotPrepareQuery)
> >> >> > SQL Server does not exist or access denied. A valid query must be
> >> >> > specified. "
> >> >> >
> >> >> > But the query is simply Select * from table...
> >> >> >
> >> >> > Any idea why these things are happening? Also, the reports are
> >> >> > already
> >> >> > successfully deployed to the server...
> >> >> >
> >> >> > Plz help...Thanks
> >> >> > --
> >> >> > pmud
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Can't open reports with Intranet user

Hi,
I made some reports and I deployed them on my local report server. I want
someone in the Intranet to be able to view them. I did set up the role to
make the intranet group to be able to browse my reports. They can view the
list of the report when they access it from http://server/ReportServer But
they just can't view it when they click on a report. It says they don't have
permissions to open ReportViewer.aspx.
I wonder why it is the problem.Ok I found the problem. The file ReportViewer.aspx didn't gave the
authorization to my Intranet group.

Can't make relation when Defining a Data Source View

I want to set the relation between two table when I defining a data source
view.
The type primary key is int and the forgien key is int32, then the relation
can't set.
They are all int type but just with different length.
How can I do?On Nov 24, 11:01 pm, "ad" <fly...@.wfes.tcc.edu.tw> wrote:
> I want to set the relation between two table when I defining a data source
> view.
> The type primary key is int and the forgien key is int32, then the relation
> can't set.
> They are all int type but just with different length.
> How can I do?
I'm not sure if I am following you correctly; however, in terms of
referential integrity (i.e., primary key to foreign key relationships)
the data types have to match quite closely. You could try casting one
of the fields to the other to make them match, of course, this may not
resolve your issue. Otherwise, you might want to change the datatype
of one of the fields to the other and use a constraint, etc to enforce
the original integer length. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

can't make a view

When I attempt to make aview I get the error:
Microsoft Access can't start the client server visual design tools. This
feature is not availabel. To make this feature available please run setup
again, select "Add or Remove Features"....
When I run the msde setup again I don't see any option to Add or remove
features...
What do I need to do?
Thanks.
hi,
SAC wrote:
> When I attempt to make aview I get the error:
> Microsoft Access can't start the client server visual design tools.
> This feature is not availabel. To make this feature available please
> run setup again, select "Add or Remove Features"....
> When I run the msde setup again I don't see any option to Add or
> remove features...
> What do I need to do?
I'm not an Access guy, but I do think this is related to Access itself and
not MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks. I'll check it out.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:44ef3tF1lvu6U1@.individual.net...
> hi,
> SAC wrote:
> I'm not an Access guy, but I do think this is related to Access itself and
> not MSDE..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||ACTUALLY. This helped a GREAT DEAL. It does say Access, doesn't it!
Thanks again!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:44ef3tF1lvu6U1@.individual.net...
> hi,
> SAC wrote:
> I'm not an Access guy, but I do think this is related to Access itself and
> not MSDE..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
--
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>

Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>