Multivalue Works Fine In The Sproc But Not In Bids Or Reportserver

Http://localhost/reportserver Works Http://&&<servername&&>/reportserver Doesn't
Hi All,
 
I have setup SSRS 2000 and gotten it to work but I am having trouble with SSRS 2005. I can't access to reportserver anywhere on the network. The only way to get to reportserver is termserv into the server and hit it with http://localhost/reportserver The server is Windows 2003 server Standard Ed. running SQL 2005 SP2 and Sharepoint Portal Server 2007. Can somebody please help? Thank you.

View Replies !

---

Package Runs Fine In BIDS, Fails When Scheduled
I have a 7 step SSIS package that manipulates some data on a DB2 database.  The package executes perfectly in Business Intelligence Development Studio.  I save the package to my SSIS store and then point my scheduled task to it and it fails after about 9 seconds everytime.  I have an identical job that works with a different DB2 database that works without any problem.  The only difference is the database it's pointing to. 

The package is executing as the same user who created it, which has sysadmin to both the SSIS store and the SQL instance the package is executing on.  When I saved the package I selected "Rely on server storage roles for access control" for the protection level.

This one is driving me crazy, can't figure it out.  Any idea's?

View Replies !

---

Sql Job Fails But When Run Outside Works Fine
Hi..
 
I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error
 
 
Error: 2008-03-24 13:52:40.22
   Code: 0xC0202009
   Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80004005  Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
 
Provider is unable to function until these components are installed.".
 
I am able to run the package outside the sql job and also connect to the oracle.
I have oracle 9i client installed on the server and sql server is 2005.
 
Any help would really be appreciated..

View Replies !

---

FTP Connection Only Works In BIDS
I have an FTP task that will only work when running in BIDS.

 

When trying to run as a package on the server or calling the package from a job, I get the following error in the log file:

 

OnError,<servername>,<user>,FTP Task,{B2F5BB68-C6F8-4EE5-ABC0-71C3636E3E4A},{B7B41A88-18DD-4AD7-8CDE-9E0C1B74DA02},6/26/2007 12:09:11 PM,6/26/2007 12:09:11 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

 

When running in BIDS it is fine.

 

Any know what is causing this?

 

 

View Replies !

---

Transfer Problems For 6.5 Next Week Works Fine
I have set up transfer of database which takes 6 hrs each saturday
Type--- CMDeXEC


last saturday it tooks just 7 mins and showed this error and did not coplete



2004/0619 4:00:18:42-spid14-Warning OPEN OBJECTS parameter too low

2004/0619 4:00:18:45-spit14-attempt was made to free up descriptors in localdes()

2004/0619 4:00:18:45-spid14-Run sp_configure to increase parameter value

2004/0619 4:00:19:95-spid14-Warning OPEN OBJECTS parameter too low

2004/0619 4:00:19:95-spit14-attempt was made to free up descriptors in localdes()

2004/0619 4:00:19:95-spid14-Run sp_configure to increase parameter value



OriginalTransfered
Data Size 19000(max 28105 mb)18640(max 18640)
Log Size 4000(13105 mb)4640 (max 4640)
Data Physical 18.5 gb18.5 gb
logPhysical 3.90 g b4.88 gb





looks like my data and log size should be increased correct

if yes how to do that?????





if worked this week fine (6 hrs )

View Replies !

---

32 Bit DTExec Fails While 64 Bit Works Fine On 64 Bit Machine
Hi,
I am executing a SSIS package using dtexec. 64 bit version of dtexec works fine. But when i use 32 bit version of dtexec, it fails. i have local admin rights. Following is error description. Please help.
 

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  9:24:30 AM
Error: 2008-03-18 09:24:32.54
   Code: 0xC0202009
   Source: IMALCRM Connection manager "IMAL SRC"
   Description: An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  H
result: 0x800703E6  Description: "Invalid access to memory location.".
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC020801C
   Source: Load Fund Detail V_FUND_DETAIL [16]
   Description: The AcquireConnection method call to the connection manager "IMA
L SRC" failed with error code 0xC0202009.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC0047017
   Source: Load Fund Detail DTS.Pipeline
   Description: component "V_FUND_DETAIL" (16) failed validation and returned er
