Is there a way to limit access from SQL Server Management Studio to SQL Server to the database server? Or to a given IP address? The case is that a group of users (in a Windows group) are allowed access to the database via an application, but should be denied access using SQL Server Management Studio. I read several articles about Appl...
I heard somewhere that declaring an Index on a date column is bad for performance, but I can't find any reference on the internet. Suggestions? ...
I have a expression : Select (2345789 * 39.456)/100 The output is 925554.5078400. I would like to display 925554.50 i.e. 2 values after decimal. If I use round ( Select round((2345789 * 39.456)/100 ,1) ), the outout is 925554.5000000. But I want exactly 2 digits after the decimal. How to do that? ...
Hello Experts, I am getting an error while connecting sql express server 2005 over the network. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. I have tried fol...
Is it possible to programmatically create a DBLink in SQL server 2005 in C#? Suppose I have database A and B. I want to create a DBlink in A to connect B. I will capture the B database info from user and create the DBLink in database A. Is this possible in C# .Net version 2.0? ...
This is my first attempt at building a sort of wiki (general one) so please support! :) I am compiling a list of methods that deal with transfering and distributing data. Please do add your thoughts to my list. I'll update the document and re-post for everyone's benefit. If I have missed a method out, please let me know and I will edit i...
Summary: I'm trying to write a text string to a column of type varchar(max) using ODBC and SQL Server 2005. It fails if the length of the string is greater than 8000. Help! I have some C++ code that uses ODBC (SQL Native Client) to write a text string to a table. If I change the column from, say, varchar(100) to varchar(max) and try to ...
We have a partitioned view with 12 member tables. The partitioning column is a date, we have one table for each month. Data are continually being inserted into the table of the current month. Older tables are constant. At the beginning of each next month a new table is going to be created and added to the view. At the same time we are g...
I noticed a strange behaviour in my Import Service today when I tried to import multiple data records. When I do it like this, all data records are imported and the auto-incremented value is correct (see screenshot): public void Create(List<Property> properties) { foreach (Property prop in properties) { dbc.Property.InsertOnSu...
In SQL Server, you can do things like this: INSERT INTO some_table (...) OUTPUT INSERTED.* VALUES (...) So that you can insert arbitrary sets of columns/values and get those results back. Is there any way to do this in Oracle? The best I can come up with is this: INSERT INTO some_table (...) VALUES (...) RETURNING ROWID INTO :out_r...
I inherited a system that stores default values for some fields in some tables in the database. These default values are used in the application to prepopulate control values. So, essentially, every field in every table in the database can potentially have a default value. The previous developer decided to store these values in a single ...
I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my field allow NULLS as I've decided null to mean "all values". Because of nulls, the following statement in my stored procedure can't work: IF EXISTS(SELECT * FROM MY_TABLE WHERE MY_FIELD1 = @IN_MY_FIELD1 AND MY_FIELD2 = @IN...
Hi all, I'm currently benchmarking partitioned tables with SQL Server 2005 to compare them to using two tables (a "live" table and an "archive" table) for a processing queue. The partitioning's being performed on a bit column 'archive', so that when the archive bit is set, the row automagically moves. Initial testing seems to show that...
Following podcast #59, I've been considering moving some images from an MS Access DB into MS SQL Server (it's the only piece I haven't migrate over yet). Right now they are stored as OLE Objects in the MS Access DB. However, I haven't got a clue what data type they should be stored as on the SQL Server side, or how to get them inserted ...
Hi all, I have a table (table variable in-fact) that holds several thousand (50k approx) rows of the form: group (int) isok (bit) x y 20 0 1 1 20 1 2 1 20 1 3 1 20 0 1 2 20 0 2 1 21 1 ...
How to put the first item on the DropDownList in blank ? In VB is something like, DropDownList.index[0] = ""; I did this: string StrConn = ConfigurationManager.ConnectionStrings["connSql"].ConnectionString; SqlConnection conn = new SqlConnection(StrConn); conn.Open(); SqlDataReader dr; string sql; sql = @"Select N...
As part of a disaster recovery test of our TFS 2008 instance I hoped to restore our backups of the database (currently on SQL Server 2005) to SQL Server 2008. I noticed a warning on MSDN that this could make a restore impossible. Has this been done in the wild? What are the risks? ...
I'm trying to take a VARCHAR(MAX) with data in it as follows: "00001001010001010111010101..." etc. Then encode it as hexadecimal for more efficient return to the client. Is it possible to do this? Either directly or converting the string into a real binary column first before calling master.dbo.fn_varbintohexstr? As an example, given ...
If I can ping my DB server, is my SQL Server connection guaranteed to work? I am using a default connection string in my code. My program runs fine locally but overseas sites are having issues and I am wondering if SQL might be using a TCP or UDP port that is still blocked. Here is the connection string "Data Source=xxxx.xxxx.com; Initi...
We have a situation where we need to store form data in our sql server but each new job we setup will have different fields with different field names and lengths. An example Job 1: Field 1: first_name - varchar(20) Field 2: last_name - varchar(30) Job 2: Field 1: first_name - varchar(15) Field 2: middle_initial - varch...