ODBC Driver Error - Timeout Expired

Very common error but wired scenario. Every client machine get this error in morning. VB application works fine until evening but when everyone goes home after shut down the machine and they come back again in morning and try to run the application they get this error. Application runs fine, It can access data, pull data, view data but It can not write any data. (Other words can not enter any data).

Application again starts working fine after I copy database on different SQL server. For temporary solution I swap database from one SQL Server to other one day and back to original SQL server next day. Every morning it takes about 2 hours to copy database. Im doing this from last few days as working solution. FYI, I have 2 different VB application, each has their own database. One working fine and other started giving me problem, the one I described above.

Few thing I want to let you know:

Recently I changed the SQL server. After I changed I started having this problem. But other application working fine. So I dont think that could be a problem. (Both application basically same in terms of development and tools they use. VB and SQL Server, ODBC connection, Crystal Reports).

In old SQL server both database had daily backup on third party backup built on different server using Client Network Backup. After I changed the SQL server I never modify backup setting. So after I moved SQL server , every night backup was trying to connect to old SQL server and but It couldnt take the backup cause I changed the machine. Again if thats the problem both application should not work but one working fine other is giving me problem.

One more thing I want to mention here is I started having this problem when I left the SQL server copying database overnight. Means, I started copying database and I left the machine ON when I came in the morning copying database was done and I just click on the OK and close the window. Basically It has finished copying database in around 2 hours after I started and I close the window when I came back next morning.

Thats the few things Im thinking about but I dont know what kind of database setting this might have changed and how to reset again. Any help will appreciated.

Dose any one know how to combine .mdf (Primary data file) and .ndf (secondary data file) ?To answer your direct question, use DBCC SHRINKFILE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp) to empty the NDF file, then use ALTER DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp) to deactivate the NDF file. At that point, you can then simply delete the NDF file because it is no longer part of your database.

As a separate issue, I'd suspect that the third party backup is somehow causing your problem with users being unable to write data. I'm not sure how it is interfering. There are many third party backup solutions, with varying degrees of compatibility with SQL Server. Almost all of them work under their ideal conditions, almost all of them break down under other conditions.

Just as a point of curiousity, does the problem persist if you reboot NT on the machine that runs SQL Server? IF there is some problem with file locking, etc. that should clear up the problem for you a lot more quickly and simply than having to copy the database to another server. It would also give you some valuable information for trying to locate the real source of your problem.

-PatP|||Thanks for .ndf tip.

No, It wouldnt clear the problem if restart the NT machine. Just today I tried another solution, I dont know if its going to work but what I did is create the new database and import the data from the original database which already solved my .ndf file problem, now I have only one data file. I dont know if its going to work. I will find out tomorrow morning.

I also noticed one thing today someone created (there is couple of people have access to server) one more filegroup which is Primary_1. There was not any file in that group so I tried to delete that group but It would not delete It just kept freezing.

One question, If I created the new database with same name and import data using SQL server Import function where I choose Import all Objects. Is it going to have any problem in terms of data. I mean tables and views are all imported. It just changed 2 data file to one data file and also got rid of that Primary_1 file group. Just wondering if its going to make any difference in data. I mean I'm going to loose some data or anything like that. Application functions fine.

Thanks.sql

ODBC Destination Issues with SSIS

Hi All,

In Migrating from DTS to SSIS we have scenario where data from OLE DB (SQL Server) is inserted into ODBC source (transoft driver). In case of DTS, ODBC component was provided. For SSIS we used the Destination Script Component and programmatically used the ODBC related calls to design similar functionality. However we are getting the following error –

Error[42000][Transoft] [TSODBC][usqld] Name expected@.

However the same program pointing to Microsoft Access ODBC driver works fine.

Any help in this direction will be greatly helpful.

Thanks,

S Suresh

Well, perhaps Transoft needs more information in their ODBC settings. That is, MS Access isn't really a database engine and hence is more "simple" to use.|||try writting the script code to insert a record into your odbc destination and run this script code from some other context, like a script task or even from VB. the goal is to separate your script/communication with driver away from something that might be SSIS specific like our ado.net connection manager or the script component. in other words, get the script to work outside of SSIS before adding more complexity :)

ODBC connection to remote SQL srver failed

Hi all,
Here is the scenario.
I installed SQL server 2000 on a NT server which already has SQL
server 6.5. This is because I want to upgrade the 6.5 to 2K. The 6.5
is the default instance, and the 2K is in a named instance.
Everything went well up to this point. I can bring up both instances
and transfer database over.
Now, the 6.5 server has been brought down due to not in use. I have a
Access mdb file with linked tables to the SQL 2K server by using a
created file DSN in ODBC manager. Everything works fine. I can open
tables and see data. But if I try to open the same Access mdb file
from remote machines, Win 95s & Win 98s, (using shortcuts points to it
on the NT machine), I get error "ODBC connection to SQL server
<my2Kserver>\<my2Kinstance> failed".
So, opening it from same machine works, opening it from remote does
not. Any ideas? By the way, I tested the network connection and it
works. Becuase I can see the shared drive and files of NT server from
other remote machines.
HansHello Hans,
H> So, opening it from same machine works, opening it from remote does
H> not. Any ideas? By the way, I tested the network connection and it
H> works. Becuase I can see the shared drive and files of NT server
H> from other remote machines.
I think, your 95's or 98's account have not enough access rights for file
operations with MDB files on the server.
Igor Shekalev, http://www.sqledit.com, SQL editor with ODBC support|||As noted above, since you are using an ODBC file DSN, check to make sure you
r client systems have read permissions on the file DSN folder.
Another thing you might try doing is updating to the latest version of MDAC
on all of your client systems that will be using the SQL Server. I know tha
t I've had serious connectivity issues with Win95 and Win98 systems to my SQ
L 2K server before installing the latest updates, particularly after the app
earance of the SQL Slammer virus last year, since MSSQL SP3 significantly ch
anged the connection protocol that SQL Server uses.
You can find the installation information for MDAC 2.7 here:
http://support.microsoft.com/defaul...10&Product=mdac
Note that MDAC 2.7 is the version that is installed with SQL Server 2000 SP3
.
If you want the latest and greatest, MDAC 2.8 is also available.
http://www.microsoft.com/downloads/...&displaylang=en
Hope this helps.
Jimsql