This Function Won't Work

Jan 11, 2005

Hi! all
Can any body help me straighten this function. Just need to get the diffrence between the two time frame in Minutes

CREATE proc setup_DataReport

as
Declare @rc1 int

Declare @rc2 int
Declare @result int

SELECT @rc1 =count(AL_Coming)
FROM Alarm_Log

where convert(char(5),AL_Coming,14) between '09:00' and '10:00' AND
AL_State = 0

SELECT @rc2 = count(AL_Coming)
FROM Alarm_Log

where convert(char(5),AL_Coming,14) between '09:00' and '10:00' AND
AL_State = 1

select @rc1 as a
select @rc1 as a

set @result = sum(@rc1 - rc2)
print @result

go

View 2 Replies


ADVERTISEMENT

Can't Get CAST Function To Work In ASP.NET

Mar 16, 2006

I'm trying to do something like this SQL statement. I have a table with a field of date/times.
i.e - 2/27/06 9:55:95 PM  Basically there are multiple entries in the table per day.
I want to count the records for particular (hence the CAST) and output the count.
Here it was I had just to see if SQL would work in ASP.NET :
SELECT     COUNT(*) AS Expr1FROM         dbo.tblActiveDrumsWHERE     [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0' OR                      NCTimeStamp - [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0'
It didn't like the AS references?
Any help would be appreciated.
 
 
 
 

View 2 Replies View Related

Can't Get Left() Function To Work With A Variable.

Jan 14, 2008

I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable.
I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable.
Anyone knows why this isn't working?
This is how I have the code:

SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)

the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:

SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.

View 5 Replies View Related

Help: About Charindex Function Doesn't Work With Variable

May 23, 2007

Hello to all,
I hope that somebody can help me.
I have written a sql query to search Partner.  I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table.
Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088. 
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0
and charindex(@IDO,B.RelationshipIDs) > 0
Using this query i get nothing.
I try to use constant in charindex and i get result.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0
and charindex('4088',B.RelationshipIDs) > 0
So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?
Thanks
Best Regards
Pinsha

View 1 Replies View Related

Power Pivot :: How Does EVALUATE Function Work

Oct 24, 2015

playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model.  I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.

View 4 Replies View Related

Does The Function SQLGetDiagRec Work Properly For Unicode

Aug 29, 2007



Hi
I am writing a application using the ODBC Calls to connect to MSSQL Database 2005 SP2.
I am using the Microsoft Visual Studio 6.0 for compilation

I have defined the UNICODE macro in the project settings.
Now when i am trying to connect to MSSQL Database using this call below

retcode = SQLConnect(dbc, (SQLTCHAR*) dsn, SQL_NTS,
(SQLTCHAR*) loginname, SQL_NTS,
(SQLTCHAR*) password, SQL_NTS);

I give a wrong username .
Then i use the function
SQLGetDiagRec to collect the error

void extract_error(char *fn,SQLHANDLE handle, SQLSMALLINT type)
{
SQLINTEGER i = 0;
SQLINTEGER native;
SQLTCHAR state[ 100 ] ;
SQLTCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
memset(state,0,sizeof(state));
memset(text,0,sizeof(text));
fprintf(stderr,
""
"The driver reported the following diagnostics whilst running "
"%s",
fn);
do
{
ret = SQLGetDiagRec(type,
handle,
++i,
state,
&native,
text,
sizeof(text),
&len );



if (SQL_SUCCEEDED(ret))
_stprintf(_T("%s:%ld:%ld:%s"), (const TCHAR *)state, i, native, text);
_stprintf(_T("Error code %s "),(const TCHAR *)state);
}
while( ret == SQL_SUCCESS );
}



Now if i check the values of "state" and "native" i get it as 0.

If i use non unicode (i.e. remove the UNICODE macro) it works fine.

Best Regards
Manoj

View 1 Replies View Related

REPLACE Function Doesn't Work With Null-bytes

Feb 7, 2006

Dear Community,We have a problem with null-Bytes in varchar-Columns, which are nothandled correctly in our application. Therefor we try to filter themout using the Transact-SQL REPLACE function.The Problem was, that the REPLACE-function didn't behave the way weexpected.Following Example demonstrates the behavior:declare @txt varchar(512)declare @i intset @txt = 'hello ' + char(0) + 'world'print @txtset @i = 1while @i <= len(@txt)beginprint str(@i) + substring(@txt, @i, 1)set @i = @i + 1endprint 'Length: ' + str(len(@txt))print 'trying to replace null-byte:'print replace(@txt, char(0), '*')print 'replace Letter h'print replace(@txt, 'h', char(39))-- end exampleOutput:hello1h2e3l4l5o678w9o10r11l12dLength: 12trying to replace null-byte:*replace Letter h'elloThe Null-Byte replace destroys the whole string. This behavior occursonly on some of ourdatabases. The others work correctly.Is it possible that it depends on some server setting?ThanksEnno

View 5 Replies View Related

Function That Works In Sql Server Management Studio Does Not Work In Derived Column Transformation Editor

May 12, 2007

Hi

I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.



I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.



Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?



Thanks for the look see...

