I have a table with two linked columns, a compulsory boolean and an optional date. There can only be a date when the boolean is FALSE. So I have this structure: CREATE TABLE FOO ( FOO_ID INT IDENTITY(1, 1) NOT NULL, MY_DATE DATETIME, MY_BOOLEAN BIT DEFAULT 0 NOT NULL, CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID) ); And I've...
I have a query as follows select * from table where col1 = @param1 and col2 = @parm2 And another select * from table where col1 = @param1 Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them. My queries are very big and i have to create 2 version...
hey everyone, I've enabled Roles in my ASP.NET web application, but I'm slightly confused as to where they are being stored. In my old project, each Role would appear in the ASP.NET Roles table, and I could see them. However, in my new project, I can't see any Roles in the database table. The strange thing is, I can still use them (and...
I have a table in SQL Server, tblMain. There's a trigger that when a row changes, it basically does a SELECT * from tblMain and inserts the changed row into tblHistory. tblHistory is a duplicate of tblMain (only much taller) and it has one extra field for a unique id. I recently added a field of type TimeStamp (which I now understand ...
I'm tracking down an odd and massive performance problem in my SQL server installation. On my system, a particular stored procedure takes 2 minutes to execute; on a colleague's system it takes less than 1 second. We have similar databases/data and configurations, but there's obviously something very different. I ran the SP in question t...
I have a following table, Client. create table Client ( ClientID int identity primary key, TaxID varchar(12), SSN varchar(12) ) GO Client can have either TaxID or SSN or both. But either one should exist. Currently, I am enforcing the rule thru following trigger. create trigger trgClient_UniqueTaxIDSSN ...
This is with SQL 2005. I have a script component inside a Data Flow Task. I would like to read from the input columns and write the data to a global user variable. I've set my input columns and added my global user variable as a ReadWriteVariable to the script component properties. Here is my code, I'm just trying to alter the value ...
I've just inherited a SQL Server 2005 database that's using service broker (this is part of a bigger project/solution). Everything about the solution is working fine. I'm trying to grok the service broker SQL, and I see this statement. BEGIN DIALOG CONVERSATION @h FROM SERVICE foo_Init TO SERVICE 'foo_Target' ON CONTRACT fooContract ...
I understand that these are table or index scans that are being performed. I'm a little unclear on what would cause some tables to have a large scan count while others would have a small scan count. In a query I am looking at right now i am getting a scan count of about 150,000 on two tables in my query, while the rest have only a few....
I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP. How do I execute the supplied sql command from within the SP? Is this even possible?- I thought it was possible using EXEC but the following: EXEC @script errors indicating it can't find a stored procedur...
I am completely new to ASP.NET programming, and was asked to work on a small project involving ASP.NET, VB (which I am new to as well) and Microsoft SQL Server 2005. Being used to php/java I was hoping to find some kind of similar API to php.net and the javadoc. It would be very useful to have as I would prefer to work with a text edito...
In SQL Server 2005, I have a Product search that looks like: select ProductID, Name, Email from Product where Name = @Name I've been asked to ignore a couple "special" characters in Product.Name, so that a search for "Potatoes" returns "Po-ta-toes" as well as "Potatoes". My first thought is to just do this: select ProductID, Name, ...
Query - Select * FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.id = tbl2.id AND tbl2.col2 = 'zyx' AND tbl2.col3 = 'abc' WHERE tbl1.col1 = 'pqr' Here I'm doing left outer join and using 'and' along with join. (First I was using joins 'and' part in 'where' but my results were not correct) My q...
Hello, Whether in SSRS 2005 or 2008, someone said that SSRS Report Model will cache the data defined within the report model and you will be required to update the report model regularly to get the new data into the report model views... is that correct? To me, it seems the report mdoel stores the definition/metdata only... could some...
This is in regards to MS SQL Server 2005. I have an SSIS package that validates data between two different data sources. If it finds differences it builds and executes a SQL update script to fix the problem. The SQL Update script runs at the end of the package after all differences are found. I'm wondering if it is necessary or a good ...
I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am i likely to see large differences in performance by rearranging the order...
I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant. I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint. Is it possible to...
I'd like to create a single trace file using SQL Profiler that spans several days so I can run it through DTA to get some advice on indexes. My issue is that I would be doing this on my laptop, which i shutdown and take home everynight, so i can't just pause it when i leave and start it when i get back. Also the server is taken down ev...
I have SQL 2005 server. I am interested in a particular statistics. I can check its last update time manually by right clicking it. How can I check it using TSQL? ...
I have an ASP.NET app using built-in Membership functionality. As such, I have a connection string in my web.config that looks like this: <add name="MembershipSqlServer" connectionString="Data Source=servername;Database=aspnetdb;uid=user;pwd=password;" /> When working on my dev machine, everything is peachy keen. But when I move thi...