Search Results

Search found 27339 results on 1094 pages for 'sql dmv'.

Page 236/1094 | < Previous Page | 232 233 234 235 236 237 238 239 240 241 242 243  | Next Page >

• Unable to connect SQL Server instance from Visual Studio 2008 Version 9.0

  - by salvationishere

  I am getting the below error from VS on an 32-bit XP Professional server even though I set Tools-Options-Database Tools-Data Connections to "SIDEKICK", which is the name of my computer. In other words SIDEKICK should default to the full SQLSERVER. In other words, I want VS to use SQLSERVER instead of SQLSERVER EXPRESS. And I can clearly see my database both from VS in the Server Explorer and also in SSMS 2008. Furthermore, I can view the tables of this database in Server Explorer from VS. I do not get any build errors. And it looks like all of the naming is consistent in my web.config file. I am developing my website according to a Microsoft tutorial, so I should not be getting an error. Yet I get the following exception when I run this code below: CreateAccounts.aspx.cs file protected void CreateAccountButton_Click(object sender, EventArgs e) { MembershipCreateStatus createStatus; //This line below is where the exception occurs MembershipUser newUser = Membership.CreateUser(Username.Text, Password.Text, Email.Text, passwordQuestion, SecurityAnswer.Text, true, out createStatus); And here is what the exception looks like: System.Web.HttpException was unhandled by user code Message="Unable to connect to SQL Server database." Source="System.Web" ErrorCode=-2147467259 StackTrace: at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) at System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) at System.Web.Security.Membership.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) at System.Web.Security.Membership.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, MembershipCreateStatus& status) at Membership_CreatingUserAccounts.CreateAccountButton_Click(Object sender, EventArgs e) in c:\Documents and Settings\Admin\My Documents\Visual Studio 2008\WebSites\WebSite2\Membership\CreatingUserAccounts.aspx.cs:line 24 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException: System.Web.HttpException Message="Unable to connect to SQL Server database." Source="System.Web" ErrorCode=-2147467259 StackTrace: at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) at System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) InnerException: System.Data.SqlClient.SqlException Message="The user instance login flag is not supported on this version of SQL Server. The connection will be closed." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=14 LineNumber=65536 Number=18493 Procedure="" Server="." State=1 StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) InnerException: The web.config connection string looks like: <connectionStrings> <add name="SecurityTutorialsConnectionString" connectionString="data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|SecurityTutorialsDatabase3.mdf;User Instance=true" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <membership defaultProvider="SecurityTutorialsSqlMembershipProvider"> <providers> <add name="SecurityTutorialsSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="SecurityTutorialsConnectionString" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="SecurityTutorials" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/> </providers> </membership>

  Read the article

• Right code to retrieve data from sql server database

  - by HasanGursoy

  Hi, I have some problems in database connection and wonder if I have something wrong in my code. Please review. This question is related: Switch between databases, use two databases simultaneously question. cs="Data Source=mywebsite.com;Initial Catalog=database;User Id=root;Password=toor;Connect Timeout=10;Pooling='true';" using (SqlConnection cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["cs"].ConnectionString)) { using (SqlCommand cmmnd = new SqlCommand("", cnn)) { try { cnn.Open(); #region Header & Description cmmnd.Parameters.Add("@CatID", SqlDbType.Int).Value = catId; cmmnd.CommandText = "SELECT UpperID, Title, Description FROM Categories WHERE CatID=@CatID;"; string mainCat = String.Empty, rootCat = String.Empty; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { mainCat = rdr["Title"].ToString(); upperId = Convert.ToInt32(rdr["UpperID"]); description = rdr["Title"]; } else { Response.Redirect("/", false); } } if (upperId > 0) //If upper category exists add its name { cmmnd.Parameters["@CatID"].Value = upperId; cmmnd.CommandText = "SELECT Title FROM Categories WHERE CatID=@CatID;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { rootCat = "<a href='x.aspx'>" + rdr["Title"] + "</a> &raquo; "; } } } #endregion #region Sub-Categories if (upperId == 0) //show only at root categories { cmmnd.Parameters["@CatID"].Value = catId; cmmnd.CommandText = "SELECT Count(CatID) FROM Categories WHERE UpperID=@CatID;"; if (Convert.ToInt32(cmmnd.ExecuteScalar()) > 0) { cmmnd.CommandText = "SELECT CatID, Title FROM Categories WHERE UpperID=@CatID ORDER BY Title;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { while (rdr.Read()) { subcat.InnerHtml += "<a href='x.aspx'>" + rdr["Title"].ToString().ToLower() + "</a>\n"; description += rdr["Title"] + ", "; } } } } #endregion } catch (Exception ex) { HasanG.LogException(ex, Request.RawUrl, HttpContext.Current); Response.Redirect("/", false); } finally { cnn.Close(); } } } The random errors I'm receiving are: 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.) 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Cannot open database "db" requested by the login. The login failed. Login failed for user 'root'.

  Read the article

• (SQL) Selecting from a database based on multiple pairs of pairs

  - by Owen Allen

  The problem i've encountered is attempting to select rows from a database where 2 columns in that row align to specific pairs of data. IE selecting rows from data where id = 1 AND type = 'news'. Obviously, if it was 1 simple pair it would be easy, but the issue is we are selecting rows based on 100s of pair of data. I feel as if there must be some way to do this query without looping through the pairs and querying each individually. I'm hoping some SQL stackers can provide guidance. Here's a full code break down: Lets imagine that I have the following dataset where history_id is the primary key. I simplified the structure a bit regarding the dates for ease of reading. table: history history_id id type user_id date 1 1 news 1 5/1 2 1 news 1 5/1 3 1 photo 1 5/2 4 3 news 1 5/3 5 4 news 1 5/3 6 1 news 1 5/4 7 2 photo 1 5/4 8 2 photo 1 5/5 If the user wants to select rows from the database based on a date range we would take a subset of that data. SELECT history_id, id, type, user_id, date FROM history WHERE date BETWEEN '5/3' AND '5/5' Which returns the following dataset history_id id type user_id date 4 3 news 1 5/3 5 4 news 1 5/3 6 1 news 1 5/4 7 2 photo 1 5/4 8 2 photo 1 5/5 Now, using that subset of data I need to determine how many of those entries represent the first entry in the database for each type,id pairing. IE is row 4 the first time in the database that id: 3, type: news appears. So I use a with() min() query. In real code the two lists are programmatically generated from the result sets of our previous query, here I spelled them out for ease of reading. WITH previous AS ( SELECT history_id, id, type FROM history WHERE id IN (1,2,3,4) AND type IN ('news','photo') ) SELECT min(history_id) as history_id, id, type FROM previous GROUP BY id, type Which returns the following data set. history_id id type user_id date 1 1 news 1 5/1 2 1 news 1 5/1 3 1 photo 1 5/2 4 3 news 1 5/3 5 4 news 1 5/3 6 1 news 1 5/4 7 2 photo 1 5/4 8 2 photo 1 5/5 You'll notice it's the entire original dataset, because we are matching id and type individually in lists, rather than as a collective pairs. The result I desire is, but I can't figure out the SQL to get this result. history_id id type user_id date 1 1 news 1 5/1 4 3 news 1 5/3 5 4 news 1 5/3 7 2 photo 1 5/4 Obviously, I could go the route of looping through each pair and querying the database to determine it's first result, but that seems an inefficient solution. I figured one of the SQL gurus on this site might be able to spread some wisdom. In case I'm approaching this situation incorrectly, the gist of the whole routine is that the database stores all creations and edits in the same table. I need to track each users behavior and determine how many entries in the history table are edits or creations over a specific date range. Therefore I select all type:id pairs from the date range based on a user_id, and then for each pairing I determine if the user is responsible for the first that occurs in the database. If first, then creation else edit. Any assistance would be awesome.

  Read the article

• Will TSQL become useless because of new ORMs? [closed]

  - by Saeed Neamati

  By introducing LINQ to SQL, I found myself and my .NET developer colleagues gradually moving from TSQL to C# to create queries on the database. Entity Framework made that shift almost permanent. Now it's nearly 2 years that I use LINQ to SQL and LINQ to Entities and haven't used TSQL that much. Yesterday, a colleague encountered a problem (he had to create a SP) and we went to help him. But we all found that our TSQL knowledge was diminished for sure, and a simple SP that seemed trivial to us 2 or 3 years ago, was a challenge to be solved yesterday. Thus it came to my mind that while TSQL's life is attached to SQL Server, and logically as long as SQL Server lives and doesn't change it's SQL language, TSQL would also live, practically it might die, and soon very few people might know it. Am I right? Do existence of ORMs like Entity Framework threaten TSQL's life and usability?

  Read the article

• SQL Saturday #44 Huntington Beach Recap

  What a great day. It was long and tiring, but rewarding in so many ways. On Sunday morning, I was driving home and I decided to take the Pacific Coast Highway from Huntington Beach.  It was a great chance to exhale and just enjoy the sun and smells of the beach (I really love SoCal sometimes). And for future reference for all you speakers, the beach and ocean are only 5 minutes from the SQL Saturday location.  I just could help noticing also the shocking number of high priced cars on the road (4 Bentleys, 3 Ferraris, 1 Aston Martins, 3 Maserati, 1 Rolls Royce, and 2 Lamborghinis).  It made me think about this: Price of all those cars: $ 150,000+.  Impacting the ability of people to learn: Priceless.  We have positively impacted the education, knowledge, capabilities of not only our attendees, but also all of their companies and people they might help as well.  That is just staggering and something to be immensely proud of. To all of my fellow community leaders, I salute you. So lets talk about the event Overall We had over 220 people register for the event and had 180+ people attend the event. I was shooting for the magical 200 number, but I guess it just gives us more motivation to make it even bigger and better next time. We had a few snags along the way, but what event doesnt, but I think everything turned out great. I did not hear any negative comments and heard lots of positive comments along with people asking when the next one is going to be (More on that later). Location- Golden West College We could not have asked for a better partner for the event. Herb Cohen from Golden West College was the wizard behind the curtains. From the beginning, he was our advocate to the GWC Board and was instrumental in getting our event approved. The day off, Herb was a HUGE help getting any and all logistics that we needed taken care of. In the craziness of the early morning registration crush it was a big help knowing that he and Bret Stateham (Blog | Twitter) were taking care of testing projectors in all the rooms. Anything we needed he was there and was even proactive in getting some things that I had not even thought of (i.e. a dumpster for all of our garbage). I cannot thank Herb enough along with other members of the GWC staff including Minnie Higgins of the Career and Technical Education Division office, Jack Taylor, public safety, and Ron Pryor, Tech Services Support. And last, but not least, the Wireless on campus was absolutely FANTASTIC! Some lessons learned Unless you are a glutton for punishment, as I no doubt am, you most certainly want to give yourself more than six weeks to plan the event. I am lucky that I have a very understanding wife and had a wonderful set of co-coordinators helping me out. A big thanks goes out to Phil, Marlon (Blog | Twitter), Nitin (Twitter), Thomas (Blog | Twitter), Bret (Blog | Twitter), Ben, and Laurie. Thankfully, the sponsor and speaker community was hugely supportive and we were able to fill out the entire event with speakers and sponsors. I have to say that there is not a lot that I would change after this years event. There are obviously going to be some things that we can do better or differently next time, but overall I think it was a great event and I was more than happy with the response we received from the community. Sponsors We obviously could not have put together our event without our sponsors. So certainly have to show them some love. Platinum Sponsors Quest Software http://www.quest.com My Space http://www.myspace.com/ Gold Strategy Companion http://www.strategycompanion.com Silver Fusion-IO http://www.fusionio.com Bronze WestClinTech http://westclintech.com Professional Association For SQL Server http://www.sqlpass.org Attunity http://www.attunity.com Sharepoint 360 http://www.sharepoint360.com Some additional Thanks Andy Warren (Blog | Twitter) Always there to answer my question and help out when I had some issues or questions with the website. The amount of work that he and everyone else put into SQL Saturday is very amazing. What a great gift to the community! Einstein Bros. Bagels They were our Breakfast Vendor and arrived perfectly on time with yummy bagels, sweets and most importantly coffee. Luccis Deli (http://www.luccisdeli.com) Luccis was out Lunch Vendor. They were great to work with and the food was excellent. They worked with us to give us a great price. Heard lots of great comments about the lunches. Definitely not your ordinary box lunch. Moving Forward Unfortunately, the work does not end after the event. We have a few things to clear up such as surveys, sponsor stuff, presentations uploaded to the website, expense reimbursement, stuff like that. Hopefully, all that should be cleared up within the next couple weeks. After that as a group we are going to get together and decide what our next steps are. We definitely want to keep some of the momentum that we are building as a SQL Community and channel that into future SQL Saturdays and other types of community events. In the meantime, for additional training be sure to check out your local User Group and PASS. San Diego SQL Server Users Group ( http://www.sdsqlug.org/home/index.cfm ) Orange County SQL Server Users Group ( http://www.sqloc.com/ ) L.A. SQL Server Users Group ( http://www.sql.la/ ) SQL PASS ( http://www.sqlpass.org/ ) 24 Hours of PASS ( http://www.sqlpass.org/24hours/2010/ ) So stay tuned, there will be more events to come in SoCal!!Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

  Read the article

• SQL 2005 Transaction Rollback Hung–unresolved deadlock

  - by steveh99999

  Encountered an interesting issue recently with a SQL 2005 sp3 Enterprise Edition system. Every weekend, a full database reindex was being run on this system – normally this took around one and a half hours. Then, one weekend, the job ran for over 17 hours  - and had yet to complete... At this point, DBA cancelled the job. Job status is now cancelled – issue over…   However, cancelling the job had not killed the reindex transaction – DBCC OPENTRAN was still showing the transaction being open. The oldest open transaction in the database was now over 17 hours old.  Consequently, transaction log % used growing dramatically and locks still being held in the database... Further attempts to kill the transaction did nothing. ie we had a transaction which could not be killed. In sysprocesses, it was apparent the SPID was in rollback status, but the spid was not accumulating CPU or IO. Was the SPID stuck ? On examination of the SQL errorlog – shortly after the reindex had started, a whole bunch of deadlock output had been produced by trace flag 1222. Then this :- spid5s      ***Stack Dump being sent to   xxxxxxx\SQLDump0042.txt spid5s      * ******************************************************************************* spid5s      * spid5s      * BEGIN STACK DUMP: spid5s      *   12/05/10 01:04:47 spid 5 spid5s      * spid5s      * Unresolved deadlock spid5s      * spid5s      *   spid5s      * ******************************************************************************* spid5s      * ------------------------------------------------------------------------------- spid5s      * Short Stack Dump spid5s      Stack Signature for the dump is 0x000001D7 spid5s      External dump process return code 0x20000001. Unresolved deadlock – don’t think I’ve ever seen one of these before…. A quick call to Microsoft support confirmed the following bug had been hit :- http://support.microsoft.com/kb/961479 So, only option to get rid of the hung spid – to restart SQL Server… Fortunately SQL Server restarted without any issues. I was pleasantly surprised to see that recovery on this particular database was fast. However, restarting SQL Server to fix an issue is not something I would normally rush to do... Short term fix – the reindex was changed to use MAXDOP of 1. Longer term fix will be to apply the correct CU, or wait for SQL 2005 sp 4 ?? This should be released any day soon I hope..

  Read the article

• Import and Export data from SQL Server 2005 to XL Sheet

  - by SAMIR BHOGAYTA

  For uploading the data from Excel Sheet to SQL Server and viceversa, we need to create a linked server in SQL Server. Expample linked server creation: Before you executing the below command the excel sheet should be created in the specified path and it should contain the name of the columns. EXEC sp_addlinkedserver 'ExcelSource2', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\Srinivas\Vdirectory\Testing\Marks.xls', NULL, 'Excel 5.0' Once you executed above query it will crate linked server in SQL Server 2005. The following are the Query from sending the data from Excel sheet to SQL Server 2005. INSERT INTO emp SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\text.xls','SELECT * FROM [sheet1$]') The following query is for sending the data from SQL Server 2005 to Excel Sheet. insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\text.xls;', 'SELECT * FROM [sheet1$]') select * from emp

  Read the article

• SQL Azure maximum database size rises from 10GB to 50GB in June

  - by Eric Nelson

  At Mix we announced that we will be offering a new 50gb size option in June. If you would like to become an early adopter of this new size option before generally available, send an email to [email protected]  and it will auto-reply with instructions to fill out a survey to nominate your application that requires greater than 10gb of storage. Other announcements included: MARS in April: Execute multiple batches in a single connection Spatial Data in June: Geography and geometry types SQL Azure Labs: SQL Azure Labs provides a place where you can access incubations and early preview bits for products and enhancements to SQL Azure. Currently OData Service for SQL Azure. Related Links: SQL Azure Announcements at MIX http://ukazure.ning.com

  Read the article

• How to clear the resent server name list in SQL Server Management Studio

  - by Pavan Kumar Pabothu

  If you are using SQL Server management Studio much the we can observer that the list of server names in the log in of it. As you can imagin a period of time after 6 month or 1 year you will see a long list of server names in the login dialog. How to clear this list...? I doesn't provide a mechanism to clean nor clear the list, so you'll have to do a little browsing through your file system. For SQl Server 2005 Management Studio, we should delete the below file C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat. For SQl Server 2008 Management Studio, we should delete the below file C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\SQLStudio.bin. After deletion we can re-login the Management studio and can see the empty list.

  Read the article

• Utility to Script SQL Server Configuration

  - by Bill Graziano

  I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility: Assist with disaster recovery preparation Identify configuration changes I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL Server Configuration project page. The application is a .NET 2.0 console application that uses SMO. It writes its output to a directory that you specify.  Disaster Planning ScriptSqlConfig generates scripts for logins, jobs and linked servers.  It writes the properties and configuration from the instance to text files. The scripts are designed so they can be run against a DR server in the case of a disaster. The properties and configuration will need to be manually compared. Each job is scripted to its own file. Each linked server is scripted to its own file. The linked servers don’t include the password if you use a SQL Server account to connect to the linked server. You’ll need to store those somewhere secure. All the logins are scripted to a single file. This file includes windows logins, SQL Server logins and any server role membership.  The SQL Server logins are scripted with the correct SID and hashed passwords. This means that when you create the login it will automatically match up to the users in the database and have the correct password. This is the only script that I programmatically generate rather than using SMO. The SQL Server configuration and properties are scripted to text files. These will need to be manually reviewed in the event of a disaster. Or you could DIFF them with the configuration on the new server. Configuration Changes These scripts and files are all designed to be checked into a version control system.  The scripts themselves don’t include any date specific information. In my environments I run this every night and check in the changes. I call the application once for each server and script each server to its own directory.  The process will delete any existing files before writing new ones. This solved the problem I had where the scripts for deleted jobs and linked servers would continue to show up.  To see any changes I just need to query the version control system to show many any changes to the files. Database Scripting Utilities that script database objects are plentiful.  CodePlex has at least a dozen of them including one I wrote years ago. The code is so easy to write it’s hard not to include that functionality. This functionality wasn’t high on my list because it’s included in a database backup.  Unless you specify the /nodb option, the utility will script out many user database objects. It will script one object per file. It will script tables, stored procedures, user-defined data types, views, triggers, table types and user-defined functions. I know there are more I need to add but haven’t gotten around it yet. If there’s something you need, please log an issue and get it added. Since it scripts one object per file these really aren’t appropriate to recreate an empty database. They are really good for checking into source control every night and then seeing what changed. I know everyone tells me all their database objects are in source control but a little extra insurance never hurts. Conclusion I hope this utility will help a few of you out there. My goal is to have it script all server objects that aren’t contained in user databases. This should help with configuration changes and especially disaster recovery.

  Read the article

• SQL Server MCM Changes and Readiness Videos

  - by Enrique Lima

  Towards the end of 2010, Microsoft made some changes to the Microsoft Certified Master for SQL Server 2008 program. The process to certification required to have a 3 week bootcamp/course in Redmond. This has changed now.  It has been mapped to 2 exams. Get information from Microsoft Learning with regards to the changes, process, resources and pricing for the certification exams.  http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx What has happened here too, is some SQL MCM rotation Instructors and SQL MCMs  have created materials to prep for those exams.  I see this as a huge benefit for individuals who are planning to take on the MCM, but really it is of huge benefit for all individuals who deal with working around SQL Server on a regular basis. Check the Readiness Videos as a great starting point http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

  Read the article

• SQL Server Express Profiler

  - by David Turner

  During a recent project, while waiting for our Development Database to be provisioned on the clients corporate SQL Server Environment (these things can sometimes take weeks or months to be setup), we began our initial development against a local instance on SQL Server Express, just as an interim measure until the Development database was live.  This was going just fine, until we found that we needed to do some profiling to understand a problem we were having with the performance of our ORM generated Data Access Layer.  The full version of SQL Server Management Studio includes a profiler, that we could use to help with this kind of problem, however the Express version does not, so I was really pleased to find that there is a freely available Profiler for SQL Server Express imaginatively titled ‘SQL Server Express Profiler’, and it worked great for us.  http://sites.google.com/site/sqlprofiler/

  Read the article

• Linq to SQL EntitySet Binding the MVVM way

  - by Savvas Sopiadis

  Hi everybody! In a WPF application i'm using LINQ to SQL classes (created by SQL Metal, thus implementing POCOs). Let's assume i have a table User and a Table Pictures. These pictures are actually created from one picture, the difference between them may be the size, coloring,... So every user may has more than one Pictures, so the association is 1:N (User:Pictures). My problems: a) how do i bind, in a MVVM manner, a picture control to one picture (i will take one specific picture) in the EntitySet, to show it up? b) everytime a user changes her picture the whole EntitySet should be thrown away and the newly created Picture(s) should be a added. Is this the correct way? e.g. //create the 1st piture object UserPicture1 = new UserPicture(); UserPicture1.Description = "... some description.. "; USerPicture1.Image = imgBytes; //array of bytes //create the 2nd piture object UserPicture2 = new UserPicture(); UserPicture2.Description = "... another description.. "; UserPicture2.Image = DoSomethingWithPreviousImg(imgBytes); //array of bytes //Assuming that the entityset is called Pictures //add these pictures to the corresponding user User.Pictures.Add(UserPicture1); User.Pictures.Add(UserPicture2); //save changes datacontext.Save() Thanks in advance

  Read the article

