Creating UserInstance DB At Application Startup
Mar 3, 2007
Hi,
I'm developing a WinForms app that will be deployed via ClickOnce deployment and that uses SQLExpress as a local data store. In order to avoid the upgrade DB problem, I'm having my application check for a database and check its version on startup. I am able to successfully create the database if it does not exist, but when I try to connect to the new database, I get a login failed error. However, if I restart the app, it is able to connect to the newly created instance. Here's what I'm doing:
If the db does not exist, I connect using this connect string:
Data Source=.SQLEXPRESS;Integrated Security=True;User Instance=True;Connect Timeout=60
I then issue a CREATE DATABASE command and run my table scripts. I then try to reconnect as
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ImportToolData.mdf;Integrated Security=True;User Instance=True;Connect Timeout=60
When i attempt to connect to the newly created database, I get a login failed error message. However, if I restart the application and try to connect using that same connection string, it succeeds. Any ideas why I would have to restart the app to successfully connect to the new database?Thanks, Scott
View 6 Replies
ADVERTISEMENT
Dec 7, 2000
database : MS SQL server ver 6.50.201
problem: server startup / server time out
details
SQL server shows usage of 100 % CPU utilization, if started automatically / manually. and does not proceed / hangs up(server time out), but win nt operates extremely slow.
The background of the situation in brief is as stated below.
The errorlog was found to have the messages
warning : OPEN OBJECTS parameter may be too low.
Attempt was made to free up descriptors in localdes()
Run sp_configure to increase parameter value.
Error : 644, severity : 21, state; state 1
the non clustered leaf row entry for page 2 row 1 was not found in index page 40 indexid 2 database 'tempdb'
Error : 2620 severity 21 state 3
the offset of the row number at offset 32 does not match the entry in the offset table of the follwoing page : page pointer = 0x1395800, page no= 40, status = 0x2, objectid = 1, indexid = 2
Action taken
I used the sp_option to increase the open objects from default 500 to 1000, and the LE threshold maximum value to 400 from the default 200.
after which I used reconfigure go and restarted the computer to take the effect. The following did work fine and server was working ok. But then from yesterday I am having the problem that the MS SQL sever is utilizing the cpu to 98 - 99 % and I am not able to connect to the server.
I tried to start the server with the minimal configuration by specifying the -f option in the service manager start up options, it showed the following message
00/12/07 10:40:49.73 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
00/12/07 10:40:50.02 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
afterwhich I tried with the option of starting the sql server with the option -c -f
which in the event detail of the win nt shows
"mesg 18109: Recovery dbid 6 ckpt(55813,8) oldest tras= (55813,0)"
The open procedure for service "MSSQLServer" in DLL "SQLCTR60.DLL" has taken longer than the established wait time to complete. The wait time in milliseconds is shown in the data.
DB-LIBRARY error - SQL Server connection timed out.
and in the errorlog present in MSSQLLOG directory
00/12/07 15:06:43.24 spid1 Recovering Database 'master'
00/12/07 15:06:43.31 spid1 Recovery dbid 1 ckpt (7944,28) oldest tran=(7944,0)
00/12/07 15:06:43.41 spid1 1 transactions rolled forward
00/12/07 15:06:43.49 spid1 Activating disk 'AM'
00/12/07 15:06:43.49 kernel initializing virtual device 1, D:MSSQLDATAAM.DAT
00/12/07 15:06:43.50 spid1 Activating disk 'AMLOG'
00/12/07 15:06:43.50 kernel initializing virtual device 2, D:MSSQLDATAAMLOG.DAT
The dbid is / was 6 for both the instances, the database which is having this dbid is database name "AM" and is having above 1300 tables (approx)
what could be the problem and solution for it?
kindly help me out of this situation.
If any more information is needed please contact me on devendrakulkarni@yahoo.com / devendra@me.iitb.ernet.in
Regards
Devendra
View 1 Replies
View Related
Jun 19, 2006
I've enabled the Fulltextsearch in some Tables of my DB.
In SQL Server Management Studio the querys are all have results and running free of errors.
If i will use the same querys in VWD, they're running in a SqlException. Translated from german:
The Fulltextsearch cant be used in the Userinstance.
But a SelectCommand from the WebSite to the IndexState:
SELECT crawl_end_date FROM sys.fulltext_indexes WHERE fulltext_catalog_id = 10
works fine.
What i'm wrong?
The ConnectionString i'm using:
="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Support.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"
The FulltextService (msftesql.exe) is running under a UserAccount.
Thanx
Klaus
View 3 Replies
View Related
Mar 16, 2007
I've been struggling with this for a few days now, so I guess it's time to ask for help. I'm working on a VB Express 2005 Single form windows application that uses a SQL Express 2005 two table data base. I can read and write to the database with no problems. I just can't back it up. I haven't tried restores yet. I created the database in SQL Server Management Studio Express and then connected to it from the VB Express application.
My.Settings.UsersConnectionString is
Data Source=.sqlexpress;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataUsers.mdf";Integrated Security=True;User Instance=True
I used the following code to establish a connection to the server that is the userinstance
Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UsersConnectionString)
Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)
Dim srv As Server = New Server(ServerConnection)
I am able to confirm the connection to the UserInstance via a message box that displays the instance name
MsgBox(srv.InstanceName.ToString)
Then this code follows:
Dim db As Database
db = srv.Databases("USERS")
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.BackupSetDescription = "Full backup of Users"
bk.BackupSetName = "Users Backup"
bk.Database = "USERS"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(AppDir & "Test_Full_Backup1.bak", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
bk.Initialize = True
'Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = False
'Set the expiration date.
Dim backupdate As New Date
backupdate = New Date(2008, 10, 5)
bk.ExpirationDate = backupdate
'Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)
'Inform the user that the backup has been completed.
MsgBox("Full Backup complete.")
Unfortunately, the line bk.SLQBackup(srv) errors off advising that - Microsoft.SqlServer.Management.Common.ExecutionFailureException {"An exception occurred while executing a Transact-SQL statement or batch."} Microsoft.SqlServer.Management.Common.ExecutionFailureException
and that
+ InnerException {"Could not locate entry in sysdatabases for database 'USERS'. No entry found with that name. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally."} System.Exception
So I went to SQL Server Management Studio Express and ran the query
use master
select * from sysdatabases
Among other lines the query returned
USERS 5 0x010500000000000515000000358A021AFA4F0C2F828BA628AD8A0200 0 1073807369 1627389952 2007-03-16 10:04:42.320 1900-01-01 00:00:00.000 0 90 C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataUsers.mdf NULL
so it appears to me that the entry for database USERS is really there.
Any ideas as to what I need to do so that I can add a program feature to backup the data are greatly appreciated.
Thank you,
MF
View 25 Replies
View Related
Feb 16, 2004
I'm trying to create a user from a powerbuilder application through powerscript.
I'm trying to create first the login using sp_addlogin
but I'm getting the following error
[microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.
Plz does someone know what should I do?
View 1 Replies
View Related
Jul 20, 2005
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
View 1 Replies
View Related
Mar 9, 2007
Hello,
I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.
John
View 3 Replies
View Related
Jun 28, 2007
Hello!!
I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.
Can anyone give me a hand?
thanks
View 3 Replies
View Related
Aug 14, 2007
hi all,
i want to create a service broker application between two different instances.
can i achieve the dialog security and transport security with out using certificate?
like we can achieve the transport security using windows authentication (both the instances are in same domain).
so how to achieve the dialog security with out using certificates.
if any body has any link plz provide me.
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!
View 3 Replies
View Related
Jun 11, 2008
Geekz,
My problem is that , I had setup blog on server say, blog.xyz.com [I am using blogengine.net, which is in ASP.Net]. It is using sql server2005 as a backend for storing its blog's posts. After settingup blog, i tried to access its database through my windows application. But while development I was getting error that
"An error has occured while establishing a onnection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the defaukt settings SQL server doesnot allow remote connections.(Provider Named Pipes, error :40 - Could not open a connection to SQL Server)"
I am using [String ConnectionString = "Data Source=sql-v123.mesa11.1Server.net; Initial Catalog=abc; User ID=xyz; Password=xyz123";]
Then I decided to created a webservice that will access the data from databse blog and store it somewhere in datatable and using windows application i will retrieve those data for end user. So I want to to know that whether i can access the datatable of webservice holding the blog data from windows application or not. if answer is Yes, then please provide me with source or some great links. Or if There is any other option so that I can access my database directly from windows application, then it would be better. Looking for your answers.
Thanks and regards
View 3 Replies
View Related
Jan 9, 2008
Hi guys, need help again.
I'm running packages from C# code and thus I need to create an instance of SqlServer.DTS.Runtime.Application.
In my maching, creating the application instance doesn't even take 1 sec but when the application was deployed to our QA server creating the instance took 30 secs (always ... every single time with no exception).
this single code is taking the 30 secs:
Application integrationService = New Application();
Does anyone have any idea what I need to check or configure to make this faster?
View 5 Replies
View Related
Sep 23, 2007
I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.
1) How do I create a new schema?
2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?
3) Are scripts that work for Express going to have any problem executing on SQL Server?
View 1 Replies
View Related
Jul 21, 2006
This is a great tutorial and it's a shame one of the more important steps was missed.
In the €œCreate the snapshot user€? section you you find the steps to create the snapshot_agent account. Then in the €œCreate the snapshot folder€? section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section €œCreate a new subscription".
To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.
Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.
View 4 Replies
View Related
Nov 3, 2007
I am studying the tutorial in SQL Server 2005 Mobile Edition Books Online, and the topic is Creating a Mobile Application with SQL Server Mobile. I have got a problem when creating a new subscription after created a new SQL Server Mobile database. And the problem is shown below:
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
* Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.
HRESULT 0x80004005 (29061)
* 無法完æˆ?作æ¥ã€‚
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Before I have met this problem, I have finished all the task. And I can browse the localhost web site by using anonymous account even I use internet explorer or browse the directly in IIS.
Does anyone can solve it?? Thank you very much~~~
View 3 Replies
View Related
Sep 3, 2003
We have a STD SQL2000 with 2GB memory, which has around 800 databases and is planed to have 1500 databases.
From Microsoft Knowledge Base Q316749, <<By using the -g startup parameter, you can instruct SQL Server to leave additional virtual memory available so that the combination of these log-related allocations and other normal allocations does not run out of virtual address space.>>
but we are not quite sure the correct sytax, so we added -g392 and -g 392 as two new startup parameters. After reboot, in the error log, "initdata: Warning: could not set working set size to 765696 KB.
1. Is this a good way to increase the virtural memory?
2. which system table contains the -g value? and how to fix this error?
thanks
-D
View 1 Replies
View Related
Sep 25, 2003
Please Advice
I started my SQL SERVER 2000 (SP 3)in single user mode using EM with the purpose of doing a test restore of the Master database. Can i remove the startup parameters as I cannot login using sa or similar login using ISQL/W utility.
Thanks for your help. Urgent.
Regards
View 5 Replies
View Related
Jan 13, 2004
Does anyone know how to tell which procedures are called when SQL Server 2000 starts up if scan for startup procs is turned on?
View 11 Replies
View Related
Mar 28, 2007
in sql server (re)start where does it find the location of Master.mdf file .
In Oracle, there is control file where the location of *.dbf file is stored and Instance find the location of dbf file from that location
In sql server where it finds the location of Databases as master, msdb etc..
View 1 Replies
View Related
Oct 4, 2006
I have two pages on a particular website that are data driven by SqlExpress. The site is in the testing stages, so there is very traffic on the site at this point. The very first time that a data driven page is accessed, the response time is approximately 30 seconds. After that, the response on the data driven pages seems normal. The data queried is no more that 30 or 40 records.
I did see the "slow startup from idle" thread on this forum - that thread suggested changing from user instances, but that's not an option here.
My hosting service requires that I specify user instances in my connection string. Is this just something that I'm going to have to live with, or is there any way to speed up this SqlExpress "start up"?
View 6 Replies
View Related
Oct 1, 2006
I have two pages on a particular website that are data driven by SqlExpress. The site is in the testing stages, so there is very traffic on the site at this point. The very first time that a data driven page is accessed, the response time is approximately 30 seconds. After that, the response on the data driven pages seems normal. The data queried is no more that 30 or 40 records. My hosting service requires that I specify user instances in my connection string. Is this just something that I'm going to have to live with, or is there any way to speed up this SqlExpress "start up"?TIA
View 1 Replies
View Related
Apr 4, 2002
Hello,
I've 10 Production Servers, which is rebooted every month by NT guys, The MS SQL Server Services & MS SQL Agent starts automatically when the Windows 2000 Advanced Server starts, I would like to create that when the MS SQL Server starts it pages me that Services started successfully, or the other way is to see the Windows Event viewer error logs to see if there is any SQL Server errors at NT server startup so that it would page me Problem starting MS SQL Server.
Would anyone please help me with this
Is there any startup procedure I could generate every time MS SQL Server is started.
How could I scan NT error logs to see the error logs for MS SQL Server and send the page.
Thanks
Roma
View 4 Replies
View Related
Dec 17, 2002
Running SQL2000/SP2. DB has approx. 750,000 rows in main table - 500 MB content. Db is set To Truncate on Checkpoint, so no Log file to speak of - ever. System is 667 Mhz PIII with 512 Mb Ram. Disk are ATA133.OS is W2k Server-SP3. Anonaly happens at System startup. Db is coupled with automatic data collection software for manufacturing. It is taking DB longer and longer to "catchup" after each boot. Pref Counters show main problem is Disk Read Queue Length. (CPU % doesn't exceed 40%, normally 7% - SQL was given 1/2 RAM - 256 MB). If Data Collection is not started, DB comes up rapidly (it seems), but manual test inserts show high - protracted disk activity, but tests don't fail, just run slow. After a number of test inserts (7 - 12+), log jam breaks and the processing time drops from 1.5 Minutes to the normal 15-20 Milliseconds!. Similar action is seen with Data collection software running at boot. By design, the Data collection software buffers all inputs to the DB, so no loss, but the disk thrashing is disconcerting. This type of problem is not seen during normal operation (system is closely watched by automated scripts). Main table is routinely reindexed and fragmentation is tracked (never gets above 5% fragmentation). No table is 'pinned' in RAM.
Hints, allegations, and out-right lies glady accepted!
View 8 Replies
View Related
Dec 25, 2001
Hi All,
Running Sql Server 2000 for Windows 98. How do I start the SQL Server Agent Automatically (that is at OS startup). Is there a way to do it at all ?
TIA.
Regards,
Kaushik.
View 1 Replies
View Related
Apr 4, 2002
Hello,
I've 10 Production Servers, which is rebooted every month by NT guys, The MS SQL Server Services & MS SQL Agent starts automatically when the Windows 2000 Advanced Server starts, I would like to create that when the MS SQL Server starts it pages me that Services started successfully, or the other way is to see the Windows Event viewer error logs to see if there is any SQL Server errors at NT server startup so that it would page me Problem starting MS SQL Server.
Would anyone please help me with this
Is there any startup procedure I could generate every time MS SQL Server is started.
How could I scan NT error logs to see the error logs for MS SQL Server and send the page.
Thanks
Roma
View 1 Replies
View Related
May 2, 2007
Hi, I'd like to create a trigger to fire it when database starts up. Is there any way to do it? If yes, would you be able to provide me with an example?
Thanks in advance.
-Alla
View 3 Replies
View Related
Oct 18, 2007
Is there a way I can set up a SQL script to run when the instancestarts up?SQL Server 2005 SP2thanks!
View 3 Replies
View Related
Jan 21, 2008
Hi,
I am getting issue while Starting SQL Service.
Error started since I rebooted the server without stopping SQL Services.
Below Errors Observed.
1. Server named pipe provider failed to listen on [ sqlquery ]. Error: 0x57
2. TDSSNIClient initialization failed with error 0x57, status code 0x50.
3. TDSSNIClient initialization failed with error 0x57, status code 0x1.
4. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
5. SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Kindly Suggest.
View 3 Replies
View Related
Nov 29, 2007
SSIS packages running slow on startup in 64 bit prod env. looks like it is taking lot of time (around 2mins) to validate the package. Delayvalidation is false (default); changing it to true didn't help. Any fix for it?
Executing packages using dtexec commad.
We have job packages which loads 10s of child packages in sequence using package execution task... taking hours to complete. Same are working fine 64 bit dev env.
Prod env:
Windows 2003 SP1
SQL Server 2005 SP2
Thanks
View 11 Replies
View Related
Mar 21, 2006
Is it possible to stop the server from going to sleep when it is idle to improve responsiveness to new connections?
View 21 Replies
View Related
Apr 26, 2006
I'm not familiar with SQL Server and am just starting things up. I have VB Express and SQL Server Express (I think - at this point, I wouldn't be surprised if I didn't), and am trying to create a database.
So how do I do that?
All I see that relates to the SQL Server 2005 is under All Programs -> Microsoft SQL Server 2005 -> Configuration Tools; I have a configuration manager, Error and Usage Reporting, and Surface Area Configuration. I assumed that in there would be a database engine in here somewhere, but I have rumaged through every command in these and found nothing on creating a new database. Nada.
Am I looking in the wrong places, did something go wrong with my install, or am I just a complete leptic?
Please Help!
View 1 Replies
View Related
Apr 13, 2008
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep
View 1 Replies
View Related
Sep 11, 2000
I would like to enter info into the startup service account so that the MS
SQL Server Agent will run under this account. Then I can run jobs.
However, I don't know why the "startup service account" info is
greyed out, which prevents me from entering this information.
Any help would be appreciated!!
View 2 Replies
View Related
Mar 31, 2003
Hi,
Is there any way to use different email accounts for sql mail dynamically on sql server 2000?
Here is the problem:
My sql server is configured to start mail session under account 'xxnotifyxx'.
There are many jobs scheduled to run queries and send out emails to different user groups within the company as well as outside the company from 'xxnotifyxx' as a sender.
However, one of the usergroup within the company is asking for sending out their emails ( from scheduled tasks that uses xp_sendmail ) using 'publishergroup' as a sender.
As far as I know, this is not possible (sql server 2000, exchange 2000 client win2000 ) until I change the mail session configuration to start under 'publishergroup' in stead of 'xxnotifyxx'. If I do this, 9 other groups have problems with this.
Is there a work around for this kind of situation?
Thanks,
Shaili
:rolleyes:
View 1 Replies
View Related
