Odd Null Behavior

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


ADVERTISEMENT

Oracle Empty String == NULL Behavior In SQLServer 2k5?

Feb 15, 2007

I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.

Can this be done? I couldn't find a setting for it.

Thanx

Peter

View 13 Replies View Related

How Can NULL Be Equal To Today's Date (was Weird Query Behavior Plz Help)

Feb 7, 2005

I have a query that is behaving a little a weird. here is the example:

i have 1 table in this table i have 2 columns wich are date and time

DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
------------------------------ ---------------------------
1/27/1997 2/27/1997
1/1/2005 2/6/2005

here is my query:

BEGIN
declare @datefin_flag datetime, @strip datetime
SELECT @strip = dateadd(d,datediff(d,0,getdate()),0)
SELECT @datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < @strip AND DATE_FIN_PERIODE_FISCALE = @strip
--select @datefin_flag
--select @strip
IF(@datefin_flag != @strip)
RAISERROR('You cant run this',16,1)
END

Well this Query should return the raiserror it returns completes successfuly
since todays date is not the same as the date in the database.
if you select @datefin_flag it returns NULL and if you select @strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?

View 7 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

Odd Behavior

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

Odd SQLTrigger Behavior

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

Strange Behavior

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

Command Behavior

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

Percent Log Used Behavior

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

Xp_cmdshell Behavior

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

Strage Behavior When Using TOP

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

Odd Scheduled Job Behavior

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

Strange Behavior

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

Different Behavior In Dynamic Sql

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

Strange Behavior

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

Unexpected LIKE Behavior

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

Strange SMO Behavior

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

Is_member Behavior

Dec 11, 2006

Hi All -

I am running sql2005 on windows 2003 all with uptodate SPs

Im sure this has been discussed somewhere, but I just dont understand why its behaving this way.

my issue is that if a 'db_owner' is a member of another db role then the is_member('some_other_role') always returns 0

example:

dbrole: testrole
ntuser: joeuser
ntgroup: testgroup
ntgroup: grpsysadmins

joeuser is a domain login.
testgroup and grpsysadmins are both nt groups.
joeuser is a member of both testgroup and grpsysadmins
grpsysadmins is a member of administrators grp on the sql server machine
testrole is a sql role
testgroup is a login into sql
testgroup is a member of testrole

1) when I do select is_member('db_owner') I get 1
2) when I do select is_member('testrole') I get 0 <== this is the wierd one
3) when I do select is_member('machinename estgroup') I get 1

I am expecting all 3 to return 1. now if I am logged as a user that is not sysadmin then select is_member('testrole') returns 1.

this behavior seems to be by design, but for the life of me i have no idea why?

View 7 Replies View Related

Weird BIT Behavior...

Aug 29, 2007

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 -

@IsReconciled Bit = Null

View 1 Replies View Related

Is This Expected Behavior?

Aug 8, 2007

I posted this at the asp.net forums but somone suggested I post it here. So:




Try this in sql server:

