Report Works Fine Stand Alone, But Fails When Used As Subreport

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 !

---

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 !

---

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

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 !

---

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 !

---

Large Report Using Subreport Fails On RS 2003
I have a report where each record has a first page (list) that includes a multi page subreport. The report works fine in most cases, but when running large reports (2000+ pages), after approx 1500 pages the subreport  fails with message: Error: Subreport could not be shown. The subreports works before this error.

The master report still works and all master pages are rendered, but the included subreports fails.

The setup is three-tier with SQL 2000, RS 2003 and XP client on separate computers.

Any hints on what to do to run large reports? Where to start to look for errors?
There are no errors in the Event Logs on the servers.

 

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 !

---

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 !

---

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 !

---

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

I have a report which has multivalue parameters enabled and If i give NULL it displays everything correctly. But if I give different ClientId it doesnt do it in the report.. But if i run my sproc in VS2005 and in ssms it works the way i want it. this is my sproc
 



Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]

@StartDate datetime,
@EndDate datetime,
@ClientId nvarchar(max)= NULL
AS
Declare @SQLTEXT nvarchar(max)
if @ClientId is NULL
BEGIN
SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId -- and o.CreatedByUserId = cp.UserId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId --AND cp.ClientId in ('+ convert(Varchar, @ClientId) + ' )
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + convert(Varchar,@ClientId) + ')
AND
o.OrderDate BETWEEN ''' + Convert(varchar, @StartDate) + ''' AND ''' + convert(varchar, @EndDate) + '''
ORDER BY
o.OrderId DESC'
exec(@SQLTEXT)
END
--return (@SQLTEXT)
 
 

 
I have 2 datasets in this report one for the above sproc  and other dataset that gives me the clientname and it is as follows




Code Snippet

ALTER Procedure [dbo].[usp_GetClientsAll]

@ClientId nvarchar(max) = NULL

AS

--Declare @ClientId nvarchar(max)

SELECT

NULL ClientId,

'<All Clients >' ClientName

FROM

Client

Union

SELECT

ClientId,

ClientName

FROM

Client

Where

ClientId = @ClientId

OR

(

ClientId = ClientId

OR

@ClientId IS NULL

)
 
 




In the first dataset Parameter list i have omitted ClientId but kept it in the report parameter.. So when i give select all it works.. but when i just select particular it gives me the same result as Select all..
 
any help will be appreciated..
REgards
Karen

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 !

---

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 !

---

A Subreport Which Aslo Has Another Subreport In A Master Report
 

Can we include a subreport which aslo has another subreport in a master report? (assuming all three reports have parameters too)
 
Thanks

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 !

---

Error: Subreport Could Not Be Shown. Works For Me But Not Other Users
I have a simple report which calls another subreport.  I can run the report successfully in both the Report Designer and through the Reporting Manager.  Security has been set for all users in the Report Manager for both the Main Report and the Sub Report. 
 
When another user tries to run the report from Report Manager they get a one line error message in the Main Report which says "Subreport could not be shown".
 
I am a Systems Administrator, during testing I have added the other users to the System Administrator Group also, but this made no difference.

 
Can anyone give me any hints?
Thanks
Rae

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 !

---

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 !

---

Fails When Use MS Access Datasourse And Ran Form SQL Job (Fine In IDE)
Hi,

I have created a relatively simple SISS package for MS SQL server 2005 SP1, the package runs without issue when started from the development environment, but fails when ran via a SQL Job.


I have other SISS jobs which run without any problems, the difference with this one is that it doesn€™t just access data the the MS SQL server databases, but it also accesses an external MS Access 2003 database (emptys tables then repopulates based on data in MS SQL)

The database is held on a network share, so I would imagine the issue is that the SQL job is ran under a different security context. Originally it was ran under €œSERVERNAMESYSTEM€?, but I created a new credential on the server giving it my own username and password, I then added a proxy to the server which used the new credietial and was given access to all sub systems.


The SQL job step was setup to run under this proxy, but still failed even though the user name on the credentials had access to the MS Access database on the network share & also matched the package CreatureName, allowing the data to be read as EncryptSensitiveWithUserKey was set in the package.


The SISS package was created by using the wizard, no ODBC connection was setup on the server.
The job history shows €œExecuted as user: ADMINusername. The package execution failed.  The step failed.€?
I have also tried this with a protection level of ServerStorage



Any sugestions?

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 !

---

SSIS Package Runs Fine; SQL Job Fails (DBF Involved)
I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).

 

The Connection Manager that works on the .dbf table is Native OLE DBMicrosoft Jet 4.0 OLE DB Provider.

 

The entire SSIS package runs perfectly when i run it from my development environment.

 

I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.

 

But when I run the SQL Job, the Job fails immediately on Step 2.

 

Here is the error message in View History of the SQL Job:

-----------------------------------------------------------------------------

