SQL Server Data Access Forum

Creating offline OLAP cube with C#.

Wed, 25 Nov 2009 19:50:50 Z

Hello everybody,

I have a problem with creating an offline OLAP cube from C# using following code:

using (var connection = new OleDbConnection()) { connection.ConnectionString = "Provider=MSOLAP; Initial Catalog=[OCWCube]; Data Source=C:\temp\test.cub; CreateCube=CREATE CUBE [OCWCube] ( DIMENSION [NAME], LEVEL [Wszystkie] TYPE ALL, LEVEL [NAME], MEASURE [Liczba DESCRIPTIO] FUNCTION COUNT ); InsertInto=INSERT INTO OCWCube([Liczba DESCRIPTIO], [NAME].[NAME]) OPTIONS ATTEMPT_ANALYSIS SELECT Planners.DESCRIPTIO, Planners.NAME FROM Planners Planners; Source_DSN=\"CollatingSequence=ASCII;DefaultDir=c:\temp;Deleted=1;Driver={Microsoft dBase Driver (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UserCommitSync=Yes;\";Mode=Write;UseExistingFile=True"; try { connection.Open(); } catch (OleDbException e) { Console.WriteLine(e); } }

I keep on getting the following exception:

"Multiple-step operation generated errors. Check each OLE database status value. No action was taken."

I took the connection string literally from OQY file generated by Excel. I had to add "Mode=Write" section, otherwise I was getting another exception ("file may be in use").

What is wrong with the connection string? How to diagnose the error? Somebody please guide me...

Jimmy Joe

I get 'access denied' when trying to read/write filestream on w2k8 r2.

Wed, 25 Nov 2009 19:35:33 Z

Hi!

I noticed in our developer enviroment that all of our sql servers installed on windows 2008 r2 reject all read or write filestream operation through ado.net.

We get a simple 'Access denied' security exception from remote machines.

Locally (directly on the server) our test works fine.

We are using windows authentication, permissions and filestream 'things' are configured properly.

All of our developer sql sever patched to sp1 and cu5.

This problem reproducable with sql 2k8 r2 too.

Only one thing what i can see that the share in the server manager console signed with red x.

With same SQL configuration everything working fine on w2k8 or any other windows os.

Any suggestion?

connection pooling

Wed, 25 Nov 2009 18:52:11 Z

I'm trying to configure connection pooling for SQL Server 2005. The client program is written in C++ and using ADO 2.81 (not ADO.NET) and SQL Native Client 9.0.
My connection string looks something like this:
"Provider=SQLNCLI;Data Source=xxx;Failover Partner=xxx;Initial Catalog=xxx;User Id=xxx;Password=xxx;Pooling=yes;Max Pool Size=2;Min Pool Size=2;"

When my client program tries to create the third connection, it creates the connection without an exception. I was expecting an exception because I set the max pool size to 2.

When look at MASTER.DBO.SYSPROCESSES table, I see 3 connection open.

When the client program closes all 3 connections, MASTER.DBO.SYSPROCESSES table shows no open connection. Again, I was expecting 2 connections to stay connected even after the connection is closed from the client side.

Could someone help me understand why the connection pooling isn't working as I expected?
thanks.

SQL 2K Simple Stored Procedure Times Out when Called by .Net Web Site

Mon, 23 Nov 2009 06:00:01 Z

Our web server makes a call to a stored procedure and that .NET code says that the proc timed out after 30 seconds.

Here is what the procedure does:

SELECT NEWID() AS 'SessionID'

Seriously - that's all it does.

Am I blind or is this a hardware issue? If so how can I investigate to show the culprit?

No data is written or read from the database.

TIA

SQL Server DIFF Backup

Wed, 25 Nov 2009 12:23:53 Z

Hi all,