• How to populate a generic list of objects in C# from SQL database

  - by developr

  I am just learning ASP.NET c# and trying to incorporate best practices into my applications. Everything that I read says to layer my applications into DAL, BLL, UI, etc based on separation of concerns. Instead of passing datatables around, I am thinking about using custom objects so that I am loosely coupled to my data layer and can take advantage of intellisense in VS. I assume these objects would be considered DTOs? First, where do these objects reside in my layers? BLL, DAL, other? Second, when populating from SQL, should I loop through a data reader to populate the list or first fill a data table, then loop through the table to populate the list? I know you should close the database connection as soon as possible, but it seems like even more overhead to populate the data table and then loop through that for the list. Third, everything I see these days says use Linq2SQL. I am planning to learn Linq2SQL, but at this time I am working with a legacy database that doesn't have foreign keys setup and I do not have the ability to fix it atm. Also, I want to learn more about c# before I start getting into ORM solutions like nHibernate. At the same time I don't want to type out all the connection and SQL plumbing for every query. Is it ok to use the Enterprise DAAB for now?

  Read the article

• Linq to SQL and concurrency with Rob Conery repository pattern

  - by David Hall

  I have implemented a DAL using Rob Conery's spin on the repository pattern (from the MVC Storefront project) where I map database objects to domain objects using Linq and use Linq to SQL to actually get the data. This is all working wonderfully giving me the full control over the shape of my domain objects that I want, but I have hit a problem with concurrency that I thought I'd ask about here. I have concurrency working but the solution feels like it might be wrong (just one of those gitchy feelings). The basic pattern is: private MyDataContext _datacontext private Table _tasks; public Repository(MyDataContext datacontext) { _dataContext = datacontext; } public void GetTasks() { _tasks = from t in _dataContext.Tasks; return from t in _tasks select new Domain.Task { Name = t.Name, Id = t.TaskId, Description = t.Description }; } public void SaveTask(Domain.Task task) { Task dbTask = null; // Logic for new tasks omitted... dbTask = (from t in _tasks where t.TaskId == task.Id select t).SingleOrDefault(); dbTask.Description = task.Description, dbTask.Name = task.Name, _dataContext.SubmitChanges(); } So with that implementation I've lost concurrency tracking because of the mapping to the domain task. I get it back by storing the private Table which is my datacontext list of tasks at the time of getting the original task. I then update the tasks from this stored Table and save what I've updated This is working - I get change conflict exceptions raised when there are concurrency violations, just as I want. However, it just screams to me that I've missed a trick. Is there a better way of doing this? I've looked at the .Attach method on the datacontext but that appears to require storing the original version in a similar way to what I'm already doing. I also know that I could avoid all this by doing away with the domain objects and letting the Linq to SQL generated objects all the way up my stack - but I dislike that just as much as I dislike the way I'm handling concurrency.

  Read the article

• Linq-to-Sql IIS7 Login failed for user ‘DOMAIN\MACHINENAME$’

  - by cfdev9

  I am encountering unexpected behaviour using Linq-to-sql DataContext. When I run my application locally it works as expected however after deploying to a test server which runs IIS7, I get an error Login failed for user ‘DOMAIN\MACHINENAME$’ when attempting to open objects from the DataContext. This code explains the error, which breaks on the very last line with the error "System.Data.SqlClient.SqlException: Login failed for user". var connStr ="Data Source=server;Initial Catalog=Test;User Id=testuser;Password=password"; //Test 1 var conn1 = new System.Data.SqlClient.SqlConnection(connStr); var cmdString = "SELECT COUNT(*) FROM Table1"; var cmd = new System.Data.SqlClient.SqlCommand(cmdString, conn1); conn1.Open(); var count1 = cmd.ExecuteScalar(); conn1.Close(); //Test 2 var conn2 = new System.Data.SqlClient.SqlConnection(connStr); var context = new TestDataContext(conn2); var count2 = context.Table1s.Count(); The connection string is not even using integrated security, so why is Linq-to-sql trying to connect as a specific user? If I change the server name in the connection string I get a different error so its using atleast part of the connection string, but apparently ignoring the UserId and Password. Very confused.

  Read the article

• How to connect to SQL Server using activerecord, JDBC, JTDS and Integrated Security

  - by Rob

  As per the above, I've tried: establish_connection(:adapter => "jdbcmssql", :url => "jdbc:jtds:sqlserver://myserver:1433/mydatabase;domain='mynetwork';", :username => 'user', :password=>'pass' ) establish_connection(:adapter => "jdbcmssql", :url => 'jdbc:jtds:sqlserver://myserver:1433/mydatabase;domain="mynetwork";user="mynetwork\user"' ) establish_connection(:adapter => "jdbcmssql", :url => "jdbc:jtds:sqlserver://myserver:1433/mydatabase;domain='mynetwork';", :username=>'user' ) establish_connection(:adapter => "jdbcmssql", :url => "jdbc:jtds:sqlserver://myserver:1433/mydatabase;domain='mynetwork';integratedSecurity='true'", :username=>'user' ) .. and various other combinations. Each time I get: net/sourceforge/jtds/jdbc/SQLDiagnostic.java:368:in `addDiagnostic': java.sql.SQLException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. (NativeException) Any tips? Thanks, activerecord (2.3.5) activerecord-jdbc-adapter (0.9.6) activerecord-jdbcmssql-adapter (0.9.6) jdbc-jtds (1.2.5) jruby 1.4.0 (ruby 1.8.7 patchlevel 174) (2009-11-02 69fbfa3) (Java HotSpot(TM) Client VM 1.6.0_18) [x86-java]

  Read the article

• ODBC continually prompts for password

  - by doublej92

  I have an application built in Access 2003 that uses a system DSN ODBC to connect to a SQL Server. The ODBC uses SQL authentication. When the application is started, the user is prompted to authenticate into the database. I have another computer set up within the same domain that has Access 2007 installed on it. I log in using the same credentials that I use to get on the machine that has Access 2003. I converted my application to Access 2007 format and everything works fine. However, when other users try to use the application, they are prompted to enter the database password every time a table is accessed. Thinking it was a problem with my ODBC, I confirmed that the connections were set up the same way on both of my machines, and the user's machine. Here is the interesting part, when the user logged into my machine, it started prompting for the password every time. When I logged into the user's machine, the application worked fine. Anyone have any ideas? All help is appreciated!

  Read the article

• SQL Quey slow in .NET application but instantaneous in SQL Server Management Studio

  - by user203882

  Here is the SQL SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = 70402 AND ta.TrustAccountID = 117249 AND tal.trustaccountlogid = ( SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = 70402 AND ta.TrustAccountID = 117249 AND tal.TrustAccountLogDate < '3/1/2010 12:00:00 AM' ) Basicaly there is a Users table a TrustAccount table and a TrustAccountLog table. Users: Contains users and their details TrustAccount: A User can have multiple TrustAccounts. TrustAccountLog: Contains an audit of all TrustAccount "movements". A TrustAccount is associated with multiple TrustAccountLog entries. Now this query executes in milliseconds inside SQL Server Management Studio, but for some strange reason it takes forever in my C# app and even timesout (120s) sometimes. Here is the code in a nutshell. It gets called multiple times in a loop and the statement gets prepared. cmd.CommandTimeout = Configuration.DBTimeout; cmd.CommandText = "SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID1 AND ta.TrustAccountID = @TrustAccountID1 AND tal.trustaccountlogid = (SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID2 AND ta.TrustAccountID = @TrustAccountID2 AND tal.TrustAccountLogDate < @TrustAccountLogDate2 ))"; cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId; cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId; cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId; cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId; cmd.Parameters.Add("@TrustAccountLogDate2", SqlDbType.DateTime).Value =TrustAccountLogDate; // And then... reader = cmd.ExecuteReader(); if (reader.Read()) { double value = (double)reader.GetValue(0); if (System.Double.IsNaN(value)) return 0; else return value; } else return 0;

  Read the article

• TRY CATCH with Linked Server in SQL Server 2005 Not Working

  - by Robert Stanley

  Hello, I am trying to catch sql error raised when I execute a stored procedure on a linked server. Both Servers are running SQL Server 2005. To prove the issue I have created a stored procedure on the linked server called Raise error that executes the following code: RAISERROR('An error', 16, 1); If I execute the stored procedure directly on the linked server using the following code I get a result set with 'An error', '16' as expected (ie the code enters the catch block): BEGIN TRY EXEC [dbo].[RaiseError]; END TRY BEGIN CATCH DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int; SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(); SELECT @ErrMsg, @ErrSeverity; END CATCH If I run the following code on my local server to execute the stored procedure on the linked server then SSMS gives me the message 'Query completed with errors', .Msg 50000, Level 16, State 1, Procedure RaiseError, Line 13 An error' BEGIN TRY EXEC [Server].[Catalog].[dbo].RaiseError END TRY BEGIN CATCH DECLARE @SPErrMsg nvarchar(4000), @SPErrSeverity int; SELECT @SPErrMsg = ERROR_MESSAGE(), @SPErrSeverity = ERROR_SEVERITY(); SELECT @SPErrMsg, @SPErrSeverity; END CATCH My Question is can I catch the error generated when the Linked server stored procedure executes? Thanks in advance!

  Read the article

• SQL Server transaction log backups,

  - by krimerd

  Hi there, I have a question regarding the transaction log backups in sql server 2008. I am currently taking full backups once a week (Sunday) and transaction log backups daily. I put full backup in folder1 on Sunday and then on Monday I also put the 1st transaction log backup in the same folder. On tuesday, before I take the 2nd transaction log backup I move the first transaction log backup from folder1 an put it into folder2 and then I take the 2nd transaction log backup and put it in the folder1. Same thing on Wed, Thurs and so on. Basicaly in folder1 I always have the latest full backup and the latest transaction log backup while the other transaction log backups are in folder2. My questions is, when sql server is about to take, lets say 4th (Thursday) transaction log backup, does it look for the previous transac log backups (1st, 2nd, and 3rd) so that this new backup will only include the transactions from the last backup or it has some other way of knowing whether there are other transac log backups. Basically, I am asking this because all my transaction log backups seem to be about the same size and I thought that their size will depend on the amount of transactions since the last transaction log backup. Can anyone please explain if my assumptions are right? Thanks...

  Read the article

• SQL Server slow in production environment

  - by Lieven Cardoen

  I have a weird problem in a customer's production environment. I can't give any details on the infrastructure, except that SQL server runs on a virtual server. The data, log and filestream file are on another storage server (data and filestream together and log on a separate server). In our local Test environment, there's one particular query that executes with these durations: first we clear the cache 300ms (First time it takes longer, but from then on it's cached.) 20ms 15ms 17ms In the customer's production environment, the SQL Server is more powerful, these are the durations (I didn't have the rights to clear the cache. Will try this tomorrow). 2500ms 2600ms 2400ms The servers in the customer's production environment are more powerful but they do have virtual servers (we don't). What could be the cause... Not enough memory? Fragmentation? Physical storage? How would you tackle this performance problem? EDIT: Some people have asked me if the data set is equal and it is. I restored their database on our environment. It's true that this was the first thing I looked at. (@Everyone: I added the edit because it will be the first thing that many will think off).

  Read the article

