Hi Guys,
I have got a weird problem which I have never faced. I have a table which has 3.8 million records. I found this information from sysindexes since I could not find the count using the Count(*). After breaking my head for a while I tried to use the Top function to get the results and I did get them but till 630000 record not a record more than that. I tried DBCC CheckDB as well as DBCC Checktable with no success. I would really appreciate if anyone can solve this problem.
I have created the following test SQL code to illustrate a realproblem I have with some SQL code.CREATE TABLE JCTable ( CustomerName varchar(50) )ALTER TABLE JCTable ADD CustomerNo intINSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe', 1 )INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'BillGates' , 1 )UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'SELECT * FROM JCTableWhen I run this SQL via the query analyser I get the errors:-Server: Msg 207, Level 16, State 1, Line 1Invalid column name 'CustomerNo'.Server: Msg 207, Level 16, State 1, Line 1Invalid column name 'CustomerNo'.Server: Msg 207, Level 16, State 1, Line 1Invalid column name 'CustomerNo'.It appears the SQL Server is trying to "pre-parse" the query andhasn't picked up on the ALTER TABLE line that adds this column and socomplains that it doesn't exist. However it doesn't end there.If I then run this query a line at a time by highlighting each line inthe query analyser and running it all works (not unexpected). Howeverthen dropping the table and then running the full SQL code once more(I.E. not highlighting each line), it then works as expected. I assumeit was somehow remembering "state" in my session so closed andre-started the Query Analyser with the same result that the code doesnow work.However changing the table name to something new brings back theerrors once more. Can anyone explain what is going on here? I amdropping the table before re-running the code each time.I'm using SQL Server 2000 if that makes a difference.Thanks.Jon.
I've set up a basic login page that grabs some data from sql server. I've set up an sql server user for the connection and it all works fine.... until now that is... I get an "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied." error..
Here's the weird thing though - I'm storing my connection string in web.config. If I open up web.config and re-save it, my login system works again - for a while anyway. Then it goes back to not working. When you save a web.config file, is it compiled in any sort of way? because when I do save it and run my web page, there's that sort of delay the first time round like there is when you update an aspx page or a dll. ? Anyway - it's as if that process sorts out my problem but only for a an hour or so and then the problem happens again.
I'm not making any changes to the web.config, just re-saving it. And everything else works fine, for a while at least, then the sql error happens.
Hi, I just installed SQL Server 2005 EE. After installed it, I took a look at the program. However, I found out that SQL Server 2005 EE doesn't any program that you can create your database on. There nothing like Ms. Access where you can run the program to create access database file. SQL Server 2005 EE doesn't have something like that so that I will have to create the database file under Visual Studio 2005 applications such as VWD or VB.
Is this just me or is that the way SQL Server 2005 EE is?
I have an application which is a SQL Server 7 back end with VB 6 SP5 front end.
For some of my users, when the VB code calls a particular stored procedure, it causes VB to die without executing the stored procedure. I am connecting via ADO.
If I execute the same stored procedure via Query Analyzer on the same machines it works perfectly.
The same code also works perfectly on all the development machines.
The error only occurs for a particular stored procedure. Other stored procedures execute perfectly on the users' machines.
I have a database which takes up 12.5Gb of disk space yet when I drop all the objects out of it, it says that I still have 9.5 Gb of disk taken up by the database!
The database itself is nothing special. There are a couple of inefficient tables (char rather than varchar etc) and one that contains a column of datatype "image" but other than that it is pretty good.
Here's a weird one.I'm running SQL Server 7 and when I run a backup something weirdhappens. When I perform the backup via Enterprise Manager by rightclicking on the database I want to backup, I click on OK but noprogress blocks show up in the window showing you the status of thebackup. The completion window pops up saying that the DB has beenbacked up. OK--Fine, maybe the backup is really quick. Then, throughExplorer, I look at the directory where the database backup is placed.It reads 0 KB - but - about a minute later the the size of the backupchanges to the correct size. Seems strange, wouldn't the "completed"window show up after the backup is out there??Well, to make matters a little more interesting, when I define thisone database in a maintenance plan, the plan will complete but nobackup is present! The log file shows it runs OK - and - when I runthe plan through query analyser it says OK, too. But, no backup ispresent.What gives?
I 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.
Hi, This thread is a reformulation of a prior thread. I created a login 'Network service' at server level in Management Studio express.I use windows authentification.Then i defined an user for my database which is associated to login 'Network service', because the application asp.net uses that account (IIS 6.0). This user received db_read and db_write roles.This works.Now i experimented a little bit and i removed from the logins at server level the login 'Network service'.Result: the application still works..Then i removed the Builtinusers login from the login list at server level.Result: i get the error: "login failed for Network service".I recreated then the login 'Network Service' at server level but not the Builtinusers login.Result: it works again.My conclusion is: one of the two logins must be in the list: Network Service or BuiltinusersIs this right?Why do i get that error when both logins are removed and not only when Network Service is removed?Thanks
Execute following T-SQL within Queary Analyzer of SQL Server 2000:=======================================DECLARE @dTest DATETIMESET @dTest='2001-1-1 1:1:1:991'SELECT @dTestSET @dTest='2001-1-1 1:1:1:997'SELECT @dTestSET @dTest='2001-1-1 1:1:1:999'SELECT @dTest=======================================You get what?This is my result which is weird:2001-01-01 01:01:01.9902001-01-01 01:01:01.9972001-01-01 01:01:02.000Then what's the reason of this weird problem?
We've had Reporting Services running in a production environ. for 6 months fine, but from Saturday every report now causes the following error (in both the Report Manager and Soap calls):
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name: date
Now, before you jump to conclusions - this error is occurring on reports with both parameters and no parameters (ie in reports that have no "date" parameter in the report).
The next bit of info is the weird bit...
It was working on Friday (25/March/2006) - so as a test, i switched the servers clock back to Friday - and BINGO... it worked. Then I changed it to Saturday (26th March) and it doesnt work. In fact for the next 7 days - the service will not work until April 2nd 2006 - (when I changed the systems date to the 2nd it worked again.) Moving forward, it looks like its working fine.
Does anyone have any suggestions? This is in a production environment, so obviously changing the sytsem date as a quick fix workaround wont suffice.
I have a dts package that is mysteriously changing back to a previously saved version. Package is saved, and re-opened with saved changes, then moments later opened again to have been reverted back to an older version. Has anyone ran into this before? The only thing that I can think of is that if a version of the package is left open on some other machine (as the older version0, it may be auto-saving, and reverting back.
I run a website, so all of this is done remotely, I do not have access to the servers directly.
On my prior host, everything on the DB worked just fine. Had it optimized and running pretty good for the site by adding indexes, etc.
Then I moved to my new faster server (new host) and at first everything seemed to be just fine. However, some users started complaining about speed in spots.
Sure enough when I checked a given page -- WHAM... the page took about 14 seconds to load. Not exactly fast. :)
So I checked to make sure the indexes copied over and sure enough they did. But it was still slow, even in Query Analyzer/MSE.
So I decided to rerun my view script... on a lark... just to see if that affected something. And sure enough it did. The page started running at 1 second or less. OK, I thought, the view just got corrupted or something.
All was fine until the next night when it happened again. I had added 12 new rows to the table (I do this nightly) and it seemed that adding the new rows slowed down the system. Until the view was rebuilt. With that done again, it's worked fine.
But each night now I have to rerun that view.
What gives??? Is there a DB setting or something causing views/indexes to not be maintained?
The prior server was 2000 or 2003. The new one is 2005.
Again, I cannot get access to the box itself except through Management Studio. The host's tech support group is less than useless, treating everyone as incompetent before they help you.
I'm trying to CAST an integer to a varchar and I keep getting e+006 in my result set. The reason I need to do this is so that I can combine 2 columns to return a string.
I was developing a data-driven website but I got this error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(I have Sql server 2005 also downloaded before) Please help me cause any website that has data can not be executed even the quick start tutorial that ships with the ASP.NET 2.0 What should I do?
Hello and thanks for taking a moment to read this message.I have two colums from which I am taking values. One of which(a bit field), i am running a CASE statement against it to get SQL Server to return a string. The other is just a simple varchar column. My SELECT statement for the columns look like this: SELECT Case tblDisplayProfile.Approved When 0 then 'Not Approved' when 1 then 'Approved' else 'Not looked at' END AS Approved, tblDisplayProfile.DisplayProfileDesc These statements do return me something. Now what I want to do is combine (concantenate) the two fields. This is where I have problems. Any suggestions would be greatly appreciated.
Why does ado.net produce this error when I kow for a fact that there are no connections to any sql server 2005 database involved - this could and probbaly has had people off looking at the wrong connection strings in their web.configs. Note:- The connection string we have is to a sql 200 db I just wanted to make that clear. CheersGegor Error Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
Hi, I moved a SQL 2000 DB to a SQL 2005 server. When I run my .NET 1.1 or 3.5 website to call a stored procedure it returns the wrong results.The SQL stored proc runs fine when I excute it within SQL Manager but gives a different result when called via .NET The funny thing is I have 3 SQL servers 2000 , 2005 , and another 2005. The same data on each of the databases and the same website. Two of the databases work but the second 2005 database my production server decrements the dates some how. The funny thing is the stroed proc is not returning dates as such but a varchar of the name of the day that's getting decremented. My stored proc is SELECT
Case datepart(dw, DT_DATE) When 2 Then 'Mon' When 3 Then 'Tue' When 4 Then 'Wed' When 5 Then 'Thu' When 6 Then 'Fri' End AS [WeekDay], Sum(VL_HOURS) as Total
FROM ...
WHERE ....
GROUP BY datepart(dw, DT_DATE) and My c# .net code is "SqlHelper.ExecuteReader( connectionstring, storedproc, paramaeters);"using MS.ApplicationBlocksWhat happens that from sql, and 2 of my websites is when week day = 4 it returns "Wed" However on my Production server it returns "Tue" ???
i have a form which takes in a lot of input...one of them is a date field..am passing all the values to the stored proc and inserting into a table from there...pretty simple...however when the user does not enter any value in the field...its errors out as...
Hello! MSSQL Server 7.0 SP1 When I open DTS designer for some unknown reason I'm getting error message "Could not create Component Categories manager". Then under Task I don't have any choices. Is there a quick way to fix it?
I was running a DTS package which transfer a complete database, about 2.3GB in size, to another database on the same server. It was necessary for environment change from dev to QA. I baby sat the package till it was rebuilding indexes (90%) complete. This morning I have the following error message on my screen,
[Microsoft][ODBC SQL Server Driver][SQL Server] [Microsoft][ODBC SQL Server Driver][SQL Server]Location: r:SPHINXNTDBMSqueryqeexecqsxchng.inl:749 Expression: (m_cbPageMac + ulPxvarSize) <=m_pxpktdesc->m_cbPageMax SPID: 40 ProcessID: 347
I have no idea at this time what caused it. And as far as I can tell there in r drive mapped wither on the serve or my computer ( I was running the DTS package from my machine).Any help is appreciated
Last night our production server went crazy. The primary (production) database MDF file had size 15Gb and then for some reason the size became 0 (zero) K, So the SQL right away reported the error:
I/O error 38(Reached end of file.) detected during read of page buffer..
Error: 823, Severity: 24, State: 10
And the database became "suspected". Now we restoring it, but have anybody seen such a thing before? I'm breaking my head to figure out what could cause this problem!!!!!
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o( what maybe the problem?
When I run the command: exec master..xp_cmdshell 'NET USE' from the analyzer the box responds there are no entries in the list.
After that, I run the command: exec master..xp_cmdshell 'NET USE Z: /DELETE' after which the box responds with a "network connection could not be found." and that's all okay.
The weird thing is: exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME' results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
RETURNS DateTime
As BEGIN DECLARE @CheckDate varChar(300) SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267') BEGIN SET @Date = Null END RETURN (@Date) END
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23 An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method? :(
INSERT INTO Contact_Info (First,Last,Age) VALUES ('Alex','Strait',19)
Now it seems basic, but there in one more field that you're not supposed to specifiy a value in the SQL, its an int IDENTITY datatype field called num_id. Now this SQL works if it doesnt have the num_id field. But heres the error is gives me, I have never seen it before
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
I have this stored procedure that works a little odd, some times it do what I want it to do which is....
- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders
but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method?
have a dts package that is run by a scheduled job which was runningsuccessfully for several months and suddenly stopped working.The dts package does the following: Truncates the table, does a datapump from a specific excel file to a table on sql server, deletes theexcel file. The job does the following:Step 1: Look to see if a file exists and if so, run the dts package. Onsuccess of this step the job goes to step 2.Step2: Looks to see if any files still exist after the first stepStep3: Failure notification. To be triggered on failure of step 1 or2.The owner of the job is the Account that is used to run theSQLServiceAgent and in the SysAdmin server role.The owner of the job is also in the SysAdmin server role.Both the accounts are Windows Accounts.The job runs at schedule time, finds the file, and runs the dtspackage. The first part of the dts package runs successfully - Thetable is cleared. However it is not doing the data pump part. Thereare no failure notifications. The job then goes to step two and sendsa message that the file still exists. The dts task is set to output anerror file which is not being updated, which tells that the task is notexecuted. If the dts package is run manually, then the package runssuccessfully.Any help would be appreciated.ThanksKR
I have just installed my server with server2003 enterprise edition, everything is working fine. However, after I make it online for awhile, I found a weird thing on the tool bar just beside the clock. There are 2 hidden icons, when I move the pointer to these 2 icons, it shows the IP address of my server. Just wanna know if there is anything wrong with that.
I'm seeing some strange behavior from a stored procedure of mine. It essentially grabs a bunch of rows using a fairly simple JOIN....here's the from statement:
Code Snippet FROM Payment PY (NOLOCK) JOIN (SELECT DISTINCT PY.AccountPaymentId, ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId ASC) AS RowNum FROM Payment PY (NOLOCK)) AS SQ ON (SQ.AccountPaymentId = PY.AccountPaymentId) INNER JOIN Payee PE ON PE.PayeeId = PY.PayeeId INNER JOIN Party PT ON PE.PartyId = PT.PartyId INNER JOIN Distribution DS ON PY.DistributionId = DS.DistributionId LEFT OUTER JOIN Account AC ON DS.AccountId = AC.AccountId INNER JOIN clm CM ON PE.clm_no = cm.clm_no LEFT OUTER JOIN PartyAddress PA ON PY.PartyAddressId = PA.PartyAddressId AND PT.PartyId = PA.PartyId WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize and ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%')) AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId)) AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId)) AND ((@PaymentDate IS NULL) OR (DATEADD(day, DATEDIFF(day, 0, PY.PaymentDate), 0) = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber)) AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled)) AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued)) AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid)) AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus)) AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus)) AND ((@IsReissued IS NULL) OR (PY.ReissuedInd = @IsReissued)) ORDER BY AccountPaymentID ASC
When I pass a 1 for the @IsReconciled parameter, I get the right number of rows back - 9779. But when I pass a 0 (zero), i get no rows back, although there are 222 rows which satisfy the condition.
Is there somethig I'm overlooking (I don't think I am...)? I don't know whay 1 works and 0 wouldn't...
FYI - the @IsReconciled parameter is set to NULL at the outset of the procedure -
Hi, I hope someone can help me with a weird problem I'm having. Towards the end of last week a number of users started reporting problems with one of our legacy applications. The application is written in VB6 connecting via ADO to a SQL 7 database. I managed to trace the error to a piece of inline SQL (shock!horror!) in a DLL that hasn't been changed since 2002, it occurs on the adodb recordset open and generates the error "could not complete cursor operation because the table schema changed after the cursor was declared" although nothing has changed on the database. It does not always happen, it depends on the contents of the strOrganisationIDs and the order of the numbers, although the same combination of numbers will generate the error every time! Initially there were certain users who were not having the error, but they have all gradually 'caught up'! There were some windows updates rolled out about the same time the errors started occuring, but un-installing them makes no difference. Here is a snippet of the code:-
Dim rsRecordSet As ADODB.Recordset Dim strOrganisationIDs As String
strOrganisationIDs = "(111974,21986,0)"
strSQL = "SELECT o.organisation_id, o.name, o.town, o.county," & _ " o.country, o.key_customer, i.industry_sector," & _ "(SELECT Name FROM SODA_User WHERE User_Id = o.User_ID) as Name1," & _ "(SELECT Name FROM SODA_User WHERE User_ID = o.Assignee) AS Name2," & _ "o.registered_on_web " & _ " FROM Organisation o, Industry_Sector i WHERE o.Sector_ID = i.Sector_ID" & _ " AND Organisation_Id IN " & strOrganisationIDs & _ "ORDER BY o.Search_Name" rsRecordset.open strSQL
I have managed to find a way around the error - I removed the spaces in front of the o.country, don't know why they were there, and they shouldn't cause the error - in fact they are there on the occasions it works, it seems to be the combination of spaces and certain numbers in the strOrganisationIDs . It sounds stupid and i've been pulling my hair out looking for a reason, as have several colleagues. I don't want to have to roll out the fix as it will be quite messy and affects a lot of users, although i may have to - but what I really want to know is why it has happened. I hope someone can shed some light on this, if any other info is required, let me know.