Could any one please help me on below error - Differntial Backup is getting failed.
OS- Windows 2003 Enterprise Edition - 32 bit
Physical server

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date:  24/11/2009
Time:  12:17:50 AM
User:  N/A
Computer: KNPC
Description:
SQL Server Scheduled Job 'Databases - DIFF Backup Plan' (0x6A705D374EBF7142AC99D60D89ED15DF) - Status: Failed - Invoked on: 2009-11-24 00:05:02 - Message: The job failed. The Job was invoked by Schedule 221 (Daily, Except Sunday). The last step to run was step 2 (DIFF Backup of ALL Databases).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date:  24/11/2009
Time:  12:17:46 AM
User:  KNPC\KNWWsql
Computer: KNPC
Description:
18210 :
BackupMedium::ReportIoError: read failure on backup device 'VDI_F5047433-F004-4C1D-A6C4-F5427DC53656_1'. Operating system error 995(error not found).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 22 47 00 00 10 00 00 00   "G......
0008: 06 00 00 00 4a 00 55 00   ....K.N.
0010: 4d 00 42 00 4f 00 00 00   P..C....
0018: 00 00 00 00               ....

Thanks,
Bala.

Cannot create new user using createuserwizard using IE

Wed, 25 Nov 2009 15:34:11 Z

I created a website using the latest version of visual web developer and SQL Express. Everthing works fine when I run the website from the developer, but when I run it from the explorer and try to create a new user account using the createuserwizard control, it tells me that the SQL database is in read-only mode.

I'm running Windows XP Pro, Service Pack 2 and IIS.

Let me know if you need more info.

Connecting and send data to SQL server in 'C'

Sun, 22 Nov 2009 06:22:30 Z

Hello,

I'm looking for any type of info that can be given on this subject.

Thanks,
-A2H

Database connection, c# application.

Mon, 23 Nov 2009 06:55:15 Z

Hi All,

I want to build an application that connects a client c# application to a database that are allocated in a server. I don't know how can I connect my application to the database, furthermore in the deployment process Do I need any configuration for the DB server?

Regards

Connect SQL Server from WinForms - Windows Authentication Mode

Fri, 20 Nov 2009 12:39:23 Z

H i,

is there a way how to connect to SQL Server from Windows Forms in C#, when Windows Authentication Mode is set in SQL Server and client (application) runs on a computer not in a domain ?

Can I use some impersonation? I tried to use LogonUser() from advapi32.dll, but it doesn't work, when computer is not in a domain :(

Thanks

SQL STATE 22003: Numeric value out of range" with TIME datatype of SQL server 2008

Fri, 20 Nov 2009 01:58:23 Z

Someone has asked a similar question (same topic) but the answers don't seem to help me.

The C++ application I'm working has an MS Access database (accessed via ODBC) that has been converted (using Full Convert) to SQL Server 2008. Note this is the free SQL "Express" version of SQL Server 2008. So I have this one table that can be updated just fine in MS Access but in SQL Server it cannot.

I get this error in the topic heading (22003). It's definitely happening on one of the data column. I can *read* the dates in the table into the C structures just fine -- they end up nicely in the TIMESTAMP_STRUCT. Everything looks fine in the the debugger -- I do not see anything out of whack when I go to update -- like negative dates or anything. I assume using ODBC is still the right approach, but perhaps I'm wrong.

I'm stuck! Help!

MFC ADO application slow when connected via OLE DB Provider for SQL Server, fast when connected via SQL Server Native Client 10.0

Thu, 19 Nov 2009 11:07:14 Z

Hi,

I have an evaluation version of SQL Server 2008 installed on Windows Server 2008 (32 bit) and have created a brand new database (which is very small) for my application to use. The app is MFC using ADO Active X controls.

I noticed that it appeared to run very slowly, typically taking several seconds to populate grids.

After a bit of poking around, I changed the OLE DB Provider it connects with from "Microsoft OLE DB Provider for SQL Server" to "SQL Server Native Client 10.0" and the performance was back to normal, populating grids instantaneously.

Here are the connection strings for each case - slow:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ControllerServerB;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=CS2008;Use Encryption for Data=False;Tag with column collation when possible=False

Fast:

Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=ControllerServerB;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=CS2008;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False

Can anyone explain this difference in speed?

I have another non-MFC application that accesses the database using ADO that also runs slowly (connects with Driver={SQL Server}), but I can't change the connection string for that without changing the source code, which I am loath to do until I understand the problem.

Thanks,

Jim.

MS Access ADP projects need to be able to use SQL Native Client

Wed, 25 Nov 2009 00:38:33 Z