Executed as user: DBASE01SQLExec. ...on 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:48:37 AM  Progress: 2007-06-14 11:48:37.91     Source: DBF to ScanUS_Process      Validating: 0% complete  End Progress  Progress: 2007-06-14 11:48:37.91     Source: DBF to ScanUS_Process      Validating: 33% complete  End Progress  Error: 2007-06-14 11:48:37.91     Code: 0xC0202009     Source: DBF to ScanUS_Process ScanUS DBF Output File [1]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  End Error  Error: 2007-06-14 11:48:37.91     Code: 0xC02020E8     Source: DBF to ScanUS_Process ScanUS DBF Output File [1]     Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database.  End Error  Error: 2007-06-14 11:48:37.95     Code: 0xC004706B     Source: DBF to ScanUS_Process DTS.Pipeline     Description: "component "ScanUS...  The package execution fa...  The step failed.

-----------------------------------------------------------------------------

 

Could someone suggest why this works OK within SSIS but not when the SSIS Pakcage is called from SQL?

 

thx very much/spirits.

 

seth j hersh

 

View Replies !

---

DTS Package Manually/locally It Runs Fine. But The Scheduled Job Run And Fails
I was facing some issue on MP. Yesterday I changed the SQL services to use the local admin account. That didn't help my MP issue. But it may have created another issue.
When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string.
---------------------------------
Executed as user: IL06EDM00SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed.
--------------------------------------------------------------

Any ideas.

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 !

---

DTS Works But Job Fails
Hi

I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.

SQL Server agent is running with same account "xxx"
DTS connects to SQL Server with sa authentication
Job owner is same account "xxx"

Job error log

Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.


I copied the DTS to another one and scheduled it

This time I got the error log
Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: 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. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.

Please help!
Thanks in advance

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 !

---

SELECT Works But UPDATE Fails. ?
This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.

View Replies !

---

SQL DTS Job Fails Where Execute Of Package Works?
I have a SQL DTS package that imports a text file as one wide column into a two column SQL table, one is an identity seed. So essentially I import then parse the data using the index key as it is 3 seperate lines of data. All works fine by running the DTS package using execute. When I schedule this as a job it fails. It indicates that I have additional white space after a column. Any thoughts on why this can ocurr?

View Replies !

---

Scheduled DTS Fails, Manual Works. Help!
Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.

My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.

I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!

View Replies !

---

DTS Execute Works, Schedule Fails
Hey all,

I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.

Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31

the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")

How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.

thanks in advance,
pete

View Replies !

---

DB Access Works In Debug But Fails When Hosted
I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails. What could I doing wrong could I be missing a setting in IIS? Any ideas??Here's my webconfig if that helps at all:  <?xml version="1.0"?><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">    <connectionStrings>        <add name="studentprofilesConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>    </connectionStrings>    <system.web>        <roleManager defaultProvider="AspNetWindowsTokenRoleProvider" />  <compilation debug="true" defaultLanguage="c#" /></system.web></configuration>  

View Replies !

---

Using A Date Filter In Query With (&< Or &>) Fails But With (=) Works ???
I have a simple ASP.NET application that allows the user to enter a Query in a TextArea and submit that to the database. Queries that contain Integer, String or other filters seem to work fine, but when the Query contains a Date or DateTime I have problems.
Here is an example that works fine in Query analyzer but fails when executed in ASP.NET.
"select top 10 * from Subscribers where StartDate < '09-03-2002'"
In Query Analyzer no problem, in ASP.NET I get this error...
Line 1: Incorrect syntax near ';'
However if the Query uses '=' instead of a '<' or '' then it works without error
"select top 10 * from Subscribers where StartDate ='09-03-2002'"
Any help would be appreciated

View Replies !

---

Problem: Replication Fails Over Modem But Works On LAN
Hi,I am having a problem with a replication over a Modem-Connection,which works fine over LAN. Has anyone experienced this problem before?Settings are:2 SQL Servers 2000, SP3on Windows 2000Publischer Database ist about 3GB, Subscriber DB about 1,5GBPublisher and Subscriber are connected via 56Kbit Dial-Up--> The Replication worked fine now this way for about 6 Months now!overall the publisher has about 20 Subscribers all connected viadial-up and they all work fine.Behavior now is like this:- Dial Up works fine- Synch starts- Synch takes some time (Upload works fine)- Synch fails- Error Message(german):Der Prozess konnte die Zeilenmetadaten auf 'Subscriber' nichtabfragen.{call sp_MSgetmetadatabatch(?,?,?)}Allgemeiner Netzwerkfehler. Weitere Informationen finden Sie in derDokumentation über Netzwerke.Der Prozess wurde erfolgreich beendet.If I connect the Subscriber-Server directly to the publisher servervia LAN it all works fine.My thesis is, that there might be some kind of "timeout" or something,as it cant be the dial-up network, because we tried different modemsand different server locations. And it all works fine on most of theother connedted subscribers. Only 2 other very big subscribersexperience the same problem, therefore i assume it could havesomething to to with slow connection speed, timeouts and an inabilityof the SQL server to handle this.Anyone seen this before? Anyone can help me?Thanks and Best Regards,Gerd

