Job Doesn't Work But Package Works Fine

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 !

---

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 !

---

Pagination And Interactive Sort For Drilldown Report Doesn't Work Fine
Hi Anyone,

 

I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.

 

Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.

 

Anyone has experience on that?

Thanks a lot

View Replies !

---

Scheduled Job Doesn't Work, But Works Manually
I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?

I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.

Can anybody help me?

Thank's!

View Replies !

---

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430' 
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View Replies !

---

DateAdd Expression Works In Tsql But Doesn't Work In Ssis
Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?

 

dateadd("dd", -1, datediff("dd", 0, getdate()))

 

Thanks.

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 !

---

'Run Package' Works On Server, But Doesn't Complete As Job.
 

I have an SSIS package is made up of SQL tasks and dataflows. The dataflows connect to an Oracle database using Native OLE DBOracle Provider for OLE DB (10g). This is the first package dealing with oracle that runs on the server.
I can execute the package manually by right clicking and going to 'Run Package' while logged in remotely from the server, but it gets hung up and does nothing if I run it as a job. I always have to quit the job. I can disable everything but the dataflows in the package and the job completes and runs fine.
 
Anyone have any ideas or similiar situations?
 
Thanks.

View Replies !

---

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?
 

Hello everybody,

 

I'm developing a report using the following structure :

 

declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)

 

set @sql = 'select ....'

 

If <conditional1>

begin

        set @where = 'some where'

end

 

If <conditional2>

begin

        set @where = 'some where'

end

 

set @sql = @sql + @where

 

exec(@sql)

 

I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.

 

I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?

 

Thanks,

 

Maurício

View Replies !

---

Package Succeeds, But Doesn't Work When Run As Job
I have an SSIS package that has this logic:
 
Map drive(batch file)

      For each file (csv) loop

      Pump file data into sql server
      Move file to "archive" directory(file system task)
     Delete File (file system task)
End Loop
 

Unmap Drive (batch file)
 
The Map/unmap code is in a batch file
c:windowssystem32et use \10.10.10.10ShareName MyPassword /USER:MyUserName /YES
 
Unmap:
c:windowssystem32et use \10.10.10.10ShareName   /DELETE /YES
 
Here are the results when running this package:
1. Running in BIDS on separate workstation. Everything OK.
2. Running on Server by right clicking on package in Integration Services (SSMS) and choosing  "run". Everything OK
3. Running as job with SQL Agent: Package succeeds but no action was taken on the files, files in "ShareName" still there, so therefore no data pumped into SQL Server.
 
Now, the difference is the SQL Agent jobs are running using a domain account proxy. I'm not sure how that would affect things though--I have the tasks in the package set to fail the package if they fail, so they are not failing, the drives are being mapped o.k.
 
The computer with the share is non-domain, but that shouldn't matter--I am specifiying the local username and password in the batch file as you can see, and as you can see it works from the workstation in BIDS on a separate machine, and works on the server too as long as I don't run it as a job. The batch file sits on both the server and the local workstation with the same local path.
 
Any idea why the files aren't actioned when run as a job?
 
Thanks,
Kayda

 
 
 

View Replies !

---

Saving DTS Package Appears To Work But Doesn&#39;t
I have several DTS packages saved 'locally' to the SQL server. I want to duplicate a package, so that I can make some changes then replace the original. I certainly don't want to rebuild the entire package from scratch. So, I open up the original package, go to the 'Package' menu and choose 'Save As', then give it a new name and press OK. No errors, all appears well, the title bar even shows the new name of the package. But, when I close the package and go the the 'local' package list, it (the new package name) doesn't appear in the list. Refresh, exit SEM, reboot - doesn't show up. I even looked in the MSDB table where packages are supposed to be stored (at least the name / package id / etc), and it doesn't show there as well. Tried from several client machines.

OS: Windows 2000 Server (advanced) SP2
SQL: SQL 2000 Server (no SP's)

Any help would be great.

Bryan Parke

View Replies !

---

Migrate DTS 2000 Package Doesn't Work
Hi everyone,

I'm stuck with this and I haven't idea how to solve it. I'm trying to migrate a dts 2000 package from BIDS and I obtain this message:

This wizard will close because it encountered the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

 

I'm going to Migrate DTS 2000 Package select my current sql2k production server (it has almost 600 dts although I think that is not any problem at all)

Wizard recognize without problems my server and then I put a folder for save them but on the next step appears the aforementioned message.

Does anyone ever faced this issue?

Thanks in advance,

View Replies !

---

Passing A Variable Between A Parent And Child Package Doesn't Work
Has someone managed to pass successfully a variable from a parent package to a child package? I€™ve tried a zillion permutations and I can€™t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is:
 

The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table.
I created a child package variable with the same name.
In the child package, I€™ve created a parent package configuration that points to the ExecutionID variable.
I am trying to read the variable in a Derived Column Task in which I have a column linked to @ExecutionID.
This doesn€™t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated.
 
Oh, I didn€™t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.

View Replies !

---

Pass-thru Query Doesn't Work With A SQL 2005 SSIS Package
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005.  I saved the package in SQL 2005 and tested it out.  If I run it from the Management Studio Console with Run->Execute ... everything works just fine.  However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode).  The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1".  The error is listed below:
 
   Code: 0xC0202009
   Source: NewPackage Connection manager "SourceConnectionOLEDB"
   Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Could not use ''; file already in use.".
 

