Re: Problem with ISNULL - Database Discussion Boards

kubben15-Oct-07 3:54

15-Oct-07 3:54

The command object has a default timeout of 30 seconds. YOu will need to set this to some sort of timeout that is long enough for the stored procedure to run. If this is a really long running sp you may need to increase some of the web site timeouts as well.

Hope that helps.
Ben

Re: Timeout problem

Mike Dimmick15-Oct-07 3:56

Mike Dimmick

15-Oct-07 3:56

Query Analyzer/Management Studio does not time out. In contrast, ADO.NET does have a default command timeout of 30 seconds. To change this, change the SqlCommand's CommandTimeout property.

You should consider investigating why this command is taking so long, however. Blocking an ASP.NET worker thread for any significant length of time seriously inhibits the scalability of your website.

DoEvents: Generating unexpected recursion since 1991

Re: Timeout problem

gratisaccount15-Oct-07 18:51

gratisaccount

15-Oct-07 18:51

Thanks for your reply.
Your answers could have solved my problem. If I limit the no. of users.
But that is the problem. No. of users will increase in coming time.
So if there is any other efficient way to solve this problem.
Plz tell me

Re: Timeout problem

kubben16-Oct-07 2:48

kubben

16-Oct-07 2:48

Neither one of us suggested you needed to limit the number of user being returned. Both of us suggested that if you increase the timeout property of your command object then they will all return. If you look at the time in your query tool that it take to execute and it is like 1 minute, then increase your command timeout to like 2 minutes and you should be fine. If the time in it takes to execute is like 5 minutes then fix your query because you are missing indexes or something if it is taking that long to execute.

Ben

Problem with ISNULL

braj yadav15-Oct-07 2:02

braj yadav

15-Oct-07 2:02

Hi all,

I have written a stored proc for searching in DB.For this purpose i have used ISNULL where in check whether the parameter is null or not.But when i use logical statement such as or in between sql statements it fails miserably.
for eg:

@skill1 varchar(50)=null,
@skill2 varchar(50)=null

select skill from JOBS
where
skill like '%'+ISNULL(@skill1,skill)+'%' or
skill like '%'+ISNULL(@skill2,skill)+'%'

