Select Statement Works In 2000 But Not In 2005

Jan 2, 2008

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 3 Replies


ADVERTISEMENT

Complex SQL Select Statement That Works But...

Jul 24, 2006

I have a pretty complex SQL statement that looks like this:
SELECT     aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact,                       SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username)                       AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM         aspnet_Employers LEFT OUTER JOIN                      aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN                      aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN                      aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active
It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".
Why does it do this?
What would i change to prevent this from happening?
 
Thanks!

View 3 Replies View Related

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works

Jun 4, 2007

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

View 2 Replies View Related

SELECT Statement Works In SQL Server Management Studio But Not In SSRS

Mar 14, 2008



Hi,

I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:


SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]

FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')

THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource

FROM Calllog where

DATEPART(MONTH, Recvddate) = 3

AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')

GROUP BY RecvdDATE, callsource) as sub

GROUP BY YEAR, CallSource

ORDER BY YEAR, CallSource

The problem is that when I attempt to use this in SSRS I get the following error:

"sub.Year is not a recognised DATEPART Option".

Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?

Thanks
Matt

View 4 Replies View Related

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

Jun 15, 2006



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 5 Replies View Related

Tx Replication Works In 2000 But Not In 2005?

Apr 28, 2006

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 17 Replies View Related

Query Works In SQL 2000 But Not In SQL 2005

Mar 15, 2006

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 3 Replies View Related

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

Aug 27, 2007

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 2 Replies View Related

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

Aug 23, 2007



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 5 Replies View Related

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

Aug 27, 2007

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 1 Replies View Related

Select Records Between Dates - Query Works In VS 2005 But It Doesn't In Asp 3

Nov 9, 2006

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 1 Replies View Related

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

Mar 28, 2008

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 5 Replies View Related

RESTORE FILELIST Is Terminating Abnormally Error When Running A DTS Package In SQL 2005. Works Fine In SQL 2000

Oct 10, 2007

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 1 Replies View Related

Select Statement In Sql Server 2000 Is Taking Too Long??

Jul 20, 2005

Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque

View 1 Replies View Related

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not

Jun 20, 2007

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



telnet sql5.hostinguk.net 1433 and

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



See below:



Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

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

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



TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT



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

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

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

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


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

I can ping the server

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

I do not have any aliases set up

No I do not force encryption

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

View 7 Replies View Related

Get Value From SQL Server 2005 Select Statement With Datareader

Nov 6, 2006

I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.  The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:System.InvalidOperationException was unhandled by user code  Message="Invalid attempt to read when no data is present."  Source="System.Data"  StackTrace:       at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)       at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)       at _Default.Login_Click(Object sender, EventArgs e) in d:My DocumentsVisual Studio 2005WebSitesWebSite1Default.aspx.cs:line 47       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) If anybody could  advise me where my stupid mistake is then i would highly appreciate it!         SqlConnection conn = new SqlConnection(getConnection());        SqlDataReader rdr = null;        SqlCommand cmd = new SqlCommand();        cmd.CommandText = "SELECT * FROM Customer";        cmd.CommandType = CommandType.Text;        cmd.Connection = conn;        try        {            conn.Open();                           rdr = cmd.ExecuteReader();                        int calc = 0;            Boolean login = false;            string[] email = new string[100];            object[] password = new object[100];            while (rdr.HasRows) // or rdr.Read();             {                rdr.Read();                email[calc]= rdr[0].ToString();                password[calc] = rdr["Password"].ToString();                if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))                {                    login = true;                }                calc++;            }        }        finally        {            rdr.Close();        }  thanks.... 

View 1 Replies View Related

UPDATE Statement Differences Between 2000 And 2005

Jun 21, 2007

I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005.



Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it.



IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL

DROP TABLE #UpdateTestA



IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL

DROP TABLE #UpdateTestB



CREATE TABLE #UpdateTestA(

UpdateTestA int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



CREATE TABLE #UpdateTestB(

UpdateTestB int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



INSERT INTO #UpdateTestA(

FullName)

