MS SQL Server :: SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare

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 !

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 !

Updating A Partitioned View In A Cursor
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?

Thanks

View Replies !

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

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 !

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 !

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 !

Incorrect Query Plan With Partitioned View On SQL 2000
I have a partitioned view containing 4 tables (example follows at end)

The query plan generated on a select correctly accesses just one of the tables

The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area

Note that the behaviour is the same with SP1 on SQL2000

I would be very grateful for any advice

Thanks

Stefan Bennett

Example follows

--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO

INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO

--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO

--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'

-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;

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 !

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 !

DTS Works, Job Fails!(data Source Foxpro And Destination SQL Server 2000
The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0

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 !

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

USE [RC_STAT]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN]

(@Region int = Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice' as Document_Type

,[Item_Ledger_Document_No] Document_Number

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

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

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

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

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE Region.Region_Key = @Region

AND [Item_Ledger_Mars_Year] = 2007

AND [Item_Ledger_Amount]= 0

UNION ALL

SELECT

[GL_Entry_Posting_DateTime]

,Budget_Reporting.[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting.Budget_Reporting_Group_ID

,Budget_Reporting_Group.Budget_Reporting_Group_Description

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

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

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

INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting As Budget_Reporting

ON Budget_Reporting.Salesperson_Purchaser_Code = Salesperson_Purchaser.Salesperson_Purchaser_Code

INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group As Budget_Reporting_Group

ON Budget_Reporting_Group.Budget_reporting_Group_ID = Budget_Reporting.Budget_Reporting_Group_ID

WHERE GL_entry.[Mars_Year] = 2007

END

View Replies !

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

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

View Replies !

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

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

BEGIN

IF @DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

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

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

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

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

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

View Replies !

DECLARE CURSOR
Is there any way to create a cursor, based on a dynamically created select_statement? Something like:
DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect
where @strSelect is previously declared as let's say varchar.
I don't want to create a stored procedure for this.

Thanks!

View Replies !

Help With Declare And Cursor

I keep getting the message

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'declare'.

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'declare'.

What am I doing wrong?

declare @dbname varchar(8000),

declare @countyname varchar (200) ,

declare @sql varchar(8000)

declare county_name cursor for

select distinct county from Zipcodes

open county_name

fetch next from county_name

into @countyname

declare dbname_name cursor for

select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'

open dbname_name

fetch next from dbname_name

into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql =

'

select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'

from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'

exec (@sql)

end

set @sql = ''

fetch next from dbname_name into @dbname

fetch next from county_name into @countyname

CLOSE county_name

DEALLOCATE county_name

CLOSE dbname_name

DEALLOCATE dbname_name

View Replies !

Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

View Replies !

Declare Cursor With Dynamic SQL?
Hello..

Can you declare a cursor with dynamic SQL?

I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.

these attempts did not work:

DECLARE crsCursor CURSOR FOR @vchrSQL
DECLARE crsCursor CURSOR FOR (@vchrSQL)

Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?

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 !

Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View Replies !

Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'

Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'

but I can't seem to come up with the right statement.

Any help greatly appreciated.

Thanks,
Judith

View Replies !

Declare Cursor For Execute Stored_procedure
Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.

View Replies !

How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.

CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

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 !

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

This code is not working.

please suggest

Nitin

View Replies !

Partitioned View
Hi! This is my first post and I really need help with Partitioned View. I'm using Sql Server 2000 and I created a partitioned view using 6 tables and now a need to create the table '7' and alter the view. But when i'm trying to insert new data i'm receiving the message: :eek:
"Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition
contains a disallowed construct."

My code is:

drop VIEW tb_sld_cob_pap
GO
CREATE TABLE dbo.tb_sld_cob_pap_7 (
cod_operacao int NOT NULL ,
cod_contrato int NOT NULL ,
sequencial_duplicata int NOT NULL ,
data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
liqex_dia_nom_outros float NULL ,
liqex_dia_moe_outros float NULL,
constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
)
GO
CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
GO

CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
GO

ALTER TABLE dbo.tb_sld_cob_pap_6
DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
GO

ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))
GO

create VIEW tb_sld_cob_pap
as
select * from tb_sld_cob_pap_1
union all
select * from tb_sld_cob_pap_2
union all
select * from tb_sld_cob_pap_3
union all
select * from tb_sld_cob_pap_4
union all
select * from tb_sld_cob_pap_5
union all
select * from tb_sld_cob_pap_6
union all
select * from tb_sld_cob_pap_7

My table tb_sld_cob_pap_6 does NOT have data with ([data_sld_pap] >= '20060201').
I'm using this script in other database and I don't have this problem.

Thank you...

View Replies !

Partitioned View
USE Northwind
GO

CREATE TABLE myTable99_1 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
CREATE TABLE myTable99_2 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
CREATE TABLE myTable99_3 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))

