|
Hi! I have such problem: when my web application make a request to
MS SQL Server 2005, and the last one have to send back a lots of data, it's throwing an error:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
SqlException
"
But on all other cases, it works well. It's very strange I think. Have you any ideas?
|
|
|
|
|
Hi,
The first thing that comes in mind is timeout. What is your operation timeout defined in connection and how long the data retrieval is taking (until you get the error).
Another thing is your connection pool. Sure that the connection isn't aging out?
Few additional questions:
- is the request a select statement or procedure call
- check the state of the connection before executing the command. Is it open?
- does the error occur immediately or when some time has elapsed
- what about database server side? Do you see the open connection
- are you able to trace the statement execution at server side
Mika
|
|
|
|
|
Timeout is set to default, it's not defined in connection string. About connection pool - I don't know, I haven't direct access to this server for now.
Answer your questions:
- Request is a select statement
- I'm using linq database context
- error occurs after 10 -50 seconds.
- no, I don't
- no I'm not
This error occurs with 80% frequency, not always.
Thanks.
|
|
|
|
|
Hmm,
What you could try is (modify only one thing at once and if nothing is changed, return to original configuration):
- add connection timeout-parameter to connection string ("Connection Timeout=60") The default id 15 seconds.
If the server is running low on resources, it may be unable to give you a connection in 15 seconds.
- increase packet size by adding "Packet Size=16384". Default is 8192.
This way the server doesn't need to send so many packets especially when large amount of data is to be sended
- Try disabling connection pooling by adding "Pooling=false" to your connection string. Default is true.
This will eliminate possible aging problems in pool
if possible at database server side you should check:
- start the query and check in database, how your connection is doing.
This can be done by using Management studio or sp_who stored procedure. You should see your connection in "runnable" state
- check the error logs for sql server. When the query is returning you an error, is anything recorded in error log
And of course you could try to run the select statement using 'normal' SqlCommand class along with data reader without LINQ. Just to make sure that LINQ isn't causing any problems.
I'm sorry that I cannot be more specific, but the problem isn't the easiest one to solve.
If you find out anything, please post your findings
Mika
|
|
|
|
|
Thanks,I'll try 
|
|
|
|
|
Situation changed, now it works, but not always. I reproduced the same query several times.
1. I logged in
2. Make this query - everything okay
3. Then I have tried to repeat it - exception occured
4. Then I made browser refresh(F5), and tried again - works well.
I have tried to reproduce the same actions locally - with the same database on my local MS SQL Server 2005 - everything good in 100%! I don't know what to say
|
|
|
|
|
Ok,
- When you say that situation changed, what did you do in order to achieve this change in behaviour?
- Do you close the connection after running the query and before the connection object goes out of scope?
Because what you wrote, it seems that something from the first execution is affecting the second, something that browser refresh resets.
If you could post the key-parts of the code (connection opening, partial connection string without any user information, command execution and connection closing) it would help a lot (I hope  ).
Mika
|
|
|
|
|
My connection string:
"Data Source=dbserver;Initial Catalog=mydb;Persist Security Info=True;User ID=user;Password=password;Connection Timeout=60;Packet Size=16384;Pooling=false"
I could not pass all programming code, it's very huge, I'll describe common steps,so, I click some button then:
1. EntitiesDataContext db = MyProject.Misc.HttpDataContext<entitiesdatacontext>.Current;
2. Client client = Application.GetClient();
3. db.GetPartsByClientType(...)</entitiesdatacontext>
Here is this property for my class httpDataContext<t> for step 1:
public static T Current {
get {
string key = typeof(T).FullName;
T datacontext = (T)System.Web.HttpContext.Current.Items[key];
if (datacontext == null)
System.Web.HttpContext.Current.Items.Add(key, datacontext = new T());
return datacontext;
}
}
For step 2 . In function GetClient I'm using the same
EntitiesDataContext db = MyProject.Misc.HttpDataContext<entitiesdatacontext>.Current;</entitiesdatacontext> to get db context.
And then on step 3 my query executing several time in cycle "for".
I hope this information will help
|
|
|
|
|
Ok,
Didn't yet have any specific idea.
Is the connection open all the time during a single request? I mean that there is no way that the connection could be closed or disposed by your code before the failing statement?
Could you:
- wire StateChange event for the connection
- every time the state changes, write to a log file (or similar) both CurrentState and OriginalState from event arguments
- wire InfoMessage event for the connection
- write every element from Errors collection from message event argument. Also write Message and Source
- wire Disposed event for the connection
- when Disposed occurs, write info about this to the log file
- write to the log file every time you get data context from httpcontext
- write to the log file every time you call open method on the sql connection
- write to the log file every time you execute the statements (especially the failing one)
- write to the log file every time you call close method on the sql connection
This should give a good output which shows the order how things are happening and some extra info what is actually happening.
Another thing that I'm thinking of is that why this doesn't happen on your local SQL Server but happens if you try to use SQL Server on another server. This could be because:
- network problems (packets are occasionally not delivered)
- the server under heavy stress and cannot respond
What do you think, could the reason be in those?
Mika
|
|
|
|
|
Hi frns,
select CONVERT(varchar ,[regdate],103) Dates,[user],[client facility],sum([payroll score])LC from [sheet1$]
group by [user],[client facility], CONVERT(varchar ,[regdate],103) order by [user]
i am programatically retreving data from excel sheet. for retriving data from excel the query i have written is the above but it is giving me error.
the error is :Syntax error (missing operator) in query expression 'CONVERT(varchar ,[regdate],103) Dates'.
How to use the convert function in excel query
regards
sunilwise
modified on Monday, December 1, 2008 4:53 AM
|
|
|
|
|
no probs i got the solution i modified my query to
string sql = "select format([dateregistered],'MM/dd/yyyy') as Dates,[user],[client facility],sum([payroll score]) as LC from [sheet1$] group by [user],[client facility], format([dateregistered],'MM/dd/yyyy') order by [user]";
its working for me.......... 
modified on Monday, December 1, 2008 4:54 AM
|
|
|
|
|
Hi ,
I am working on some report generation project.Any idea about inserting data from MS SQL SERVER in MS EXCELL Sheet.![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
G Singh
|
|
|
|
|
This example is using SQL 2005.
Create a new Excel file: c:\testXL.xls
Open the file and write ID into the cell A1, FirstName into the cell B1.
Sace and close the file.
The code below will add 3 rows to the Excel file.
CREATE TABLE #T (ID INT IDENTITY(1,1), FirstName nvarchar(20))
INSERT INTO #T (FirstName) SELECT 'John'
INSERT INTO #T (FirstName) SELECT 'Kyle'
INSERT INTO #T (FirstName) SELECT 'Stacey'
INSERT INTO
OPENROWSET (
'Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=c:\testXL.xls;HDR=YES;'
, 'SELECT ID, FirstName FROM [Sheet1$]'
)
SELECT ID, FirstName
FROM #T
DROP TABLE #T
Remember, you have to have the Excel file closed while running the query.
In order to have the code above working PROBABLY you'll have to run also this code to enable *Ad Hoc Distributed Queries* usage.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
|
|
|
|
|
Thanks leoinfo for providing this infn.
G Singh
|
|
|
|
|
Don't cross post in the forums.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I am using the SQL Server to send an email by executing the msdb.dbo.sp_send_dbmail stored procedure. The problem is that the command is executed but the mail is queued and never sent. That is the email is not being received by the destination. Can someone help me with it please?
Thanks
Regards
|
|
|
|
|
Check that you have a configured profile.
If you pass the profile name as a parameter to the procedure, make sure that the profile exists and is correctly configured.
If you do not pass the profile name, check that you have a public profile which is default or the user executing the command has a private profile.
Check (with Surface Area Configuration) that database mail is enabled. By default, it's disabled
Check that you are a member of DatabaseMailUserRole in msdb.
You can increase logging on mail using command:
EXECUTE msdb.dbo.sysmail_configure_sp 'LoggingLevel' , '2';
This may help to pinpoint the problem.
Information about the queue is retrieved as follows:
sysmail_help_queue_sp @queue_type = 'Mail';
Hope this helps you,
Mika
|
|
|
|
|
Hi i am getting probs in nested cursor...
Outer cursor i am getting managers...,
Inner cursor based on managers getting employee details...,
In this for all the managers the last employee records i am getting more than one time...,
This is my cursor:
DECLARE curMgr CURSOR FOR
SELECT distinct ManagerID FROM @tbl_TempManagers
OPEN curMgr
FETCH NEXT FROM curMgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @EmpDetail = ''
SELECT @Count=Count(*) FROM @tbl_TempEmployee
--
SET @Counter=1
DECLARE curDetailList CURSOR FOR
SELECT distinct EmployeeName from @tbl_Tempemployee where ManagerId = @mgID order by EmployeeName
OPEN curDetailList
FETCH NEXT FROM curDetailList INTO @EmployeeName1
SELECT dbo.tbl_Login.MID,
convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description],
TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date],
CreationBy as [Name Of Employee] FROM tbl_TSEnterData
INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName
where CreationBy=@employeename1
AND TSDate BETWEEN
CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101)
order by CreationBy asc,TSDate Desc
WHILE (@@FETCH_STATUS = 0 AND @Counter <= @Count)
BEGIN
SET @EmpDetail = @EmpDetail + @EmployeeName1 + ', '
FETCH NEXT FROM curDetailList INTO @EmployeeName1
SELECT dbo.tbl_Login.MID,
convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description],
TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date],
CreationBy as [Name Of Employee] FROM tbl_TSEnterData
INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName
where CreationBy=@employeename1
AND TSDate BETWEEN
CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101)
order by CreationBy asc,TSDate Desc
SET @counter=@counter+1
END
CLOSE curDetailList
DEALLOCATE curDetailList
INSERT INTO @Result VALUES (@mgID, @EmpDetail)
FETCH NEXT FROM curmgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
END
CLOSE curmgr
DEALLOCATE curmgr
Actuall o/p have to be like this:
Managerid Date EID EmplName
22 7-2-2008 100 Name1
22 7-3-2008 100 Name1
Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2
Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3
Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4
But,I am getting o/p of that cursor like this:
Managerid Date EID EmplName
22 7-2-2008 100 Name1
22 7-3-2008 100 Name1
Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2
Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2
Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2
Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3
Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3
Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3
Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4
Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4
Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4
See that all the managers last employee record coming more than one time
Becoz of cursor loop i am getting like this...,
How to solve this.......
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
One possibile problem is that you are calling FETCH NEXT on your inner cursor at the start of the inner loop. Move the call from the start to the end.
Also, why are you inserting records like
INSERT INTO @Result VALUES (@mgID, @EmpDetail)
When @EmpDetail is a comma separated list of values? There really isn't any reason to do this when you are using a relational database. The relationship between Manager and Employee is maintained when you do this:
CREATE TABLE Manager(
ManagerID INT,
ManagerName VARCHAR(50)
)
CREATE TABLE Employee(
EmployeeID INT,
ManagerID INT,
EmployeeName VARCHAR(50)
)
Then when you want to get data from both tables you do this:
DECLARE @EmployeeID INT
SET @EmployeeID = 5
SELECT EmployeeID,
EmployeeName,
ManagerName
FROM Employee E
INNER JOIN Manager M ON M.ManagerID = E.ManagerID
WHERE EmployeeID = @EmployeeID
When you try and maintain a delimited list in a column you're breaking normalization and it will make the data a nightmare to maintain. If you need to enforce the relationships between tables, create foreign key references and they will prevent you from corrupting the relationships you've defined. If you're going to define your data the way it seems you are, you might as well be using Excel or just plain text files.
|
|
|
|
|
Now its creating problem in second manager entry that employee record only binding more than one:
Its modified cursor look at this:
DECLARE curMgr CURSOR FOR
SELECT distinct ManagerID FROM @tbl_TempManagers
OPEN curMgr
FETCH NEXT FROM curMgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @EmpDetail = ''
SELECT @Count=Count(*) FROM @tbl_TempEmployee
--
SET @Counter=1
DECLARE curDetailList CURSOR FOR
SELECT distinct EmployeeName from @tbl_Tempemployee where ManagerId = @mgID order by EmployeeName
OPEN curDetailList
WHILE (@@FETCH_STATUS = 0 AND @Counter <= @Count)
BEGIN
SELECT dbo.tbl_Login.MID,
convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description],
TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date],
CreationBy as [Name Of Employee] FROM tbl_TSEnterData
INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName
where CreationBy=@employeename1
AND TSDate BETWEEN
CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101)
order by CreationBy asc,TSDate Desc
SET @counter=@counter+1
FETCH NEXT FROM curDetailList INTO @EmployeeName1
END
CLOSE curDetailList
DEALLOCATE curDetailList
FETCH NEXT FROM curmgr INTO @mgID
Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid
END
CLOSE curmgr
DEALLOCATE curmgr
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
modified on Friday, July 18, 2008 3:27 AM
|
|
|
|
|
Instead of a cursor, why not try this:
<br />
SELECT <br />
dbo.tbl_Login.MID,<br />
convert(varchar(20),TSDate,101) as [Date],<br />
TSSubject as [Subject],<br />
TSDescription as [Description],<br />
TSHour as [Hour],<br />
TSMin as [Minute],<br />
convert(varchar(20),CreationDate,101) as [Creation Date],<br />
CreationBy as [Name Of Employee] <br />
FROM @tbl_TempManagers M<br />
INNER JOIN tbl_login E ON E.Mid = M.ManagerID<br />
INNER JOIN tbl_TSEnterData D ON D.CreationBy = E.UserName<br />
WHERE D.TSDate BETWEEN DATEADD(wk, -1, @d) <br />
AND DATEADD(dd, -1, @d) <br />
order by D.CreationBy asc,D.TSDate Desc<br />
Temp tables are just like regular tables and can be used in all the same ways. You don't need a foreign key reference to join tables - foreign key references are for data integrity, they aren't required at all in the database, but they are certainly needed to prevent data corruption. So based on what I can tell from your script you can use joins on your temp tables.
Also, as a side note - remove the CONVERT function from your where clause - you're using it to format the date values, but the only reason to format those values is if TSDate is a VARCHAR.
|
|
|
|
|
Hi,
Iam compacting my MSAccess database using the CDaoWorkspace's CompactDatabase function in VC++. Its throwing exception as " Record(s) can't be read; no read permission on DB" . Can anyone let me know the reason for it.
|
|
|
|
|
Sounds like a permission issue. I'd double check that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi i have two tables
I have to compare this 2 tables...,
Now which dates are not in table1 that date and that ename have to come in select statement
Table1:
TSID TSdate EName
100 7-21-2008 Name1
101 7-21-2008 Name1
102 7-22-2008 Name1
103 7-26-2008 Name1
104 7-23-2008 Name2
105 7-25-2008 Name2
Table2:
Nodates
7-21-2008
7-22-2008
7-23-2008
7-24-2008
7-25-2008
7-26-2008
output:
Date Name
7-23-2008 Name1
7-24-2008 Name1
7-25-2008 Name1
7-21-2008 Name2
7-22-2008 Name2
7-24-2008 Name2
7-26-2008 Name2
how to do that
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
Either your output data isn't correct for the example you gave us, or I'm not deciphering your question correctly.
What I think you might want is the Not In syntax:
Select TSDate, EName
From Table1
Where TSDate Not In (Select Nodates From Table2)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