Microsoft, PLEASE update the Access ADP platform to be able to connect to SQL Server 2005 using the SQL Native Client. ADP is a great in-house development platform, but MS seems to have decided to stop supporting it.

The problem is, the sql ole db driver that the ADP projects use, cause problems when querying complicated views or stored procedures on SQL Server 2005. Queries that worked great under SQL Server 2000, time out under 2005. When run directly on the server, there is no problem. When they are run from the ADP file, the execution plan of the query is totally messed up. I watched what SQL Server 2005 does with tracing, it's running certain segments of the query over and over.

If I run that same query in Access, through an ADO connection, where I specify the Native Client as the provider, then I don't have that problem, which proves that the problem is the old SQL OLE DB driver. So we need the project's default connection needs to be work properly with SQL Server 2005.

Data ransfer from different filegroups..

Tue, 24 Nov 2009 06:14:46 Z

Hi,

"I need to migrate individual tables from different filegroups into a single file group in a database".

Thanks in Advance,

Regards,
Prvn

Using collation for international languag other than English

Sat, 07 Nov 2009 22:16:34 Z

The SQL 2005 server has "SQL_Latin1_General_CP1_CI_AS" as default collation to have english characters.
I am trying to input "Korean" language into the database, and then now I see funny "???" character after I insert a data.

What is my option to use Korean character on my database?
Should I create whole new database?

Thanks.

Desktops are working great using OLE DB, but how do you connect SQL Server 2005 to MS Access on Tablet PCs used in the field?

Tue, 24 Nov 2009 18:19:53 Z

Hi,

I haven't been able to find any direct advice on this so far.

I am converting an Access 2003 database that was divided into a front end and back end. The back end has replicated tables that were used by the Access front end to synchronize to a similar Access application used by field persons on tablet PCs.

I un-replicated the backend and moved it to SQL Server 2005, so the Access front end on the desktop works smoothly with the SQL Server backend. The desktop pcs using Access are connecting to the SQL Server using OLE DB linking that is checked and maintained by ADOX code.

The next step as I see it is to find out how best to replicate from the SQL Server 2005 backend to the tablet pcs using Access. These tablet pcs aren't connected the whole time so they need to use replication and can't use linked tables.

Does anyone have any suggestions?

Thanks for your help

Alex