ror code 0xC020801C.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC004700C
   Source: Load Fund Detail DTS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2008-03-18 09:24:32.54
   Code: 0xC0024107
   Source: Load Fund Detail
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  9:24:30 AM
Finished: 9:24:32 AM
Elapsed:  2.078 seconds

View Replies !

---

Job Doesn't Work But Package Works Fine
hi,

I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.

Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.

Any ideas?

View Replies !

---

Remote Connection Tests Fine, But Nothing Works On The Page Itself.
If this post belongs somewhere else I appologize. I have spent several days trying to solve this problem with no luck. My site is online. Hosted at NeikoHosting. I can connect to the database remotely when adding a datacontrol. It tests fine. But when running the page it won't connect. Even if I go in and change the Web.Config connection string to a local Data Source provided to me by Neiko, it still won't work. It just won't connect. Here are the two connection strings in the Web.Config, minus my login info: Only the remote string will pass testing. Neither works on the site. <add name="yourchurchmychurchDBConnectionString" connectionString="Data Source=MSSQL2K-A;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
<add name="yourchurchmychurchDBConnectionString2" connectionString="Data Source=66.103.238.206;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
 Here is the stack trace, if that helps.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
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.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:



[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.OleDb.OleDbConnection.Open() +37
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.FormView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.FormView.EnsureDataBound() +163
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

So....... HELP!!!!
 
Thank you!

View Replies !

---

DTS Job Failing Execution When Scheduled, Works Fine Manually.
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Help!!!

View Replies !

---

Query Works Fine Outside Union, But Doesn't Work .. .
hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??


select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc




i've included the union query here for completeness of the question



begin
declare @current_date datetime
set @current_date = GETDATE()


select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

end

View Replies !

---

Using Symmetric Key Problem With Encryption, Decryption Works Fine
Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword  variable. But it's not working. It doesn't give me an error but gives me wrong data...

 

 

declare @thePassword as varbinary(128)

,@inTargetPassword as varchar(255)

,@pwd3 as varchar(255)

,@theUserId bigint

set @theUserId= 124564

set @inTargetPassword = 'test'

OPEN SYMMETRIC KEY Key1

DECRYPTION BY CERTIFICATE sqlSecurity;

Select @pwd3=EncryptByKey(Key_GUID('Key1')

, @inTargetPassword, 1, HashBytes('SHA1', CONVERT( varbinary, [UserObjectId])))

from table1 where UserObjectId= @theUserId

close symmetric key Key1

View Replies !

---

Slow Query....drop Index Works Fine!!!!!
We are running MS RS and SQL Server 2000 SP3.

We have one LEDGER,  where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered).  To get AR we use this table.

 

Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block). 


If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.

 

This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....

 

What is this. ....is it our QUERY, index or huge Transactions or no free space ???

 

We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?

 

Also is they any way to KILL all  SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????

 

Thanks,

View Replies !

---

Report Works Fine Stand Alone, But Fails When Used As Subreport
I have a report which I have tested and works fine.  now I'm trying to use it as a subreport.  the "outer" or main report is very simple: it just has a company standard banner and some header/footer information, and then a single subreport.  there is no passing of parameters between main report and sub report.  the subreport does have its own parameter to govern its dataset, and provides its own default for that. 

 

The error that I'm getting is this:

 

[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜subreport1€™: An error has occurred during report processing.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Year€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Month€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Date€™. The data extension returned an error during reading the field.

[rsMissingFieldInDataSet] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The data set €˜WarrantMasterCube€™ contains a definition for the Field €˜Wt_TO_MTD€™. The data extension returned an error during reading the field.

[rsNone] An error has occurred during report processing.

 

 Of course, this doesn't happen when I execute the subreport by itself.  What kinds of things should I be looking at to get to the bottom of this.  Thanks!

View Replies !

---

Package Works In BIDS, But Not In SQL Server 2005
Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?



Code Snippet

Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()

fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)

If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

View Replies !

---

Package Can't Open File From Job (works From BIDS)
Hi,
 
I have a problem that's baffling me. I have a package that loads some files into the database. If I run it from BIDS, it works fine. But if I run the package from the job, I get this error:
 
Cannot open the datafile "D:myFoldermyFile.TXT". 
 