What am I doing wrong?
 

View Replies !

---

Package With File System Task Doesn't Work Without Sensitive Data With User Keys
This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

Report Work Fine In IE But Not In Mozilla
Hi,

I have created some Sql Server Reports. The display of these reports works fine in IE but gets scattered when opened in Mozilla.
Is there any special setting needs to be done?

Reply would be appreciated.

Regards,
Ruchir

View Replies !

---

Transact SQL AND Works, OR Doesn't
I have a stored procedure with a where clause like this:
WHERE
  Q.EffectiveDate >= @FromEffectiveDate  AND   Q.EffectiveDate <= @ToEffectiveDate AND   I.InsuredName LIKE '%' +  isnull(@PreQuoteDesc,I.InsuredName) + '%'  AND     isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'   AND   rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND  rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND   Q.quoteID  = isnull(@quoteID,Q.QuoteID) AND   Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND  rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND     isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND  (   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks

View Replies !

---

ReplMerge.exe Works But RMO Doesn't -
I have been successful in getting a Merge Replication to happen via the web using ReplMerge.exe, but unsuccessful in doing the same using RMO.

 

The following code is what I am using for merge replication via the web using the ReplMerge.exe and this is working fine.

 

replString = "C:Program FilesMicrosoft SQL Server90COMREPLMERG.EXE"

Dim procID As Integer

Dim newProc As Diagnostics.Process

Dim si As Diagnostics.ProcessStartInfo

si.WindowStyle = ProcessWindowStyle.Hidden

newProc.StartInfo = si