Damn this (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Mon, 27 Oct 2008 19:44:37 Z

Well my problem is this error message Message="A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

but :

-can't connect to SqlServer Express 2005 on my network server from my application while ITS RUNNING. What I mean is that when I use Server Explorer Window on VS2008 every thing is fine (I can even insert and modify data from server explorer), in fact, when I use Datatables and I choose to preview data through TableAdapters (Design Time) I get data, and also can run the queries from there directly to the data base, but when I'm debbugin the application I get this error.

-when I run Sql Server Management Studio, its also fine, even though I can't see the Sql instance on the server (or any of my partners machine) when I try to browse them. But if I type the server path (servername\instancename) everything is OK.

-I know the problem is not the connection string 'cause mya partners nd I use the same one to connect to the server, and no one has this problem.

-also tried to shut down my firewall and the server's also, and nothing.

-ok here are the specs for both, server and client pc:

SERVER:

*Running Windows Server 2008 64 bits

*Sql Server Express 2005 SP2 x86 (WOW)

Client:

*Running Windows Vista Ultimate SP1 64 bits

*Sql Server Express 2005 SP2 x86 (WOW)

*Sql server 2008 Enterprise 64 bits

*VS 2008 Team Suite.

Everyone Else:

*Windows Vista ultimate 32 bits

*Sql Server Express 2005 SP2

So can you help me?? please, I'm working localy only, so is a pain doing this

Can't run SSIS package from a batch file

Tue, 03 Nov 2009 22:05:30 Z

I am trying to run an SSIS package from a batch file with this code:

echo
d:
cd \DTSPacks
dtsrun /MyServer /E /N Eval_Emp_Info.dtsx
pause
cd\
echo off

I get this error:

Error: -2147467259 (80004005); Provider Error: 17 (11)
   Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exi
st or access denied.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context: 0

Any help is appreciated. I'm a rookie at this.

Thanks,

Steve

Steve

How to optimize mysql subquery performance?

Mon, 23 Nov 2009 06:52:59 Z

Dear All,

I have subquery like this

SELECT col1,col2,col3,col4,col5 FROM tablename as m WHERE col3 =(SELECT MAX(col3)FROM tablename WHERE col1 = m.col1)ORDER BY col1

here col3 as date column

Here my table contains around 7000000 records

But it was taking too much time to execute this subquery. And also we have created index for col1 and col3. And we have used dataset and datareader for load the data. But the query was taking more time to execute. And we have tried with limit command also but there is no use.

How to increase the execution performance of the subquery?. I need to fill all the results into dataset or datareader with in seconds (with less time).

We are using C# with MYSQL database.

Any help must be appreciated.

Thanks & Regords
Jayaram

TransactionScope Object Calling a Stored Proc that has its own transaction , Will this work ?

Thu, 12 Nov 2009 18:23:19 Z

I have a fairly simple question on using System.Transactions namespace :

   using(TransactionScope tx= new TransactionScope())
{
     //Some Logic here
    ...Calls a Stored Procedure called InsertNewCustomer()
   //If success
tx.Commit();
}

and inside InsertNewCustomer :

Create Procedure InsertNewCustomer
as
Begin

  BEGIN TRAN
   /* Some Logic here
*/
COMMIT TRAN

End

Will this work from the Dataaccess layer code if my .net application I start a TransactionScope within which if i call a stored proc that implements its own transaction . Will this work ???

If yes or no pls give some reasons around your posts .. I'm trying to understand the underlying mechanics ....

Thanks for your help/time ....

Anand

problem with foxpro table data in sql linked server

Mon, 23 Nov 2009 03:05:02 Z

i created a linked server to a foxpro free table folder. so far i can run select queries any of the tables just fine .
the first problem i've noticed is after the foxpro application makes changes to the data .... i cant see the changes to the table data on my linked server .... all the data i see is like a snapshot of the data at the time the linked server was created.

i need to have complete access to the tables from foxpro and ms sql.

Ms-sql2008 on server2k3 dc
i dont know the exact foxpro version as it is part of an application we use here.

i created the linked server with this sql statement.

EXEC sp_addlinkedserver

@srvproduct='',

@server='Reservations',

@provider='VFPOLEDB',

@datasrc='D:\Web_data\Disp\'

can someone please help me out here

10/09 Security Update Hinders OLE DB connectivity ?

Fri, 20 Nov 2009 16:05:32 Z

HI. I'm not sure if this is the right spot for this question, but couldn't find any place that appeared "closer"....I have had a stored procedure that has run for the last 3-4 years without fail. It creates a linked server to an XLS spreadsheet (97-2000) and imports the data and does other "stuff" to create a phone list table that is then served up on our portals.
There has been no code change, no XLS location change -- no user who runs it "change". However, after our netadmin put on all of the security patches for october, the process will not make the connection to the XLS and create the table any longer.
It is possible, of course, that this is a coincidence, but it's making me crazy, as I can't find anything that indicates "something" changed w/ the permissions -- which I'm guessing it must be....

The code which has been working w/o fail (until now) :
EXEC sp_addlinkedserver N'PhoneExcelLink',
@srvproduct = N'Jet 4.0',
@provider =N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'\\UNC Path To the File\Phone.xls',
@provstr = N'Excel 8.0;'
GO

EXEC sp_addlinkedsrvlogin 'PhoneExcelLink', 'false','domain\username', NULL, NULL
GO

EXEC sp_addremotelogin 'PhoneExcelLink', '--username--', '--password--'
GO

DECLARE MenuTC Cursor Static For
select * FROM OPENQUERY(PhoneExcelLink, 'select * from [Sheet1$]')
Open MenuTC

I've tried the stored procedure, as well as running it in SQL itself, under my own login, which has admin rights both for SQL, as well as the network (NTFS). It fails regardless.
Obviously something has changed since the process was last run on 10/1 ......and I'm theorizing it is permissions -- but I can't see what....
and I can't seem to find anything in the description of the last security update that indicates anything might have changed for SQL server, its connectivity or general "permissions".....

if anyone has any ideas, I'd be grateful !
Thanks !

Kristi

Why, after importing a database into SQL 2008 can I not delete new records from a table in an Access interface over obdc? I get a 3197 error.

Thu, 19 Nov 2009 21:09:01 Z

In a new SBS 2008 premium installation I imported a database onto the addictional SQL server. After a few problems were resolved with Null fields and primary keys everything seems to be working fine except when we go to delete records from a table that were added since the migration I get a 3197 error. Deleting records that were in the original db prior to the migration works fine. I tried an new Access interface with newly linked tables with the same results. The new records cannot be deleted using a form nor from the linked table. However, the records can be deleted using a delete query from a client as well as at the server.

Problem with Linked Server and Microsoft.ACE.OLEDB.12.0

Mon, 23 Nov 2009 15:59:28 Z

Hello,

I've seen variants of this question posted, but no answer that works for me. I need to read entries in a Sharepoint List with SQL. I know how to access the UserData table directly, but I also know this is not supported. So, trying to use Microsoft.ACE.OLEDB.12.0 for this and using the following to set up a linked server:

sp_addlinkedserver @server ='RAIDLISTS', @srvproduct= 'OLE DB Provider for ACE', @provider='Microsoft.ACE.OLEDB.12.0',
@provstr='FMT=Delimited;WSS;HDR=NO;IMEX=2;DATABASE=http://tulam03;LIST={00860C9D-8F42-49C2-A6B4-84EBFC84EDC9};VIEW=;RetrieveIds=Yes','SELECT * FROM list')

