Long Running Query In SQL 2005 But Works Fine In SQL 2000

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 !

---

Query Working Fine On 2000 But Not On 2005
Hello to all,

if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.

When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.

Here it is:







Code Snippet

SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2) 



NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest)
Any ideas?

Thanx

Alex

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 !

---

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 !

---

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

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

There error generated is:

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

FOR UPDATE cannot be specified on a READ ONLY cursor

 

Here is the cursor declaration:

 

declare some_cursor CURSOR

for

select    *

from       part_view

FOR UPDATE

 

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

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

View Replies !

---

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

View Replies !

---

Query Works In SQL 2000 But Not In SQL 2005
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM   VWCONTACT
WHERE  VWCONTACT.ACCOUNTID = 2615
       AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
       AND VWCONTACT.EMAIL LIKE '%@%'
       AND NOT EXISTS (SELECT VWCONTACT.ID
                       FROM   (SELECT DISTINCT VWCONTACT.[ID] ID
                               FROM   VWCONTACT
                                      JOIN (SELECT CONTACTID
                                            FROM   VWCONTACTATTRIBUTEVALUE
                                            WHERE  CONTACTATTRIBUTEID = 22943
                                                   AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                   AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
                                        ON VWCONTACT.ID = TMP0.CONTACTID
                                      JOIN (SELECT CONTACTID
                                            FROM   VWCONTACTATTRIBUTEVALUE
                                            WHERE  CONTACTATTRIBUTEID = 22943
                                                   AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                   AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
                                        ON VWCONTACT.ID = TMP1.CONTACTID
                               WHERE  VWCONTACT.ACCOUNTID = 2615
                                      AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
                       WHERE  VWCONTACT.ID = NESTEDQUERY1.ID)
       AND NOT EXISTS (SELECT VWCONTACT.ID
                       FROM   (SELECT DISTINCT VWCONTACT.[ID] ID
                               FROM   VWCONTACT
                                      JOIN (SELECT CONTACTID
                                            FROM   VWCONTACTATTRIBUTEVALUE
                                            WHERE  (CONTACTATTRIBUTEID = 22943
                                                    AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                    AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
                                                    OR (CONTACTATTRIBUTEID = 22943
                                                        AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                                                        AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
                                        ON ATTR.CONTACTID = VWCONTACT.ID
                               WHERE  VWCONTACT.ACCOUNTID = 2615
                                      AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
                       WHERE  VWCONTACT.ID = NESTEDQUERY1.ID)
       AND NOT EXISTS (SELECT VWCONTACT.ID
                       FROM   (SELECT DISTINCT VWCONTACT.[ID] ID
                               FROM   VWCONTACT
                                      JOIN (SELECT CONTACTID
                                            FROM   VWCONTACTATTRIBUTEVALUE
                                            WHERE  CONTACTATTRIBUTEID = 23102
                                                   AND ATTRIBUTEVALUE = 'Houston') TMP0
                                        ON VWCONTACT.ID = TMP0.CONTACTID
                                      JOIN (SELECT CONTACTID
                                            FROM   VWCONTACTATTRIBUTEVALUE
                                            WHERE  CONTACTATTRIBUTEID = 22944
                                                   AND ATTRIBUTEVALUE = 'TX') TMP1
                                        ON VWCONTACT.ID = TMP1.CONTACTID
                               WHERE  VWCONTACT.ACCOUNTID = 2615
                                      AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
                       WHERE  VWCONTACT.ID = NESTEDQUERY1.ID)

when I modify the query like the following in SQL Server 2005 it works.  Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM   VWCONTACT
WHERE  VWCONTACT.ACCOUNTID = 2615
       AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
       AND VWCONTACT.EMAIL LIKE '%@%'

EXCEPT

((SELECT DISTINCT VWCONTACT.[ID] ID
  FROM   VWCONTACT
         JOIN (SELECT CONTACTID
               FROM   VWCONTACTATTRIBUTEVALUE
               WHERE  CONTACTATTRIBUTEID = 22943
                      AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                      AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
           ON VWCONTACT.ID = TMP0.CONTACTID
         JOIN (SELECT CONTACTID
               FROM   VWCONTACTATTRIBUTEVALUE
               WHERE  CONTACTATTRIBUTEID = 22943
                      AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                      AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
           ON VWCONTACT.ID = TMP1.CONTACTID
  WHERE  VWCONTACT.ACCOUNTID = 2615
         AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
 UNION
 (SELECT DISTINCT VWCONTACT.[ID] ID
  FROM   VWCONTACT
         JOIN (SELECT CONTACTID
               FROM   VWCONTACTATTRIBUTEVALUE
               WHERE  (CONTACTATTRIBUTEID = 22943
                       AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                       AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
                       OR (CONTACTATTRIBUTEID = 22943
                           AND ISNUMERIC(ATTRIBUTEVALUE) = 1
                           AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
           ON ATTR.CONTACTID = VWCONTACT.ID
  WHERE  VWCONTACT.ACCOUNTID = 2615
         AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
 UNION
 (SELECT DISTINCT VWCONTACT.[ID] ID
  FROM   VWCONTACT
         JOIN (SELECT CONTACTID
               FROM   VWCONTACTATTRIBUTEVALUE
               WHERE  CONTACTATTRIBUTEID = 23102
                      AND ATTRIBUTEVALUE = 'Houston') TMP0
           ON VWCONTACT.ID = TMP0.CONTACTID
         JOIN (SELECT CONTACTID
               FROM   VWCONTACTATTRIBUTEVALUE
               WHERE  CONTACTATTRIBUTEID = 22944
                      AND ATTRIBUTEVALUE = 'TX') TMP1
           ON VWCONTACT.ID = TMP1.CONTACTID
  WHERE  VWCONTACT.ACCOUNTID = 2615
         AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))

View Replies !

---

How To Kill A Long Running Query Running On A Background Thread.
    
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?

The query would be running on SQL Server 2005 from a Windows form application using the Background worker component.  So the query would have been started from the background workers DoWork event using ado.net.  If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.

Is there a way to do this?

Thanks
 
 
 

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 2005 Thinks A SQL 2000 Backup Is Corrupt, But SQL 2000 Restores Just Fine
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message.  However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.

Any ideas?

Thanks
Mike Mattix

View Replies !

---

Long Running Query - Only From .NET
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table.  When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds.  When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help! 

View Replies !

---

Why Is The Query Running For So Long?
When I execute the following stored procedure it runs for about a minute.

CREATE PROCEDURE EquipmentListByProduct
(
@iProdTypeId int
)
AS

SET NOCOUNT ON
DECLARE@iError int, @iRows int

SELECT pn.prodTypeId, pn.prodId, pn.prodName
FROM prodNames pn
WHERE pn.prodTypeId = @iProdTypeId

SELECT@iError = @@ERROR, @iRows = @@ROWCOUNT
IF ( @iError <> 0 )
BEGIN
RETURN@iError
END

IF ( @iRows = 0 )
BEGIN
RETURN-1
END

RETURN@iError
GO


The table only has 22 records.
Do I need to index the table? If so how do I do this?

View Replies !

---

Long Running Query
i all,

I want to run my idea with you about the proposed architecture for
generating a
complex query.

Functionality
In QTD, Users will run a query that will take a long time to run and will
generate upto 5000 records.

A user chooses to either view the results of the query in their browser or
download the results from the web site in a CSV file (XML is out of the
question
here). For viewing the query, users will receive 1 page of records at a
time.
The next set of records is send when they scroll to the next page.

The proposed architecture to process this query is:

1. Users will save there the query criteria to a query table.
2. A SQL Server scheduled job will run the query and saves the results to a
permanent holding table. The job will create the CSV file if the user chose
the
download option.

Job Processing
The job performs the following processing (all functionality is implemented
in
stored procedures) described at a high level:

Loop for ever or until the job is terminated

sleep for 2 seconds (sleep is a configurable setting)

for each record in the query criteria table

do processing
...
...
next

end loop

Reasons For This Architecture
The reasons for choosing this architecture are:

Scalability
MTS and IIS are all about scalability.

Implementing this functionality in VB components creates a more simplified
architecture and will be easily to maintain. However, the more users that
are
running MTS components/ASP pages the more resources are used up and the
longer
transactions will take. The system must perform complex transaction
processing.
Moving large recordsets around the network and cache large recordsets in MTS
and
IIIS is not wise.

Performance
There could be a slight degradation in performance due to the saving of the
query criteria and generating the query results. However, this query is
very
complex because of

* the business rules
* the business requirements
* the scolling logic is quite complex.
* the query must read tables with millions of records

One option considered was to run the query everytime a user scrolled to the
next
page of query results. An option that would have lead to unhappy users.

Extendibility
Should queries start to queue due to many users generating queries then a
number
of jobs can be created to process different types of jobs, for example:

- Job dedicated by process ie download or view the query.
- Jobs by different user group eg Intranet users or Internet users.

Also, there is a requirement to generate other complex queries in different
formats eg PDF or Word.

Software
The QTD Architecture is 3-tier architecture using IIS, MTS and SQL Server


What do you think of the following architecture that I am proposing for QTD.
Is
there anything that I have missed etc


Thank you for your time.

Nick

View Replies !

---

Long Running Query
i all,

I want to run my idea with you about the proposed architecture for
generating a
complex query.

Functionality
In QTD, Users will run a query that will take a long time to run and will
generate upto 5000 records.

A user chooses to either view the results of the query in their browser or
download the results from the web site in a CSV file (XML is out of the
question
here). For viewing the query, users will receive 1 page of records at a
time.
The next set of records is send when they scroll to the next page.

The proposed architecture to process this query is:

1. Users will save there the query criteria to a query table.
2. A SQL Server scheduled job will run the query and saves the results to a
permanent holding table. The job will create the CSV file if the user chose
the
download option.

Job Processing
The job performs the following processing (all functionality is implemented
in
stored procedures) described at a high level:

Loop for ever or until the job is terminated

sleep for 2 seconds (sleep is a configurable setting)

for each record in the query criteria table

do processing
...
...
next

end loop

Reasons For This Architecture
The reasons for choosing this architecture are:

Scalability
MTS and IIS are all about scalability.

Implementing this functionality in VB components creates a more simplified
architecture and will be easily to maintain. However, the more users that
are
running MTS components/ASP pages the more resources are used up and the
longer
transactions will take. The system must perform complex transaction
processing.
Moving large recordsets around the network and cache large recordsets in MTS
and
IIIS is not wise.

Performance
There could be a slight degradation in performance due to the saving of the
query criteria and generating the query results. However, this query is
very
complex because of

* the business rules
* the business requirements
* the scolling logic is quite complex.
* the query must read tables with millions of records

One option considered was to run the query everytime a user scrolled to the
next
page of query results. An option that would have lead to unhappy users.

Extendibility
Should queries start to queue due to many users generating queries then a
number
of jobs can be created to process different types of jobs, for example:

- Job dedicated by process ie download or view the query.
- Jobs by different user group eg Intranet users or Internet users.

Also, there is a requirement to generate other complex queries in different
formats eg PDF or Word.

Software
The QTD Architecture is 3-tier architecture using IIS, MTS and SQL Server


What do you think of the following architecture that I am proposing for QTD.
Is
there anything that I have missed etc


Thank you for your time.

Nick

View Replies !

---

Long Running Query
Hi,

  I'm trying to optimize a long running (several hours) query. This query is a cross join on two tables. Table 1  has 3 fields - ROWID, LAt and Long. Table 2 has Name, Addr1,Addr2,City,State,Zip,Lat,Long.

Both tables has LatRad - Lat in radians, LonRad- Lon in Radians. Sin and Cos values of Lat and Lon are calulated and stored to be used in the distance formula.

What I'm trying to do here is find the nearest dealer (Table 2) for each of Table 1  rows. The Select statement takes a long time to execute as there are about 19 million recs on table 1 and 1250 rows in table 2. I ran into Log issues- filling the transaction log, so I'm currently using table variables and split up the process into 100000 recs at a time. I cross join and calculate the distance (@DistValues) and then find the minimum distance (tablevar2) for each rowid and then the result is inserted into another Table (ResultTable).

My Code looks like this:

Declare @DistValues table (DataSeqno varchar(10),Lat2 numeric(20,6),Lon2 numeric(20,6),StoreNo varchar(60), Lat1 numeric(20,6),Long1 numeric(20,6),Distance numeric(20,15))

Declare @MinDistance table (DataSeqno varchar(10) primary key,distance numeric(20,15))

Insert into @DistValues

Select DataSeqno,T.Lat Lat2,T.Lon Lon2,S.StoreNo,S.Lat Lat1,S.Long Long1,

distance=3963.1*Case when  cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad)  as numeric(20,15)) not between -1.0 and 1.0 then 0.0 else acos(cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad)  as numeric(20,15))) end

from dbo.TopNForProcess T , dbo.Table2 S where  Isnull(T.Lat,0) <> 0 and Isnull(T.Lon,0)<> 0

Insert into @MinDistance

Select DataSeqno,Min(distance)  From @DistValues Group by DataSeqno

   Insert into ResultTable  (DataSeqno,Lat2,Lon2,StoreNo,LAt1,Long1,distance)

    Select D.DataSeqno, D.Lat2, D.Lon2, D.StoreNo, D.LAt1, D.Long1, M.distance  from @DistValues D Inner Join @MinDistance M on D.DataSeqno = M.DataSeqno and D.Distance = M.Distance

I created a View called TopNForProcess which looks like this. This cut down the processing time compared to when I had this as the Subquery.

SELECT TOP (100000) DataSeqno, lat, Lon, LatRad, LonRad, SinLat, cosLat, SinLon, CosLon FROM Table1 WHERE (DataSeqno NOT IN (SELECT DataSeqno FROM dbo.ResultTable)) AND (ISNULL(lat, 0) <> 0) AND (ISNULL(Lon, 0) <> 0)

I have indexes on table table1 - Rowid and another one with Lat and lon. Table2 - Lat and Long.

Is there any way this can be optimized/improved? This is already in a stored procedure.

Thanks in advance.

View Replies !

---

Large Long Running Query
 

The query show below is designed to use seasonal profiles to compute 53 weeks of forecast data and then from that compute the number of weeks of supply of each item at each location.  The query works but the volume of data produced (20+M rows) is substantial.  If I limit the CTE to a single location, it run is 2 seconds and returns 41,000 rows.  But when run for all locations and items, it runs for more than 4 hours.  Would I do better converting the CTE to a sub-query and adding an index to improve the performance of the main query?
 

WITH Forecast AS

(SELECT Location_Idx

,Item_Idx

,Week_Code

,(CAST(AnnualQty AS DECIMAL(9))/53.0)*[Profile] AS fcst

FROM dbo.FactReplenishmentProfile rp

INNER JOIN dbo.FactSeasonalProfile sp

ON sp.SeasonalProfile_Idx = rp.SeasonalProfile_Idx

)

SELECT fcst1.Location_Idx

,fcst1.Item_Idx

,fcst1.Week_Code

,fcst1.fcst AS WeekQty

,SUM(fcst2.fcst) AS CumQty

FROM Forecast fcst1

INNER JOIN Forecast fcst2

ON fcst2.Location_Idx = fcst1.Location_Idx

AND fcst2.Item_Idx = fcst1.Item_Idx

AND fcst2.Week_code <= fcst1.Week_Code

GROUP BY fcst1.Location_Idx,fcst1.Item_Idx,fcst1.Week_code,fcst1.fcst

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 !

---

Running Create Table Query From SQL 2005 In SQL 2000
I had a table in SQL Server 2005 for which I generated the Create Table Script using SQL Server 2005 Generate Script feature. Now I wanted to run it on SQL Server 2000 but it fails. I have used both SQL Server 2000/2005 options in Script for Server Version option but it still fails. I can update the query and use the Alter statement to add the Primary Key but I have other 25 tables like these and I was wondering if there is an easy/automated way using which I don't have to make any modifications. The query from SQL 2005 is shown below -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user_role](
[role_id] [int] NOT NULL,
[role_desc] [varchar](30) NULL,
CONSTRAINT [PK_user_role] PRIMARY KEY CLUSTERED
(
[role_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks,
Saurav

View Replies !

---

ADO Client Disconnects After Running A Long Query
I am having a problem executing long running queries from an ASP applicationwhich connects to SQL Server 2000. Basically, I have batches of queries thatare run using ADO in a loop written in VBScript. This works pretty welluntil the execution time of a single query starts to exceed some threshold,which I am trying to narrow down. I can typically run 2 - 10 queries in aloop, with the run time being anywhere from under a minute to an hour ormore. Now that this application is being subjected to run against some largedatabases (25 - 40G), I'm having problems getting the application tocontinue beyond the first query if it takes a while to run.I used SQL Profiler to try to diagnose what was going on. I can see thequery executes to completion, but immediately after completing I can see an"Audit Logout" message, which apparently means that the client hasdisconnected. The query durations vary from 45 or 50 minutes to up to over90 minutes. I have the ADO connection and query timeouts set to very largevalues, e.g. 1000 minutes, so I can't think its that. My guess is that thereis some IIS setting or timeout that I am running up against and theconnection to SQL Server is just dropped for some reason.The configuration isNT 4.0 SP6SQL Server 2000 SP3IIS 4.0Internet Explorer 5.5I'm only running into this problem on the very largest databases we runagainst. The vast majority continue to function properly, but this is goingto happen more often as time goes on the databases continue to grow in size.Any advice is appreciated,-Gary

View Replies !

---

Running Query In SQL 2005 Stored Procedure Against Table On SQL 2000
I'm trying to set up Service Broker Services on SQL 2005 x86.  I've got two services set up, and a stored procedure associated with one of them.

View Replies !

---

SQL Server 7.0 Index Rebuilds - Long Running Query
I have a long running read only query. When I rebuild the indexes using sqlmaint.exe setting the index free space = 40% the query execution time reduces to seconds.

If I rebuild the indexes using 10-30% free space the query continues to run for several minutes.

This is opposite what all the manuals on index tuning say. All page splits should be cleaned up after the rebuild no matter what the free space is

View Replies !

---

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


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



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

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

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

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

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

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



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





looks like my data and log size should be increased correct

if yes how to do that?????





if worked this week fine (6 hrs )

View Replies !

---

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

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

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

View Replies !

---

Job Doesn't Work But Package Works Fine
hi,

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

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

Any ideas?

View Replies !

---

Query That Never Finishes In SQL2005 But Worked Fine In 2000
Hi,
 
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
 
Cheers
Steve
 

SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H

FROM

RowRuns AS R1

INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId

INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId

INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId

INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId

INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId

INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId

INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId

INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId

INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId

INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId

INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId

INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId

INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId

WHERE

((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))

AND

((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))

AND

((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))

AND

((R8.ParamID='61a714fa-8b20-1e7e-1adb-c680f72ddf0d'))

AND

((R10.ParamID='d9f0645c-e1be-b5c2-906f-ff3c5b9de0df'))

AND

((R12.ParamID='1916773f-1bf9-eea5-e702-5f293b3047a2'))

AND

((R14.ParamID='c37d4377-f6dd-69bc-16ef-bd06c76f400e'))
 

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 !

---

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 !

---

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 !

---

LongestRunningQueries.vbs - Using A VB Script To Show Long-running Queries, Complete With Query Plans
Try this script to see what queries are taking over a second.To get some real output, you need a long-running query. Here's one(estimated to take over an hour):PRINT GETDATE()select count_big(*)from sys.objects s1, sys.objects s2, sys.objects s3,sys.objects s4, sys.objects s5PRINT GETDATE()Output is:session_id elapsed task_alloc task_dealloc runningSqlText FullSqlTextquery_plan51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,sys.objects s3, sys.objects s4, sys.objects s5 SQL PlanClicking on SQL opens the full SQL batch as a .txt file, including the PRINTstatementsClicking on Plan allows you to see the .sqlplan file in MSSMS========Title: Using a VB Script to show long-running queries, complete with queryplans.Today (July 14th), I found a query running for hours on a development box.Rather than kill it, I decided to use this opportunity to develop a scriptto show long-running queries, so I could see what was going on. (ReferenceRoy Carlson's article for the idea.)This script generates a web page which shows long-running queries with thecurrently-executing SQL command, full SQL text, and .sqlplan files. The fullSQL query text and the sqlplan file are output to files in your tempdirectory. If you have SQL Management Studio installed on the localcomputer, you should be able to open the .sqlplan to see the query plan ofthe whole batch for any statement.'LongestRunningQueries.vbs'By Aaron W. West, 7/14/2006'Idea from:'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp'Reference: Troubleshooting Performance Problems in SQL Server 2005'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxSub Main()Const MinimumMilliseconds = 1000Dim srvnameIf WScript.Arguments.count 0 Thensrvname = WScript.Arguments(0)Elsesrvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)If srvname = "" ThenMsgBox("Cancelled")Exit SubEnd IfEnd IfConst adOpenStatic = 3Const adLockOptimistic = 3Dim i' making the connection to your sql server' change yourservername to match your serverSet conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' this is using the trusted connection if you use sql logins' add username and password, but I would then encrypt this' using Windows Script Encoderconn.Open "Provider=SQLOLEDB;Data Source=" & _srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"' The query goes heresql = "select " & vbCrLf & _" t1.session_id, " & vbCrLf & _" t2.total_elapsed_time AS elapsed, " & vbCrLf & _" -- t1.request_id, " & vbCrLf & _" t1.task_alloc, " & vbCrLf & _" t1.task_dealloc, " & vbCrLf & _" -- t2.sql_handle, " & vbCrLf & _" -- t2.statement_start_offset, " & vbCrLf & _" -- t2.statement_end_offset, " & vbCrLf & _" -- t2.plan_handle," & vbCrLf & _" substring(sql.text, statement_start_offset/2, " & vbCrLf & _" CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _" ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _" END) AS runningSqlText," & vbCrLf & _" sql.text as FullSqlText," & vbCrLf & _" p.query_plan " & vbCrLf & _"from (Select session_id, " & vbCrLf & _" request_id, " & vbCrLf & _" sum(internal_objects_alloc_page_count) as task_alloc, " &vbCrLf & _" sum (internal_objects_dealloc_page_count) as task_dealloc " &vbCrLf & _" from sys.dm_db_task_space_usage " & vbCrLf & _" group by session_id, request_id) as t1, " & vbCrLf & _" sys.dm_exec_requests as t2 " & vbCrLf & _"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _"where t1.session_id = t2.session_id and " & vbCrLf & _" (t1.request_id = t2.request_id) " & vbCrLf & _" AND total_elapsed_time " & MinimumMilliseconds & vbCrLf & _"order by t1.task_alloc DESC"rs.Open sql, conn, adOpenStatic, adLockOptimistic'rs.MoveFirstpg = "<html><head><title>Top consuming queries</title></head>" & vbCrLfpg = pg & "<table border=1>" & vbCrLfIf Not rs.EOF Thenpg = pg & "<tr>"For Each col In rs.Fieldspg = pg & "<th>" & col.Name & "</th>"c = c + 1Nextpg = pg & "</tr>"Elsepg = pg & "Query returned no results"End Ifcols = cdim filenamedim WshShellset WshShell = WScript.CreateObject("WScript.Shell")Set WshSysEnv = WshShell.Environment("PROCESS")temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & ""filename = temp & filenameDim fso, fSet fso = CreateObject("Scripting.FileSystemObject")i = 0Dim cDo Until rs.EOFi = i + 1pg = pg & "<tr>"For c = 0 to cols-3pg = pg & "<td>" & RTrim(rs(c)) & "</td>"Next'Output FullSQL and Plan Text to files, provide links to themfilename = "topplan-sql" & i & ".txt"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-2)f.Closepg = pg & "<td><a href=""" & filename & """>SQL</a>"filename = "topplan" & i & ".sqlplan"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-1)f.Closepg = pg & "<td><a href=""" & filename & """>Plan</a>"'We could open them immediately, eg:'WshShell.run temp & filenamers.MoveNextpg = pg & "</tr>"Looppg = pg & "</table>"filename = temp & "topplans.htm"Set f = fso.CreateTextFile(filename, True, True)f.Write pgf.CloseDim oIESET oIE = CreateObject("InternetExplorer.Application")oIE.Visible = TrueoIE.Navigate(filename)'Alternate method:'WshShell.run filename' cleaning uprs.Closeconn.CloseSet WshShell = NothingSet oIE = NothingSet f = NothingEnd SubMain

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 !

---

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 !

---

SQL 2000 To SQL 2005 Works For One 2000 Server But Not The Next
 

I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:

SQL Server 2000 as Publisher/Distributor

SQL Server 2005 Standard as Subscriber

The connection is via the Internet with snapshots using FTP.

I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.

II am setting up the same scenario for location B. Here is my problem:

In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next

HERE IS THE PROBLEM:

On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.

I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).

WHAT'S DIFFERENT:

Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).

The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher.  On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.

ANY IDEAS?

View Replies !

---

Tx Replication Works In 2000 But Not In 2005?
Hello,

I've got a simple transactional replication set up.  I have a separate publisher, distributor, and subscriber with 76 articles (tables only) being pushed from the distributor. 

I have this exact setup with the same tables and data working in the SQL 2000 environment.  I am testing replication on our SQL 2005 test servers before moving to production, however when the distributor attempts to push out the initial snapshot I keep getting this error.

Error messages:


Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102

Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102

Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156

Anyone know anythign about it?

Thank you,

Aaron Lowe

View Replies !

---

Select Statement Works In 2000 But Not In 2005
I hope this is a easy one. We are trying to find a fix for a select statement that works in 2000 but not in 2005 with a simple select statement.

The easiest statement that will duplicate the error is:

TestTable has 3 columns: Primary, strTest, strTest2

 

SELECT     strTest, strTest AS Name
FROM         TestTable
ORDER BY strTest2

 

If you sort by the Primary column you will not receive an error.

How can you select the same column twice and then sort in the SQL statement?
 

View Replies !

---

Working With SQL 2005 Interface But Internally It Works As SQL 2000
Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.� So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)� So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?

View Replies !

---

Working With SQL 2005 Interface But Internally It Works As SQL 2000
Hi!

I have installed SQL 2000 and SQL 2005 in my computer. But none of 2005 feature is working. So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000:
€œMicrosoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)€?

So, how can I change it for working with SQL 2005?. It€™s like I€™m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?

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 !

---

SSIS Package Run Fine But Not Working When Running In A Job
 

 I have a 64bit Ent Ed, Created a SSIS package, deployed it, its running when running from sql server but not running as a job

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 !

---

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 !

---

SQL 2005 Express Edition SP2 - Query Takes A Long Time To Run(sometimes)
 

Hi,
 
I have problem with JDBC 2005 (1.1) running against SQL 2005 Express edition (SP2).  Sometimes, the statement takes long time (more than 10 seconds).  Sometimes, the same statement takes just a few seconds.  It is very unpredictable. 
The query that we have problem is most of the time is join sql statement.
 
Does anyone see this problem?

 
Thanks,

View Replies !

---

Query Running In SQL Server 2000
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?

thx,

Kat

View Replies !

---