Hi I have a search ui with 3 all optional search criteria. 2 of them are simple criteria for a where statement, that I should be able to solve with this: http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters. The last criterion is using full text search where I join the result from ContainsTable. Is ...
I am planning for monitoring application which will use SQL 2005/08 database. It will monitor old server(s) which are using SQL 2000 db. Tools task is to pick the data from the servers do some statistics and insert the data back into tools own database for reporting purpose. I am using .NET(C#) as development environment. What i would ...
This is a continuation from When I update/insert a single row should it lock the entire table? Here is my problem. I have a table that holds locks so that other records in the system don’t have to take locks out on common resources, but can still queue the tasks so that they get executed one at a time. When I access a record in this l...
I have a SQL Server with a number of databases. Most are for applications, but some store data for reporting and analysis. I also have information that is not specific to any one database, but can be used by several of them. A good example is my company's fiscal calendar. I store this information in a table. Putting the same fiscal cale...
Can we return null value from stored procedure. i dont want to use collase or isnull. I want to capture NULL at the frontend. Is it possible ? Edit: I am using Sql Server 2005 eg. where i want to use CREATE PROCEDURE [Authentication].[spOnlineTest_CheckLogin] @UserName NVARCHAR(50) AS BEGIN TRY BEGIN TRAN ...
I have a server application, and a database. Multiple instances of the server can run at the same time, but all data comes from the same database (on some servers it is postgresql, in other cases ms sql server). In my application, there is a process that is performed which can take hours. I need to ensure that this process is only execu...
Are there any recomended field sizes for commonly used string data storage types? For example things like FirstName, LastName, AddressLine1, AddressLine2, City, State, PostalCode, EmailAddress etc. I am sure anyone who has created a table to store these commonly used data fields has had to make a decision as to what maximum size to use. ...
I am building an application and I want to batch multiple queries into a single round-trip to the database. For example, lets say a single page needs to display a list of users, a list of groups and a list of permissions. So I have stored procs (or just simple sql commands like "select * from Users"), and I want to execute three of the...
I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented? ...
I wanted to run a .net application on a laptop. I created the application(aspx pages and SQL database) on a computer that has VS 08 installed. Now i wanted to run this application on a laptop that does not have SQL sever 2005 installed. As far as the aspx pages if i publish them to a zip drive copy it to my laptop and go into IIS and de...
I'm working with a SQL server and several databases. Each database may have many schema's, such as dbo., admin., other., and I'm getting tired of scrolling around trying to find things. If I'm working on a schema specific project, I want to filter out everything else from my view except that schema. As dbo, I see everything which means ...
I am currently building my own CMS for various reasons that could take a long to explain... However i am looking for a dynamic solution to create templates for pages within the CMS and all areas must be editable via the administration area, maybe large text areas broken into multiple areas, text and image area on a page etc. ----- edi...
Hi all. I came across a weird situation when trying to count the number of rows that DO NOT have varchar values specified by a select statement. Ok, that sounds confusing even to me, so let me give you an example: Let's say I have a field "MyField" in "SomeTable" and I want to count in how many rows MyField values do not belong to a dom...
Say ServerA.TableA is replicated (via Transactional Replication) to ServerB.TableA. If a trigger existed on ServerB.TableA that fired when a row was inserted or updated, would the trigger fire as rows are replicated from ServerA.TableA? ...
I have been tasked with converting a SQL Server database into a MySQL 5.* database. I feel well-read up about converting between datatypes. However, I read that MySQL does ANSI, not T-SQL, and doesn't support cursors. My question is, what am I supposed to do with all of my SQL Server functions and sprocs (some of which use cursors) ? ...
I need to find if /3GB switch and /PAE is enabled on a server. Also, I want to know the size of page file and physical RAM on the server. I can check them manually but how can I check them using TSQL on both SQL 2000 and SQL 2005? ...
How do I delete only the top row of a table in SQL Server? I did: set rowcount 1; delete * from table; set rowcount 0; But I am not sure if its a good way to do it. Is there any better way to accomplish that? ...
I have a string in a SQL Server table whose format is like this... nvarchar int nvarchar int nvarchar There are no obvious delimiters other than some chars are numeric and others are alpha. How do I reference the second int value? ...
Hi, I have one flat MAIN_TABLE. I need to insert the records from this table to multiple TABLES. eg. MAIN_TABLE col1, col2, col3, col4, col5 PARENT1_TABLE PT1_ID(PK), col1,col2 PARENT2_TABLE PT2_ID(PK), col3,col4 PARENT2_CHILD_TABLE P2C_ID(PK), PT2_ID(FK), col5, col6 so on. The goal is, I have to move the record from that flat M...
I write a stored procedure in SQL Server like this select distinct top 5 videos.videoid,videos.videotitle,videos.videoname, convert(varchar,videos.posteddate,106) as posteddate,videos.approvedstatus, videos.videoimage,(ISNULL(videos.views,0.0)) as [views],videos.privacy, (isnull(videos.rating,0.0)) as rating,videos.userid, u...