• Cannot update a single field using Linq to Sql

  - by KallDrexx

  I am having a hard time attempting to update a single field without having to retrieve the whole record prior to saving. For example, in my web application I have an in place editor for the Name and Description fields of an object. Once you edit either field, it sends the new field (with the object's ID value) to the web server. What I want is the webserver to take that value and ID and only update the one field. There are only two ways google tells me to do this: 1) When I get the value I want to change, the value and the ID, retrieve the record from the database, update the field in the c# object, and then send it back to the server. I don't like this method because not only does it include a completely unnecessary database read call (which includes two tables due to the way my schema is). 2) Set UpdateCheck for all the fields (but the primary keys) to UpdateCheck.Never. This doesn't work for me (I think) due to my mapping layer between the Linq to Sql and my Entity/ViewModel layer. When I convert my entity into the linq to sql db object it seems to be updating those fields regardless of the UpdateCheck setting. This might be just because of integers, since not setting an int means it is a zero (and no, I can't use int? instead). Are there any other options that I have?

  Read the article

• SQL statement with datetimepicker

  - by David Archer

  This should hopefully be a simple one. When using a date time picker in a windows form, I want an SQL statement to be carried out, like so: string sql = "SELECT * FROM Jobs WHERE JobDate = '" + dtpJobDate.Text + "'"; Unfortunately, this doesn't actually provide any results because the JobDate field is stored as a DateTime value. I'd like to be able to search for all records that are on this date, no matter what the time stored may be, any help? New query: SqlDataAdapter da2 = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate"; cmd.Parameters.Add ("@p_StartDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date; cmd.Parameters.Add ("@p_EndDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date.AddDays(1); cmd.Connection = conn; da2.SelectCommand = cmd; da2.Fill(dt); dgvJobDiary.DataSource = dt; Huge thanks for all the help!

  Read the article

• T SQL Rotate row into columns

  - by cshah

  SQL 2005 using T-SQL, I want to rotate rows into columns. Sample script: Use TempDB Go CREATE TABLE [dbo].[CPPrinter_InkLevels]( [CPPrinter_InkLevels_ID] [int] IDENTITY(1,1) NOT NULL, [CPMeasurementGUID] [uniqueidentifier] NOT NULL, [InkName] [varchar](30) NOT NULL, [InkLevel] [decimal](6, 2) NOT NULL, CONSTRAINT [PK_CPPrinter_InkLevels] PRIMARY KEY CLUSTERED ( [CPPrinter_InkLevels_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[CPPrinter_InkLevels] ON INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (1, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Black', CAST(0.60 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (2, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Cyan', CAST(0.69 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (3, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Magenta', CAST(0.55 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (4, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Yellow', CAST(0.51 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (5, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Black', CAST(0.64 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (6, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Cyan', CAST(0.43 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (7, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Magenta', CAST(0.30 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (8, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Waste Tank', CAST(0.18 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (9, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Black', CAST(0.60 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (10, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Cyan', CAST(0.69 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (11, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Magenta', CAST(0.55 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (12, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Yellow', CAST(0.51 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (13, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Light Black', CAST(0.64 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (14, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Light Cyan', CAST(0.43 AS Decimal(6, 2))) Go SELECT * FROM [dbo].[CPPrinter_InkLevels] --Desired output CPMeasuremnetGUID, Ink1, Level1, Ink2, Level2, Ink3, Level3....

  Read the article

< Previous Page | 232 233 234 235 236 237 238 239 240 241 242 243  | Next Page >