Hello everyone! Little background: I'm working in a large company with a lot of branches. We have several applications with separated databases sometimes on different servers. But every database contains a table with a list of branches and their relationships. I want to automatically synchronize these tables when one of them changed. M...
Does a SQL Server Computed Column require "persistence" in order for it to be indexed efficiently? It's a Name and a LoweredName (computed) column combination. So I don't believe it deals with the precision stuff mentioned in a MSDN article. ...
How can you create a new job in SQL Server 2005 with the use of code? And not in SQL Server Management Studio. ...
SQL Server to MySql conversion, i want convert the SQL Server query dump to mysql database. I have SQL Server query dump. Please give me a good conversion tool free of cost. I tried with phpmyadmin import feature, its not working. ...
I've been investigating making performance improvements on a series of procedures, and recently a colleague mentioned that he had achieved significant performance improvements when utilising an INNER JOIN in place of EXISTS. As part of the investigation as to why this might be I thought I would ask the question here. So: Can an INNER...
This query (part of a sproc) will be executed quite a lot: SELECT TOP (@numRecords) BlogPost.postId,BlogPost.creationDate, BlogPost.header,BlogPost.markedupContentAbstract FROM dbo.BlogPost ORDER BY BlogPost.creationDate DESC Should I put an index on the 'creationDate' field in the BlogPost table? Should I have a view wh...
I am having an issue with SQL Server 2005's BCP utility. I am running SQL Server Express 2005 on Windows Vista. I am trying to create a non-XML format file for some tables in my database. I run the following code: bcp 'jProd_000-1'.users format nul -T -f users.fmt I get the following error: An Error occurred while processing the...
How would I create an IDENTITY column in SQLServer with text in the column? Example: ABCD-987065 ABCD-987066 ABCD-987067 ...
i am trying to grant execute privs in stored proc in more than one database. The problem is this stored proc might not be in some of the databases. So how can i write a script which checks if stored proc exists in database and if does then provide execute privs for user? ...
Given the SQL Statement: SELECT * FROM MY_TABLE WHERE SomeNumberField in (0,99999) If I can guarantee that the majority of rows in MY_TABLE have SomeNumberField set to 99999, and can project that this will remain the case indefinately, is it better to write the above query like this: SELECT * FROM MY_TABLE WHERE SomeNumberField in ...
If I have a set of SQL (ie. a script containing arbitrary SQL statements), is there any way of finding out what the minimum permissions are required to execute the SQL? (I'm thinking of something similar to the "Permissions required by the application" area in Visual Studio when viewing the Security tab on the project properties page of...
Right now i have code that initiates transactions on SQL Server using the intended method: ExecuteNonQuery(connection, "BEGIN TRANSACTION"); try { DoABunchOnStuff(connection); DoSomeMoreStuff(connection); JustAFewMoreThings(connection); ExecuteNonQuery(connection, "COMMIT TRANSACTION"); } catch (Exception) { ExecuteNo...
I have a table with one DateTime column. I want to find the rows with lowest time which their times are more than a variable myTime. How can I do that? What kind of index will increase the performance? ...
Hello I want to design a schema of my SQL database in Visio But the default database is Microsoft Access, and some datatypes do not exist there (such as tinyint). How can i change the database type to SQL ? I'm using visio 2007 Thanks! ...
hi i have application which use a local sqlserver file to store data.i made a install package for this application ,but i want to make the the database file (*.mdf) hidden from user sight. in fact i don't want him/her to be able to see tables or other details of database , while he/she can use this by my windows application. is there any...
Hi, I am maintaining some code that has a trigger on a table to increment a column. That column is then used by a 3rd party application A. Lets say that the table is called test with two columns num1 and num2. The trigger runs on each insert of num1 in test. Following is the trigger: USE [db1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTI...
I've got a really large table (10+ million rows) that is starting to show signs of performance degradation for queries. Since this table will probably double or triple in size relatively soon I'm looking into partitioning the table to squeeze out some query performance. The table looks something like this: CREATE TABLE [my_data] ( ...
We recently put a new production database into use. The schema of this database is optimized for OLTP. We're also getting ready to implement a reporting server to be used for reporting purposes. I'm not convinced we should just blindly use the same schema for our reporting database as we do for our production database, and replicate data...
I cannot store the date data type variables using stored procedure. My code is: ALTER PROCEDURE [dbo].[Access1Register] -- Add the parameters for the stored procedure here @MobileNumber int, @CitizenName varchar(50), @Dob char(8), @VerificationCode int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- inter...
I need to copy data from one database to another using a VB.NET program. The target database is SQL Server the source database is some proprietary ODBC compliant database. I need to loop through a list of table to copy. Read the data from the source database table for a given modified date. Delete the corresponding date from the target ...