I am well aware that MS SQL Server does not allow variables to be used in the OPENQUERY statement and I'm aware of the workarounds. What I'd like to know is, does anyone know WHY variables or concatenated strings are not allowed? The hoops this causes people to jump through is simply astounding and it just isn't in line with other func...
Currently we are using check constraints for business rules implementation, but I want to know should we implement business rules in sql or Business logic(c# code) layer. I have searched on the net and finds that check constraints are good to use. please let me know if somebody knows more int detailed abt it. One more thing is that data ...
Hi all, This is a problem I come across every so often; I always end up writing some horrible bodge, but I'm sure there must be a correct way to deal with it (after all, it's surely not uncommon to want to work with UK pound symbols). The problem: the user enters a UK pound symbol (£) into a textfield in the CMS. They click save, and t...
I want to insert value into SQL Server but there is problem is I pass both value in parameter then it's not insert otherewise if i selecting one value then it's insert my database name is sample and table is item this is perfect insert statement or not ? try { int val = stmt.executeUpdate("INSERT item (patientid,itemid) VALUES(nPat...
This is the query I'm using: DELETE TB1.*, TB2.* FROM TB1 INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID WHERE (TB1.PersonID)='2' It's working fine in MS Access but getting error (Incorrect syntax near ','.) in SQL Server Express 2005. How to solve it? Please help. ...
I am working with SQL Server 2005 and I have trigger on a table that will copy an deletions into another table. I cannot remove this trigger completely. My problem is that we have now developed an archiving strategy for this table. I need a way of "pausing" a trigger when the stored proc that does the archiving runs. ...
We have a team of sales persons who travel often carrying a laptop loaded with Sales data in a SQL Server Express database. What are the options available to encrypt a SQL Server Express database? ...
Hi all, I am using stored procedure with RAISERROR. The error raised by the SP is not caught by the try catch statement in c#.Net. I'm using SqlDataSource for SQL connection and SqlDataSource_Updating event to update the data. SQL Code: DECLARE @count int @count=0 if(@count = 0) begin RAISERROR('Updation failed. record alread...
I have a SQL Server 2008 instance with several databases and I'm currently writing a C# application to access those databases. In this app, the end user can select a database they want to connect to. I already have a list of all databases on the server, how can I limit that list to those databases the user can log in to? Or, how can I q...
I have the following tables: Financial: PK_FinancialID FK_SchoolID School: PK_SchoolID Class: PK_ClassID FK_SchoolID ClassName Both Class and Financial have Foreign Key relationships to School. I want to make a query that would show all classes that are related to Financial rows that meet certain criteria. Initially I think...
We have many clients using our application, and each of them has their own, identically structured database on our MS SQL Server. We also have a central database for information that is invariant and therefore shared among all the clients, such as the USPS ZIP Code database. Each client database has a view to these tables, like so: cr...
If I run a stored proc using the SqlCommand and the SqlCommand times out does the StoredProc continue to execute or does it get force to quit when the SqlCommand disconnects? ...
Developing in VS2008 on win2008 machine. Connecting to SQL Server 2000 Db on win2003 server protected void Page_Load(object sender, EventArgs e) { string connectionString = "Server=myserver;Initial Catalog=mydatabase;User Id=myuser;Password=mypassword;"; SqlConnection conn = new SqlConnection(connectionString); ...
I have exported an ESRI shapefile to SQL Server 2008 using Manifold, which gives me a column "Shape" of type Geometry. The Manifold .prj file looks like this GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]], PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]] so I assume the SRID is 4326? How...
I have an .mdf file with Visual Studio 2008 with one table in it that has numerous columns of various types. How can I generate the SQL statement that produces this table so that I can alter it and create another similar table? Is there a way to do this without SQL Server? ...
When calling several stored procedures for one stored procedure is this the right or best way to go about it on SQL Server 2008 ? CREATE PROCEDURE [dbo].[DoStuff] AS BEGIN SET NOCOUNT ON; declare @result int BEGIN TRANSACTION BEGIN EXECUTE @result = dbo.UpdateTHIS @ID = 1 IF @result != 0 ROLLBACK ...
Is it common practice in a star schema to prefix tables as a dimension or fact table? Is is also common practice to have column names prefixed with the table name? In my normal OLTP databases, I don't do this, but I'm seeing examples of this type of naming in star schemas. Does it make sense to have a different set of naming standards ...
Looking for a "Source Control" plugin to integrate SSMS with Subversion/SVN. Anyone know of one? ...
We use a fairly straightforward naming standard for indexes that I can validate programmatically, but I am struggling with naming indexes with INCLUDE columns. If I do it based on a simple formula, it's too easy to build names that are too long. Then my programmatic validation fails, and I end up with truncated names. For those of you u...
My table (SQL Server 2008) has 1 million+ records, when I try to order records by datetime, it takes 1 second, but when I order by ID (int), it only takes about 0.1 second. Is there any way to improve the efficiency? (I already added the datetime column to the index) ...