CREATE INDEX myTable99_1_IX ON MyTable99_1
(Account, Ledger)
CREATE INDEX myTable99_2_IX ON MyTable99_2
(Account, Ledger)
CREATE INDEX myTable99_3_IX ON MyTable99_3
(Account, Ledger)
GO

CREATE VIEW myView99
AS
SELECT Account
, Ledger
, PostDate
FROM myTable99_1
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_2
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_3
GO

SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
GO

DROP VIEW myView99
DROP TABLE myTable99_1, myTable99_2, myTable99_3
GO

OK, so I thought I knew this, but I'm looking for parallelism...not only am I no getting it, I'm getting an Index scan....is it becuse I didn't put any data in the table? I thought it would stil show my index seek with parallelism

What up, homey?

View Replies !

Partitioned View
I have a big table with about 40 million rows. Questions I have are:
1. Do I need to split this table into several small tables and then create a partitioned view or would one big table good enough for performance?
2. If I create a partitioned view then does that mean I have to continually adding tables to this view since there will be new records that need to be added? Is there a way to automatically create new tables to be added to the partitioned view?

View Replies !

Partitioned View On SS2000
There has been a functional change under SS2000 such that a partitioned view needs to be partitioned on a primary key.
Under SS7 we had the data logically divided into separate tables based on an identifier. As a single table this is over 70Gb, but this breaks down into 18 individual tables within a view.
The performance under SS7 was very good as the query would only look at relevant tables, but SS2000 now looks at all the tables in the view.
There is an example of the problem below and I would very much appreciate any constructive contributions towards finding a resolution to this.
Create table table1 (f1 char(10), f2 int)
Create table table2 (f1 char(10), f2 int)
GO
Alter table table1 with check add constraint chk_table1_f2 check (f2 = 1)
Alter table table2 with check add constraint chk_table2_f2 check (f2 = 2)
GO
insert into table1 values ('aaa',1)
insert into table1 values ('bbb',1)
insert into table1 values ('ccc',1)
insert into table2 values ('xxx',2)
insert into table2 values ('yyy',2)
insert into table2 values ('zzz',2)
GO
create view tableview as
select * from table1 union all
select * from table2
GO
-- the execution plan under SS7 shows that only table1 will be scanned
-- for the following query, whereas under SS2000, both tables are
-- scanned.
select * from tableview where f2=1

View Replies !

More Than One Column In Partitioned View??
Hello..

Im having trouble to make a partitioned view when I use more than one column in check constraint!

Should I use check constraint on all the primary keys? or is't possible to have 5 primary and check constraints on 2 of them ?

I've read
Creating a partitioned view (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp?frame=true)

But i cannot see limitations on this???

View Replies !

Partitioned View Misbehaving!
Hi all,

I have a partitioned view on SQL Server 2000 containing 4 tables (example below)

The query plan generated on a select correctly accesses just one of the tables

The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area?

Note that the behaviour is the same with SP1 on SQL2000

Example follows

--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO

INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO

--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO

--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'

-- Look at execution plan for update
-- This accesses all partitions - Why - it includes the partition key?

update sales_national
set total = 100
where ordernum = 23456 and
region = 'South';

I would be very grateful for any advice

Thanks

Jaishel.

View Replies !

Partitioned View Problem
Hi all,

I am designing 3 p:artitioned views for 3 tables. Those tables grow up in 1.5 millions of rows per month (each one), so I decided to partition those tables monthly. The issue is that if I want to create the views with more than 256 months (256 tables) SQL Server says: 'Server: Msg 106, Level 15, State 1, Procedure Jugadas, Line 258Too many table names in the query. The maximum allowable is 256.'

Is there any workaround for this?

Another solution maybe?

PD1: I've tested with less than 256 tables and it works fine, I can update and query the tables (except for a couple of querys where I've got to join 2 or more of the involucred views in which case I got a similar error saying about a 260 table limit).

View Replies !

Partitioned View Question

I have a table that I'm trying to scale out into a partitioned view. It's about 30 million rows. It's a workflow table and I have a taskID in the table. Originally the table was partitioned on this column but performance still wasn't what I wanted it to be, so we figured out how we could partition on a bit flag of IsOpen.

Question #1) Anyone know a best practice for creating apartitioned views on multi-columns?

What I'd like to try to do to lower the complexity of the original partitioned view is to create a view of partitioned views. Is this even possible (This is Q#2, BTW).