select COALESCE(a1, char(254)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

select COALESCE(a1, char(255)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1



The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:

W
Y
Ya
Z
þ

The second one sorts like this:

W
Y
ÿ
Ya
Z

Is this expected behavior?

View 1 Replies View Related

JOIN Behavior

Aug 29, 2007

I'm seeing mixed results when I run a certain JOIN query...

Here's the original query:



Code Snippet
SELECT DISTINCT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.ReissuedInd -- Added 8/15/2007 AMR
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
--Removed 8/14/2007 AMR , AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno FROM Payment PY (NOLOCK)
LEFT JOIN (SELECT PY.AccountPaymentId, PA.AddressLine1, ROW_NUMBER() OVER(ORDER BY PA.addressline1 ASC) AS RowNum
FROM PartyAddress AS PA INNER JOIN
Payee AS PE ON PA.PartyId = PE.PartyId INNER JOIN
Payment AS PY ON PE.PayeeId = PY.PayeeId
) 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 (((1*1000) - 1000) + 1) AND ((1*1000) - 1000) + 1000




This query numbers rows based on the PartyAddress. When I keep the WHERE clause in the query, it returns 810 rows. There exists a relationship between PARTY and PARTYADDRESS. There are 10001 rows in the PARTY table and 810 corresponding rows in the PARTYADDRESS table. Not every PARTY will have a corresponding PARTYADDRESS.

What the query SHOULD do is return all rows from PARTY and it's corresponding PARTADDRESS entry, if there is one.

When I take the WHERE clause out, I get back all 10001 rows - what I'm expecting.

Why would the "WHERE RowNum" clause affect the wuery? How can I get around that?

Thanks!!

View 1 Replies View Related

Strange Behavior

Mar 5, 2008

I have 2 packages that for ease I'll call Parent & Child. The Parent package calls the Child package as the 4th step in the process. Once the Child has completed, the Parent has a few more imports that it does.

The Portfolio table is loaded in the Child package which is step 4 in the Parent package. Then in step 5 a few tasks utilize that Portfolio data for lookups.

The strange part is that there are probably 4 or 5 data tasks that do lookups against the Portfolio data in Step 5 (step 5 is a container). All but 2 of the data tasks retreive data from the Portfolio data. The other 2 don't find any data and just move on. Once the package stops, if I simply execute those tasks they run and load the data correctly.

It seems to me to be a caching or an isolation problem but I can't find a solution.

Any ideas?

View 7 Replies View Related

Backup, Truncation, Behavior

Mar 22, 2005

Scenario:
I have a database in SQL Server 2000 with 90gb of data. It is growing every day with a some millions records. In order to get superior performance I rebuild the indexes every night. The job takes about 1.5 hours, and sometimes causes to the log file to grow to 30-40 gigs. I do a backup of the log files every 20 minutes, and this causes an log truncation to happen automatically (according to the MS documention).
Problem:
The log files makes the disk goes full. I cannot insert new disks at this point.
Question:
When rebuilding the indexes takes 1.5 hours, and I do a log backup (which also does a truncation) every 20 minutes, will the log backup ever get to do something with the transaction log while its still working on a transaction (doing the rebuild of index)?
Observation:
It seems like the 20 minute log backup is of no help because I see the log files grows so big.

BR
Henrik
Norway

View 2 Replies View Related

Puzzling NOT EXISTS Behavior

Aug 10, 2005

CASE CLOSED: removing the FROM clause noted below handles the problem ....I've used "NOT EXISTS" in inserts before and thought I understood how they work, but I'm puzzled why I get three rows from the following sub-query, which I want to use to prevent errors from duplicate keys. Since it gives me three rows, it will actually try to insert three duplicate records and cause a primary-key fault, which is a twin to the very thing I'm trying to avoid. (of course, there are three records already in the table, none having the key of 20050810)The sub-query is acting like it's using "WHERE DemoDate <> 20050810" instead of a "NOT EXISTS".Attempts to use this statement causes a termination with no records inserted.DemoDate is the primary key and an INT field in SQL Server. Four other int columns for this table have default values.<code>INSERT INTO DemoStats (DemoDate)SELECT     20050810 AS Expr1FROM         DemoStats         <-- remove this line so the effective table has only one row, when the NOT EXISTS is TRUEWHERE     (NOT EXISTS                          (SELECT     *                            FROM          DemoStats                            WHERE      DemoDate = 20050810))</code>

View 5 Replies View Related

Odd Sqldatasource Insert Behavior

Jan 20, 2006

I have a sqldatasource (code listed below) whose insert Paramaters are control parameters.  My aspx page has a textbox and a submit button.  the button onclick runs the sqdatasource1.insert.
What I get is every other insert inserts the text in textbox2 and every other insert enters nothing for the namecust value.  I have a required field validator which correctly prevents submission if textbox2 is empty. 
How do I fix this?
:<code>
<asp:Panel ID="Panel1" runat="server" Height="50px" Width="548px">
<asp:Button ID="Button1" runat="server" Text="New Prospect" ValidationGroup="insertCust" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox2"
ErrorMessage="Prospect Name can not be blank" ValidationGroup="insertCust"></asp:RequiredFieldValidator>
<asp:TextBox ID="TextBox2" runat="server" Width="330px" ValidationGroup="insertCust"></asp:TextBox></asp:Panel>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"
SelectCommand="SELECT DISTINCT CODETERR FROM dbo.F_arcus() AS F_arcus_1 WHERE (DATEINAC = 0) AND (rtrim(CODETERR) <>'')">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"
InsertCommand="INSERT INTO dbo.BudgetProspects(NameCust, CodeTerr) VALUES (@Namecust, @codeterr)"
SelectCommand="SELECT CustomerID, NameCust FROM dbo.BudgetProspects WHERE (CodeTerr = @codeterr)"
UpdateCommand="UPDATE dbo.BudgetProspects SET NameCust = @namecust">
<UpdateParameters>
<asp:Parameter Name="namecust" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="RadioButtonList1" Name="codeterr" PropertyName="SelectedValue" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter ControlID="textbox2" Name="Namecust" PropertyName="text" />
<asp:ControlParameter ControlID="RadioButtonList1" Name="codeterr" PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
</code>
codebehind button_click:
<code>
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not TextBox2.Text Is Nothing Then
SqlDataSource1.Insert()
TextBox2.Text = ""
End If
End Sub
</code>

View 1 Replies View Related

Very Strange Behavior Of SS2K...

Aug 2, 2005

Hi all,
I face a problem as follows: We have an application runnig on SS2K.We log every delete of

documents(from Archive table) in another table.Now it seems some of the rows have deleted strangely

without any delete log by our application.We assumed there is somebody who has direct access to

database and delete them manually(obviousely our app does not generate any log in this situation)But

there is no people.We check that with admins many times.
Does SQL Server itself deletes rows for any reason? How can I know what is happening?Do you think

our app flaws somewhere?
Thanks a lot for your attention.

View 1 Replies View Related

Stored Procedure Behavior

Jun 4, 1999

I have a stored procedure that does a lookup on a particular field. Sometimes it runs almost instantly and other times it drags. Running the proc through ISQL it will come back with less than 10 logical reads and other times it comes back with over 800,000 logical reads. This only happens on our production box(of course). Anybody seen anything like this? Thanks
-Bob-

View 1 Replies View Related

Very Strange Behavior Of SS2K...

Aug 2, 2005

Hi all,
I face a problem as follows: We have an application runnig on SS2K.We log every delete of documents(from Archive table) in another table.
Now it seems some of the rows have deleted strangely without any delete log by our application.We assumed there is somebody who has direct access to database and delete them manually(obviousely our app does not generate any log in this situation)But there is no people.We check that with admins many times.
Does SQL Server itself deletes rows for any reason? How can I know what is happening?Do you think our app flaws somewhere?

Thanks a lot for your attention.

View 2 Replies View Related

Strange Behavior With MSSQL

Oct 5, 2004

Hi,
We are noticing some strange behavior with MSSQL. I was hoping somebody can shed some light.

Since the past few days in our production database we have been getting the following error

Could not allocate space for object 'Person' in database 'PROD' because the 'PRIMARY' filegroup is full...


Some data on your system

The PRIMARY filegroup is 20G in size. And 80% of it is free. Also, the Primary filegroup is setup to auto grow and there is about 20G free space at the OS level. So, I don't think it has anything to do with the filegroup.

I started doing some research on the 'person' object (table), run sp_spaceused etc... to get some data. On a trail and error basics I run DBCC INDEXDEFRAG on the 'person' table and the error went away.

Questions

1. Why is the error misleading? Why does it say, the 'PRIMARY' filegroup is full?
2. Why am I getting this error and why does running DBCC INDEXDEFRAG fix the problem?
3. I can understand the index being fragmented and needing a defrag, but can MSSQL server actually fail with this error if the index is fragmented too much?
4. What data can I look at and prevent this from happening in the future?

Any other data will be much appreciated.

Thanks so much.

View 4 Replies View Related

Strange Tempdb Behavior

Mar 22, 2006

Running SQL 2000 SP4 on Windows 2000 Server.
When a SELECT query is executed in Query Analyzer results are displayed in the results pane, fine...when an "ORDER BY" clause is added to the select stmt the query runs for apprx. 20 seconds then displays "TempDb log is full [Error 9002, Severity 17]". (The tempdb is set to autogrow/10%/unrestricted and plenty of storage space) The next time the query is executed after getting the "tempdb log is full" error, the server reboots upon query execution. As soon as F5 or ctrl-e is pressed to execute the query the server does a hard crash - black screen then reboot...no warnings, no event viewer log, no sql log warnings/errors, no drwtsn log, no hardware log message errors...nothing.

Re-applied Windows 2000 SP3 and SQL SP4 to server, same behavior.

View 4 Replies View Related

Curious DTSWizard Behavior

Apr 12, 2007

Can someone try and explain to me how a sql query can run fine and return data, yet when I try to run it in dtswizard, while the "preview" view shows the data, actually running it returns zero rows?

This is on SQL2005 express and since I can't get dtsrun or dtexec to work, I'm using auto-it to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer...."

View 6 Replies View Related

Strange Behavior In Dynamic Sql

Aug 17, 2007

Hi folks,

I have some code, that just works. But when I put it into a exec() I get a strange error. First the code

exec ('
select
year,quarter, min(price) as minimum
into #temptable from
(
select
ntile(4) over (partition by year,quarter order by price) as rang
,year
,quarter
,price
from
(
select distinct id,year,quarter,price from #tbl1
) as a
) as b
group by rang,year,quarter

Select year ,quarter,
(
SELECT CAST(minimum as varchar(max)) + ","
FROM #temptable t2
where t1.year=t2.year AND t1.quarter=t2.quarter
FOR XML PATH("")
)
from #temptable t1
group by year,quarter

')

SQL Server says, that Insert Into is missing a column name. It points at line with FOR XML PATH("").
Any Idea what's wrong here?

The Output without exec (and correct quotes) looks like:

200430.00,252.90,331.40,470.00,
200440.00,241.00,325.00,450.00,
20051102.00,242.90,326.37,448.00,
200520.00,253.00,340.00,480.00,
200530.00,250.00,325.00,465.00,
2005443.00,260.00,355.00,490.00,

Thank you

View 3 Replies View Related

Strange SSIS Behavior

Oct 10, 2007

I created a very simple SSIS package (it just updates a single row in a table). When I execute the package from the command line (using dtexec), it takes about a second to finish, as expected. But when I execute it using dtexec via xp_cmdshell, it takes about 91 seconds. When I use a SQL job to execute the package as an operating system type, it takes 91 seconds. Using a SQL job to execute it as a SSIS package takes again 91 seconds. It appears that something is causing a delay of about 90 seconds before the package actually gets executed. I tried changing the SSIS service account, but that didn't change anything. Why is executing the package through SS2005 different than executing it directly from the command prompt?

View 4 Replies View Related







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