Using MS SQL 2000I have a stored procedure that processes an XML file generated from anAudit program. The XML looks somewhat like this:<ComputerScan><scanheader><ScanDate>somedate&time</ScanDate><UniqueID>MAC address</UniqueID></scanheader><computer><ComputerName>RyanPC</ComputerName></computer><scans><scan ID = "1.0" Section= "Basic Overview"><scanattributes><scanattribute ID="1.0.0.0" ParentID=""Name="NetworkDomian">MSHOMe</scanattribute>scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....This is the Update portion of the sproc....CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)ASDECLARE @iTree intDECLARE @assetid intDECLARE @scanid intDECLARE @MAC nvarchar(50)CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Namenvarchar(50), scanattribute nvarchar(50))/* SET NOCOUNT ON */EXEC sp_xml_preparedocument @iTree OUTPUT, @docINSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))IF EXISTS(select MAC from tblAsset where MAC = @MAC)BEGINUPDATE tblAsset set DatelastScanned = (select ScanDate fromopenxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScan set ScanDate = (select ScanDate fromopenxml(@iTree,'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =getdate()FROM tblScanDetail INNER JOIN #tempON (tblScanDetail.GUIID = #temp.ID ANDtblScanDetail.GUIParentID =#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)WHERE MAC = @MAC!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERTSTATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUSSCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXISTYET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, b.ID, b.ParentID,tblScanAttribute.ScanAttributeID,@scanid, b.scanattribute, DateCreated = getdate(), LastModified =getdate()FROM tblScanDetail LEFT OUTER JOIN #temp a ON(tblScanDetail.GUIID =a.ID AND tblScanDetail.GUIParentID = a.ParentID ANDtblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN#temp b ON tblScanAttribute.Name = b.NameWHERE (tblScanDetail.GUIID IS NULL ANDtblScanDetail.GUIParentID ISNULL AND tblScanDetail.AttributeValue IS NULL)ENDELSEBEGINHere are a few table defintions to maybe help out a little too...if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAssetGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblAsset]GOCREATE TABLE [dbo].[tblAsset] ([AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[AssetTypeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DatelastScanned] [smalldatetime] NULL ,[NextScanDate] [smalldatetime] NULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NULL) ON [PRIMARY]GO-----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblScan]GOCREATE TABLE [dbo].[tblScan] ([ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetID] [int] NULL ,[ScanDate] [smalldatetime] NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblScanDetail]GOCREATE TABLE [dbo].[tblScanDetail] ([ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOTNULL ,[ScanID] [int] NULL ,[ScanAttributeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[GUIParentID] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[AttributeValue] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO------------------------------------------------------------My problem is that Insert statement that follows the update intotblScanDetail, for some reason it just seems to insert everything twiceif the update is performed. Not sure what I did wrong but any helpwould be appreciated. Thanks in advance.
My problem is, I am writing a code with classic asp, to connect to sqlexpres database and somehow something blocking me. My first try was unsuccessfull , it was keep getting timeout. then I did something and now I am getting this error message shown below;
Error Type: Microsoft OLE DB Service Components (0x80040E21) Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. /myfolder/opendb.asp, line 5
I am using SA user to connecto the server. When I checked the server with managment tool I found out sa doesn't have permission to use this new database of mine. So I tried to add that by going into
Security>logins>SA >properties > usermapping. Then I found and checked the new database . I get an error message shown below;
Create failed for user 'sa' (microsoft.sqlserver.express.smo)
additional information:
an exception occured while executing a transact-sql statement or batch.
(microsoft.sqlserver.express.connectioninfo)
Cannot use the special principal 'sa' (microsoft sql error, error 15405)
Hello. I see a lot of threads re this but I havent seem an answer. Please help. I have Win xp pro SP2 with msde, IIS 5.1 and Web Data Administrator on two separate machines.
With one machine everything works great. On the other, When I hit the Web Data Administrator Login button (windows integrated or SQL) it just shows the hour glass and doesnt do anything.
On the one that works, I can change the server to one on our lan with sqlserver and it will link in fine. However with the other, the same hourglass shows.
IIS and msde are both running and seem to work fine. I can browse to localhost virtual directories (I have dotnetnuke running using msde on localhost) and access the database from dotnetnuke and visual studios.
I have made sure my localhost is a "Trusted site" und ie browser and I have enable windows integrated authentication for the default web site and the webadmin virtual directory.
So i get this version of sqlexpress up and running and i copy all my databases over on to it. Redo my Logins for te server and then add the the DSN to ODBC admin console. And still no good >:_( this is the error i get:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "publicSite" requested by the login. The login failed.
I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.
Is there any way to detach/unlock .mdf file connecting by SQLExpress?
I see this question a lot, so I thought I would go ahead and post the answer here.
Q: How many connections does SQL Server Express support?
A: Unlike MSDE, SQL Server Express does not limit connections. SQL Server Express supports:
1 gb RAM (note the machine can have more memory, but 1gb will be used by SQL Express) 1 processor (again, the machine can have more, but just 1 proc will be used by SQL Express) 4 gb database size (note, there can be multiple databases, but none of them individually can be more than 4 gb)
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)
I am having difficulty converting my CGI(perl module) connection from mySQL to SQLexpress. Does anyone know what the connection string should look like? Listed here is my current connection string. I'm trying to get this done this week. Please email me if you have an answer in addition to posting.
I followed an excellent tutorial (from Microsoft) about creating and using a SQLExpress db table internal to your website and the site I created works just fine - locally. When I publish to my web host provider I get this error:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
My web host provider supports .NET 2.0 but doesn't support MS SQL Server. But that should matter - right? Because I'm putting the db instance right in the website?
I did a search and followed some of the advice:
I went into the SQL Server Surface Area Config Mgr and set SQLExpress for local and remote connections and rebooted/republished. I also have SQL Server 2005 installed as a separate entity (if that makes a difference).
I still get the above error. This is the connection string from the web.config:
I am running SqlExpress on a Vista machine as a server and accessing it from machines running XP. I could not connect with the Vista server until I turned off the firewall, despite creating exceptions for both the sqlexpress and sqlbrowser services in the firewall setup. Obviously, I don't want to run on a wireless network without a firewall for any length of time. What else to I need to enable in order to run with my firewall up? In SMSS, I could 'see' the Vista server on the network with the firewall up, but I could not 'see' the SERVERSQLEXPRESS service or connect to it .
I am using Visual Studio 2005. I am creating an SQL database with a web enabled front end. I am using Microsoft SQL Server Management Studio to create the database objects and an ASP.NET web page created in Visual Studio for the front end. My problem is centered around the two server instances which come with Visual Studio (MSSQLSERVER and SQLEXPRESS). The database objects are being created on MSSQLSERVER by Microsoft SQL Server Management Studio. However, in a Visual Studio ASP.NET web page, when I create a new conection string using the menu functionality, in the SQLDATASOURCE object or in the server explorer, only (My Computer Name)SQLEXPRESS shows up as an option. I have tried typing (My Computer Name)MSSQLSERVER in but the database list just comes up blank. I know that MSSQLSERVER exists because I have one active connection string to it created by a friend. They had to juggle a lot of things to make MSSQLSERVER appear and since I was just getting started then it went over my head. Whatever they did was temporary though and I havn't been able to repeat it to add new connection strings. To eliminate some of the obvious things in other posts:
*MSSQLSERVER is Started in the Services Screen *In the configuration manager MSSQLSERVER has shared memory enabled *On the advice of one post or proceedure I enabled TCP/IP and restarted the service (I think this is actually for remote connections everything I am doing is on one PC)
What I really want is to have things permanently set up so MSSQLSERVER always shows up as an option when creating a connection string in Visual Studio. If this means getting rid of SQLEXPRESS I won't shed too many tears.
As you have noticed everything in this is centered around Visual Studio's menu functionality. I am currently make thee code paradigm shifts.
Form -> ASP.NET VB6 -> VB .method ADO -> SQL
I have created some pages in ASP.NET and have started creating objects using source instead of designer. I have automated some of the functionality on those pages using VB .method programing While I have created some simple SQL select and bulk insert statements my SQL is still pretty weak and I haven't learned connection strings or driving changes back to the database using Transact-SQL
I am using a virtual dedicated server to host sqlexpress at a remote site. I can't get the connection from MS SQL Server Management Studio Express to connect over the Internet. I only have the box IP address (xxx.xxx.xxx.xxx) and have tried various forms of xxx.xxx.xxx.xxxsqlexpress as the server name.
Can someone point me in the right direction please?
When I use asp.net 2.1 to connect sql 2005 express locally I got error message to say
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: SQL Network Interfaces, error: 25 - Connection string is not valid)
Hello, I had started my project with the express edition, then I switched to the pro one. The problem is that in my webconfig I still have .SQLEXPRESS and I don't know how to modify that to use the full version of Sql server. <add name="MyConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDataBase.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" /> When I look at the surface area manager I can see that I have 2 instances running : MSSQLSERVER and SQLEXPRESS.When starting Sql Server Management Studio I can connect to 2 servers: NEWDELL and NEWDELLSQLEXPRESS.And when I try to replace SQLEXPRESS by MSSQLSERVER in the connectionstring, then it doesn't connect.I remember having lost connection already when playing with all these tools, so I pay attention now ! That's why any help would be really appreciated ! Thank you.
The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".
Any thoughts why this might be and how I can fix this?
I have created an installation application which will install the application with SQL Express and .NET Framework 2.0.
If i install this application in a Fresh system(i.e which is not having SQL Express), it is installing the application with new database instance successfully.
But if i try to install the same in a system which is already having SQL Express, throwing "Object reference exception" because it is not able to create the new database instance.
I Have a correct connectionstring and it connected the database without any problems but once I close the program, the problems start. When I reopen the solution, I recceive error messages as
The databse already exists, choose a different database name.
Because of numerous problems trying to get sqlexpress working, I uninstalled it with the intention of reinstalling (via Add or Remove Programs). However, now when I try to reinstall it, I get a message that the I am not making a changes so it won't let me install it.
I do have sql server 2005 developer's edition installed; is that the reason? and does that mean I cannot have both installed on the same machine?
I have the Visual Studio 2005 Team Suite trial version and the SQL Server 2005 trial (which came with team suite) installed in my machine. When I installed the SQL Server 2005 I installed it in the Windows Authentication mode. So every time I open SQL Server 2005 it doesnt ask for Username/Password (Its greyed out). The only thing available were: Server Type: Database Engine ServerName: ServerSue Authentication: Windows Authentication. Here is my Problem: I created a small application in C#. In the web.config file I created the following: <appSettings> <add key="resumecon" value="SERVER=ServerSue;database=Resume;"/> </appSettings> Then in the Code behind I created the following string: string strResCon = ConfigurationManager.AppSettings["resumecon"].ToString(); Then in method (for a Login page) I created the connection string as follows and wrote code to insert some values into the Resume database. I called this method on a button click event. SqlConnection conLogin = new SqlConnection(strResCon); When I run the page and when I click the button I get the following error: Login failed for user ''. The user is not associated with a trusted SQL Server connection. I think some thing is wrong in the addkey of webconfig? How do I change this?
Hi all, I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it. Any idea
My total test page is shown below. I monitor the connections by SP_WHO2. Without the second call, connection pooling seems to be working, ie I refresh my browser repeately but the number of connections as seen from SP_WHO2 does not increase. However, if I have the second call, every time I refresh the page at the browser, the number of connections increases by one. This is obviously not acceptable in a real world application. I tried both Integrated Authentication (with no impersonation) and using a hardcoded service account. Both have the exact same results. In fact this test is not about multi-user yet, it is the same single user just refreshing the same page. May I know what have I done wrong? All the documentation from Microsoft says close the connection after using it. In the case of SqlDataSource how do I close the connection? Thanks <%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> protected void Page_Load(object sender, EventArgs e) { SqlDataSource1.SelectCommand = "Select ID from Master"; System.Data.SqlClient.SqlDataReader reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); if (reader.HasRows && reader.Read()) Label1.Text = reader["ID"].ToString(); SqlDataSource1.Dispose();
//second call: read from another table SqlDataSource1.SelectCommand = "Select Name from Students"; reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); if (reader.HasRows && reader.Read()) Label1.Text += reader["Name"].ToString();
Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.
If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!
I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.
Im running it on Windows Server 2003 Standard.
Any help would be really apriciated as i have tried everything!!!
Hi, I'm fairly new to the asp.net enviroment.. I have a project in asp.net and I need to upgraded to asp.net 2.0... I just did that, and the few errors that I got are resolved already, the only error that I have not been able to figure out is when I tried to retrieve data from a database I get the following error: An error has ocurred while establishsing 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) and it points to the following line:
Dim ds As DataSet = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings(GlobalD.CfgKeyConnString), CommandType.StoredProcedure, "PG_LST_MENU_BY_SUBJECT") As I said before my project with the previous version works fine, and this is the only part that is giving me trouble... thank you
Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway): When I'm at the office, this is my connection string, pretty normal: connectionString="Data Source=ServerNameInstanceName;Initial Catalog=blah..." But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd. First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using "ServerNameInstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now "ServerNameInstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier. I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!
Here is the connect tring from table properties:ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidatesHere is the connect string from the ADO .Open connect string:"ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"Here is the ADO .Open code. Set CNN = New ADODB.ConnectionDim strDEFConn As StringstrDEFConn = FixConnStr(DEFCONN)CNN.Open strDEFConn The last line fails with the CNN.Open strDEFConn with this message:Run-time error '-2147467259 (80004005)';[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified This code works when the SQL Server is on it's own server, but for testing at my own office, I have SQL Server on the same machine as the Access application. I'm getting the above error where SQL Server and the Access app are on the same machine. I can open a linked SQL table from the user interface, and VBA code that deals with the tables as Access tables works. It is the ADO .Open statement where the error happens. Thanks for any help you can give me on getting this to work.
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
As I trun Work offline - true, my connection manager for log file says-it cant acquire connection while work offline is true.Where as other oledb connections work fine.
Even it tried to get around by putting DelayValidation as true, but didnt work.
I am pretty new to reporting software and I was just wondering in reporting services 2005 when working with multiple datasets and data connection string in order to populate text boxes if the data is a number it is prefixed with SUM at the start of the expression and if it is text it is prefixed with FIRST even though it is just for one value.
I was wondering if this is normal or have I messed up somewhere?