******************************

ALTER FUNCTION [dbo].[convdate]

(

@indate nvarchar(10)

)

RETURNS nvarchar(10)

AS

BEGIN

-- Declare the return variable here

DECLARE @outdate nvarchar(10)

set @outdate =

substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+

substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+

substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2)



RETURN @outdate

END

********************************



And the error...



expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.

Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()

View 3 Replies View Related

CASE Function Home Phone Work Phone Cell Phone

May 8, 2008



Hi,

I'm trying to create a case function for home phone ,work phone and cell phone. The thing is some of the home phone numbers either null, zero or less than 10 digits then i'd like to get either cell phone or work phone if they are not null, zero or less than 10 digits.

I'd appreciate it if you could help me on this?

Thanks in advance.

View 19 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View 7 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Calling A Function From A Function?

Apr 11, 2008

Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View 2 Replies View Related

Use Getdate Function In A Own Function

Jul 20, 2005

Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View 5 Replies View Related

Why Does This Not Work

Feb 27, 2007

 My error is that 'Name tr is not declared'  tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'.
What is the correct way?        Try            conn.Open()            Dim tr As SqlTransaction            tr = conn.BeginTransaction()            cmdInsert1.Transaction = tr            cmdInsert1.ExecuteNonQuery()            cmdInsert2.Transaction = tr            cmdInsert2.ExecuteNonQuery()            cmdInsert3.Transaction = tr            cmdInsert3.ExecuteNonQuery()            tr.Commit()        Catch objException As SqlException           tr.Rollback()            Dim objError As SqlError            For Each objError In objException.Errors                Response.Write(objError.Message)            Next        Finally            conn.Close()        End Try 

View 5 Replies View Related

Can't Get LIKE To Work

Apr 19, 2007

I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me.
Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind
stored procedure only returns if input exactly matches L_Name
PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN
I tried: WHERE L_Name LIKE ' % L_Name % '  no luck. What am I missing?
Thank you
 

View 2 Replies View Related

SP Does Not Work.

May 24, 2007

SP below is not work.It gives null for @kaysay 
I used parametric table name. Is this the problem? 
My aim to calculate record count of a table
Thanks. 
 
CREATE PROCEDURE Tablodaki_Kayit_Sayisi(@TABLO varchar(30))AS
Declare @kaysay bigintDeclare @SQLString nvarchar(100)Declare @Param nvarchar(100)
Set @SQLString = N'Select @kaysayOUT = count(BELGE) From ' + @TABLOSet @Param = N'@kaysayOUT bigint'
Execute sp_executesql@SQLString,@Param,@kaysayOUT = @kaysay
Select @kaysay
Return
 
GO

View 3 Replies View Related

Can Anyone Tell Me Why The Following Sql Does Not Work?

Sep 3, 2007

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle
FROM HomePageImage H
JOIN shortlist SL on H.StoryId = SL.id
(INNER JOIN category C on H.CategoryId = C.CategoryId)
order by date DESC

View 4 Replies View Related

Please Help Me To Keep My Work...(:

Apr 12, 2004

Hello,

I am trying to query with my stored procedure. I am getting comma separated list as input parameter (which is VARCHAR) .

The table column, which I have to compare with input parameter value , is in INTEGER datatype.

So when , I compare Like as follows:-

" AND TABLE1.EMPID IN (@INPUTPARAMETERLIST)"

I am getting error. "Syntax error converting the varchar value '34,343' to a column of data type int."

Could anybody help me , to solve this ?

Thanks !

Nicol

View 1 Replies View Related

Why Won't This Work!?

Aug 31, 2004

I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.

Thanks

Form1 is the column in my db.




SqlCommand myCmd = new SqlCommand();

myCmd.Parameters.Add("@ClientCode",SqlDbType.VarChar).Value = TextBox2.Text;

myCmd.CommandText = "UPDATE table SET Form1 = 'Yes' WHERE ClientCode = @ClientCode";


sqlConnection1.Open();
myCmd.Connection = sqlConnection1;

if( CheckBox1.Checked == true)
{
myCmd.ExecuteNonQuery();
}

sqlConnection1.Close();

View 3 Replies View Related

DBA Work

Aug 29, 2001

Hi,

Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??

Thanks in advance.

View 2 Replies View Related

Does This Work?

Mar 15, 2007

Declare @StartDate datetime
Declare @EndDate Datetime
Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0)
Set @EndDate = getdate()

The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?

The Yak Village Idiot

View 1 Replies View Related

Why Did It Work?

Aug 6, 2007

I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records.
It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...

There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.

Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?

View 3 Replies View Related

Why Does One Work, But Not The Other?

Mar 25, 2008



I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?

Thanks




Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
AND b.Finance <> 'Unknown'
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL










Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL
AND b.Finance <> 'Unknown'

View 8 Replies View Related

Coalesce Does Not Seem To Work

Sep 27, 2006

  Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile    Business     PrivateNULL        345           NULL4646        65464        65765NULL                        564654654     564           6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number  from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks

View 1 Replies View Related

Update Does Not Work Well

Sep 29, 2006

Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.

View 1 Replies View Related







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