Invalide object name 'INSERTED'

In an after insert/update trigger I have the following:
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
..
END
It compiles but when I run it, I get a message: "Invalide object name
'INSERTED'"
But if I take the SET out of the IF like this, it works fine:
SET @.iCustID = (SELECT CustID FROM INSERTED)
IF UPDATE(CustName)
BEGIN
..
END
Can someone explain why?
Thanks,
KeithSorry. My mistake. It doesn't work either way. What does work is if I do
this (I mean it runs without errors):
SELECT CustID FROM INSERTED
IF UPDATE(CustName)
BEGIN
..
END
I need to get CustID into a variable so that I can pass it to a stored
procedure as follows:
Keith
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
EXEC @.bSomeVar = spTest @.iCustID
..
END|||Strange, never had that. I would have suggested that the problem was case
sensitivity (BOL lists the table as 'inserted', not 'INSERTED') but you say
it works when you move the SET out of the IF block
Even if this worked, you would have a problem anyway if more than 1 row was
updated in one go, because you'd be trying to set a numbers of rows to a
scalar variable.
Dan
Keith wrote on Wed, 26 Apr 2006 11:33:59 -0400:

> In an after insert/update trigger I have the following:
> IF UPDATE(CustName)
> BEGIN
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> ...
> END
> It compiles but when I run it, I get a message: "Invalide object name
> 'INSERTED'"
> But if I take the SET out of the IF like this, it works fine:
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> IF UPDATE(CustName)
> BEGIN
> ...
> END
> Can someone explain why?
> Thanks,
> Keith
>|||Geeze. Never mind. Not enough sleep last night. I moved some code from the
trigger to a stored procedure and didnt' change "INSERTED" to the actual
table name in the stored procedure. The error was there, not in the trigger.
Keithsql

Invalid Objects Error

Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.Please post the DDL for the VIEW and the underlying TABLEs.

Hugh Scott

Originally posted by James Aiello
Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.|||Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello|||I have to confess that diagnosing SQL code is not my strong suit. Some may wonder if I even have a strong suit ;-). It's a fair question.

In looking at your code, I am troubled by the white space between various concatenations. There's nothing wrong with it per se, but I just wonder if one of them is slightly off.

The other thing that came to my mind was the possibility that one or more records in the view might exceed 8000 (?) bytes -- the max allowed by SQL (the precise number is in BOL, and I don't have it in front of me). Looking at the underlying tables, that did not appear to be a problem, since the address fields appeared to be nicely limited to nvarchar(40). Still, if some of the white space were improperly delimited and it were combined with some extra-long addresses, I suppose it might conceivably result in a record with too many characters. Not sure how to check for and identify the problem record.

Some additional to do's for you:

1. Right click on the tables in EM and find Generate SQL Scripts (it's at the bottom of the pop-up, I think). Generate theSQL scripts and save them to a file. Then, post those scripts to the forum. That way others who are much smarter than me can help you work on the problem.

2. Populate a new database with the views and the underlying tables (from the DDLs you just created). Do your DTS Wizard while there is no data in the database (if it succeeds, then I might possibly be on to something). If it fails, then you haven't wasted much time and you can focus in on the syntax of the view.

3. If the DTS Wizard does work, then start populating the tables with data from your production machine. Take it in increments and see if you can re-create the error.

I hope this helps.

Hugh Scott

Originally posted by James Aiello
Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello

Invalid Object Name?

Hi

I am developing a windows application that connects to a sql 2000 server. I have created a stored procedure and I am trying to execute the stored procedure in the query analyzer.

Here is the code for the stored procedure

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

Select count([@.dlname].[Auditors working code])
from [@.dlname]
where [Auditors working code] = @.CB
GO

Here is the code that I am attempting to use in the query analyzer

Declare @.dlname as nvarchar(25)
set @.dlname = 'SWMC-OP-02-2005'
Declare @.CB as nvarchar(10)
set @.CB = 'CB'
Execute CountofComebacks @.dlname, @.CB

I get the following error in the query analyzer

Server: Msg 208, Level 16, State 1, Procedure CountofComebacks, Line 9
Invalid object name'@.dlname'.

Any assistance would be greatly appreciated.

thanks

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)
GO

If you want to know more about how this works google for Dynamic SQL. There are disadvantages in using this approach too. there are plenty of articles over the net that xplain about "Dynamic SQL".

|||

First let me say thank you for your response.

That being said I am still getting an error when attempting to execute the sp in the query analyzer.

Here is the code in the query analyzer

Declare @.dlname as nvarchar
Set @.dlname = 'SWMCOP022005'
Declare @.CB as nvarchar
Set @.CB = 'CB'
Declare @.NumberofCBs as integer
Execute CountofComebacks @.dlname, @.CB, @.NumberofCBs

Here is the sp

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10),
@.NumberofCBs as int
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)

return @.NumberofCBs
GO

and here is the error I receive in the query analyzer

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
The 'CountofComebacks' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Is this because of the syntax error on line 2?

thanks

|||

throw in an ISNULL function.

SET @.sql = 'Select ISNULL(count([' + @.dlname + '].[Auditors working code]),0) from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

sql

Invalid object name...