It seems like a permissions issue, but the job runs under a local admin account, and not to mention, the very same package/job reads and loads files from this exact same directory, with no problems.
 
Halp.

View Replies !

---

Problems Publishing My Personal Website - Works Fine Locally!
People,I'm trying to publish my first website and am having a few problems.I've got Visual Web Developer 2005 Express and am trying to use the Personal Website Starter Kit. (my SQL server is SQL Server Express Edition 2005 - which is also running on my local machine)It seems to work fine when I run it on my localhost, as soon as I ftp it up to my web hosting company, I get an error message (see below) :-An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) My hypothesis is :-It would appear to me that when running locally, the starter kit website uses my installation of SQL 2005 Express Edition, but when I upload all the files, I'm guessing the application is still trying to point at a local instance of SQL on my local PC which it now cannot see. I'm guessing I need to somehow upload the SQL database onto my web host (I've purchased 100M of SQL Server 2005 space), and point the application at that SQL instance instead. But I don't know if I'm right about all this, or indeed how to do it if I am. Can anyone help?Much thanks in advance,Will

View Replies !

---

Long Running Query In SQL 2005 But Works Fine In SQL 2000
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db.  The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well.  But still no luck, still running extremely long.  1 hour 20 minutes.
 
I'll try to give you some background on these table.  Weeklysalehistory has approx 30 fields.  I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well.  So I think my indexes are fine.
 
/* Update WeekEnding Date for current weeks WeeklySales Records */
Update  WeeklySalesHistory  set
    weekendingdate =
 (SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
 
Weekly sales has approx 100,000,000 rows
Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records)  Although I don't think this is issue since on 2000 has same thing and works fine.

 
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
 
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this.  I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what.  I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras?  Any help would be greatly appreciated.
 
 
Stacy

View Replies !

---

Stored Procedure Works Fine, But Doesnt Preview? (Beginner)
I modified this stored procedure with the code highlighted, and now it runs forever on reporting services. Can anyone tell me what might be causing this? Here's my code. When i run it in Management studio and the dataset in reporting services, it works fine. But when it comes to previewing it, it runs forever.

 

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN] Script Date: 06/29/2007 11:34:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN]