newProc = Diagnostics.Process.Start(replString, "-Publication pub_mergetest

-Publisher publishername

-Subscriber subscribername

-Distributor distributorname

-DistributorLogin sa

-DistributorPassword pwd

-DistributorSecurityMode 0

-PublisherDB MergeTest-Publisher

-SubscriberDB MergeTest-Subscriber

-PublisherLogin sa

-PublisherPassword pwd

-SubscriberLogin sa

-SubscriberPassword pwd

-SubscriptionType 1

-SubscriberSecurityMode 0

-LoginTimeOut 30

-ParallelUploadDownload 1

-QueryTimeOut 300")

 

procID = newProc.Id

newProc.WaitForExit()

Dim procEC As Integer = -1

If newProc.HasExited = -1 Then

procEC = newProc.ExitCode

End If

 

However, I cannot get the same to work via RMO. The error i get back is "The process could not connect to subscriber "subscribername".  What am I missing? Any help and feedback is greatly appreciated.

 

Dim _mergeAgent As MergeSynchronizationAgent = New MergeSynchronizationAgent()

_mergeAgent.Publication = "pub_mergetest"

_mergeAgent.Publisher = "publishername "

_mergeAgent.Subscriber = "subscribername"

_mergeAgent.Distributor = "distributorname"

_mergeAgent.DistributorLogin = "sa"

_mergeAgent.DistributorPassword = "pwd"

_mergeAgent.DistributorSecurityMode = SecurityMode.Standard

_mergeAgent.PublisherDatabase = "MergeTest-Publisher"

_mergeAgent.SubscriberDatabase = "MergeTest-Subscriber"

_mergeAgent.SubscriberLogin = "sa"

_mergeAgent.SubscriberPassword = "pwd"

_mergeAgent.SubscriberSecurityMode = SecurityMode.Standard

_mergeAgent.SubscriptionType = SubscriptionOption.Pull

_mergeAgent.PublisherLogin = "sa"

_mergeAgent.PublisherPassword = "pwd"

_mergeAgent.PublisherSecurityMode = SecurityMode.Standard

_mergeAgent.LoginTimeout = 30

_mergeAgent.QueryTimeout = 300

_mergeAgent.UseWebSynchronization = True

_mergeAgent.InternetUrl = "https://###.###.###.###/virtualdirectory/replisapi.dll"

_mergeAgent.InternetLogin = "domainusername"

_mergeAgent.InternetPassword = "pwd"

_mergeAgent.InternetTimeout = 5000

_mergeAgent.Synchronize()

 

View Replies !

---

CLI Connection To SQL Works, C++ Doesn't
I'm trying to connect to my SQL, and both ways I tried with CLI work.  However, I have no luck with native C++, with the same string.  Any ideas why?  Here's the error message I get, and the source.

IM008
[Microsoft][ODBC Driver Manager] Dialog failed







Code Snippet

#include<iostream>
#include<windows.h>
#include<sql.h>
#include <sqlext.h>
using namespace std;
#using <mscorlib.dll>
#using <System.dll>
#using <System.Data.dll>
using namespace System::Data;

void main()
{
    System::Data::SqlClient::SqlConnection^ cSql=gcnew System::Data::SqlClient::SqlConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ");
    cSql->Open();
    cSql->Close();
    System::Data::OleDb::OleDbConnection^ cOledb=gcnew System::Data::OleDb::OleDbConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
    cOledb->Open();
    cOledb->Close();

    SQLRETURN iRet;
    SQLHANDLE EnvHndl;
    SQLHANDLE ConnHndl;
    iRet = SQLAllocHandle(1, 0, &EnvHndl);
    iRet = SQLSetEnvAttr(EnvHndl, 200, (SQLPOINTER)3, 0);
    iRet = SQLAllocHandle(2,EnvHndl, &ConnHndl);

    wchar_t ConnStr[255];
    wcscpy(ConnStr,L"Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
    short ConnStrLength=wcslen(ConnStr);
    iRet = SQLDriverConnect(ConnHndl, 0, (wchar_t*)ConnStr, ConnStrLength, (wchar_t*)ConnStr, 255, &ConnStrLength, 1);

    long NativeError;
    wchar_t*Msg=new wchar_t[10000];
    wchar_t*SqlState=new wchar_t[10000];
    int iParDiag=1;
    short MsgLen=10000;
    iRet = SQLGetDiagRec(2, ConnHndl, iParDiag, SqlState, &NativeError, Msg, 512, &MsgLen);
    wcout<<ConnStr<<L'';
    wcout<<SqlState<<L'';
    wcout<<Msg<<L'';
}

View Replies !

---

Subscription Reports Fail To Email While Others In The Same Batch Work Fine.
SSRS 2000
Data driven subscription uses a SQL query to create report parameter values. 
 
The subscription errors are inconsistent meaning sometimes no reports in a batch fail and sometimes 3 out of 5 will fail.  I don't think it is data related because I'm using the same data each time.
 
When a report fails to email there is a consistent error related to rendering a chart.  That message is in red below.
 
The data source is Analysis Services 2000.  Has anyone seen this before?  I've seen a lot of postings that are similar but no solutions.  Is it possible for RS to lose data or get corrupt data from an MDX query?
 
Is there a timeout between RS and Analysis Services (PivotTable Service) to check?
 
SSRS trace log:
 
ReportingServicesService!runningjobs!237c!10/25/2007-14:15:01:: w WARN: Thread pool pressure: turning off threads
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: System.ArgumentException: Invalid parameter used.
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
   at Dundas.Charting.WebControl.ChartImage.GetImage()
   at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
   at Dundas.Charting.WebControl.ChartImage.GetImage()
   at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
   --- End of inner exception stack trace ---
ReportingServicesService!reportrendering!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
 Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
   at Dundas.Charting.WebControl.ChartImage.GetImage()
   at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
   at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
   at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
   --- End of inner exception stack trace ---
ReportingServicesService!library!237c!10/25/2007-14:15:01:: i INFO: Initializing EnableExecutionLogging to 'True'  as specified in Server system properties.
ReportingServicesService!emailextension!237c!10/25/2007-14:15:01:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
   at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
   at Dundas.Charting.WebControl.ChartImage.GetImage()
   at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
   at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
   at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
   at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
   at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
   at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
   at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
   at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
   at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
   at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
   at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)
   at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
   at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
   at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Data Driven Notification for activation id 1ab1f67c-82c0-4dba-9745-a4057ab1cb4f was saved.
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
ReportingServicesService!notification!237c!10/25/2007-14:15:01:: Notification 15307c1b-0b60-493d-b2c1-fde89780ff06 completed.  Success: False, Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., DeliveryExtension: Report Server Email, Report: SalesPerfScorecard_Email, Attempt 0
ReportingServicesService!dbpolling!237c!10/25/2007-14:15:01:: NotificationPolling finished processing item 15307c1b-0b60-493d-b2c1-fde89780ff06

View Replies !

---

Can't Connect From A Vista Client To SQL 2005 Box,other Machines Work Fine
Hey. I've a windows vista machine which can't connect to SQL 2005 box. Other machines which are lower than vista work fine. Also, vista box can connect to other SQL 2005 instances but can't connect to only one instance. But, I can connect to the instance fine from my XP box. Also, it's not a permissions issue. This user is an SA and can connect form some other machine. SQL 2005 box is at 2047 version right now. And, the user is able to connect to some other boxes which are also at 2047. Please help. Thank you

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 !

---

DataSet Works... DataTable Doesn't... (ODBC)
I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet.  For example:    'oConn is defined elsewhere...        Dim sErr as String = ""        Dim dt As New DataTable        If Not oConn Is Nothing Then            Try                Dim sSQL as String = "select 1"                Dim oCommand As New OdbcDataAdapter(sSQL, oConn)                oCommand.Fill(dt)            Catch ex As Exception                sErr = "Database Error: " & ex.Message            Finally                sqlCloseConnection(oConn)            End Try        End Ifthis works fine and my dt DataTable object gets one row.  However using this as the SQL:                Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work.   It executes with no errors, but the DataTable has no rows.  Finally, if I replace the DataTable with:                Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable.  Only when it's a SP or sql with some logic does it require the DataSet approach.  This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?  

View Replies !

---

My SP Works On 2005 Bit Not On 2000 It Doesn't Step Through My Code.
 

Hi
 
I have a SP that works on SQL 2000 but not on 2005
 
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
 
here is apiece of my code. I hope it Helps.

 
 
 



Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @numValues

SET @CurrentValue = (SELECT SCOPE_IDENTITY())
IF @ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@CurrentValue, @ClientID, @UsergroupID)
END
IF @DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@CurrentValue, 1, @DateOfBirth)
END
-------------------My Code Stops here ------------------------------
IF @Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 1)
END
IF @Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 3)
END
 
 
Any help would be greatly appreciated
 
