We have a change control environment where the developers give scripts to change control people to run. we have dev,qa, & production environments. I want to conditionalize a couple segments to do some different things depending on what database the change control person is running my script. If @dbname='dev' then begin --do some dev ...
I can't figure out why this query would be so slow with variables versus without them. I read some where that I need to enable "Dynamic Parameters" but I cannot find where to do this. DECLARE @BeginDate AS DATETIME ,@EndDate AS DATETIME SELECT @BeginDate = '2010-05-20' ,@EndDate = '2010-05-25' -- Fix date range to...
I want to create backup SQL tables using variable names. something along the lines of DECLARE @SQLTable Varchar(20) SET @SQLTable = 'SomeTableName' + ' ' + '20100526' SELECT * INTO quotename(@SQLTable) FROM SomeTableName but i'm getting Incorrect syntax near '@SQLTable'. It's just part of a small script for maintence so i ...
Every example of using PIVOT in MSSQL shows people using this to aggregate data. I'm trying to exploit this to just simply transpose rows to columns For instance, consider the follwoing data SELECT 11826 ID,cast('64 ' as varchar(1000)) as answer,75098 QuestionID,2785 CollectionID into #temp insert into #temp SELECT 11827 ID,cast('S...
I am using RijndaelManaged 128 bit encryption to encrypt passwords in my ASP.NET application. What should the password column size be in the database (SQL Server 2008). ...
After 5 versions of Visual Studio I think I should be able to right mouse click on a Stored Procedure node of a database connection in the Server Explorer window and be able to search (at least) SQL Server code for the occurrence of a string. I don't want to have to rely on running an Stored Procedure to search SYSCOMMENTS. I want a rea...
In the JDBC code, I have the following that is working with SQL Server: CallableStatement stmt = connection.prepareCall("{ call getName() }"); ResultSet rs = stmt.executeQuery(); if(rs != null) { while(rs.next()) { //do something with rs.getString("name") } } Multiple rows are returned for the above situation. I under...
I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you a...
On SQL Server, the sys.objects table includes "Type" and "Type_Desc" attributes. For example, for one of my DBs: SELECT DISTINCT [Type], Type_Desc FROM Sys.Objects ORDER BY [Type] Returns: C CHECK_CONSTRAINT D DEFAULT_CONSTRAINT F FOREIGN_KEY_CONSTRAINT FN SQL_SCALAR_FUNCTION FS CLR_SCALAR_FUNCT...
I have a table tblSequence with 3 cols in MS SQL: ID, IP, [Timestamp] Content could look like this: ID IP [Timestamp] -------------------------------------------------- 4347 62.107.95.103 2010-05-24 09:27:50.470 4346 62.107.95.103 2010-05-24 09:27:45.547 4345 62.107.95.103 2010-05-24 09:...
I want to execute select statement within CTE based on a codition. something like below ;with CTE_AorB ( if(condition) select * from table_A else select * from table_B ), CTE_C as ( select * from CTE_AorB // processing is removed ) But i get error on this. Is it possible to have if else within CTEs? If not is there a w...
What is the SQL command to forcibly close all other connections to the a database. This is for SQL Server 2008 ...
I am trying to use .NET TimeZoneInfo class from a SQL CLR function, as described in stackoverflow questions here and here. While researching possible solutions, I found this post that suggests using SafeHandle to safely handle the resources accessed by TimeZoneInfo. How do I use SafeHandle in this situation? ...
I really like the new SQL Server Database projects in Visual Studio 2010. I also like using the "Generate DROP statements for objects that are in the target database but that are not in the database project" option in the deployment properties. However, I do not want to manage Stored Procedures and Functions using this interface; I hav...
I have a database that I need to connect to and select from. I have an SQL Login, let's call it myusername. When I use the following, no SELECT permission shows up: SELECT * FROM fn_my_permissions ('dbo.mytable', 'OBJECT') GO Several times I tried things like: USE mydatabase GO GRANT SELECT TO myusername GO GRANT SELECT ON DATABASE...
I have two tables notifications and mailmessages. Notifications table - NotifyTime - NotifyNumber - AccountNumber MailMessages table - id - messageSubject - MessageNumber - AccountNumber My goal is to create a single sql query to retrieve distinct rows from mailmessages WHERE the accountnumber is a specific number AND the ...
Hi I heard about doing staging tables and I am wondering how you actually do it. I want to do a SqlBulkCopy into a staging table. Then then update the real table. I heard in SQL Server 2008 that you can use something called merge but I am using SQL Server 2005 so I heard I have to use a update inner join? I am not sure how that would...
I'm writing a .NET web app that will read and write information to a SQL Server 2008 database. Some of this information will be highly confidential in nature so I want to encrypt certain data elements. I don't want to use TDE or any full-database encryption for performance reasons. My main concern is protecting this sensitive data as a...
Hi Friends, I have been trying to analyze performance issue with SQL Server 2005. We have 30 jobs, one for each databases (30 databases, one per each client). The jobs run at early morning at an interval of 5 minutes. When I run the job individually for testing, for most of the databases it finishes in 7 to 9 minutes. But when these j...
Hi Friends, When we use sp_send_dbmail to send email with attachment, the attachment gets copied into a folder inside C:\Windows\Temp. As we have many emails to be sent every day, the temp folder grows rapidly. This is the case with SQL Server 2005. We noticed that, with SQL Server 2008, we dont see these file under temp folder. Is th...