I'm in the process of testing an application and it's database and for this I want to restart my testing each time completely clean. This application loads a large amount of data from Twitter. Therefore, before I start, I delete all data from the database and kill any processes from my web account associated with this application. When I...
There are some constraints to this question; I don't have the ability to fundamentally alter any database structure. The challenge here is that I have rows in a database that contain information that really should be on its own row. A somewhat simplified example of the column structure: [PersonID] [FirstName] [LastName] [FirstNameGuest...
Hi, I've got the following tables in a CMS database (SQL Server 2008) for a website: SiteMap Page Link ========= ========= ========= SiteMapId PageId LinkId LeftNode Name Name RightNode UrlId Url -- etc -- -- etc -- Sitemap table contains the website hiera...
I have a list of values, and want to know which ones don't already exist in the table. (This is on sql server 2000) Consider the following: CREATE TABLE myTable ( foo VARCHAR(10) ) GO INSERT INTO myTable ( foo ) SELECT 'aaa' UNION ALL SELECT 'bbb' UNION ALL SELECT 'ccc' GO SELECT foo FROM m...
Hello, I'm using SQL Server 2005. I am migrating data over from a current database (single table) to a new database (normalized - many tables). In the new database, I have a base table (let's call it "BaseTable"), and multiple other tables (let's call them "DependentA", and "DependentB"). Some of the data from the old database will go t...
Hello I need to get schema details for the passed table name using information_schema. As part of output need ColumnName, Constraint_Name, Constraint_Type. How can this be done Thanks ...
I'm refactoring some older SQL, which is struggling after 4 years and 1.7m rows of data. Is there a way to improve the following MS SQL Query: SELECT ServiceGetDayRange_1.[Display Start Date], SUM (CASE WHEN Calls.line_date BETWEEN [Start Date] AND [End Date] THEN 1 ELSE 0 END) AS PerDayCount FROM dbo.ServiceGetDayRange(GET...
I was recently asked to speed up a C#/ASP.NET/SQL Server business app website. Since I just started, I don't know too much about the internals. So where do I start? Sight unseen, what is the single most important thing affecting performance on a system like this? Database tuning? Hardware? Individual page optimization? What is the...
How to use this code if we are going to use sql server database becaUSE in this code we used MS Access as the database private void btnSearch_Click(object sender, System.EventArgs e) { String pcode = txtPcode.Text; int ctr = productsDS1.Tables[0].Rows.Count; int x; bool found = false; for (x = 0; x<ctr; x++) { if (product...
Hi, Every page in an mdf file(sql server) has a m_tornBits field in the page header. Can anybody explain what this value means here is an example of a page header : - PAGE HEADER: Page @0x1A198000 m_pageId = (1:135) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x2 m_...
How to install sql server 2005 express edition via command prompt (silent install). Further the installed instance should be accessible via lan instance name: test username : sa password : admin123 Can you plz generate the required script for it. Thanks in advance. ...
I need to determine the available space on the drive where my database lives. I know about the xp_fixeddrives procedure but how do I get the info for the specific drive where my database resides? ...
I’m using .Net 3.5 and SQL Server 2008 Express. Should I use the administrator user for the connection string, Or should I create a new user with limited permissions? If I need to create a new user for the connection string: What security permissions should I grant him? How do I set those permissions? Thanks. ...
How can I find sql server port number from windows? Is there any generic way to find port number for sql server 2000, 2005 and 2008? ...
I'm evaluating TeamCity, and am using it with HSQLDB. Now, I'm ready to use an external database, and I have the option to use either Oracle or SQL Server. Everything being equal, Oracle is the preferred option due to corporate policy. Which database will give me the smoothest experience together with TeamCity? ...
Table1 ID Date 001 01/02/2009 001 02/02/2009 ... ... 001 28/02/2009 002 01/02/2009 002 02/02/2009 ... ... 002 28/02/2009 Table2 ID Date Salary 001 02/02/2009 800 001 25/02/2009 500 002 01/02/2009 300 ..., I want to join the two tables Tried Query SELECT table1.id, table1.date, table2.salary FROM table1 LEFT OUTER JOIN table2 ...
Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow: SELECT TOP 100 qt.TEXT AS 'SP Name', SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset ...
Hello. I want to write an CLR procedure which takes a text and returns a table with all the words in this text. But I can't figure out how to return a table. Could you please tell me it? [Microsoft.SqlServer.Server.SqlFunction] public static WhatTypeShouldIWriteHere Function1(SqlString str) { string[] words = Regex.Sp...
I've noticed that we have a lot of indexes that begin with a certain column and that column has low cardinality (i.e. Company and 99% of records belong to the 'live' company). Therefore these indexes are next to useless (from what I've read) as they are not providing a means of segregating the data in the table to be found quickly. So,...
I get the following error when calling a stored procedure that has a table valued parameter as one of the parameters The EXECUTE permission was denied on the object 'ValidationErrors' ValidationErrors is a TVP created with the following statement: CREATE TYPE [dbo].[ValidationErrors] AS TABLE( [ErrorMessage] varchar NOT NULL ) ...