Kind Regards
Carel greaves

View Replies !

---

SQL Query That Works In SQL Server Management Studio, But Doesn't On .NET 2.0 Page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is? 

View Replies !

---

Select Records Between Dates - Query Works In VS 2005 But It Doesn't In Asp 3
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance

View Replies !

---

Windows App Form Works: Datagrid Doesn't 'login Failed' ..tutors Out There?
Anyone live in seattle (meet on cap hill)? I have been stuck for weeks now and can't wait any longer wasting time - i will pay someone to set me up correctly quickly. I doubt this will be resolved online, but here goes... I need a datagrid to bring up simple database info (northwind) instead of showing the error 'login failed for mycomputeraspnet'. I am using windows authentication and my string is connectionstring = datasource=(local)\netsdk;initial catalog=Northwind;integrated security=sspi; persistent security=false;, I have seen online they say alter the authorization in the app virtual directory-- is that the C/inetpub/wwwroot or the web config of my project? I did so in the web config of my project, and also I added the impersonate=true to same already. I shared all the folders and made sure they are not read-only. I am flabbergasted.
Paul
XP pro 2004 sp 2
vis studio 2002 w updates
C# Asp web app project

View Replies !

---

Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

View Replies !

---

Please Help.. Can't Get ASP.NET App To Work On Web Server, Works On Local System
I have an application I developled for a shopping cart function using Wrox ASP.NET book as a guide. I have everything working great on my local system using SQL 2005 Express. When I move the app to an IIS 6.0 Web Server with SQL 2005 Express installed I get the following error.
Line 103:    Public Overridable Property Cart() As Wrox.Commerce.ShoppingCartLine 104:        GetLine 105:            Return CType(Me.GetPropertyValue("Cart"),Wrox.Commerce.ShoppingCart)Line 106:        End GetLine 107:        Set
It has some comments up top about not being able to establish a connection to the SQL Express Database. I have made the changes to allow TCP/IP and Named-Pipes
The App has to be connecting to the database because it displays grid views during the order process.
The Database is located in the App Data folder
IIS is set to Integrated Authentication and configured as a Web Application.
Can someone please tell me what is wrong.
thanks.....

View Replies !

---

SSIS Package Runs Fine Except As A Scheduled Job
I have an SSIS package created from a SQL 2000 DTS using the Migration Wizard. The package imports data from a MySQL database to a SQL 2005 64-bit database running on 64-bit windows server 2003. The package runs fine when executed from SQL Server Management Studio but when I schedule it as a job it fails with:

Executed as user: [the domain admin account]. The package execution failed.  The step failed.

I've tried a lot of different ways to make this work including creating a new SSIS package. Again, the package ran fine except when it was scheduled as a job.

Any ideas would be appreciated.

thanks!

View Replies !

---