View Replies !

---

DTS Import Works Directly, Fails When Scheduled
I'm new to DTS packages, but managed to create one that successfully empties a table in SQL Server then imports data from a Foxpro file on another server into it. It runs fine if I execute it from DTS, but fails if I schedule it to run in SQL Server Agent (using the "Schedule Package" option in DTS). I think the relevant portion of the error returned when the job fails is:

Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'hrpersnl.dbf' does not exist.

The file does exist. I also tried to execute it from a stored procedure, but got a similar error. Any thoughts on why it runs one way but not the other?

TIA

View Replies !

---

WebRequest Over HTTPS Fails Under SQLCLR, But Works Elsewhere
I have some code in a SQL CLR stored procedure that calls out to a web service at UPS to obtain tracking information for a package.

The code fails on the last line, in the call to WebRequest.GetRequestStream(), with the following exception:

"System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetRequestStream()
   at UPSTracking.TrackShipment(String TrackingUri)"

Here is the relevant code:

WebProxy proxyObject = new WebProxy();
WebRequest request = WebRequest.Create("https://www.ups.com/ups.app/xml/Track");
request.Proxy = proxyObject;
request.Method = "POST";
request.ContentLength = strXMLData.Length;
request.ContentType = "application/x-www-form-urlencoded";
StreamWriter sw = new StreamWriter(request.GetRequestStream());


This same code works just fine when not running in the SQL CLR assembly. The assembly is marked as External, but I've also tried marking it as Unsafe, which did not work either.

Any suggestions on what the problem might be or how to troubleshoot this further would be greatly appreciated.

Thanks,

Steve F.

View Replies !

---

HTTP Connection From Iis Fails But Works From Excel
Hi,
 
I can use Http connection to Analysis Services 2005 from Excel but when I try to use IIS it fails.
AS and IIS are on different machines and not in the same domain.
 

In the event log of the server with AS i can see following event:
 

Event Type: Information
Event Source: MSOLAP ISAPI Extension: \?C:Inetpubwwwrootolapmsmdpump.dll
Event Category: (269)
Event ID: 10
Date:  5/20/2008
Time:  10:17:02 AM
User:  N/A
Computer: *******
Description:
The description for Event ID ( 10 ) in Source ( MSOLAP ISAPI Extension: \?C:Inetpubwwwrootolapmsmdpump.dll ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: .


I assume it's permission issue. Excel and IIS must be using different user to connect to AS. I tried with different authentication methods on the iis but without success.
 
How can I make IIS connect with proper permissions?
 
Dariusz

View Replies !

---

Login Fails For FQDN But Works For IP Address
When attempting to connect to Reporting Services (SQL2005) with FQDN, the logon prompt fails after 3 attempts:
http://sqlReportServer.mydomain.net/ReportsManager/Pages/Folder.aspx
 
If use the IP, it prompts for credentials and works:
http://192.168.0.23/ReportsManager/Pages/Folder.aspx

 
 

I have checked the SQL instance name and seems good. RDP by FQDN works fine, etc. Any hints?
CW
--

View Replies !

---

DB Update Fails In Program, Works In Management Studio?
I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement.
All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To  ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing'
           
As you can see, nothing that complex. The line that is causing the problem is the case: 
custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To  ELSE custType END 
all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value. 
Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page.
No exceptions are being thrown, it just doesn’t update the data. 
Any ideas? 
Thanks
 

View Replies !

---

Login Fails For Network SQL Server But Works For Localhost
I have an ASP.NET webform:This connection works:   "Server=localhost;uid=sa;pwd=;database=pubs"but this connection DOES NOT work:  "Server=dnrsqlt1;uid=sa;pwd=;database=pubs"dnrsqlt1 is a sql server my network.Do I have to do something to users ASPNET or  IUSER_Machinename on the remote machine

View Replies !

---

Procs Fails In MS Access, Works In Management Studio
I have a stored procedure which is run through MS Access (yuck). It does not appear to fail, but it does not populate certain tables, and is also rather complex. I did not write it, and am trying my best to fix it. However, when I run the same procedure with the same parameters from within Management Studio (database state is the same, I restore from backup before trying out each execution) it populates the tables correctly. I have profiled both executions (from MS and from Access) and it is running with the same NTUsername, and thus the same permissions. The process used to work and then some changes were made to the DB which seem to have broken it from Access. As far as I know, the Access code has not changed, although it may have done so. The proc call from Access uses the same parameters, and does not throw an error, although it does not appear to close the connection to the DB (I'm looking into this). Access uses an ODBC connection to connect to SQL Server.

Any ideas?

View Replies !

---