Hello all,
however, this is my first question to this news. I am working with RS SP1,
and have question. I have example procedure:
CREATE PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
CREATE TABLE #table (ID SQL_VARIANT)
INSERT INTO #table(ID) VALUES (@.ord)
SELECT * FROM #table
DROP TABLE #table
GO
When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID) I
get following error:
Could not generate a list of fields for the querry...
Invalid object name #table
Ofcourse, this procedure works good in Query Analyser. Anyone has idea, why
this is not working ?
--
Ing. Branislav GerzoI got it to work w/o a problem. However, I do see the same error if I enter
the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation dialog
box while attempting to define the dataset it uses. Try actually executing
the procedure with a parameter or passing in a static value from the Generic
Query Designer data window once you've defined the dataset. If you use
static value like (EXECUTE GEGE_test_a '11'), simply change it after to use
your query parm.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
> I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
> why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Use a table ariable instead of the temp table:
ALTER PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
DECLARE @.table TABLE(ID SQL_VARIANT)
INSERT INTO @.table(ID) VALUES (@.ord)
SELECT * FROM @.table
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Dejan Sarka [DS], on Friday, October 29, 2004 at 17:17 (+0200)
contributed this to our collective wisdom:
DS> Use a table ariable instead of the temp table:
DS> ALTER PROCEDURE GEGE_test_a
DS> @.ord SQL_VARIANT AS
DS> SET NOCOUNT ON
DS> DECLARE @.table TABLE(ID SQL_VARIANT)
DS> INSERT INTO @.table(ID) VALUES (@.ord)
DS> SELECT * FROM @.table
DS> GO
thanks, I was afraid that someone will answer like this. Ofcourse,
this works, but my problem is, that in my situation I have to fill
@.table_var with result of another procedure. And I found this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
A3:
1. Tables variables cannot be used in a INSERT EXEC or SELECT INTO
statement.
2. You cannot use the EXEC statement or the sp_executesql stored
procedure to run a dynamic SQL Server query that refers a table
variable, if the table variable was created outside the EXEC statement
or the sp_executesql stored procedure. Because table variables can be
referenced in their local scope only, an EXEC statement and a
sp_executesql stored procedure would be outside the scope of the table
variable. However, you can create the table variable and perform all
processing inside the EXEC statement or the sp_executesql stored
procedure because then the table variables local scope is in the EXEC
statement or the sp_executesql stored procedure.
Ofcourse, i'd like to use table variables, they are fast, they are
cool. But, how to fill them with result of another procedure ?
I can't cheat them in any way, I have only one idea for that -
procedure which fill @.tabl_var using cursors. But I hope there is
better way do this.
Dejan, please help.
--
...m8s, cu l8r, Brano.
[Alright, who g r e a s e d the tagline?.]|||John H. Miller [JHM], on Friday, October 29, 2004 at 11:14 (-0400)
typed the following:
JHM> I got it to work w/o a problem. However, I do see the same error if I
enter
JHM> the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation
dialog
JHM> box while attempting to define the dataset it uses.
anyone knows, why this error occurs ? I can't use temp tables in my
procedures ?
JHM> Try actually executing
JHM> the procedure with a parameter or passing in a static value from the
Generic
JHM> Query Designer data window once you've defined the dataset. If you use
JHM> static value like (EXECUTE GEGE_test_a '11'), simply change it after to
use
JHM> your query parm.
No, it also doesn't work, I get the same message back. (could not
generate a list...). I really don't know why, it is known bug, or
what?
Thanks a lot. My all work stops on this :(((
--
...m8s, cu l8r, Brano.
[Applaflammaphobia: A vacation fear that the house will bu]

invalid object name..(my error message)

i'm working on an application using vs 2005, sql server2000, with c# asp.net

i can access many tables in my db that the dbo is the dbowner for them, but when i access few tables that the owner for them is dswebwork, i recieved an error says, invalid object name tbluser...which tbluser is table name...this is the error message in details....

Invalid object name 'tblUsers'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'tblUsers'.

Source Error:

Line 57: string passWord = txtPassword.Text;Line 58:Line 59: Users users = new Users(Constants.DB_CONNECTION,Line 60: userName, passWord);Line 61:

Source File:e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs Line:59

Stack Trace:

[SqlException (0x80131904): Invalid object name 'tblUsers'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52 System.Data.SqlClient.SqlDataReader.get_MetaData() +130 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +307 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +151 WebWorksBO.DBElements.BaseDataSQLClient.FillDataset(DataSet dsToFill) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\BaseDataSQLClient.cs:97 WebWorksBO.DBElements.dbUsers..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\dbUsers.cs:38 WebWorksBO.AppElements.Users..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\AppElements\Users.cs:370 LoginMaster.LoginUser() in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:59 LoginMaster.imgbtnOK_Click(Object sender, ImageClickEventArgs e) in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:46 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +102 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +141 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

so..i hope to help me...i need to deploy this project soon...

Append the owner to the table name, when you use it or change the owner to dbo

Select * from dswebwork.tblUsers

Invalid object name!

Hi,
I'm trying to create a new table by merging two files together. They both
have exactly the same table structure. I.e. they are both got 1 field called
ref varchar(255).
The code I'm using is:
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
The error message I'm getting is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'U_T_XmasOnly'.
Can you please tell me what I'm doing wrong?
Thanks in advance
RobRobert
I think you missed INTO within the INSERT statement
It shoul be
INSERT INTO U_T_XmasOnly(REF)
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> Hi,
> I'm trying to create a new table by merging two files together. They both
> have exactly the same table structure. I.e. they are both got 1 field
called
> ref varchar(255).
> The code I'm using is:
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> The error message I'm getting is:
>
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'U_T_XmasOnly'.
> Can you please tell me what I'm doing wrong?
> Thanks in advance
> Rob|||The INTO is optional, so the INSERT statement is fine, as long as the table
already exists. Your object 'U_T_XmasOnly' musn't exist.
Looking at your statement "I'm trying to create a table" I think what you
want to do is this:
SELECT ref
INTO U_T_XmasOnly
FROM
(
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
) x
But what you should really do is create your table first, then use the
INSERT syntax above:
eg
CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
Basically the syntax for adding records to an _existing_ table is the
INSERT, creating a table on they fly and adding records at the same time is
the SELECT INTO, and really you should create the table first, then add the
records, using the CREATE TABLE and INSERT.
Let me know how get on.
Damien
"Uri Dimant" wrote:

> Robert
> I think you missed INTO within the INSERT statement
> It shoul be
> INSERT INTO U_T_XmasOnly(REF)
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> called
>
>|||Hehehe, right , I have never thought about it.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:45A4A9EE-A080-45AC-85E7-E3DC24EB2BD6@.microsoft.com...
> The INTO is optional, so the INSERT statement is fine, as long as the
table
> already exists. Your object 'U_T_XmasOnly' musn't exist.
> Looking at your statement "I'm trying to create a table" I think what you
> want to do is this:
> SELECT ref
> INTO U_T_XmasOnly
> FROM
> (
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> ) x
> But what you should really do is create your table first, then use the
> INSERT syntax above:
> eg
> CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> Basically the syntax for adding records to an _existing_ table is the
> INSERT, creating a table on they fly and adding records at the same time
is
> the SELECT INTO, and really you should create the table first, then add
the
> records, using the CREATE TABLE and INSERT.
> Let me know how get on.
>
> Damien
> "Uri Dimant" wrote:
>
both

Invalid object name while reading data out of an SQL Database

Hi all,
I'm a complete newbie on ASP.Net.

I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.

When I try to open the site, I always get the same error:
Invalid object name 'Members'

I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.

Here is the Code of the page so far:

<%@. Page Language="VB" Debug="True" Strict="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQlClient" %>
<script runat="server">
Sub Page_Load (ByVal Sender As Object, _
 ByVal E As EventArgs)
 Dim connStr As String
 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
 connStr += "database=tempdb;"
 connStr += "Truster_Connection=yes"
 Dim conn As New SQLConnection(connStr)
 conn.Open()
 Dim sql As String
 sql = "SELECT COUNT (*) FROM Members"
 Dim cmd As New SQLCommand(sql, conn)
 Dim ergebnis As String
 ergebnis = cmd.ExecuteScalar().toString()
 Dim t As String
 t = "Die Tabelle Members hat " & _
 ergebnis & " Zeilen. <br>" & _
 "Das Kommando lautet: " & _
 cmd.CommandText & "<br>" & _
 "Der Kommandotyp ist: " & _
 cmd.CommandType
 ausgabe.innerHTML = t
End Sub
</script>
<html><head><title>
Demo zu SQLCommand.ExecuteScalar
</title></head>
<body>
<h3>Demo zu SQLCommand.ExecuteScalar</h3>
<p runat="server" id="ausgabe" />
</body></html>

Thanks for your help an sorry for my english.

Greets
Flash_Prince

The problem is objects in tempdb is valid for limited time so your object the Member table does not exist. Microsoft provided the tempdb for SQL Server to be used to proccess complex queries and test queries so when you create a table if you did not drop it SQL Server will drop it at some point. Try the links below to download sample databases you can create and use with SQL Server 2005. The databases comes with tables but you can create your own tables so you can add the member table to any of the three database. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

|||

The problem is, that i want to use the database without entering the path of the database file in the sourcecode. When I use the nordwind database, all the sql queries are working very well. But if I use the Nordwind Database, I only know to connect by OleDB.

Can you tell me how to use a database in the way decribed above with a sql connection?

I hope, this is described understandable, cause my english is not the best.

|||

You need to create connection string and most of the information you need is covered in the two links below. Hope this helps.

http://www.connectionstrings.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||The problem is in your connection string. You have a typo: "Truster_Connection=yes" should be "Trusted_Connection=yes".

Invalid Object Name when trying to reference a database

Hi There

Let me start by explaining what I am trying to achieve as there may be a better way to perform this. I have two databases and I want to refer to four tables - one in one database and three in the other in a sql select statement.

When I use the query builder in VWD I can only see tables for the database that is defined in the connection string - so therefore how do I add tables from another database? After reading a few posts I realized that all I have to do is use the syntax: database.owner.table (or [database].[owner].[table]). Unfortunately this didn't work and came up with the error "Invalid Object Name".

Assuming that I must have got the owner incorrect I used the Query Tool in SQL Server Express Edition and it worked perfectly! So my sql must be correct, but why will it not work inside VWD?

The two databases are namedassessmentandaspnetdb - the assessment database contains the tablereviews and the aspnetdb database contains the tablesTutorDetails,EmployerDetails andStudentDetails.

Here is the connection string and sql:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Assessment.mdf;Integrated Security=True;User Instance=True

SELECT Reviews.Programme, aspnetdb.dbo.TutorDetails.Tutor_DisplayNameAS [Tutor Name],

aspnetdb.dbo.EmployerDetails.DisplayNameAS [Employer Name], aspnetdb.dbo.StudentDetails.Student_DisplayNameAS [Student Name]

FROM ReviewsINNERJOIN

aspnetdb.dbo.StudentDetailsON Reviews.StudentID= aspnetdb.dbo.StudentDetails.UsernameINNERJOIN

aspnetdb.dbo.EmployerDetailsON Reviews.EmployerID= aspnetdb.dbo.EmployerDetails.UserNameINNERJOIN

aspnetdb.dbo.TutorDetailsON Reviews.TutorID= aspnetdb.dbo.TutorDetails.UserName

WHERE(Reviews.ReviewID= 15)

Disregard the value of '15' in the WHERE statement as this was just put in to ensure I got some valid data back, this will be replaced with a variable eventually.

I know the sql statement works properly, I think the problem may be something to do with either the connection string - any ideas would be much appreciated.

Regards

Stuart

If this is Sql Server 2000 I'm pretty sure you need to add a link to the other servers and you may have to do so with 2005 too. Have a look atsp_addlinkedserver.|||

Hi Brian

Thank you for your response. I am using SQL Server Express, but the two databases reside on the same server. I am struggling to find anymore information on this point which surprises me as I cannot be the only person who wants to get data from more than one database!

sp_addlinkedserver seems to be used for adding additional servers into the query but not actually for adding additional databases. It seems that the connection string I used may be limiting me to only using that database - in my case "assessment", but surely if that is the case then how would someone reference other databases on the same server?

Regards

Stuart

|||Your connection string seems to reference the file instead of using the actual sql server. Try changing it to:-

Data Source=.\SQLEXPRESS;Initial Catalog=Assessment;Integrated Security=True;User Instance=True

and make sure Assessment is registered in sql server.|||

Brian

Thank you once again for your reply. Unfortunately this method isn't working - it throws up an error about connecting. The connection string that I originally had is the default one create by VWD and seems to be the one quoted in many online articles, but I cannot believe that it restricts acess to just the database it quotes in the connection.

The error was Error 26: Error locating server/instance. It mentions about checking whether SQL Server accepts remote connections, I have checked this and it does.

Any more thoughts?

Regards

Stuart

|||You need to make sure the db appears in Management studio by right-clicking databases and selecting attach. I suspect you added this db in VWD but this uses a file method not disimilar to using an access db. It's allows for xcopy of 2005 dbs but I suspect that if you want to reference other dbs then they all need to be in the sql server environment.
Also you may need to change your connection string to reference the server i.e. machinename\sqlexpress. I have successfully tested this on my machine so I think it may be the problem. If you haven't got Management Studio Express it's the new enterprise manager.Download it here.|||

Brian

Thank you once again for your comments. I have been thinking for a while that it must be related to the fact that sql server express when attached using "attachdb=" appears as a server with just one database when you connect to it using the standard connection string generated in VWD. As such it doesn't matter how many times you try and refer to other databases it will claim they do not exist.

I already had the Management Studio Express and used that to attach the databases, however it threw up all sorts of issues with the application when it came to executing it. Typically one part of the application which used the original connection string would get hold of the database and prevent the others from accessing it and so on...

Anyway, cut a long story short. I wiped the original connection strings and replaced them with connection strings that would be used to connect to SQL Server running on a remote machine - removing the "attachdb=" element that causes so many problems. I also had to change the provider setup in web.config otherwise my membership wouldn't work.

But it now works! I cannot believe other people haven't come across this before? In the future I am going to setup the connection strings in this way from the beginning as it seems to provides a lot more flexibility.

Once again, thank you for your support.

Regards

Stuart

|||Glad you got it sorted. I suppose it's early days with Sql Server and I'm sure people will happen accross this problem in the future.sql

Invalid object name when trying to access sql server through vb ap

Hi,
We're in the process of migrating our live server (Windows NT Server 4.0 sql
6.5) to windows 2003 and sql server 2000.
I've setup a test server running windows 2000 and sql server 6.5, i've
copied the database across to the dev server and fixed the orphaned logins
and made sure the database is consistent which has come out fine.
when we try to acess the database with our vb app we get the error invalid
object name dbo.table name, i think this is something to do with permissions
so i granted myself select access onto the problem table but i still get the
same error, i then granted myself select access onto all the tables and i
still get the same error.
is there another permission that i need to enable?
played around with restoring the msdb database from the live server to the
dev server then going in on single user mode to restor the master from the
live server to the dev server but it all goes pear shaped and i've got to
completely re-install 6.5
any help/advice would be appreciated
thanks
lavan
hi don't worry, i've solved it, i forget to se tthe default database so the
app was going straight to master.
cheers anyway
"lavann" wrote:

> Hi,
> We're in the process of migrating our live server (Windows NT Server 4.0 sql
> 6.5) to windows 2003 and sql server 2000.
> I've setup a test server running windows 2000 and sql server 6.5, i've
> copied the database across to the dev server and fixed the orphaned logins
> and made sure the database is consistent which has come out fine.
> when we try to acess the database with our vb app we get the error invalid
> object name dbo.table name, i think this is something to do with permissions
> so i granted myself select access onto the problem table but i still get the
> same error, i then granted myself select access onto all the tables and i
> still get the same error.
> is there another permission that i need to enable?
> played around with restoring the msdb database from the live server to the
> dev server then going in on single user mode to restor the master from the
> live server to the dev server but it all goes pear shaped and i've got to
> completely re-install 6.5
> any help/advice would be appreciated
> thanks
> lavan

Invalid object name when trying to access sql server through vb ap

Hi,
We're in the process of migrating our live server (Windows NT Server 4.0 sql
6.5) to windows 2003 and sql server 2000.
I've setup a test server running windows 2000 and sql server 6.5, i've
copied the database across to the dev server and fixed the orphaned logins
and made sure the database is consistent which has come out fine.
when we try to acess the database with our vb app we get the error invalid
object name dbo.table name, i think this is something to do with permissions
so i granted myself select access onto the problem table but i still get the
same error, i then granted myself select access onto all the tables and i
still get the same error.
is there another permission that i need to enable?
played around with restoring the msdb database from the live server to the
dev server then going in on single user mode to restor the master from the
live server to the dev server but it all goes pear shaped and i've got to
completely re-install 6.5
any help/advice would be appreciated
thanks
lavanhi don't worry, i've solved it, i forget to se tthe default database so the
app was going straight to master.
cheers anyway
"lavann" wrote:
> Hi,
> We're in the process of migrating our live server (Windows NT Server 4.0 sql
> 6.5) to windows 2003 and sql server 2000.
> I've setup a test server running windows 2000 and sql server 6.5, i've
> copied the database across to the dev server and fixed the orphaned logins
> and made sure the database is consistent which has come out fine.
> when we try to acess the database with our vb app we get the error invalid
> object name dbo.table name, i think this is something to do with permissions
> so i granted myself select access onto the problem table but i still get the
> same error, i then granted myself select access onto all the tables and i
> still get the same error.
> is there another permission that i need to enable?
> played around with restoring the msdb database from the live server to the
> dev server then going in on single user mode to restor the master from the
> live server to the dev server but it all goes pear shaped and i've got to
> completely re-install 6.5
> any help/advice would be appreciated
> thanks
> lavan