I've also tried this:

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'WSS;HDR=NO;IMEX=2;DATABASE=http://tulam03;LIST={00860C9D-8F42-49C2-A6B4-84EBFC84EDC9};VIEW=;RetrieveIds=Yes',
'SELECT * FROM list')

In the first case, the linked server is established, but when I try to access the list, I get the following message:

Cannot initialize the datasource object of OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for linked server "RAIDLISTS". (Microsoft SQL Server, Error: 7303).

In the second case, I get the similar message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Strangely, the following set of PowerShell commands work just fine:

$connstring = 'Provider=Microsoft.ACE.OLEDB.12.0;WSS;HDR=NO;IMEX=2;DATABASE=http://tulam03;LIST={00860C9D-8F42-49C2-A6B4-84EBFC84EDC9};VIEW=;RetrieveIds=Yes'
$spConn = new-object System.Data.Oledb.OleDbConnection($connString)
$spConn.open()
$qry='Select * from list'
$cmd=new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$da=new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt=new-object System.Data.dataTable
$da.fill($dt) > $null
$dt.select()

What am I doing wrong?

Cache Stored Procecdure call in DTS

Mon, 23 Nov 2009 09:30:03 Z

Howdy All

I'm having problems calling a Cache stored procedure (Intersystems) in a DTS package. Dont know if anyone has experience of cache, but i can connect to a cache database no problem, i can execute cache sql no problem, but the minute i call a cache stored procedure, I get this error

http://img163.imageshack.us/img163/7940/errmsg.jpg

The same cache stored procedure can be called from winsql no problem and runs fine. When it attempts to run the stored procedure, it actually creates the table at the other end and names the columns correctly but refuses to move data.

Any Cache/DTS experts out there?

Thanks in advance
Adam

Problem with SQL string

Sat, 21 Nov 2009 19:12:41 Z

It took a bit of troubleshooting to figure out what is going on with some of my code, but I think I almost got a handle on it.

When I run this code:

cmd.CommandText = String.Format("Select CrawlDate, Count(*) From Events Where CrawlDate >= #{0}# And CrawlDate <= #{1}# Group By CralwDate", startDate, endDate)

I get this error:

da.Fill(ds)

Message: Incorrect syntax near ‘12’

For a long time that made absolutely no sense to me as there is not a single ‘12’ in my table (which is now quite small after chopping out almost all records) and there is no December...I just couldn’t figure it out...

So I changed that cmd line to this:

cmd.CommandText = String.Format("Select * from Events")

Then, at least, I could move past the error.

When I try to run this code:

cmd.CommandText = String.Format("Select * from Events Where CrawlDate >= #{0}# And CrawlDate < #{1}#", _

selectedDay.ToString("yyyy/mm/dd"), selectedDay.AddDays(1).ToString("yyyy/mm/dd"))