VALUES ('Barney Rubble')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'First')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'Second')



SELECT * FROM #UpdateTestA



UPDATE a

SET a.UpdateData = b.UpdateData

FROM #UpdateTestA a

INNER JOIN #UpdateTestB b on b.FullName = a.FullName



SELECT * FROM #UpdateTestA



DROP TABLE #UpdateTestA

DROP TABLE #UpdateTestB



Hope this solves a problem that you were having too.

View 3 Replies View Related

2000 To 2005 SQL Statement Order By Issue

Jul 9, 2007



Hi,

I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:



ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC



The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help



- T.A.

View 7 Replies View Related

Update Statement Works Ok But No Result

Nov 24, 2005

here is my update statement in a stored procedure:

create proc proc_add_comp
    @comp_answer nvarchar(300),
    @admin nvarchar(100),
    @comp_id int
as

update tbComp set
   
comp_answer = comp_answer + ' - ' + @admin + ', ' + @comp_answer

where comp_id=@comp_id

then I try it like this :
exec proc_add_comp 'new answer','by me',1

result is : (1 row(s) affected)

but when I look in the db, nothing was changed, comp_answer still has its old value..
comp_answer is nvarchar type column..isnt add operation allowed in update statement?
thanks...

View 1 Replies View Related

Update Statement That Works For One Record?

Apr 20, 2015

have this update statement that works for one record. How do I write it to include multiple records at once. see sample below.

update
mklopt
set

FRMDAT = '12/31/2014'

where

JOBCOD = 'PH14789'

I also want to include the following instead of running it one at a time

PH17523

PH17524

PH17525

PH17553

PH17555

PH17556

PH17557

PH17558

PH17571

PH17573

PH17574

PH17575

PH17576

PH17577

PH1757

View 5 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Problem With Select From 2000 To 2005

Oct 9, 2006

I have a query which was generated by a user using a report writter, so I have no control over what the user selected or how the query was generated.

The following query runs perfectly under SQL 2000 SP 4 32 bit and has for a long time. We are testing on a SQL 2005 SP1 (2153 Build) 64 bit with the exact same data.

The following query on SQL 2000 runs in 3 seconds, on SQL 2005 ran for 42 MINUTES before we cancelled it.

SELECT
CustomerID = TABLEB.CustomerID,
CustomerName = TABLEA.Group_Name_1_A,
CustomerContact = TABLEA.Group_Name_2_A
FROM
TABLEA
RIGHT OUTER JOIN TABLEB ON (TABLEB.CustomerID=TABLEA.CustomerID)
RIGHT OUTER JOIN TABLEC ON (TABLEC.CustomerID=TABLEB.CustomerID)
WHERE
TABLEB.CustomerID
IN (..  List of 38 CustomerIDs...)

Note: TABLEA and TABLEB are actually views into TABLEC. I don't know if that is relavant or not yet.

This appears to be this problem: http://support.microsoft.com/kb/318530 which existed in 2000 and was fixed with SP3. Can anyone confirm if this problem exists in 2005??

In diagnosing the problem, several things cause the query to work properly under 2005 and return in 3 seconds: Changing the WHERE TABLEB to WHERE TABLEA. Removing the RIGHT OUTER TABLEB, which is technically unneeded. The most interesting is shortening the WHERE IN clause to only 11 items, which is not possible for the report. Removing the PK from TABLEC OR making the PK index non-clustered.

The est execution plans between 2000 and 2005 is like comparing "Apples to Automobiles". That both start with "A" and that is about the only similarity.

Any ideas?

View 6 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

UNION Statement That Works In SQL Server But Not Access

Nov 29, 2006



Hi,

Is there a way I can get this select Union statement to work in Access.

SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND

Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge'

AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router

I get this message in Access: Query input must contain at least input of query

Thanks for any help

Chris

View 1 Replies View Related

Sql Statement Case In Where Works Management Studio Not In C#

Mar 17, 2008