Invalid Object Name When linking to SQL Table from access!

After Upsizing a table to sql I linked to that table using access db

Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!

Can you help!

If I recall correctly, when you link to external tables in MS Access, the linked table names are not the same as the underlying table so for example a table named foo in SQL Server could become linked as dbo_foo in Access.

I suggest you look at the Tables view in Access and rename the table to match what you want it to be or modify your forms to accept the linked table name.

Regards,

Uwa.

Invalid object name 'tsvw_

Hi Everyone!

Is someone here can help me on this?

I receive a error when inserting or Updating row in a replicated table

the error is Invalid object name 'tsvw_*****

What to do with this

More detail please... What version of SQL server are you using? What type of replication are you using? and etc...|||

Sorry, here some more

I'm using MSDE version of SQL 2000, and 3 layer merge replication, on a WAN

|||when you say three layers, do you mean you have a republishing scenario? "tsvw_*" is a view used by merge replication, my guess is that it may have been dropped somewhere during your setup. If you're doing a republishing scenario, make sure you set up A to B first, then republish B to C.

Invalid object name 'syspublications'

Before reinstalling sql server and restoring old system databases I
forgot to delete and replication that was no longer needed. Now,
Expired Subscriptions Clean Up job fails with the following error:
Error 208: Invalid object name 'syspublications'. I don't need this
replication and would like to delete that completed but everytime I
try I get the same error message.
Please advise how can I delete all manually.
Thank you,
T.
Locate the problem database. do this sp_replicationdboption 'Problem
Database','Publish','false'
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"T" <T@.discussions.microsoft.com> wrote in message
news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
> Before reinstalling sql server and restoring old system databases I
> forgot to delete and replication that was no longer needed. Now,
> Expired Subscriptions Clean Up job fails with the following error:
> Error 208: Invalid object name 'syspublications'. I don't need this
> replication and would like to delete that completed but everytime I
> try I get the same error message.
> Please advise how can I delete all manually.
> Thank you,
> T.
|||This is what I get:
Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> Locate the problem database. do this sp_replicationdboption 'Problem
> Database','Publish','false'
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "T" <T@.discussions.microsoft.com> wrote in message
> news:20736A29-69AF-4B7E-8BB8-C5B1BF5313CF@.microsoft.com...
>
>
|||is this table there? if not run this script in the problem database.
We may need to go back and forth to add the necessary objects a few times.
CREATE TABLE [dbo].[syspublications](
[description] [nvarchar](255) NULL,
[name] [sysname] NOT NULL,
[pubid] [int] IDENTITY(1,1) NOT NULL,
[repl_freq] [tinyint] NOT NULL,
[status] [tinyint] NOT NULL,
[sync_method] [tinyint] NOT NULL,
[snapshot_jobid] [binary](16) NULL,
[independent_agent] [bit] NOT NULL,
[immediate_sync] [bit] NOT NULL,
[enabled_for_internet] [bit] NOT NULL,
[allow_push] [bit] NOT NULL,
[allow_pull] [bit] NOT NULL,
[allow_anonymous] [bit] NOT NULL,
[immediate_sync_ready] [bit] NOT NULL,
[allow_sync_tran] [bit] NOT NULL,
[autogen_sync_procs] [bit] NOT NULL,
[retention] [int] NULL,
[allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
[snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
[alt_snapshot_folder] [nvarchar](255) NULL,
[pre_snapshot_script] [nvarchar](255) NULL,
[post_snapshot_script] [nvarchar](255) NULL,
[compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
[ftp_address] [sysname] NULL,
[ftp_port] [int] NOT NULL DEFAULT ((21)),
[ftp_subdirectory] [nvarchar](255) NULL,
[ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
[ftp_password] [nvarchar](524) NULL,
[allow_dts] [bit] NOT NULL DEFAULT ((0)),
[allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
[centralized_conflicts] [bit] NULL,
[conflict_retention] [int] NULL,
[conflict_policy] [int] NULL,
[queue_type] [int] NULL,
[ad_guidname] [sysname] NULL,
[backward_comp_level] [int] NOT NULL DEFAULT ((10)),
[allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
[min_autonosync_lsn] [binary](10) NULL,
[replicate_ddl] [int] NULL DEFAULT ((1)),
[options] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"T" <T@.discussions.microsoft.com> wrote in message
news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...[vbcol=seagreen]
> This is what I get:
> Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
> Invalid object name 'syssubscriptions'.
>
> "Hilary Cotter" wrote:
|||Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
"Hilary Cotter" wrote:

> is this table there? if not run this script in the problem database.
> We may need to go back and forth to add the necessary objects a few times.
> CREATE TABLE [dbo].[syspublications](
> [description] [nvarchar](255) NULL,
> [name] [sysname] NOT NULL,
> [pubid] [int] IDENTITY(1,1) NOT NULL,
> [repl_freq] [tinyint] NOT NULL,
> [status] [tinyint] NOT NULL,
> [sync_method] [tinyint] NOT NULL,
> [snapshot_jobid] [binary](16) NULL,
> [independent_agent] [bit] NOT NULL,
> [immediate_sync] [bit] NOT NULL,
> [enabled_for_internet] [bit] NOT NULL,
> [allow_push] [bit] NOT NULL,
> [allow_pull] [bit] NOT NULL,
> [allow_anonymous] [bit] NOT NULL,
> [immediate_sync_ready] [bit] NOT NULL,
> [allow_sync_tran] [bit] NOT NULL,
> [autogen_sync_procs] [bit] NOT NULL,
> [retention] [int] NULL,
> [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
> [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
> [alt_snapshot_folder] [nvarchar](255) NULL,
> [pre_snapshot_script] [nvarchar](255) NULL,
> [post_snapshot_script] [nvarchar](255) NULL,
> [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
> [ftp_address] [sysname] NULL,
> [ftp_port] [int] NOT NULL DEFAULT ((21)),
> [ftp_subdirectory] [nvarchar](255) NULL,
> [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
> [ftp_password] [nvarchar](524) NULL,
> [allow_dts] [bit] NOT NULL DEFAULT ((0)),
> [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
> [centralized_conflicts] [bit] NULL,
> [conflict_retention] [int] NULL,
> [conflict_policy] [int] NULL,
> [queue_type] [int] NULL,
> [ad_guidname] [sysname] NULL,
> [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
> [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
> [min_autonosync_lsn] [binary](10) NULL,
> [replicate_ddl] [int] NULL DEFAULT ((1)),
> [options] [int] NOT NULL DEFAULT ((0))
> ) ON [PRIMARY]
>
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "T" <T@.discussions.microsoft.com> wrote in message
> news:1C8FA2D9-4811-4975-81C8-EE744C25C7D4@.microsoft.com...
>
>
sql

Invalid object name sysperfinfo

When I execute this statement through ASP.NET

select cntr_value FROM sysperfinfo

I get this error,

System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'.

Any ideas why?

It sounds to me like your connectionstring (the string that points to the database which you are querying) is pointing to a database that does not have a table entitled 'sysperfinfo'|||

To add to the previous post some things have changes about sysperfinfo. Try the links below for more. Hope this helps.

sysperfinfo

In SQL Server 2005,sysperfinfo returns abigint value for thecntr_value column. Modify applications that usesysperfinfo to make sure that they can handle thebigint values of thecntr_value column.

In SQL Server 2005,sysperfinfo is a compatibility view. You should use thesys.dm_os_performance_counters dynamic management view instead.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

http://www.sqlservercentral.com/columnists/jsack/troubleshootingsqlserverwiththesysperfinfotable.asp

|||

Maybe you're in the wrong database, try:

select cntr_value FROM master..sysperfinfo

Invalid object name 'sysmergepublications' Please Help

Hi,
I'm trying to use Alternate Sync Partner.
I have found this how-to http://support.microsoft.com/kb/321176
but when I execute procedure ( step 9, my names are different )
sp_addmergealternatepublisher @.publisher = 'PublisherA'
, @.publisher_db = 'TestA'
, @.publication = 'DemoPublication'
, @.alternate_publisher = 'PublisherB'
, @.alternate_publisher_db = 'TestB'
, @.alternate_publication = 'DemoPublication'
, @.alternate_distributor = 'PublisherB'
I get this error
"Server: Msg 208, Level 16, State 1, Procedure
sp_addmergealternatepublisher, Line 38
Invalid object name 'sysmergepublications'."
What can be wrong? It is running on Sql Server 2000 SP 3a , replication
between PublisherA and PublisherB is OK.
Best Regards
Wojciech Znaniecki
Uytkownik "Wojtek Z" <wojtas_z@.poczta.fm> napisa w wiadomoci
news:csoa5s$bqe$1@.nemesis.news.tpi.pl...
> "Server: Msg 208, Level 16, State 1, Procedure
> sp_addmergealternatepublisher, Line 38
> Invalid object name 'sysmergepublications'."
Sorry
My mistake,
Before sp_addmergealterpublisher i should have exec
use [db_name]
GO
Now it is ok.
Wojciech Znaniecki

Invalid object name 'sysmergearticles'.

Hi,
When trying to push a subscription I am getting the above error. It's a 2005
database upgraded from 2000.
I had disabled replication on the server before moving to 2005. The db has
some 2000 tables, and recently I dropped many unwanted tables usin T-SQL and
a table with 'useful tables' entries.
When I looked at the original database (backup before cleaning) , I could c
the 'sysmergearticles' table there. The [sysmergearticles] and
[sysmergepublications] tables are there with data in it.
[sysmergesubscriptions] table has no entries.
I tried using sp_replicationdboption with False and the table gets dropped ,
but then it doesn't even allow to create publication. Gives error :
Invalid column name 'column 1'
gives a long list of such columns.
Any suggestions?
So, you completely disabled publishing on 2000 before migrating to 2005
correct? Do you have remote connections enabled on the 2005 instance? Does
publishing configure properly?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:CDAAD96B-4854-4B7B-89F1-3CA648DD9BF3@.microsoft.com...
> Hi,
> When trying to push a subscription I am getting the above error. It's a
> 2005
> database upgraded from 2000.
> I had disabled replication on the server before moving to 2005. The db has
> some 2000 tables, and recently I dropped many unwanted tables usin T-SQL
> and
> a table with 'useful tables' entries.
> When I looked at the original database (backup before cleaning) , I could
> c
> the 'sysmergearticles' table there. The [sysmergearticles] and
> [sysmergepublications] tables are there with data in it.
> [sysmergesubscriptions] table has no entries.
> I tried using sp_replicationdboption with False and the table gets dropped
> ,
> but then it doesn't even allow to create publication. Gives error :
> Invalid column name 'column 1'
> gives a long list of such columns.
> Any suggestions?
>

invalid object name 'sys.objects'

Our scripts have a sql server 2005 requirement.Some of our customers have reported the error invalid object name 'sys.objects' . On going through some of the posts in this forum it seems to me that sys.objects is a view introduced in sql server 2005. So i suspect this problem might be occuring because the user is trying to run our script on an older version of sql server. I want to know if this is the only possibility or if there is any other case this error can occur in sql server 2005.

Thanks,
CCThat is the obvious possibility. It seems very unlikely that a system catalog view was dropped or tampered with.

invalid object name sys.endpoints

I'm running Microsoft SQL Server 2005 - 9.00.3042.00 (X64). I tried
installing the patch from KB 934458, but it failed because it was looking for
files that don't exist. Now when I try to log in to the server, I get a
invalid object name sys.endpoints error. How do I fix this?Hi
sys.endpoints is a view in the master database, if it is not there your best
option may be to re-install SQL Server.
John
"Code Wench" wrote:
> I'm running Microsoft SQL Server 2005 - 9.00.3042.00 (X64). I tried
> installing the patch from KB 934458, but it failed because it was looking for
> files that don't exist. Now when I try to log in to the server, I get a
> invalid object name sys.endpoints error. How do I fix this?
>
Have you checked thesql

Invalid object name sp_totHotelDailyAdviceMASundays.

Please ignore my previous thread.
Basically I have 'sp_totHotelDailyAdviceMASundays' which calls 'sp_rptHotelMonthlyInvoiceSundays' which uses fn_rptHotelDailyDetailsMASundays.

I am now getting the following error when I run the top most sp; 'sp_totHotelDailyAdviceMASundays':

Server: Msg 208, Level 16, State 3, Procedure sp_rptHotelMonthlyInvoiceSundays, Line 3
Invalid object name 'sp_totHotelDailyAdviceMASundays'.

1) The top most sp is:
CREATE PROCEDURE dbo.sp_rptHotelMonthlyInvoiceSundays (@.aDate datetime) AS
SELECT Firms.FirmID, sp_totHotelDailyAdviceMASundays.AccountTypeID, AccountTypes.AccountType, Firms.CompanyTypeID,
Firms.InvoiceAddressee, Firms.InvoiceAddresseeEMailAdd,
Firms.CommissionAdviceAddressee, Firms.CommissionAdviceAddresseeEMailAdd, Firms.[Firm Name], Firms.Address,
Firms.Town, Firms.PostCode, sp_totHotelDailyAdviceMASundays.FirmID1,
sp_totHotelDailyAdviceMASundays.SumOfInvoiceSubTotal, sp_totHotelDailyAdviceMASundays.SumOfOrderDiscountAmount,
sp_totHotelDailyAdviceMASundays.SumOfInvoiceTotal, sp_totHotelDailyAdviceMASundays.SumOfInvoiceVatElement, sp_totHotelDailyAdviceMASundays.CustomerTypeID,
sp_totHotelDailyAdviceMASundays.SumOfNotFinalised
--vw_totHotelDailyAdviceMASundays.[Taken Date]
FROM Firms INNER JOIN (AccountTypes INNER JOIN sp_totHotelDailyAdviceMASundays (@.aDate) ON AccountTypes.AccountTypeID =
sp_totHotelDailyAdviceMASundays.AccountTypeID) ON
Firms.FirmID = sp_totHotelDailyAdviceMASundays.FirmID1
WHERE (((Firms.CompanyTypeID)=8))
GO

2) sp_totHotelDailyAdviceMASundays, which is used in (1), above:
CREATE PROCEDURE [dbo].[sp_totHotelDailyAdviceMASundays] (@.aDate DateTime) AS
SELECT FirmID1, OfficeSiteID,[Office Site Name], Sum(OrderDiscountAmount) AS SumOfOrderDiscountAmount, Sum(InvoiceTotal) AS SumOfInvoiceTotal,
Sum(InvoiceVatElement) AS SumOfInvoiceVatElement
FROM fn_rptHotelDailyDetailsMASundays (@.aDate)
GROUP BY FirmID1, OfficeSiteID,[Office Site Name]
GO

3) fn_rptHotelDailyDetailsMASundays, is a udf used in (2), above.
CREATE FUNCTION fn_rptHotelDailyDetailsMASundays ( @.aDate datetime)
RETURNS TABLE
AS
RETURN
( SELECT Firms.FirmID AS FirmID1, [Office Sites].OfficeSiteID, ......
FROM ((Firms INNER JOIN [Office Sites] ON .......
WHERE ( ((DATEPART(Month,[TakenDate]))=DATEPART(Month,@.aDate) ) AND ((DAtEPART(Year,[TakenDate]))=DATEPART(Year,@.aDate)) AND
(DATEPART(dw, [TakenDate])=1) AND .......

Both (2) and (3) work fine.

The above error occours when I execute (1) like so:
dbo.sp_rptHotelMonthlyInvoiceSundays '3/3/04'

Note that the date parameter, aDate, is passed on from (1) to (2) to (3), the udf and is
only used in the udf's where clause and is never used in (1) or (2).

Thank you in advance again.check my reply for the other post...

Invalid object name 'ReportServerTempDB.dbo.PersistedStream'

Hi,
I've restored my report server 2005 from serverA to serverB and
Reporting Services will run and I can access the reports, but the
above error will pop whenever I try to run a report. I followed the
steps outlined below - where did I go wrong?
1) backed up the encryption key on serverA
2) backed up the report server databases on both servers
3) shut down RS on serverB
4) detached the reportserver database on serverB
5) renamed reportserver.mdf and ldf to reportserver.mdf_old and
reportserver.ldf_old
6) restored reportserver.mdf and ldf from serverA onto serverB
7) started reporting services on serverB - failed to initialize
8) restored encryption key and then deleted the row from
REPORTSERVER.DBO.KEYS where machinename = 'serverB'
9) restarted reporting services and the pages displayed
10) accessed the report that I wanted to view and the error above
popped
11) tried deleting and re-creating reportserverTempDB by dropping and
recreating from the CatalogTemp.sql file from the install - made sure
that the collations matched
Here is the error text:
ReportingServicesService!runningjobs!4!4/3/2007-09:17:42:: e ERROR:
Error in timer Database Cleanup (NT Service) :
System.Data.SqlClient.SqlException: Invalid object name
'ReportServerTempDB.dbo.PersistedStream'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DatabaseCleanupTimer.CleanBatch()
at
Microsoft.ReportingServices.Library.DatabaseCleanupTimer.DoTimerAction()
at
Microsoft.ReportingServices.Diagnostics.TimerActionBase.TimerAction(Object
unused)On Apr 3, 10:41 am, "Tim" <timgoldenst...@.msn.com> wrote:
> Hi,
> I've restored my report server 2005 from serverA to serverB and
> Reporting Services will run and I can access the reports, but the
> above error will pop whenever I try to run a report. I followed the
> steps outlined below - where did I go wrong?
> 1) backed up the encryption key on serverA
> 2) backed up the report server databases on both servers
> 3) shut down RS on serverB
> 4) detached the reportserver database on serverB
> 5) renamed reportserver.mdf and ldf to reportserver.mdf_old and
> reportserver.ldf_old
> 6) restored reportserver.mdf and ldf from serverA onto serverB
> 7) started reporting services on serverB - failed to initialize
> 8) restored encryption key and then deleted the row from
> REPORTSERVER.DBO.KEYS where machinename => 'serverB'
> 9) restarted reporting services and the pages displayed
> 10) accessed the report that I wanted to view and the error above
> popped
> 11) tried deleting and re-creating reportserverTempDB by dropping and
> recreating from the CatalogTemp.sql file from the install - made sure
> that the collations matched
> Here is the error text:
> ReportingServicesService!runningjobs!4!4/3/2007-09:17:42:: e ERROR:
> Error in timer Database Cleanup (NT Service) :
> System.Data.SqlClient.SqlException: Invalid object name
> 'ReportServerTempDB.dbo.PersistedStream'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
> stateObj)
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, DbAsyncResult result)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseCleanupTimer.CleanBatch()
> at
> Microsoft.ReportingServices.Library.DatabaseCleanupTimer.DoTimerAction()
> at
> Microsoft.ReportingServices.Diagnostics.TimerActionBase.TimerAction(Object
> unused)
This MS article might be of assistance.
http://msdn2.microsoft.com/en-us/library/ms159093.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique - it turned out to be a permissions issue on the
ReportServerTempDB in that RSExecRole role