(@Region int = Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice' as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime, 'SALESAMP') * -1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL * -1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE Region.Region_Key = @Region

AND [Item_Ledger_Mars_Year] = 2007

AND [Item_Ledger_Amount]= 0

UNION ALL

SELECT

[GL_Entry_Posting_DateTime]

,Budget_Reporting.[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE GL_entry.[Mars_Year] = 2007



END

 

 

View Replies !

---

Works Fine In Designer But When I Load The Report It Doesn't Work
works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????

Item has already been added. Key in dictionary: '9' Key being added: '9'

View Replies !

---

Permission Issue With Tempdb Works Fine In SQL2000 But Not SQL2005
the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

 
 

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

 IF @DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

 

 EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')
 EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

 

 END
 
The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.
 
 

View Replies !

---

Havin Trouble Inserting Records To A Table.. Update Works Fine
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
 IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
 Any help will be appreciated..
Regards,
Karen

View Replies !

---

Works Fine Inside BI Dev. Studio, But Fails When Scheduling It In SQL Server 2005.
Hi!

View Replies !

---

Access Denied To ReportServer And Reports, Works Locally But Fails Remotely
 

I am getting the error:
 

HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.
Internet Information Services (IIS)
 
when I attempt to connect to Reports or ReportServer from my desktop, but it works normally when I login with the same userid and run it directly on the server, using IE.  It is prompting me for a login 2 or 3 times before failing.
 
My configuration is:
 
Report Server system: 

Windows Server 2003 R2 SP2 - 32bit

SQL Server 2005 Reporting Services Enterprise Edition
Windows & Web Service run as a domain account
Database connection is using domain account - not in db_owner, but in RSExec roles
Database is on another server, and is Native, not SharePoint integrated
Reports & ReportServer are in a separate application pool
Reporting Services had SP2 installed before it was configured
The Rport Server is a VM
 
Database server

Windows Server 2003 R2 SP2 - x64        
SQL Server 2005 Enterprise Edition 64bit
Version 9.00.3200.00
 
The Report Manager was not working at all, and I discovered that there was no entry in the .Net Framework version on the APS.Net tab in properties for the Reports virtual directory.    I am not seeing any errors or anything unusual in the Event log or in the ReportServer log files.
 
FrontPage 2002 extensions were installed, and then removed.  I noticed that this installed a SharePoint virtual directory, and that disappeared when I removed FrontPage extensions.
 
The domain group my userid is in is in the local Administrators group on the ReportServer system, and I have added this group as a System Administrator and Content Manager through the report Manager.
 
I would greatly appreciate any suggestions.
 
Thanks,
Bill

View Replies !

---

Query Works - Sproc Fails
I have a query that works fine but fails as a sproc.
QUERY:
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = 'Brown'
 
SPROC:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetProfileId]
@UserName nvarchar
AS
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = @UserName
The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0

View Replies !

---

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped.

I created an updateable partioned view of a very large table.  Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.

There error generated is:

Server: Msg 16957, Level 16, State 4, Line 3

FOR UPDATE cannot be specified on a READ ONLY cursor

 

Here is the cursor declaration:

 

declare some_cursor CURSOR

for

select    *

from       part_view

FOR UPDATE

 

Any ideas, guys?  Thanks in advance for knocking your head against this one.

PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing.  Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.

View Replies !

---

Table Visibility Not Functioning Correctly On Server, Works Fine In Visual Studio
I have a report problem.  I'm using a parameter to dynamically control visibility for two tables.  If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead.

This all works fine in Visual Studio.  When I publish it to my report server, the visibility controls no longer function and both tables always display.  Any ideas here?

I'm running 2005, SP2 CTP.

View Replies !

---

Xp_sendmail: Failed With Mail Error 0x80040111 It Works Fine When You Do A Test From Enterprise Manager
 

Hello I am receiving the dreaded mail error listed above.  I can send out a test E-mail from Enterprise Manager to operators, but I cannot run this Transact query:
 
EXEC master.dbo.xp_sendmail @RECIPIENTS = araz***@***.com(removed email address),
                                    @SUBJECT = 'test'
 
I receive:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111

 
 
I have stopped/restarted the SQL SERVER AGENT but haven't done much else as I haven't been able to. 

Should it work through transact SQL if the test email works from Enterprise Manager?

This is SQL 2000 SP4 running on Win2K in the domain.  Thank you.

View Replies !

---

Package Runs Fine From BIDS But Dtexec On Same Box Gives &&"insufficient Product Level&&" Error
Running eval. edition of Sql Server Standard 2005.  "Insufficient product level" error is thrown during validation phase of an OleDBCommand data flow task.  This task type is not licensed in Sql Server 2005 standard? The component runs a very simple sql update statement against a one row table in Sql Server 2005.

If it works from BIDS, should it not work from dtexec.exe on the same box? 

Does dtexec run under the security context of the logged in user?

 Thanks,

Ken

View Replies !

---

RESTORE FILELIST Is Terminating Abnormally Error When Running A DTS Package In SQL 2005. Works Fine In SQL 2000
Currently I receive the following error when executing script within a DTS package in SQL 2005 (it seems to be working in SQL 2000):
 

Processed 27008 pages for database 'Marketing', file 'Marketing_Data' on file 5.

Processed 1 pages for database 'Marketing', file 'Marketing_Log' on file 5.

BACKUP DATABASE successfully processed 27009 pages in 15.043 seconds (14.708 MB/sec).

(5 row(s) affected)

Msg 213, Level 16, State 7, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.
 
The code I am using is:
 

-- the original database (use 'SET @DB = NULL' to disable backup)

DECLARE @DB varchar(200)

SET @DB = 'Marketing'

-- the backup filename

DECLARE @BackupFile varchar(2000)

SET @BackupFile = 'C:SQL2005 dbsMarketing.dat'

-- the new database name

DECLARE @TestDB2 varchar(200)

SET @TestDB2 = datename(month, dateadd(month, -1, getdate())) + convert(varchar(20), year(getdate())) + 'Inst1'

-- the new database files without .mdf/.ldf

DECLARE @RestoreFile varchar(2000)

SET @RestoreFile = 'C:SQL2005 dbs' + @TestDB2

DECLARE @RestoreLog varchar (2000)

SET @RestoreLog = 'C:SQL2005 dbs' + @TestDB2

-- ****************************************************************

-- no change below this line

-- ****************************************************************

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)

SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)

SET @LogFile = @RestoreLog + '.ldf'

IF @DB IS NOT NULL

BEGIN

SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')

EXEC (@query)

END

-- RESTORE FILELISTONLY FROM DISK = 'C: empackup.dat'

-- RESTORE HEADERONLY FROM DISK = 'C: empackup.dat'

-- RESTORE LABELONLY FROM DISK = 'C: empackup.dat'

-- RESTORE VERIFYONLY FROM DISK = 'C: empackup.dat'

 

 

 

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB2)

BEGIN

SET @query = 'DROP DATABASE ' + @TestDB2

EXEC (@query)

END

RESTORE HEADERONLY FROM DISK = @BackupFile

DECLARE @File int

SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)

DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp

(

LogicalName varchar(500),

PhysicalName varchar(500),

type varchar(10),

FilegroupName varchar(200),

size int,

maxsize bigint

)

INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'

SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data

PRINT @Log

TRUNCATE TABLE #restoretemp

DROP TABLE #restoretemp

IF @File > 0

BEGIN

SET @query = 'RESTORE DATABASE ' + @TestDB2 + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +

' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +

QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)

EXEC (@query)

END


 
 
 
 

View Replies !

---

OR Works Fine, When I Add AND Things Go Wrong (was &"Help With Simple Query&")
Hi I really need some help I have been banging my head against a brick wall for the last day or so...

I need some assistance with a query. Maybe what I am trying to do is not possible but I am sure it is.

I have a ASP page with a SQL 2005 Express backend. I would like to query a table based on a dropdownlist and checkboxes on my form.

The perameter in the drop down list is a service so I am querying records based on a service. The checkboxes are locations such as north,east, south and west. So I am building OR statements with these. For example I am looking for plumbers in the north and east. The OR part works fine. Its when I add the service into the query things go wrong.

Here is my tables

Company
---------

PK CompanyID
ComapnyName

Service
--------

PK ServiceID
FK CompanyID
ServiceName

Region
--------

PK RegionID
FK CompanyID
North
East
South
West

The below works fine when querying companies in different locations:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.North, TblRegion.East, TblRegion.South, TblRegion.West
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID
WHERE (TblRegion.Scotland = @Pram1) OR
(TblRegion.Wales = @Pram2) OR
(TblRegion.NorthEast = @Pram3) OR
(TblRegion.NorthWest = @Pram4)

Now the problem is when I add in the service table. Sometimes I get all records returned. So even if I query a plumber in the south and east I get records for companies that dont even have a particular service in the perameter.

I can provide more info if need - I am guessing my design is all wrong. The way I see this happening from a GUI is a dropdown being the service WHERE perameter and the truefalse BIT tick boxes being the OR perameters - any help would be great - many thanks in advance.

View Replies !

---

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server.  It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.

We use rectangles to force page breaks to page 2 and to page 3.

When running the report on the report server, it shows and prints fine.

When running the report from the QA website internally, it shows and prints just fine.

When running the report from the production website from a machine internally, it shows and prints just fine.

When running the report from outside of the company network, the report is jacked.  It obliterates large chunks of text, crams text together, and creates blank pages.

I need help in determining where I even begin with trouble shooting this!

View Replies !

---

MS Access Back-end Works Fine, But SQL Server Back End NOT Working For Access Project
Hi

I have 2 forms, each associated with its own table - they are linked together only by the fact that they share a common field, CustomerID....

I create a form (for table 1, called Customers) that brings up another form (for table 2, called Projects) based on the CustomerId that is selected in the first form from the Customers table - with a SQL back end it brings up ALL records in the second form (for the second table) with any CustomerIDs that exist in both tables - I set the link criteria [forms]![Customers]![CustomerID] and with an MS access back-end it works fine, but with a SQL back end it still returns ALL records with any ID that matches....not just the one I selected in the Customers form..... why??

thanks for reading this and for your help...

View Replies !

---

Reportserver Is Asking For Username And Password Everytime I Call Http://servername/reportserver
Please help everytime i call http://servername/reportserver it is presenting the login screen, if i enter the username and password then i can access the reportserver screen, same with reportmanager tool.