I get this error:

da.Fill(ds)

Message: Incorrect syntax near ‘#’

I think SQL Server just can’t handle the syntax. Originally this code was written for showing dates in a calendar which received data from an Access DB. Instead of Access, I am using SQL Server, and as a result, I had to change some of the code in my project. I think now the code is fine, but I am not building the SQL string properly. Can someone please tell me the proper syntax for doing what I’m trying to do in a SQL DB?

I can provide all code if it helps, but I really think I have isolated the problem to the SQL string. The rest of the code will probably work fine once I get this string issue resolved.

Thanks a ton!

Ryan---

"A transport-level error has occurred "- while connecting to SQL Server 2005

Sun, 27 Jul 2008 13:36:55 Z

Hi,

I am using .net 2005 and sql server 2005 in my web application. After login to my web page, i m constatnly getting this message. so, i must have to signout and signin again. but after signin same erorr is coming again.

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Please guide me regarding this.

ODBC connection failing on 64 version of Windows 2003 server

Wed, 03 May 2006 16:59:36 Z

I have an application that talks to a SQL backend using an ODBC DSN connection. It works fine when running on a 32bit machine but fails in the 64bit environment. The DSN is setup properly on the 64bit box and the Test Connection returns sucess. However when my application tries to use the DSN to connect to the database I recieve the following error.

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

How to resolve this? A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Fri, 20 Nov 2009 10:20:11 Z

Hi, When the application reads data from database server, it throws out these kinds of exceptions and then the connection is closed, the network teams already checked everything and could not find any log related, and it happens randomly, one a day or once a week, it is hard to reproduce it, if anybody experienced or know how to fix this please share with me.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I searched in the google, and found a lot of people had the same issue and I can not find a right anser to this.
I did try this on my server,
"excute type regedit then add the following registry key and reboot the server.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\SycAttackProtect{DWORD} = 0
then reboot the server", but this does not fix it.
also I got some good comment from my colleage,
"
It could be many reasons.
But it’s usually always drilled down to a bug in the Connection Pooling. (But Microsoft doesn’t seem to be that keen on fixing it).
Sometimes it happens that you get a connection from the pool that is already broken, and in my opinion this is the bug, the Connection Pool shouldn’t give you a dead connection.
Then there is the reason why the connection is broken in the first place, and there are numerous. Using a WAN is one reason.
The “An existing connection was forcibly closed by the remote host” is quite often due to heavy load on the SQL server, but it is also common that the SQL server is fine, but the “DOS Attack protection” in windows is on. I think this setting came in W2K3, it’s a setting in the registry that need to be changed. Because the windows machine think there are to many connections coming in a short time from the same IP, then it will drop some of the connections, and in this case the connection to the SQL Server, hence the “forcibly closed”. This setting is annoying especially if the server is behind firewalls and is protected in many other ways, and that you don’t know about it in the first place. It could affect other services as well.
I’ve experienced myself issues with TCP/IP Chimney enabled in a SQL Cluster, together with Broadcom network cards, when TCP/IP Chimney was disabled these “forcibly closed by remote host” disappeared completely, and there was 10-20 per day.
But if you start googling about the connection pooling issue, together with forcibly closed etc. Then you will find there are numerous other reasons one more wild than another.
The safe bet to get it fixed is to disable connection pooling, but that on the other hand will kill performance significantly and is really nothing you should do.
Since it isn’t that common for you I guess taking care of the error in a nice way is probably the easiest until a solution is found. On the other hand, since it’s uncommon a lot of people live with it then, just nice to know it’s nothing wrong with their programming."
it seems there is not instant way to fix it, I know there are a lot of experts here, could anybody help me out please? thanks a lot!

Network-related or instance-specific error - Connection Medium Problem

Thu, 19 Nov 2009 13:36:33 Z

Hi guys,

I know there's hundreds of topic about this error out there, but not a single one seems to apply to my specific situation.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

We have three branches, 1 head office and two satellite branches. Our in-house developed application connects to a SQL Server 2005 instance running on a server with a live IP which the branches connect through and a local IP that the head office connects through i.e. app at branches connect through <live IP>\<instance name> and the head office connects through <local IP>\<instance name>. The application is exactly the same across all three branches except for the connection string used...it's exactly the same for both satellite branches though.