here if i don`t enter skill2, @skill2 becomes null and hence the other part returns 1 and thus the query returns all data from table .

Pls help me for going about this

Thankx in Adv

Re: Problem with ISNULL

kubben15-Oct-07 2:45

kubben

15-Oct-07 2:45

I don't think you want to use isnull. I think what you are looking for:
where @skill is null or skill like '%'+@skill

Hope that helps.
Ben

Re: Problem with ISNULL

Frank Kerrigan15-Oct-07 3:07

Frank Kerrigan

15-Oct-07 3:07

Try and use Coalesce

Coalesce(@skill1,skill2,"")

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: Problem with ISNULL

Michael Potter15-Oct-07 5:44

Michael Potter

15-Oct-07 5:44

As written, your query will return every row when either @skill1 or @skill2 is NULL because skill always matches skill throughout all rows.

Try something simpler like this:

((skill LIKE '%' + @skill1 + '%') OR
(skill LIKE '%' + @skill2 + '%'))

Concatination with a NULL value results in a NULL value. LIKE NULL will allways return NULL and therefore not qualify the row for inclusion. The OR will allow the other line to qualify or disqualify the row.

The added parenthesis are there for the OR. I have always had trouble with OR when I let the parser/compiler handle what is being OR'd.

exception

Trustapple15-Oct-07 0:32

Trustapple

15-Oct-07 0:32

hi,
i am getting an exception while inserting data into a ms access database in "da.Fill(ds);"
"No value given for one or more required parameters."

my code is like this

DataSet ds = new DataSet();

try
{
openconnection();
cmd = new OleDbCommand(query, con);
OleDbDataAdapter oda = new OleDbDataAdapter(query, con);

//fill the DataTable
oda.Fill(ds);
closeconnection();
return ds.Tables[0];
}
catch (Exception)
{
return ds.Tables[0];
}
finally
{
closeconnection();
}
thanks in advance

j

Re: exception

Andy_L_J15-Oct-07 2:54

Andy_L_J

15-Oct-07 2:54

Dont you need to specify a table name in the oda.Fill(ds,"SomeTable")?

The Fanatical All Black Supporter - Kea Kaha

Re: exception

Mike Dimmick15-Oct-07 4:02

Mike Dimmick

15-Oct-07 4:02

Firstly, you're attempting to fill a dataset with the results of a query, not insert data into a database with this code. Is that what you intended?

Secondly, the cmd object is not used. I don't know if you intended this. Passing the query string to the data adapter's constructor causes it to construct its own command object for its SelectCommand property. You can pass your own command object by using the version of the constructor that takes an OleDbCommand object.

I think the reason for the error is that the query string has one or more ? characters in it, indicating replaceable parameters, and you've not specified the values of those parameters. You'll need to use the command's Parameters collection for this.

DoEvents: Generating unexpected recursion since 1991

JOINS

karoitay14-Oct-07 23:53

karoitay

14-Oct-07 23:53

Hi,

I Have two tables with same columns (Defferent reccords)
what query do i have to write in order to get the records from both tables?

Thanks.

Re: JOINS

John-ph14-Oct-07 23:56

John-ph

14-Oct-07 23:56

karoitay wrote:
I Have two tables with same columns (Defferent reccords)
what query do i have to write in order to get the records from both tables?

something like this...

Select ColA, colB, colC From Table1
UNION
Select ColA, colB, colC From Table2

Regards
- J O N -

thanks

karoitay15-Oct-07 0:26

karoitay

15-Oct-07 0:26

How do I pass DB name as parameter to stored procedure?

Steven J Jowett14-Oct-07 22:22

Steven J Jowett

14-Oct-07 22:22

I have an MS SQL Server 2005 database that uses data from another MS SQL Server database created by a third-party software company. Both are in the as instance.

I wish to create a stored procedure in my database to retrieve data from the third-party database.

The following code works fine :-

SELECT TOP (100) PERCENT NLNominalAccountID AS NominalAnalysisId, AccountNumber, AccountCostCentre AS CostCentre, AccountDepartment AS Department, AccountName<br />
FROM  MMSv3_DemoData.dbo.NLNominalAccount<br />
ORDER BY AccountNumber, CostCentre, Department

The MMSv3_DemoData is the third-party database.

My problem is that I wish to my the database name an input parameter as have tried the following code :-

set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
go<br />
<br />
ALTER PROCEDURE [dbo].[spGetNominalAccounts]<br />
@Database AS VarChar(50)<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
SELECT     TOP (100) PERCENT NLNominalAccountID AS NominalAnalysisId, AccountNumber, AccountCostCentre AS CostCentre, AccountDepartment AS Department, AccountName<br />
FROM         [@Database].dbo.NLNominalAccount<br />
ORDER BY AccountNumber, CostCentre, Department;<br />
END<br />

What I get when executing the stored procedure is :-

Invalid object name '@Database.dbo.NLNominalAccount'.

Any help apprceiated.

Thanks

-- modified at 4:51 Monday 15th October, 2007

Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

Re: How do I pass DB name as parameter to stored procedure?

Marek Grzenkowicz14-Oct-07 23:43

Marek Grzenkowicz

14-Oct-07 23:43

You have to build a T-SQL statement dynamically inside your stored procedure and then execute it using sp_executesql.

DECLARE @sql nvarchar(100)
SET @sql = 'SELECT * FROM ' + @Database + '.dbo.FOO'
EXEC sp_executesql @sql

Re: How do I pass DB name as parameter to stored procedure?

Steven J Jowett14-Oct-07 23:54

Steven J Jowett

14-Oct-07 23:54

The answer is always simple, when you know how.

Thanks alot, big help

Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

Unable to complete login process due to delay in opening server connection

Anilkumar K V14-Oct-07 20:39

Anilkumar K V

14-Oct-07 20:39

I am getting an error in Replication between SQL Server 2005 and SQL Express when connecting to . "Connecting to Subscriber". The error message is given below. "Unable to complete login process due to delay in opening server connection". Why? Do we need to increase the logintimeout for "Connecting to Subscriber". How can we increase it?

Message
2007-10-15 06:37:58.398 Startup Delay: 8503 (msecs)
2007-10-15 06:38:06.898 Connecting to Distributor 'ACR-MANGO'
2007-10-15 06:38:06.976 Initializing
2007-10-15 06:38:06.976 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2007-10-15 06:38:06.991 Connecting to Subscriber 'ACR-ANJILI\SQLEXPRESS'
2007-10-15 06:38:46.133 Agent message code 20084. The process could not connect to Subscriber 'ACR-ANJILI\SQLEXPRESS'.
2007-10-15 06:38:46.148 Category:NULL
Source: Microsoft SQL Native Client
Number: 08001
Message: Unable to complete login process due to delay in opening server connection
2007-10-15 06:38:46.148 The agent failed with a 'Retry' status. Try to run the agent at a later time.

Database is suspect

Phan Van Thao14-Oct-07 16:19

Phan Van Thao

14-Oct-07 16:19

I use SLQ server 2000.
I have a database but i can not log in to it. It is suspect.
Then I copy .mdf and .ldf files, then i deleted database.
Now i want to repair it. Can repair it? How do repair it?
Thanks

Re: Database is suspect

Pete O'Hanlon15-Oct-07 2:01

Pete O'Hanlon

15-Oct-07 2:01

Can you do an sp_attachdb to attach the database?

Deja View - the feeling that you've seen this post before.

Re: Database is suspect

Phan Van Thao15-Oct-07 15:03

Phan Van Thao

15-Oct-07 15:03

I has ever attach the database but not complete.

Windows authentication - not prompted? [modified]

dandy7214-Oct-07 11:36

dandy72

14-Oct-07 11:36

I have SQL Server Express 2005 running on Windows Server 2003. I installed SQL Express using pretty much all the default options (including Windows Authentication). The machine name is SQLBOX.

I have Visual Studio 2005 on my primary development machine; its name is DEVBOX.

I'm not in a domain environment. The account I use to log onto DEVBOX doesn't exist on SQLBOX, and vice-versa. That is, if I try to access a share, for example, on SQLBOX when I'm on DEVBOX, I'm prompted for a username/password (which I then provide as "SQLBOX/username" and that account's password). That's never been a problem.

I want to access a database on SQLBOX from within VS2005 running on DEVBOX. If I go to Server Explorer, select Data Connection, then Add Connections, I can see that SQLBOX is shown in the Server Name dropdown (meaning, DEVBOX managed to correctly enumerate all instances of SQL Server on my network).

The problem, at this point, is that if I leave the radio button on "Use Windows Authentication", I have no option to enter proper Windows credentials ("SQLBOX/username"). The "Select a database name" dropdown remains blank (since, I'm assuming, VS can't enumerate the databases on SQLBOX if it can't log on), and if I click either the OK or Test Connection buttons, VS simply returns with a timeout. I'm never prompted for a username/password, which fails because, I assume, DEVBOX/username is supplied by the system.

Does a Windows account with a matching name/password have to exist on both machines in such a scenario? Or do I have to change SQL Express so it uses SQL Server Authentication?

I've connected to other remote SQL machines with VS many times in the past, but I've always had matching accounts that existed on all machines. This is not the case here, so I'm assuming that's why this is now failing...

Incidentally, there might be something else at work here, because frankly I would've expected a more appropriate authentication error message instead of a timeout, but I've already gone through all the settings I could think of to allow remote access to the database, including using the Config Manager tool to allow both Named Pipes and TCP/IP...

-- modified at 17:45 Sunday 14th October, 2007

Re: Windows authentication - not prompted?

Mike Dimmick15-Oct-07 3:52

Mike Dimmick

15-Oct-07 3:52

Windows authentication is really only intended for use within a domain. The fact that it also works in a workgroup where the username and password are the same on client and server is a bit of an accident, coming from NTLM's workgroup file sharing support. You cannot specify a password for Windows authentication.

You will have to allow SQL Server authentication on the server. The reason it is generally not recommended is that the passwords are sent in the clear, so can be seen with a network analyzer. It is possible to secure the channel so everything, commands, passwords and retrieved data, are all encrypted - for this you need an SSL certificate on the server. See 'Encrypting Connections to SQL Server' in Books Online (e.g. look up 'SSL' in the Index). You then need to set Encrypt='true' in the connection string.

To enable SQL Server authentication appears to need a registry change, if you don't have Management Studio available. It may be easier to rerun setup, or to download SQL Server Management Studio Express from Microsoft.

Also, you should be aware that remote connections are disabled by default in SQL Server 2005 Express and Developer Editions. To enable them, go to the Configuration Manager tool and enable TCP/IP. You can also use SQL Server Surface Area Configuration but from memory that's not available for Express Edition. This may explain why the timeout occurs rather than getting a 'login failed' error.

DoEvents: Generating unexpected recursion since 1991

Re: Windows authentication - not prompted?

dandy7215-Oct-07 10:15

dandy72

15-Oct-07 10:15

This is exactly the sort of answer I was after. Thank you 10^6 times. Big Grin | :-D

Set Null in Ms Access DB again!

Whoami Whoami14-Oct-07 8:35

Whoami Whoami

14-Oct-07 8:35

Hi all again!
I am designing a web site ready to use both SQL and Ms Access DB.
I have a relation between say Picture and Category. When I delete a category, all the related pictures category will set to NULL. I have no problem with SQL, I set SetToNull in SQL but when I setting up relation in MS Access, I can't find any option, It raise an error when I want to delete category, how I can make MS ACC to set that field to NULL while deleting that record?

Thanks.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.