I have a bit of an odd problem in an sql statement where it works in management studio but not in c# when setting up a table adapter the sql is:


DECLARE @week char(2)

SET @Week = 7

SELECT student_id, acad_period, register_id, register_group, week_no, absence_code, attendance_type

FROM dbo.sttdstud

WHERE (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND

week_no = Case @Week when null Then '#' Else @Week End


the idea is if they don't enter a number it would bring up all records. This works in mangaement studio but c# brings up the error sql server doesn't support udt on excecution of the sql. This is sql server 2000. Any ideas why this doesn't work and how to fix it.

View 3 Replies View Related

View SELECT Differences Between SQL 2000 &&amp; 2005

Apr 8, 2008

I have the following a view on a SQL2K box that uses the following SELECT statement:

SELECT



SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)


The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.

My question is why this works in SQL2K and not SQL2K5?

Thanks,
Kevin

View 1 Replies View Related

How SELECT In WHERE Clause Works?

Jun 8, 2008

Please help 
I'm trying to do a select command but doesn't return any record
I have two tables one is "lists" another one is "list_records"
in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site
 
Here is my query (I got 0 record return) 
 
SELECT *FROM lists
list_reccords
WHERE NOT EXISTS (SELECT *
                                                   FROM lists,
                                                      list_records                                       WHERE list_records.is_visited = 1
                                             AND lists.list_id = list_records.list_id)

View 4 Replies View Related

Select Query Works On V2 And Not On V1.2

Aug 27, 2005

Hi

The query below with table join works on my dev server sql server MC
v2, but returns only NULL values for Questiona and Module on the live
server MC v1.2, is this error to do with the version or something else
like relationships?

SELECT    
Induction_Module.Induction_Module, Induction_Questions.Question,
Induction_AnswerIncorrectStats.AnswerIncorrectNo,
                     
Induction_Questions.AnswerCorrect,
CAST(Induction_AnswerIncorrectStats.DateAnswered AS VarChar(11)) AS
DateAnswered
FROM         Induction_Questions LEFT OUTER JOIN
                     
Induction_Module ON Induction_Questions.InductionModuleID =
Induction_Module.Induction_ModuleID RIGHT OUTER JOIN
                     
Induction_AnswerIncorrectStats ON Induction_Questions.QuestionID =
Induction_AnswerIncorrectStats.QuestionID


Regards

Steve

View 4 Replies View Related

How This Select Statment Works Please It Is Urgent?

Apr 21, 2007

hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?

tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply

I am Looking for your reply !

View 1 Replies View Related

Select Into Works, Create Table Does Not

Jul 23, 2005

HiWe are migrating from Access to SQL server. The code is in VB and usesDAO3.6. I have successfully made the connection to the databases onthe SQL server and done operations with recordsets. I have also used aSELECT INTO command to create new tables. However CREATE TABLE andALTER TABLE commands do not work.Any ideas?The DBA says we have owner privileges on the databases and thereforeshould be able to do what we like.Many thanksSi

View 1 Replies View Related

SELECT Works But UPDATE Fails. ?

Jul 23, 2005

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 17 Replies View Related

SSRS 2005 V SSAS 2000 Mutli-Select Parameters

Nov 2, 2007

Has anyone managed to crack getting multi-select parameters to work from an SSRS 2005 report which is querying an SSAS 2000 cube.

SSRS 2005 does support Multiselect, however SSRS 2000 did not. Given that i am querying an SSAS 2000 cube, i get the impression that i am also limited to SSRS 2000 functionality regarding to multi-select parameters.

Is there anyone out there that ever managed to get a wor around, say by using filters on a table or anything really that coud simulate the same behaviour....

View 3 Replies View Related

CROSSTAB ::works In Access But Not In MS SQL 2000

Nov 13, 2005

I am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::

Table 1 --> Info
InfoID
Name

Table 2 --> Item
InfoID
Num
Value

TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num

in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all

does someone knows how to translate it for MS SQL (the table structures are exactly the same)?


thank you

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved