Worked In 2000 But Not In 2005
Feb 22, 2008
Hi there,
I have a query with a rather large (46 table) 1:1 join. Technically, I'm creating a view then trying to select from that view. I seem to be able to create the view ok but cannot resolve a select from it. The error message is:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 191, Level 15, State 1, Line 1
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'WHERE'.
This worked in 2000 without a problem
Here is the join:
FROM [HUSKY].USEP0712E.dbo.AGAF_us T1 INNER JOIN [HUSKY].USEP0712E.dbo.AGAM_us T2 ON T1.CODE = T2.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGASN_us T3 ON T1.CODE = T3.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBF_us T4 ON T1.CODE = T4.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBLK_us T5 ON T1.CODE = T5.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBM_us T6 ON T1.CODE = T6.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHF_us T7 ON T1.CODE = T7.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHM_us T8 ON T1.CODE = T8.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHSP_us T9 ON T1.CODE = T9.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIF_us T10 ON T1.CODE = T10.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIM_us T11 ON T1.CODE = T11.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIND_us T12 ON T1.CODE = T12.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNH_us T13 ON T1.CODE = T13.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNHF_us T14 ON T1.CODE = T14.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNHM_us T15 ON T1.CODE = T15.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOF_us T16 ON T1.CODE = T16.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOM_us T17 ON T1.CODE = T17.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOTH_us T18 ON T1.CODE = T18.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWF_us T19 ON T1.CODE = T19.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWHT_us T20 ON T1.CODE = T20.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWM_us T21 ON T1.CODE = T21.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.WH_AOH_us T22 ON T1.CODE = T22.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.WNH_AONH_us T23 ON T1.CODE = T23.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGAF_us T24 ON T1.CODE = T24.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGAM_us T25 ON T1.CODE = T25.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGASN_us T26 ON T1.CODE = T26.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBF_us T27 ON T1.CODE = T27.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBLK_us T28 ON T1.CODE = T28.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBM_us T29 ON T1.CODE = T29.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHF_us T30 ON T1.CODE = T30.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHM_us T31 ON T1.CODE = T31.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHSP_us T32 ON T1.CODE = T32.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIF_us T33 ON T1.CODE = T33.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIM_us T34 ON T1.CODE = T34.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIND_us T35 ON T1.CODE = T35.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNH_us T36 ON T1.CODE = T36.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNHF_us T37 ON T1.CODE = T37.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNHM_us T38 ON T1.CODE = T38.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOF_us T39 ON T1.CODE = T39.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOM_us T40 ON T1.CODE = T40.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOTH_us T41 ON T1.CODE = T41.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWF_us T42 ON T1.CODE = T42.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWHT_us T43 ON T1.CODE = T43.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWM_us T44 ON T1.CODE = T44.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.WH_AOH_us T45 ON T1.CODE = T45.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.WNH_AONH_us T46 ON T1.CODE = T46.CODE
View 5 Replies
ADVERTISEMENT
Nov 14, 2007
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 14 Replies
View Related
Feb 27, 2008
I have SQL 2000 Standard edition installed on a Windows server 2003 on a virutal box. SQL was upgraded to 2005 but during the install wizard, it did not prompt me for the Instance Name to upgrade. When done, I see the SQL Management Studio and when I connect, my databases are listed in the studio but the version number says 8.0.760 across the top of management studion which is SQL 2000 sp3.
Enterprise Manager is still available. It appears SQL 2005 was installed side by side with SQL 2000. Does that sound correct?
I downloaded SQL 2005 standard edition from my open license agreement. Don't know if that makes a difference.
I also tried upgrading SQL 2005 via the command prompt and it still shows verison number 8.0.760.
Where am I going wrong?
Thanks.
View 7 Replies
View Related
Mar 6, 2007
I am developing an app in VB.Net 2005, and I installed SQL Server 2005 locally on my machine. My app access multiple databases on SQL and has worked like a charm in the past. Today, when I go to access my app, I receive an error when trying to connect to the SQL database.
"An error has occured 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 connection"
As I said before, this is a local install and worked fine until today. I checked the services running and Here is what I have:
SQL Server Integration Services - Running
SQL Server FullText Search - Stopped
SQL Server(SQLEXPRESS) - Running
SQL SERVER (MSSQLSERVER) - Stopped <---------- I tried restarting this and it times out
SQL Server Analysis Services (MSSQLSERVER) - Stopped
SQL Server Reporting Services (MSSQLSERVER) - Stopped
SQL Server Browser - Stopped
SQL Server Agent (MSSQLSERVER) - Stopped
I am unable to start any of these services... they all time out. Any help would be greatly appreciated!
View 4 Replies
View Related
Dec 7, 2007
Code Block
Hi All,
I've got a stored procedure called from a trigger on another database. There are two xp_cmdshell statements in the procedure. One writes a couple variables, passed from the trigger, into a text file. The second fires off an executable that uses the text file via a batch file.
Here's the code that worked on the old 32bit SQL2000 box
Code Block
begin
declare @cmd varchar(150)
select @cmd = 'echo ' + @sVariable1 + ',' + @sVariable2 +' > c:aFolderTextFile.txt'
exec master..xp_cmdshell @cmd
declare @cmd2 varchar(150)
select @cmd2 = 'c:aFolderRun.bat'
exec master..xp_cmdshell @cmd2
end
The new box is 64bit SQL2005. When invoked the stored procedure executes without an error. The textfile is written sucessfully. But, the executable called from the second xp_shell statement does not produce the expected result. Because there is no error I'm having trouble determining where the failure lies.
To troubleshoot the problem I've tried:
Running the batch file from the command prompt in the system32 directory... Success
Using sp_xp_cmdshell_proxy_account to be sure of the credentials of the running xp_cmdshell... Failure
My questions:
Does 64 Bit SQL2005 have a different set of parameters when invoking an executable from xp_cmdshell? ie Do I need to rewrite the batch file?
Is there another way call an executable, SQLCMD maybe?
Any help is appreciated...
Code Block
The batch file if you're curious:
"c:Program Files (x86)someVendersomeAppsomeEXE.exe" "C:aFolderTextFile.txt"
View 3 Replies
View Related
Apr 3, 2007
I have views that cast the data into data types.
On 2005 when retrieving the views using a where clause i get errors. I have put together an example below that illustrates the error.
CREATE TABLE TestData ( Id VARCHAR(10), [Description] VARCHAR(20), [Type] INT)
CREATE TABLE TestTypeLookup( [Type] INT, [Description] VARCHAR(20))
GO
INSERT INTO TestTypeLookup VALUES (1, 'Type 1')
INSERT INTO TestTypeLookup VALUES (2, 'Type 2')
INSERT INTO TestData VALUES ('AA', 'Description for AA', 1)
INSERT INTO TestData VALUES ('10', 'Description for 10', 2)
INSERT INTO TestData VALUES ('20', 'Description for 20', 2)
GO
CREATE VIEW TestView AS
SELECT TOP 100 PERCENT CAST(Id AS INT) Id, [Description]
FROM TestData
WHERE [Type] = (SELECT TOP 1 [Type] FROM TestTypeLookup WHERE [Description] = 'Type 2')
ORDER BY CAST(Id AS INT)
GO
SELECT * FROM TestView WHERE Id = 10
GO
DROP VIEW TestView
DROP TABLE TestData
DROP TABLE TestTypeLookup
GO
View 11 Replies
View Related
Sep 29, 2006
Very nice; that worked.
It seems like a lot of code for each date field, but we are up and running now.
View 1 Replies
View Related
Jul 23, 2005
HiI've a SP in MSDE that will update a table. How could I know if itworked to return a value to the SP caller ?Thanks in advanceJ
View 1 Replies
View Related
Dec 13, 2001
Is there a system variable I can use to check if my update statement was successful and updated exactly one row.
Thanks
View 1 Replies
View Related
Aug 18, 2006
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
View 1 Replies
View Related
Dec 19, 2007
Ok, I know that there is a very smart programmer out there that can resovle my issue.
I am trying to calculate time worked by 15 minute intervals.
Example:
Emp 1 started work at 13:00:00 and worked 183 minutes
Emp 2 started work at 17:15:00 and worked 150 minutes
Emp 3 started work at 08:30:00 and worked 17 minutes
I need to show the following results:
time employee #of_min_worked
----------------------------------------------
08:30:00 3 15
08:45:00 3 2
09:00:00
08:30:00
08:45:00
09:00:00
09:15:00
09:30:00
09:45:00
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
11:15:00
11:30:00
11:45:00
12:00:00
12:15:00
12:30:00
12:45:00
13:00:00 1 15
13:15:00 1 15
13:30:00 1 15
13:45:00 1 15
14:00:00 1 15
14:15:00 1 15
14:30:00 1 15
14:45:00 1 15
15:00:00 1 15
15:15:00 1 15
15:30:00 1 15
15:45:00 1 15
16:00:00 1 3
16:15:00
16:30:00
16:45:00
17:00:00
17:15:00 2 15
17:30:00 2 15
17:45:00 2 15
18:00:00 2 15
18:15:00 2 15
18:30:00 2 15
18:45:00 2 15
19:00:00 2 15
19:15:00 2 15
19:30:00 2 15
19:45:00
20:00:00
20:15:00
20:30:00
20:45:00
21:00:00
21:15:00
21:30:00
21:45:00
22:00:00
22:15:00
22:30:00
22:45:00
23:00:00
23:15:00
23:30:00
23:45:00
View 1 Replies
View Related
Feb 7, 2008
I am working with Two Server X and Z
In X server, I have a linked server named CustSrv which is connecting to Z server
In the Z server I have a Database named SalesDB
I have a view name vw_CusgtomerData in my X server which is selecting data from SALESDB..Customer_Tbl from the Z server through that linked server (CustSrv)
The View is simple selecting data from Customer_Tbl from SalesDB
SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
[Note here using * for all columns?? Is it ok for performance aspects]
Now I have some Application which are using that view through some stored procedure.Few of them passing some parameters like Cust_Id etc
Now my query is that.. Am I fulfilling all performance issues?
Or
What is the suggestive way to fetch data from that remote (Linked Server) server to get good performance benefit?
In my opinion we can fetch data 4 different way from that linked server¦
A.SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
B.exec GetCustomerData 65
[Sp_GetCustomerData is a Storedprocedure which is passing a parameter 65 that is Customer_ID and the procedure is selecting data from the view vw_CusgtomerData]
C.SELECT cu_customer_id, cu_customer_name FROM vw_CusgtomerData ORDER BY cu_customer_name
Or
SELECT * FROM vw_CusgtomerData where Customer_ID=65
D.select * FROM OPENQUERY
(CustSrv,'SELECT Customer_ID,cu_customer_name FROM SalesDB.dbo. CUSTOMER_Tbl ORDER BY cu_customer_name ')
Am I bypass the concept of view and fetch data directly in the stored procedure through the linked server ??
View 7 Replies
View Related
Aug 11, 2006
I created a DTS Package. It worked fine if I execute the DTS from DTS designer. It failed after I right clicked to Schedule Package, and than right clicked on the job to Start Job from Management - SQL Server Agnet - Jobs. When I clicked View Job History, the error was "The job failed. The last step to run was step 1 (DTSPackageName).
Why did it fail from scheduled DTS? Thanks.
DanYeung
View 1 Replies
View Related
Jun 22, 2014
I create a package that has a foreach loop container, inside the FE container the is a dataflow, script task and a file system task, on the outside of the FE loop I have a SQL execute SQL task. I had it working then it just stopped. Been looking to see why but can't see why, is there something I missed. I changed the onError script task event handler, to propagate = false, because the last file in the source directory is being written to until 11:59 pm and is locked, and I get error file is being used by another process. I am at a loss as to why it would just stop working.
View 4 Replies
View Related
Apr 3, 2006
Hi, I am trying to enable remote access on a SQL Server 2005 standard edition instance. I have enabled it in the properties using the Management studio and have enabled it under Sql Server Surface Area Configuration under "Database engine".
But it didn't work. I know for a fact that my connection string is correct (I tested it on the actual machine)
So I checked everything again and I noticed in the Sql Server Surface Area under "Analysis Services" there is another "remote connection" checkbox to be enabled, however it won't let me enable it there because it says the "Sql Browser" is not started. I checked and the Sql Browser is started.
I'm thinking maybe the problem there is that I have an instance of SQL 2000 running and perhaps it is the sql browser for that (I can remotely connect ot my SQL 2000 instance).
I checked under Computer management -> services and their is no SQL Browser for the instance of SQL 2005. Not even a thing to enable. Just the Default instance SQL Browser, which is for my SQL 2000 instance. Am I missing something here?
Also there is no SQL Browser to enable in the management or Surface Area Configuration programs.
I have also tried to connect using the default port number (1433) and the IP (to bypass the need for the SQL Browser). It apparently finds my Server 2000 instance because it doesn't accept my user name and password and connot find the specific database (note: I verified the username and pswd are correct by testing the values on the SQL machine). How do I find what port number a specific instance of SQL Server is running on?
View 5 Replies
View Related
Jul 2, 2014
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL
[Code] ....
EXPECTED RESULT:
Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5
View 5 Replies
View Related
Jul 18, 2007
I have code
Function GetDealCount(reportItems)
return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)
End Function
Function GetSumNotionalAmount(reportItems)
return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)
End Function
That I am calling from a textboxes in the page header
= Code.GetDealCount(ReportItems) & " Deal(s)"
also
= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")
When I preview the report in VS.NET I get values showing.
When I deploy the report I just get #Error showing.
Also this report used to work fine in RS2000
Does anyone know the cause of this issue?
View 6 Replies
View Related
Nov 30, 2007
Hi all,
Not sure what's going on here...
I had reports using the HTML Viewer working in RS 2000. The servers were both O/S Windows 2000, SQL Server 2000, IIS 5.0, Reporting Services 2000) The default toolbar and parameters area were formatted and looked fine.
We just migrated to new servers with Windows 2003 Server, SQL Server 2005, IIS 6.0, Reporting Services 2005. I have the reports working fine...except the toolbar and parameters areas look as if there is no formatting at all...as if the style sheet isn't being applied (not even the default one --see below).
Although I don't think this is necessary....I tried the following to resolve the issue....
Updated the RSReportServer.config file.
Driveletter:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config
Checked that the HtmlViewer.css file was in the ...ReportServerStyles folder.
Added the following line between the
<Configuration>.......</Configuration> tags in the RSReportServer.config file.
<HTMLViewerStyleSheet>HtmlViewer</HTMLViewerStyleSheet>
Find
|
Next
Select a format XML file with report data CSV (comma delimited) TIFF file Acrobat (PDF) file Web archive Excel
Export
I can't find anyone else having this issue...any ideas?
Hope someone can help...thanks for looking....
Leslie
View 3 Replies
View Related
Jul 19, 2007
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 8 Replies
View Related
May 19, 2008
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies
View Related
Mar 13, 2006
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies
View Related
Apr 30, 2008
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
View 4 Replies
View Related
Dec 18, 2007
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
View 3 Replies
View Related
Sep 21, 2006
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies
View Related
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
Jan 19, 2007
Most of our sql servers ar still sql 2000. Our programmers created many sql 2000 database diagrams using EM. But they can not access them under sql 2005. (They now have only sql 2005 tools installed on their boxes.)
Question: can we reinstall the sql 2000 client tools on their boxes without affecting the current sql 2005 install on their boxes?
Question: is there any workaround negating the need to ihstall the sql 2000 client?
TIA,
barkingdog
View 1 Replies
View Related
Aug 10, 2007
Hello Everyone,
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied.
SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then
MessageBox.Show("Open")
End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
Your advice and help is very appreciated
Thank you
Ika
View 3 Replies
View Related
Apr 23, 2008
Hello,
I would like to ask you if there is better to recreate database structure on 2005 from 2000 and move data or to just load
2000 backup.
Currently I loaded the backup, but I am wondering if there might be slightly better performance on 2005 when recreating structure on 2005 to loading 2000 backup?
Does loading 2000 backup create 2005 binary structure?
Thanks for help in advance
View 5 Replies
View Related
Aug 2, 2006
How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??
View 1 Replies
View Related
Dec 1, 2005
P4 2.8G Hz, 512 M RAM.thanks.
View 3 Replies
View Related
Aug 3, 2006
If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
Thanks,
Grant
View 1 Replies
View Related
Oct 12, 2006
Hi all.My webhost prepared a server for me with sql 2000 instead of sql 2005.Will my asp.net 2005 application work there?I generated a script to export my sql server 2005 objectsLots of failures appeard.IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name =N'aspnet_WebEvent_FullAccess' AND type = 'R')CREATE ROLE [aspnet_WebEvent_FullAccess]GOThis is the error:Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'ROLE'.The object is part of the asp.net security/login components - I did not create these objects myself!Please tell me what to do next..........TIAGuy
View 5 Replies
View Related
May 24, 2007
Hi, What would I lose out(functionality wise) if I were to use MS SQL 2000 instead of 2005? What are the main differences, and considerations to be factored in before choose which database to work on?
View 2 Replies
View Related