Head office has no problems connecting with the application. Branch A has no problems either. Branch B however does. Branch B's network is setup as follows...ADSL connection into a wireless/wired router that each user then gets internet access through. If anyone in Branch B connects to the internet through 3G or their cellphones on their PCs, then the application works. But not through the ADSL setup. However, to make it more interesting...one of the guys made an initial connection to the internet using his cellphone as the modem and successfully logged into the application...he then disconnected the internet from his cellphone and connected through a cable connection to the ADSL router and successfully managed to carry on utilising the application, performing actions that would have performed SQL queries to the server...and all ran successfully. But then he closed the app and opened it again (cable to router connection) and again login failed.

Please any ideas.

Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.).

Tue, 23 Jan 2007 06:01:44 Z

I am facing a issue with bulk upload on Test Server.

Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error.

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\\ServerName\input\FileName.csv" could not be opened. Operating system error code 5(Access is denied.).

For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine.

If the command is run from Test Server connecting to any SQL Server instance including Test Server it is running fine.

Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server

2) Given the read/write rights on shared folder to everyone.

Query:

SELECT DISTINCT * FROM OPENROWSET

(

BULK '\\ServerName\input\FileName.csv',

FORMATFILE='\\ServerName\Format.xml'

)

AS FileList

Please provide me with some solution. What can be the reason for such behaviour?

ODBC Error: SQLState 28000 & Server Error 18452

Wed, 18 Nov 2009 13:42:47 Z

Hello,

I have created two new tables in a SQL database instance on a Deticated SQL server. The database in question has been in use for years with no problems. I created these two tables and linked them like the others to an Access interfaces as linked tables using a new System DSN.
Most of the time but not all of the time I get the dialog listed in the title here : ODBC Error: SQLState 28000, Server Error 18452 Login failed for ...
This only happens when I use the objects on the form that are bound to the new tables. the other objects touching other tables work fine.

Thanks.

Restore a SQL Database under SQL Server 2005

Fri, 20 Nov 2009 09:01:37 Z

Hi,
what is the best way to restore a sql 2005 Database?
I become a .bak File from a customer and now I will restore the database on my system.
I use the SQL 2005 Enterprise Edition for the restore.

The next question is:
Have anyone restore a sql 2005 .bak file in a sql 2008 Server?
If yes, how?
And are there some trouble?

Best Regards

Bernd

SQL Server to support Vietnamese

Tue, 17 Nov 2009 03:08:26 Z

Has Microsoft SQL officially support Vietnamese language? Currently we have prospect in Vietnam to use our solution but when we run a test internally we realize that there are quite a number of things that cannot work well eventough the collation of the database is in Vietnamese.

Certain Vietnamese character that we input to SQL turn out to be special question mark(?). Anyone experience the same?

Link server error

Fri, 13 Nov 2009 05:08:59 Z

Hi,

We have two SQL server 2005 (64 bit) servers, which pull the data from AS400 servers through link sever .This link server has been created using system DSN and Microsoft OLEDB provider for ODBC driver.

But since yesterday this link severs of one of the server went on toss, When ever we do the restart the SQL sever , for next two, three attempts the test connection process succeed but after than the it goes in hug up state .Pleas let me know if any one can put some light on this.

Many Thanks,

Rushikesh

SQL delete excess duplicates

Wed, 18 Nov 2009 23:11:01 Z

Hi, I have never studied SQL so what I know is what I have self taught (aka i'm a beginner)

I have a query I am running and I have it look at records. It searches for duplicates and returns to me the duplicate records. The only unique field that separates the duplicates is and ID which is just a 5-6 digit number.

   I have the duplicates all together, but wish to get rid of each duplicate except for one "keeper"

EXAMPLE:

ID firstname lastname
1    Jane        Doe
2    Jane        Doe

3 John        Doe
4    John        Doe
5 John        Doe

My query returns results like the one above with some additional information.... I want to be left with the following...

ID firstname lastname
1    Jane        Doe
3 John        Doe

One good record.......

I figure I can manual delete the records, but was hoping there is something in SQL where I can delete all but 1?

I am open to all sorts of ideas, thanks!

Time out connection when access database from VS.net

Mon, 16 Nov 2009 04:54:55 Z

Dear All,

I have the big problem when accessing database from web base application..
when I tried to login to MS sql server 2008 no error rised.. but when we do some transaction three or four time the error rised..
After error rised my MS sql server 2008 can't be accessed by any other client...event by ODBC.. anyone can share how to solve this problem???
Please help

sql server performance

Fri, 13 Nov 2009 06:32:50 Z

how to improve sql server performance?

BCP Connection Error: SQLState = 28000, NativeError = 18456

Wed, 18 Nov 2009 20:54:17 Z

The following bcp command using xp_cmdshell doesn't work returning SQL Connection error. Looks like bcp command doesn't take Windows-Authenticated ID.
exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -U"Domain1\test" -P"Test$12" -c -t","'

SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Domain1\test'.

However, the following works, which uses SQL-Authentication Login ID.
exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -U"test" -P"test" -c -t","'

Is there anyway to use Windows-Authenticated ID for bcp command? Please advise.

32 bit DSN in folder sysWOW64 not accessible in code

Wed, 18 Nov 2009 14:02:11 Z

Hi,

I have a web application on Windows 2007 Server 64 bit. I have also created DSN for 32 Bit using odbcad32.exe in *%SystemRoot%\sysWOW64 folder. I can see DSN and Drivers there. If I test the connection from there ,I get successfull connection. If I try to connect to DB using this DSN from C# code ,I get the error Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. I guess its trying to find out DSN in System32 folder (which does not have this dsn/drivers).
How can I access DSN defined in syswow64 folder in my code.

Thanks,
Gaurav Dixit

Need help to convert oracle function in the query below to Sql linkserver fuction in the query

Wed, 18 Nov 2009 16:57:56 Z

The query below works fine in Oracle database, but I need help in converting the query to work in Sql linkserver(the date function). I need someone to help with an equivalent query in SQL server. Thanks

SELECT hk_rg_ord.rec_re_ts,
  to_date(to_char(hk_rg_ord.ord_re_ts,'DD-Mon-YYYY')) AS OrdDate,
  hk_rg_ord.ord_br AS Ordbr,
  to_date(to_char(hk_rg_ord.cre_de_date,'DD-Mon-YYYY')) AS CreDate,
  to_date(to_char(hk_rg_ord.pro_comp_date,'DD-Mon-YYYY')) AS newDate,
  hk_rg_ord.st,
  hk_rg_osis.cu_ads as Ads,
  hk_rg_osis.cu_nme As Cname,
  hk_rg_ord.sl_stn As slPnber,
  hk_rg_ord.isp_id As ISP,
  CASE WHEN ((hk_rg_ord.dry_ind = 'Y')) then 1 else 0 end as IndLoop,
  CASE WHEN ((hk_rg_ord.UNI_IND = 'Y')) then 1 else 0 end as Uni,
  hk_rg_osis.sl_spd as Spd,
  CASE WHEN ((hk_rg_osis.by_ind = 'Y')) then 1 else 0 end as BY,
  hk_rg_osis.por_id As ByProv,
  CASE WHEN ((hk_rg_osis.lob_cd = 'B')) then 1 else 0 end as CuTyId,
  hk_rg_osis.hm_ct_tns as tns,
  CASE WHEN ((hk_rg_osis.lng_ind = 'S')) then 2 else 1 end as LngId,
  CASE WHEN ((hk_rg_osis.sl_prd_tye = 'STA SL ACCESS')) then 1 else 0 end as Sta
FROM hk_rg_osis, hk_rg_ord
WHERE (
  (hk_rg_ord.ord_br = hk_rg_osis.ord_br(+))
     and (hk_rg_ord.pro_comp_date between sysdate-2 and sysdate+1)
  and (hk_rg_ord.act_typ = 'ADD')
  and (hk_rg_osis.lob_cd ='B)
  and not (hk_rg_ord.ins_opt_ind = 'TEH')
  and ((hk_rg_ord.Ord_br like 'C%') or (hk_rg_ord.Ord_br like 'I%'))
  and (hk_rg_ord.eh_id = '121')
  AND not((hk_rg_osis.AEG_CD)='FRDT')