under iis manager for the reportserver virtual directory i have given anonymous login access.

do i have to set any other extra permissions other than anonymous login(iusr_machinenname)

thank you all for the information.

 

 

View Replies !

---

ReportServer - Http://localhost/ReportServer - Error
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.

And my ReportServer log:
<Header>
  <Product>Microsoft SQL Server Reporting Services Version 9.00.1399.00</Product>
  <Locale>en-US</Locale>
  <TimeZone>Pacific Standard Time</TimeZone>
  <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__03_24_2006_16_45_06.log</Path>
  <SystemName>USPROFILING</SystemName>
  <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
  <OSVersion>5.2.3790.65536</OSVersion>
</Header>
w3wp!webserver!1!3/24/2006-16:45:06:: i INFO: Reporting Web Server started
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing ConnectionType to '1'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsSchedulingService to 'True'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsNotificationService to 'True'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsEventService to 'True'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsWebServiceEnabled to 'True'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing ProcessRecycleOptions to '0'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonFlags to '1064'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing SecureConnectionLevel to '0'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DisplayErrorLink to 'True'  as specified in Configuration file.
w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WebServiceUseFileShareStorage to 'False'  as specified in Configuration file.
w3wp!resourceutilities!1!3/24/2006-16:45:06:: i INFO: Reporting Services starting SKU: Enterprise
w3wp!resourceutilities!1!3/24/2006-16:45:06:: i INFO: Evaluation copy: 0 days left
w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds.  Cycle: 600 seconds
w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
w3wp!library!1!03/24/2006-16:45:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetWebConfigAuthenticationAttribute(String attrName)
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetAuthenticationType()
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.get_UsingWindowsAuth()
   at Microsoft.ReportingServices.WebServer.Global.EnableAntiDos()
   at Microsoft.ReportingServices.WebServer.Global.ShouldRejectAntiDos()
   at Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e)
   --- End of inner exception stack trace ---
w3wp!library!1!03/24/2006-16:45:09:: i INFO: Exception dumped to: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson
w3wp!library!1!03/24/2006-16:45:11:: i INFO: Catalog SQL Server Edition = Enterprise
w3wp!library!1!03/24/2006-16:45:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetWebConfigAuthenticationAttribute(String attrName)
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetAuthenticationType()
   at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.get_UsingWindowsAuth()
   at Microsoft.ReportingServices.WebServer.Global.EnableAntiDos()
   at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e)
   --- End of inner exception stack trace ---
w3wp!library!1!03/24/2006-16:45:14:: i INFO: Exception dumped to: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson
 
Any help would be appreciated:
 
System 2003 Server IIS6 and Sql2005 enterprise all housed on Virtual Server. all housed locally.

 

View Replies !

---

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
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: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View Replies !

---

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP
I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!


PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View Replies !

---

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running

 

telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password

 

See below:

 

Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

 Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.

The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:

 

TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT

 

Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

I would expect this as the DNS has not been advised to encrypt the conection.

This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.


This is on a XP machine trying to connect to the remote webhosting company via the internet.

I can ping the server

 I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled

I do not have any aliases set up

No I do not force encryption

 I wonder if you have any further suggestions to this problem?

 

View Replies !

---

EXEC Of A Sproc Within Another Sproc
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy



Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int

EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended

WHERE
EntryLogID = @EntryLogID

EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID



Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID

RETURN


*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID


UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID

View Replies !

---

Exec Sproc In Sproc
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID


GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??

Tks

View Replies !

---

MultiValue Parameters
 Hi.  I am fairly new to reporting.  I have a report that currently displays queried data in both a table and a matrix.  I'd like for the data that is displayed to be based off of my report parameters.  I have three, all of which are multivalue.  Currently, I am able to display the report, based on the only the first value of TWO of the three parameters.  I tried using the dataset parameters, but was not successful with linking them to my report parameters.  Please help!  In addition, is there a way to set multivalue parameters to only display distinct values?  Thanks, in advance, for any help.

View Replies !

---

Multivalue Parameters - Is This Possible ??
 

