Bizarre Case Behavior.
Jul 23, 2005
I'm doing a select which includes the following:
case
when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
then ''
else
convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
',''),10))
end as QuarterStartDate
The COMMENT_4 field is a char(51), and contains values like: ' QTR:
03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
the convert to datetime within the case, data returns as expected,
retaining NULLs and blanks. If I leave the convert in, it converts
every row to datetime and seems to disregard the case expression.
I've attempted converting COMMENT_4 to a varchar first, and that didn't
help either. Also this does not seem to be affected by removal of the
LTRIM and RTRIM. Googling for this turns up no similar results. Have
I simply nested too far within the case, or what?
Any and all help and advice would be greatly appreciated.
Thanks!
View 2 Replies
ADVERTISEMENT
Feb 5, 2008
sql2k5
sp2
We moved a very tiny db (< 100 mb) from 2000 to 2005 (via attach/ detach) recently and now there are a couple sprocs which take forever to run from the app. Yes, the stats have since been updated as I reorg the indexes weekly. Now here is where it gets really strange, I can run this sproc in 0 seconds via Query Analyzer (QA). In other words I:
Have a developer run it from the app while monitoring in Profiler. There is a setting in the app for a 30 timeout, so after 30 seconds the app blows up and I see the exact code that the developer tried to run.
I can grab that code from Profiler, plop it into QA, and run it. Results are returned instantly. We have done this test a number of times, so I know it's not just a one time thing.
How can this be? The only thing that got changed in the app was the connection string to the new 2005 server.
View 9 Replies
View Related
Jul 5, 2006
Good afternoon,I have a bizarre question. When running the following query:select SomeColumnName from TableA where PK_TableA in(select PK_TableA from TableB)I get results. This should not be feasible, because the query withinthe in clause:select PK_TableA from TableBis not possible - there is no PK_TableA column within the TableB table.Running the sub-query alone gives an error, but when using it assub-query in the first statement, I get every row within TableA.If it helps any, the exact query I'm running is:select demonstratorid from DirectSalesAgent where DirectSalesAgentId in(select directsalesagentid from WebsiteSubscriptionPayment)Shouldn't a query return an error if the sub-query has an invalidcolumn name?
View 1 Replies
View Related
Feb 19, 2008
Running into a very bizarre problem here. I'm querying a couple of tables based on a date range. Nothing fancy; simply stuff. The query runs fine when I hard-code the dates, but when I use variables, the query just hangs.
This works fine:
SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>='2008-02-09 00:00:00.000' AND A.SomeDate<'2008-02-09 01:00:00.000'
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
This does NOT: NO clue why :confused:
DECLARE @FromDate DATETIME
, @ToDate DATETIME
SELECT @FromDate = '2008-02-09 00:00:00.000'
SELECT @ToDate = '2008-02-09 01:00:00.000'
SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
Any ideas?
-a
View 5 Replies
View Related
Jan 11, 2007
I previously posted about a problem where it seemed that changing thecase of the word "BY" in a SELECT query was causing it to run much muchfaster.Now I've hit the same thing again, where basically almost any change Imake to how the query is executed (so that it still performs the samefunction) causes the performance to jump from a dismal 7 or 8 secondsto instantaneous. It's a very simple query of the form:SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0which was running fine until a moment ago, when it suddently startedrunning hopelessly slowly. If change anything in the query tolowercase (or the Min to uppercase), it runs fine again. Last timesomeone suggested something about a bad plan being cached, and after abit of research I found the commands DBCC DROPCLEANBUFFERS and DBCCFREEPROCCACHE. Sure enough, after running these, the query startedrunning fine again. The question isa) why is this happening? Is it a bug in my code, or in SQL server?b) is it worth detecting it and fixing it automatically? I.e, should Iput some code in that notices that a query is running far too slowly,then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will thatcause other problems?Thanks
View 29 Replies
View Related
Jan 22, 2008
Background: We have an Asp.Net page where you enter a serial number (for a part my company manufactures), and it performs some updates to the data. Before it does, it does some simple selects for validation.Problem: Testers reported that the page will work fine for a while (5 minutes?), but then suddenly start throwing command timeout exceptions. I discovered that the timeouts always occur at the same validation query. Other queries are executed before this one, and they never timeout. The query is nothing fancy. It joins five tables, has a simple where clause, and selects a count of the records. It runs in Management Studio in under one second. The system is in a development environment under very little load. There's never a valid reason for the timeout. The where clause is parameterized, and the query itself is stored in our data access layer as a string. We execute it using SqlHelper's ExecuteScalar method. There's truly nothing remarkable about the query or how we execute it.After some playing with the page for a bit, and getting lots of successes, I'll get the timeout myself, and it will continue to occur for each additional attempt. If I let the system "rest" for a period of time (a couple of hours), it will be working again.The fun bit: When the system is in "timeout mode", if I go into our data access layer and add a single space character anywhere in the command text (where it has no semantic meaning) the timeouts go away. Take the extra space out, timeout again. Put it back in, no timeout. I did this (jaw agape) for a dozen times and the result was always the same!In previous attempts at debugging this problem, it would be in "timeout mode" and I'd make a semantic change to the query (hoping to fix it) and the problem would go away. I'd tell the testers it was fixed just to have them come back a day later and say it's not. That's what made me finally try the whitespace idea. Changing the string itself made the problem go away temporarily. What I do understand about this:SQL Server will cache the execution plan of a text query, and re-use it on successive queries. It probably caches it based on some hash of the query (just a guess), and changing one character would cause the hashing function to generate a different hash, hence it's a "different" query to SQL Server. So it won't use the same cached execution plan, and will make a new one. If the original execution plan was somehow borked, then that would explain why the timeouts return once I put the query back to it's exact original form. ('Cause it will use that same broken execution plan). This would also explain why letting the system rest would alleviate the problem. I imagine the execution plan eventually expires from the cache.One more thing: If I stop using SqlHelper, and contruct the SqlCommand myself, I can specify a command timeout. If I set it really high, it will eventually complete the query. It will just never do it within the default command timeout period. What exactly is going on is a mystery to me. I thought of a fix: Make the query non-constant and append a random comment at the end to make it appear different each time to SQL Server! LOL! If it came to that, I would immediately submit my own code to TheDailyWTF.com. :D
View 10 Replies
View Related
Jul 23, 2005
I am getting a failure on the db backup job of one of my maintenanceplans. It is coming back with the generic error message of,"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The stepfailed."I then checked the Database Maintenance Plan History page, but thisshows all the steps having run successfully. If I check the drives forthe actual backup files, they exist and look healthy too!There is plenty of space on the drives, so it is not that.I've checked the NT logs and all they say is,"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DBMaintenance Plan for All User Databases''(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:2005-06-29 21:00:04 - Message: The job failed. The Job was invoked bySchedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "Which doesn't give me any clues.Since the backups have actually run to success, I am going to take offthe option on the maintenance plan to "Verfiy the integrity of thebackup upon completion". Maybe it is this that is causing problems,rather than the backup?Anyone had anything similar?
View 4 Replies
View Related
Mar 1, 2006
I have a maintenance plan that consists of several parts. It basically backs up all the databases, deletes old backups and then shrinks the databases.
The odd thing is that it appears to back up all the databases, can't tell if it does step 2 or 3 and then it fails with the errors below.
How do i correct this short of throwing it out and starting from scratch?
This is a package originally from one server that i'm trying to deploy to a 2nd server. Already using configuration files to chagne the target connections etc.
thx.
PackageStart,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:16 PM,2/28/2006 5:09:16 PM,0,0x,Beginning of package execution.
OnError,WSWT4361,NT AUTHORITYSYSTEM,{56B53144-6DA7-4276-B37B-A09B1254DD3C},{56B53144-6DA7-4276-B37B-A09B1254DD3C},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,OnPreExecute,{B88BD5B5-138F-4024-A2A5-D60403296701},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 2 Replies
View Related
Feb 5, 2008
Good afternoon,
3 of my fellow employees and I am having some strange issues with SQL Server...
When we are connected to the local network, we are unable to log into our SQL Server Instances locally through SSMS or programs that use SQL Server unless we have our firewalls "default"(no exceptions). Of course, this is useless if we need to make client connections from other computers. So, if we add an exception, say "port 1433", the client CAN connect, but we can no longer connect locally...to ANY instances.
We have tried adding exceptions to the firewall for all SQL ports and programs. We've also made sure we've got the latest hotfixes and service packs. If I log in as the local computer admin and do not connect to the domain and turn off my firewall, everything works fine.
1. Tried enabling Remote Connections
2. Allowed multiple ports through firewall
3. Allowed multiple programs through firewall
4. Re-installed SQL
It's almost as if something on the local network or domain is causing an issue with our firewall.....
Anyone have any ideas?
Thanks,
View 2 Replies
View Related
Apr 18, 2008
I have a controller package that coordinates the execution of a few child packages. The controller is responsible for logging errors/events to a logging database: each event hander has a corresponding Execute SQL Task that executes an stored proc in the logging database.
The ServerName of the logging database connection is set by a package configuration environment variable (ENV_DB_SERVER).
The controller runs seemlessly on development. However, when we move it to UAT, we get this strange, and truncated, error:
Log Job History (ORD_Daily_CMIDW1_Load)
Step ID 1
Server NAMCFMSSDDB602
Job Name ORD_Daily_CMIDW1_Load
Step Name Controller CMIDW1 - Daily Load
Duration 00:00:12
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: RELXXXX. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:25:46 PM Error: 2008-04-14 13:25:57.97 Code: 0x00000000 Source: Log Package error of Job Description: String or binary data would be truncated. End Error Error: 2008-04-14 13:25:58.03 Code: 0xC002F210 Source: Log Package error of Job Execute SQL Task Description: Executing the query "EXEC usp_Log_Event @App_ID = 'ORD', @Comp_ID = 'DailyLoad-CMIDW1', @Comp_Start_Dt = '4/14/2008 1:25:57 PM', @Task_Start_Dt = '2008-04-14 13:25:57.958', @Task = 'Log Package start', @Status = 'Error', @Msg = '0: String or binary data would be truncated.', @Node = 'xxxxxx', @Executor = 'RELXXXXX" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connecti... The package execution fa... The step failed.
If you look at the stored procedure call, it has the message "0: String or binary data would be truncated." This is the message that should be logged to the logging db. However, the call itself appears to fail with "String or binary data would be truncated." Yet when we run the exact sp call, taken from the above error, against the db, it executes fine.
So we thought maybe when we moved to UAT the environment variable wasn't set properly and therefore the sp execution would fail.
Well, I tested that out on dev by changing the environment variable to trash and got this error (which is what one would expect):
Message
Executed as user: RELXXXXX. ...o acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:06:52.92 Code: 0xC00291EC Source: Log Package start Execute SQL Task Description: Failed to acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:07:09.24 Code: 0xC0202009 Source: Controller CITILINK - Daily Load Connection manager "conn ALERTS" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the . The step failed.
So it would seem that the UAT deployment is connecting to the logging database just fine, but is generating some kind of truncation error that seems impossible to reproduce on dev. We have tried everything we can to reverse-engineer the error on dev...
Anyone? There are bits of brick stuck to my forehead... =) Thanks a lot.
View 5 Replies
View Related
Oct 31, 2007
Hi
I have an excel file with 2 rows that I am importing into a staging table. The format on the excel sheet is a custom one which is DD-MMM-YY so in the actual cell the data appears as 01-NOV-07 and 30-OCT-07. The format of the data in the input window above the spreadsheet is 01/11/2007 and 30/10/2007.
The format in the Excel source of the data flow task is Unicode String [DT_WSTR] length 255. I then have a data conversion step that changes this to a string [DT_STR] length 255 as the staging table is non unicode. I then have a derived column function that simply does a ISNULL replace on NULL values to blank. Finally there is an OLE DB destination which is the staging table.
This is where the problem occurs. If I breakpoint the package here and look at the staging table the dates now read as 11/1/2007 and 10/30/2007. The data type in the staging table is varchar 255. I am not doing any other transformations or T-SQL stuff on the data - it is being flipped to MM/DD/YYYY during the import.
By the way I am english hence I need the english variation on the date. I know i could put some T-SQL in to flip it around to english again but wondered why it was happening like this
thanks in advance
View 1 Replies
View Related
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Jan 6, 2005
Hello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
View 4 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Aug 17, 2005
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies
View Related
May 29, 2008
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
View 5 Replies
View Related
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
Aug 19, 2007
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies
View Related
Nov 5, 2007
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
View 1 Replies
View Related
Feb 6, 2007
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.
View 10 Replies
View Related
Aug 29, 2007
I 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.
View 1 Replies
View Related
Feb 1, 2008
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 Related
Jan 14, 2005
I 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
View 1 Replies
View Related
Dec 17, 1999
I'd like to understant the Percent Log Used behavior...
I monitored the Percent Log Used. For a specific database, it was 50 % used. I backup up the transaction log and the Percent Log Used was still 50 % used. For another database, the log was 60 % used. After the backup, it was 80 % used!!!!
Would you help me to understant this situation?
Thank you,
Fabio
View 2 Replies
View Related
May 28, 1999
Hi all,
I have a question regarding some very odd NT/SQL behavior that is exhibited when running a shell command through xp_cmdshell. If I do a 'net use z: servernamesharename' from the xp_cmdshell, it shows up in Explorer with the icon for a local drive! This would not be more than an ordinary M$ bug, except for the fact if go to a command line, I can't do a 'net use z: /delete' because it says that the network drive isn't there! Can someone shed some light on this?
Thanks,
Ed Molinari
Emerald Solutions
View 2 Replies
View Related
Jun 3, 2004
Hi
I'm using MSSQL 2000 (with SP3) on Win2000.
Now, before installing the service pack I encountered with several strange bugs in the MSSQL mostly in queries that used TOP, gladly they were all fixed when installing the service pack... or so I thought...
So yesterday while trying to optimize a heavy query (7 joins - 2 of them are left joins from different tables crossed to the same table) I encountered yet again and with the latest service pack with an even stranger bug.
First the returned records are just not always the same, meaning, for example when I use TOP 465 in the SELECT statement, the last record which is 465 contains some value, when using TOP 466 the record before the last which is record 465 contains different value!... of course both with the same ORDER BY clause.
Also when I view the execution plan it's also not same in both cases, with TOP 465 it's one way (and much much faster) and with TOP 466 the plan is completely different and much slower...
Does anyone encountered with this phenomenon? Any suggestions?
BTW, don't pay too much attention to the number 465, in my case this is the border of the problem, after trying this query on different tables I found that each has it's own border that after it the TOP starts to freak out.
Thanks for the help!
Inon.
View 14 Replies
View Related
Jun 30, 2004
I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc.
View 1 Replies
View Related
Nov 29, 2005
I have a SP that usually works fine (0-16 CPU time, 40 ms Duration), but from time to time the server hangs with apparently no reason. The SP has a lock timeout set to 500, so it should abort if a lock timeout error (1222) occurs but it doesn't. The Profiler reports very long execution time (over 30 sec), and because of that all other SP calls are blocked, 'cause the transaction opened by the first sp execution is not finished yet.
Any other attempts to identify other blocking queries did not show me anything suspect (sp_lock, dbcc opentran) other then the usual blocked chain. I'm starting to think about an IO bottleneck, or IO failure, that could block the disk access and cause the delay. The status of RAID 5 is healthy.
The server is used as storage system for a website (approx. 2000 concurrent users), and occasionally I noticed an ASP queue, but this strange behavior occurs even during the peak-off hours.
Any thoughts ?
-----
HP Server - 2 CPU @ 3,4 ; 4 GB RAM; SCSI - RAID 5
Windows 2000 Advanced Server - SQL Server 2000 SP4
View 1 Replies
View Related
Feb 18, 2008
I would like to ask for help. We had no problems with dynamic queries in SQL 2000, which were very fast. But when we ran the same queries in SQL 2005, it was many times slower lasting several seconds. I guess it has something to do with creating execution plan, because when I run it second time, it is suddenly extremely fast. But after just a little change (like adding space character), the speed is very slow again. If it is caused by execution plan in SQL 2005, is it somehow possible to change its settings so that it will behave like in sql 2000?
Thank you for answers!
View 2 Replies
View Related
Jul 23, 2005
Hi folksI have an C# app. connecting to a MS-ACCESS database with several tables.In a specific situations I have problems with a DateTime type in a table.The problemis when I want to select records from a table in a specific period the dayand monthseems to be swapped in the query, but it only happens when the swappinggives avalid date eg.12/10/2005 (12. Oct. 2005) returns records on 10/12/2005 (10. Dec. 2005)23/05/2005 (23. May 2005) returns records correctly since 05/23/2005 is nota valid date with danish regional settings.The query is:"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE [BeginDate] >= #" +_start + "# " AND [BeginDate] <= #" + _end + "#"_start and _end are of type DateTimeMy PC in running with danish regional settings and if I shift to en-USsettings in the control panel, thisfixes the problem, but that is not a solution for me.Any suggestions to solve this problemThanks in advance.Kim W.
View 4 Replies
View Related
Apr 22, 2007
SQL Server 2000 SP4.Running the script below prints 'Unexpected':-----------------------------DECLARE @String AS varchar(1)SELECT @String = 'z'IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'PRINT 'Expected'ELSEPRINT 'Unexpected'-----------------------------If the @String variable is set to 'y' (or in fact any ANSI character otherthan 'z'), the result is 'Expected'. The comparison also evaluates asexpected if CHAR(255) is replaced with CHAR(254). The server collation, ifthat matters, is SQL_Latin1_General_CP1_CI_AS.It would be helpful to find the explanatin of this behavior. Thanks.--(remove a 9 to reply by email)
View 2 Replies
View Related
Nov 1, 2007
I am have the following code below on a standalone computer and it worked perfectly. Suddenly, without any significant changes to the code there were no Servers instances found on my local computer. I know there are several server instance on the computer. Why is it acting so unpredictable? The same thing happened when I tried SQLDMO.
// Get a list of SQL servers available on the networks
DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow drServer in dtSQLServers.Rows)
{
String ServerName;
ServerName = drServer["Server"].ToString();
if (drServer["Instance;] != null && drServer["Instance"].ToString().Length > 0)
ServerName += " + drServer["Instance"].ToString();
if (cmbServer.Items.IndexOf(ServerName) < 0)
cmbServer.Items.Add(ServerName);
}
View 3 Replies
View Related
Jul 20, 2006
I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.
Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)
create table #t2
(
ID int,
DateField datetime
)
insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)
select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField
drop table #t1
drop table #t2
The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?
Thanks in advance.
View 4 Replies
View Related
