Hi,anyone could explain this : I have installed the same asp.net application on 2 distinct hosts, each with its own SQL Server 2000 database.However, in some SQL queries in Stored Procedures, the results do not come out the same.For example, on one server the query "select @value = (select ....)-(select ...)" works and on the other one it returns a null valueAny hint ?Thanks for your helpJohann
View 2 RepliesI was able to successfully create a database maintenance plan for SQL Server 2000 Transaction Log Shipping for a few databases a few weeks ago. Yesterday, I created a few more but to my surprise, I can no longer do it. I can create a maintenance plan but the job it creates does not start even if I force the job to start. I did exactly the same thing as what I did (as I document everything I do) before but no luck.
Has anybody had this experience before?
Hi there,
We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.
To reproduce the issue you can do the following
Create this structure in a 2000 and 2005 server instances
CREATE TABLE test
(a int,
b varchar(30))
INSERT INTO test
(a, b)
VALUES (1, '2')
INSERT INTO test
(a, b)
VALUES (3, '3a')
INSERT INTO test
(a, b)
VALUES (4, '4')
Then run the following statement in both servers:
UPDATE test
SET a = ltrim(rtrim(b))
WHERE b NOT LIKE '%a%'
AND ltrim(rtrim(b)) <> a
In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:
Msg 245, Level 16, State 1, Line 20
Conversion failed when converting the varchar value '3a' to data type int.
By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.
I know fixing this instance itself is easy but I€™m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?
Any guidance is much appreciated.
Thanks!
Hello,
I have a quick question about automating installation of SQL 2005. This question doesn't relate as much to SQL 2005 as it does setting up a database server.
I know that it's usually standard practice to allow SQL to "lock pages in memory". What I am trying to do is find a command line utility that will allow me to add this step to the SQL install script. Any help is appreciated. Thank you.
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ???
is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
With Vista Ultimate (release), I installed SQL server 2005 Developer edition. I installed SP1, apparently successfully. But my Windows update history shows the SP being installed successfully every night. I have automatic updating turned on (via AD policy).
Has anyone seen this, and does anyone know how to fix it?
Thanks in advance.
Peter
A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2), and
one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.
jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.
Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.
Thanks,
-- Damodar Periwal
Hi!
I have created a setup in visual studio and set the sql server as a pre-requsites.
now when the setup is run from
setup.exe
it checks whether sql server is installed... if installed then continues otherwise install sql server express edition, now this installation is silent,
I need to set the sa password during installation of sql server . i have no option to set the password during installation, can any one help
Hello all,
I am intsalling SQL 2005 in an activepassive cluster. The OS is Windows 2003 with SP1. Everything installs OK but the sql server service cannot start afterwards. There are many access errors in the Security Event log - see example below - and numerous "SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. " errors in both the SQL and Application event logs. I have tried installing SQL in both mixed mode and under Windows Authentication and get the same errors. I can install SQL 2005 outside of the cluster on both nodes. Other items of note: This was a SQL 2000 cluster. I uninstalled SQL 2K, the virtual name was changed (IP remained the same) and the DNS entry was updated to reflect the new name. Early in the installation process - 3 or 4 days ago - I got a few errors about the Server Principal Name had to be updated but when I do a setspn -l the both nodes are listed and the entries look fine, though there are no entries for the sql service (MSSQLSvc). Not sure if there should be entries for the virtual name, but there aren't any for either the old or new name. Does the new virtual name need to be added to AD? Here is the security error - Event ID is 560 and it looks like there's a problem with Remote Access and is generated when trying to access either node:
Category: Object Access
Type: Failure Audit
Event_ID: 560
User: NT AuthorityNetwork Service
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: RemoteAccess
Handle ID: -
Operation ID: {0,134558507}
Process ID: 1516
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: CM-OHCO-DS006B$
Primary Domain: SWNA
Primary Logon ID: (0x0,0x3E7)
Client User Name: NETWORK SERVICE
Client Domain: NT AUTHORITY
Client Logon ID: (0x0,0x3E4)
Accesses: Query status of service
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x4
One other informational message that may be of use - looks like there's a problem with dll's for remote access, though this isn't pertaining to SQL:
The description for Event ID ( 0 ) in Source ( srmservice ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: srmservice error: 0, Started the agent (srmagent.exe)..
I have successfully installed SQL Server 2005 SP1 but when I go to the "Microsoft Update" site it is still shown as required. I have tried going back to an old image of the PC and installing SQL Server 2005 and SP1 again but still the "Microsoft Update" page is not picking up the fact that SP1 has installed successfully. Anyone had the same problem or know of a fix?
Steve
I've had some problems with Reporting Services and getting connected to the database server after configuration. I am now getting the following error message:
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) (rsRPCError)
In my Database Setup everything runs fine when I click apply except for the Setting Connection Info for Reporting Server this has a yellow triangle with a ! and says that the report server cannot access internal information.
I also have not been able to Initialize as there are no instances to initialize and none are created when I go through setup. Is there something I need to delete from the registry or the file system that does not get removed upon uninstall?
Thanks.
Emily
Hi,
Do we have to manually install the ODBC 3.70 drivers for SQL server 7.0, or do they get automatically installed during the SQL server 7.0 installation. I did not see anything when I installed. So I am thinking that the drivers have to be installed separately
Thanks
Sach
I really don't understand this, in an attempt to upgrade/install SP3a, I download the file sql2kdesksp3.exe, which from the documentation, you can either use for upgrading or new installs. I run a new install; in the new instance, I execute "select @@version" and get "Service Pack 1"??
View 8 Replies View Relatedthis may well be the wrong place for my post, if so please move it.
I have a variety of boxes with SQL tools installed on them. For some reason last week several of them seem to have gone a bit squif.
Server 1. Enterprise manager fails to run, giving message MMC could not create the snap-in. QA runs fine.
Server 2. Load EM click on a database and try Tools --> QA it comes back with File Not Found. Add isqlw as an external tool and you can run that ok, but still File Not found on the QA tab.
I know its not data corruption, but I hope someone can give me a clue on this!
Cheers!
I am currently attempting to install SQL Server 2005. I had to install once due to a faulty install now when I try re-install it says all components are installed and won't continue install procedure and shuts itself down. Anyone else have this issue and know the work around or fix?
View 5 Replies View RelatedHi everyone,
I can't figure out what this problem may be:
At my application I'm installing sqlexpr32.exe by doing this:
Code Block
System.Diagnostics.Process proc1 = System.Diagnostics.Process.Start(
Environment.ExpandEnvironmentVariables("%SQLHome%")+ @" sqlexpr32.exe", "/norebootchk /qb reboot=ReallySuppress addlocal=all " + "instancename=SQLEXPRESS SQLAUTOSTART=1");
proc1.WaitForExit();
proc1.Close();
when I do this for the first time the sql setup runs but gives me an error saying that the file sqlncli.msi (Native Client) was not found
but if I go to the temp directory the msi is there..
when I run the app for the second time the sqlexpr32.exe detects the Native Client setup file and does the installation perfectly..
What could it be? Some thread issues?
Thanx in advance
I had a working SQL 2005 that I had to uninstall. I tried to verify things were cleaned up appropriately after the uninstall and rebooted afterwards.
When I went to reinstall I choose to install on another logical drive under raid 5 (lets say H:) in the EXACT same configuration as it was previously installed sans some of the services. Post-installation the data, Install etc folders you would expect to be under H:Program FilesSQL Server are actually under C:Program FilesSQL Server and the only thing installed under the H: selection was the 90/ folder. We uninstalled again and went back through the process and confirmed the selection of drive H: and AGAIN it installed it all to C:.
Please help!!!!!!!
I completed an install of SQL 2005 SP2 on Windows Server 2003. Everything worked fine I can connect while logged into the box, while connected thru RDP. I can create databases add users without any issue. Until!!!!
I have a third party application (Tririga) which I run the install and indicate the SQL database (in this case local) The application begins to install but when I get to the point of adding the SA password the install fails.
"login fails for sa) 18456 sev 8 (wrong password) but it is the same password I use to connect to the DB. So I change the sa assword and try again and it still fails.
any suggestions would be nice. by the way on machines on which I have run an upgrade from 2000 (sql) to 2005 (sql) the install does not fail.
Hi All,
if someone can point me to documentation on this I would appreciate it.....
If there isn't any....
I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.
This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in?
Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....
It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do?
Thank You in Advance
Peter
www.peternolan.com
We are having some problems with the SQL Server 2005 Keep Alive mechanism causing dropped connections. The Keep Alive time and interval appear to work as documented. However the number of retries made before dropping the connection seems to be variable. When running Network Monitor I have seen this vary between 3 and 9 retries. The value for TcpMaxDataRetransmissions in the registry is set to 5. Does anyone know what the correct number of retries should be for SQL Server 2005 Keep Alive? Is this dynamically determined or is this a bug?
View 2 Replies View RelatedHi Friends,
I am struggling with creating a simple stored procedure, I want to take max(timekey) out in one variable @Val in a dynamic SQL statement as below in SP. When I create this SP, it creates successfully but when I run it I get error
Select @Val=MAX(TimeKey) FROM ABC
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Val".
Can someone help me in understanding this wierd behavior? And how to get rid of this. Table and SP scripts are as below
CREATE TABLE ABC (TimeKey int, Data_Val int)
CREATE Procedure MAXVal(@Table_Name Varchar(30))
AS
BEGIN
DECLARE @Val INT,
@SQL Varchar(100)
SET @SQL='Select @Val=MAX(TimeKey) FROM '+''+@Table_Name+''
EXECUTE (@SQL)
END
Thanks,
Kul
I am trying to install SQL 2005 that came with my Visual Studio 2008 on my Vista Machine, it will install successfully, but when I want to get to say, the Management Studio, or the Configuration Manager, there is no "Microsoft SQL 2005" node in my All Programs. what gives? you kind of need those. thanks.
View 1 Replies View RelatedI have two SQL Server Instances on two servers. One server is my webserver and database server and the other one is just a database server. i have an application that calls a stored procedure located on the webserver/database server that runs a query on the OTHER database server. I use linked tables in my first instance to make the call possible.
Everything was working just fine for months until the database server was restarted and the IP address was changed. The name of the database is the same however and my first SQL Server instance has no problems running queries on the other databases tables. However, when you try to run the application i get the following error:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection
I have mixed mode authentication selected and my security uses the security context with username=sa and password=sa.
So here's the weird part.
The application will only run correctly when i manually run a SQL command from my webserver's SQL Analyzer on the linked SQL Server. however, after a few minutes, the same error comes back!! so as a temporary fix, i scheduled a dts job to run a simple query on the linked server every two minutes, so the application keeps working! It's almost as if the webserver's sql server forgot that the linked server is there, and by running a simple query in query analyer, the connection gets refreshed and everythings normal again - for about 3 minutes!
I am completely stumped by whats happeneing and appreciate any help. Thank you.
I'm a wee bit of a newbie concerning DTS and have inherited a db with a DTS containing a Copy SQL Server Objects task set to run nightly. Essentially, it does an informal backup of some core data.
Recently, I was notified that one of the tables it copies over is now empty on the Destination db. The DTS shows that it runs successfully with no errors logged, the table in question IS selected to be copied from the Source database, there IS data in the Source database table, and every other table in the Destination database is populated appropriately.
Any ideas on what would cause this one table to be empty without generating any errors?
FYI, running SQL Server 2000.
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
http://msdn.microsoft.com/vstudio/express/sql/download/
Thank you,
Bashman
I nabbed a couple from some recent installs I did, and, assuming I can get my SAN guy to always give drives the same letter assignment, it looks fairly useful.
There's still a bunch of additional stuff to do, like turn on instant file initialization, size/create additional tempdb files, and set up alerts and database mail, etc.
Can the configuration file be expanded to handle any of that? Do you use something else to template/automate your SQL installs? The more I do it, the less I want to keep doing it.
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
This was originally posted on DBForums.com, so here is the link:
http://www.dbforums.com/showthread.php?t=1614086
Since some of the Microsoft staff come around here occasionally, I figured I should at least link to it here. This is the gist of the problem, though. I was asked to come up with a script to create all required data directories in case an emergency was declared, and someone had to rebuild one of our database servers. Most of you are probably thinking of hitting up the sysaltfiles table about now, but this will turn into a cautionary tale. Try it if you dare. The one requirement is that you install the data for SQL Server in a non-standard directory that has a short path (such as C:MSSQL8, instead of the whole C:Program files...).
What I am unclear on is whether this is a problem in the reverse function, the r(l)trim function, or the fixed-width datatype. I have confirmed that transferring the data to a temp table did not eliminate the...oddity.
select filename
from master..sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename)), filename
from sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename))
from sysaltfiles
where dbid = 2
I have also had two independent DBAs confirm this oddity exists, so this should be relatively easy to replicate.
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 Replies View RelatedI have three tables I am using, aspnet_Users, Stories,
CustomizedStory. Stories and
CustomizedStory are related via a foreign key StoryID. I’ve setup the tables so that when I delete a
Story row it cascade deletes the corresponding row from CustomizedStory.
Each CustomizedStory row has a reference to UserID from aspnet_Users.
Since, I didn’t want to mess with the table definition by adding a cascade
delete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:ALTER
TRIGGER [dbo].[DeleteCustomizedStories] ON [dbo].[aspnet_Users] FOR DELETEAS
BEGIN DELETE FROM dbo.Story
WHERE StoryID
= (SELECT StoryID FROM dbo.CustomizedStory WHERE
UserID = (SELECT UserID FROM deleted))END
The problem I am having is that it deletes all of the
CustomizedStory rows as specified by the cascading option, but doesn’t delete
the Story rows. I can’t seem to understand
why this is happening, especially when I
explicitly told it to delete story rows.
I've done a new tabel that insert the UserId that in a uniqueidentifier get from Membership.GetUser().ProviderUserKeySo if I want to make a select statement threw storedprocedure in codebehind it runs as it shouldCode behindDim GetCustomersCars As CustomerCarByUserId = New CustomerCarByUserId MyCars.DataSource = GetCustomersCars.CarByUserId(Membership.GetUser().ProviderUserKey)MyCars.DataBind() But in when I use ObjectDataSource it fails<asp:ObjectDataSource id="ObjectDataSource1" runat="server" selectmethod="CarByUserId" typename="CustomerCarByUserId"> <SelectParameters> <asp:Parameter defaultvalue="Membership.GetUser().ProviderUserKey" name="UserId" type="Object" /> </SelectParameters> </asp:ObjectDataSource>I've tried with Membership.GetUser().ProviderUserKey.ToString(), but that doesnt work. Error message:InvalidCastExceptionI connect to the same source in both cases.Any one with an Idee ?
View 1 Replies View RelatedI get the error " 'commandBehavior' not declared". What does this mean? What is CommandBehavior exactly?
'
Sub Page_Load(Sender As Object, e As EventArgs)
' Obtain categoryId from QueryString
Dim connectionString As String = "server=(local); trusted_connection=true; database=SalesSide"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "Select tblIdent.fldStockNo, tblIdent.fldProgram, tblIdent.fldGenus" & _
"tblIdent.fldVariety, tblIdent.fldSize, tblAvailability.fldQuantity" & _
"FROM tblIdent INNER JOIN tblIdent On tblAvailability.fldStockNo = tblIdent.fldStockNo"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open
dgAvailability.DataSource = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
dgAvailability.DataBind()
sqlConnection.Close
End Sub