i am trying to build a report that uses multivalue parameters - the report is a simple listing report that lists out a list of cases as a table - the data is obtained from a view which just returns all details for all cases

there is no user input for the selection details - am going to be calling the report and passing over the parameter values directly to it.

there are several parameters for the report - they all default to "All"

for each of these parameters, i need to be able to either use the default value of "ALL" or to enter a list (comma separated ?)

q1) could i just enter the list as a string comma separated list (rather than as multivalued parameters) and then use this list to filter the data using the FILTERS tab on the dataset

q2) if q1 is not possible, if i use multivalue parameters, how do i get my list of parameters into the report as the application calling the report just has a list of values

thx

mark

View Replies !

---

Multivalue Parameter!!!
I have multivalued parameter in my report. I have text box to display heading of the report. the report heading is dependent on the parameter selected.

with single value parameter the text box displays the correct parameter but it cannot with multi valued parameter. Is there any way to display the last value of the parameter in the heading of the report in case of multivalued parameter?

 

Ex. multivalued parameter is "YEAR"

my report shows the data for 3 years say 2003,2004,2005. report header should show 2005(last of the parameter list) report.

 

regds,

View Replies !

---

Multivalue Parameters
how do I create a multivalue parameter that has a default value of 'All' Or how to I I get the "Select All" option as the default parameter?

View Replies !

---

Using Multivalue Parameter
We have a report that has a banner page.  The user can select categories from a multi-select parameter and then this is passed into the report as a csv file.  The actual report works doing this, however, we need the banner page to use this parameter's value (the multiple selected labels) to display a string of the selected values.  For the textbox expression, we are using =Parameters!Cat.Value, but it always wants to reference Paramaters!Cat.Value(0).  We were thinking of using a function in code to pass in the parameter and then concantenate each value into on string and return that string, but isn't there an easier way to do this?

 

Thanks for the information.

View Replies !

---

MultiValue Parameter
Hello,

 

I have in my report a text box that lists the user selection.  When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

 

Thank you,

 

Itzhak

View Replies !

---

MultiValue Parameter Help
I have passed a list of parameters to a multivalue parameter in a SQL Report.

I can click the drop down and see the list of parameters that were passed.

 

In my select statement for the report, here is the condition I am using in the where clause.

 

AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@RUs))

 

Nothing is returned in the result set if more than one value is passed to the multivalue parameter.

 

Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.

 

For example, in the multivalue dropdown list, there are the following values:

 

211

212

 

If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.

 

 

View Replies !

---

SP2 And Multivalue Parameters
Hi,

I need to set a label in my report when all the values in a certain multivalue parameter are selected. In other words, i would need to check whether this parameter has the value "Select All" selected or not.
How do i accomplish this?

Thanks in advance,
Claudio

View Replies !

---

I Don't Understand How This Works, However It Works (sometimes)
I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS(  sections work however usualy they do, in this case it does not andt I can't seem to find out why.

Does anyone see the error?

Thanks

--=========================================
--Create a local temporary table that hold
--all the data from the source table
--=========================================

SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC

--=========================================
--Remove table entries that are no longer
--needed or that have to be updated
--=========================================

DELETE FROM LTTSTOCK

WHERE NOT EXISTS( SELECT * FROM #TEMP

WHERE LTTSTOCK.WarehouseNo = LTWHLO

AND LTTSTOCK.Location = LTWHSL

AND LTTSTOCK.ItemNo = LTITNO

AND LTTSTOCK.NumberAvail = LTAVAL

)

--=========================================

--Insert data that is missing or that

--needed to be updated and was previously

--deleted

--=========================================

INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

View Replies !

---

Need Help Normalizing Multivalue Column
Hi!


I have a table with the following columns:

account_nr, account_totaling_members, account_type

 

the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"

I need to normalize this so that i get records like:

"10", "1001", "sum"

"10", "1001", "sum"

"10", "1002", "sum"

..and so forth

 

Does anyone have any idea how to accomplish this?

View Replies !

---

Output Multivalue Parameters
I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:

Parameters!Names.Value(0)

Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?

Thank you!!

View Replies !

---

Passing Multivalue Parameters
Hello Friends,

I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.

Till this everything is fine.

Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.

Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.

 

Thanx Friends,

Raktim

View Replies !

---