View Replies !

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...

Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:

Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--    interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     -- SET @errmsg = 'Unsupported reporting format:' + @authCode
    -- RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
        ,ImportedReportID
        ,AuthorityCode
        ,[ID]
        ,[Field1] AS RecordType
        ,[Field2] AS FormType
        ,[Field3] AS ItemID
        ,[Field4] AS EntityCode
        ,[Field5] AS LastName
        ,[Field6] AS FirstMiddleNames
        ,[Field7] AS Title
        ,[Field8] AS Suffix
        ,[Field9] AS AddressLine1
        ,[Field10] AS AddressLine2
        ,[Field11] AS City
        ,[Field12] AS [State]
        ,[Field13] AS ZipFull
        ,[Field14] AS OutOfStatePAC
        ,[Field15] AS FecID
        ,[Field16] AS Date
        ,[Field17] AS Amount
        ,[Field18] AS [Description]
        ,[Field19] AS Employer
        ,[Field20] AS Occupation
        ,[Field21] AS AttorneyJob
        ,[Field22] AS SpouseEmployer
        ,[Field23] As ChildParentEmployer1
        ,[Field24] AS ChildParentEmployer2
        ,[Field25] AS InKindTravel
        ,[Field26] AS TravellerLastName
        ,[Field27] AS TravellerFirstMiddleNames
        ,[Field28] AS TravellerTitle
        ,[Field29] AS TravellerSuffix
        ,[Field30] AS TravelMode
        ,[Field31] As DptCity
        ,[Field32] AS DptDate
        ,[Field33] AS ArvCity
        ,[Field34] AS ArvDate
        ,[Field35] AS TravelPurpose
        ,[Field36] AS TravelRecordBackReference
    FROM ImportedNativeRecords
    WHERE ImportedReportID IS NOT NULL
    AND ReportType IN ('RCPT','PLDG')
    ORDER BY ImportedRecordID -- this should work but gives syntax error!
    );

END

View Replies !

Will This Query Be Optimized For A Partitioned View?
Hello :-)My question is: If I query a partitioned view, but don't know the valuesin the "where x in(<expression>)" clause, i.e.: select * from viewAwhere intVal in(select intVal from tbl1) . Compared to: select * fromviewA where intVal in(5,6).Of course "intVal" is partitioning column.Will this result in an optimized query that searches only the relevanttables?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Partitioned View Performance Question
I'm fairly new to partitioned views, but am implementing one in test to see if it would be a worthy candidate for production.

The test server is basically a workstation with 2 drives, one for data, one for logs. There is a fairly large table (87M rows) and a partitioned view written against multiple tables with columns matching those in the 87M row table.

To mirror a typical day in production, I needed to insert about 2.5M rows into each (the big table, and the view). However, when doing so, I didn't get the performance increase I'd expected.

Inserting the 2.5M records into the 87M row table took approximately 6.5 minutes. The insert into the view took approximately 2 hours and 52 minutes.

This seems absurd. The data added is heavily based on one particular date (ie; most of the data will be for one date, with maybe 1% of the data being of other dates). The tables behind the view are broken up by date. So, most of the data would have went into one table, with a small percentage going into other tables.

Default Fill Factors were used, no out of the ordinary tuning done anywhere. There's an index on the date field in the 87M row table, but that's about it.

I'm confused, any ideas?

View Replies !

Bulk Insert In To A Partitioned View?
Greetings once again my SQL friends,

I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :

Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation

How to solve this problem?

View Replies !

Partitioned View && Computed Column..
Hello,

please enlighten me regarding an issue with partitioned view... There are 3 tables in my DB of a similar structure:

CREATE TABLE Table1 (value1 varchar(1))
CREATE TABLE Table2 (value1 varchar(1))
CREATE TABLE Table3 (value1 varchar(1))

INSERT INTO Table1 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

INSERT INTO Table2 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

INSERT INTO Table3 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

As sometimes we need to access all data from these tables, a view has been created:

CREATE VIEW AllData AS
SELECT value1, '1' as table_id from Table1
UNION ALL
SELECT value1, '2' as table_id from Table2
UNION ALL
SELECT value1, '3' as table_id from Table3

The problem is that while running a query like

SELECT * from AllData WHERE value1 = 'a' and table_id = '3'

I see a table scan being performed on all 3 tables, not just table3 - i.e optimisation engine doesn't care for my table_id computed column and for that fact that required data is located ONLY in Table3.

Is there any way to force optimiser to consider this column andrrebuild a plan? If not - how can I rebuild a view (I can't modify tables) to achieve that? Maybe create an index for a view?

Thanks in advance. RTFM and search don't seem to clarify this for me...

View Replies !

Optimization Of Query On Partitioned View
Schema below.

The execution plan shows that this query is correctly optimized to check only the underlying Employee_2008 table.

select * from Employee where ReportingYear = '2008'

This query is not optimized and checks both Employee_2008 and Employee_2007:

declare @ry varchar(4)
set @ry = '2008'
select * from Employee where ReportingYear = @ry

How can I get second query to be optimized correctly?

Schema:

CREATE TABLE [dbo].[Employee_2007](
    [EmployeeID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Employee_2008](
    [EmployeeID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE VIEW [dbo].[Employee]
AS
SELECT
'2007' ReportingYear,
EmployeeID,
Name
FROM Employee_2007

UNION ALL

SELECT
'2008' ReportingYear,
EmployeeID,
Name
FROM Employee_2008

View Replies !