Categoria: SQL Server

How to enable remote Connection sql server 2008

admin0 Comments

Error message: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)” How to solve this issue? There are a couple of things that might be going on here… (All of the following configurations are made on the computer running your SQL Server 2008 instance) Allow remote connections to this server The first thing you want …

Continue Reading

Problem with Identity Specification in SQL…

admin0 Comments

Error message when changing the identity column to “Yes” and save the changes: “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.” It’s not a bug. It’s a safety measure added to SQL 2008’s management studio to indicate that the change that you’ve requested to the table requires that the table be dropped and recreated. It’s there so that people don’t ‘accidentally’ make changes …

Continue Reading

SQL Server Dumper

admin0 Comments

SQL Server Dumper enables you to dump selected SQL Server database tables into SQL INSERT statements, that are saved as local .sql files and contain all the data required to create a duplicate table, or to be used for backup purposes. You can choose to create an individual .sql file for each table, or combine all selected tables into a single file. This program implements the following special features: Foreign Keys order tables in text file in order to insert data without colisions/errors Primary Key IDENTITY guarantees the value of the IDENTITY field Reference to itself eliminates the constraint and …

Continue Reading

SQL SERVER – Insert Data From One Table to Another Table

admin0 Comments

INSERT INTO SELECT – SELECT INTO TABLE Following three questions are many time asked on this blog. How to insert data from one table to another table efficiently? How to insert data from one table using where condition to anther table? How can I stop using cursor to move data from one table to another table? There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as …

Continue Reading

SQL to Select a random row from a database table

admin0 Comments

Select a random row with MySQL: SELECT column FROM table ORDER BY RAND() LIMIT 1 Select a random row with PostgreSQL: SELECT column FROM table ORDER BY RANDOM() LIMIT 1 Select a random row with Microsoft SQL Server: SELECT TOP 1 column FROM table ORDER BY NEWID() Select a random row with IBM DB2 SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY Select a random record with Oracle: SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1 source link: http://www.petefreitag.com/item/466.cfm

Continue Reading

Insert Multiple Rows in SQL Server

admin0 Comments

In SQL Server 2005, in order to insert 3 rows to a table, you had to run 3 INSERT statements: insert into Customers (Name, City, Phone) values (‘Customer #1’, ‘Jerusalem’, ‘2343245’) insert into Customers (Name, City, Phone) values (‘Customer #2’, ‘Tel Aviv’, ‘0987345’) insert into Customers (Name, City, Phone) values (‘Customer #3’, ‘Haifa’, ‘275466’) In SQL Server 2008, you can insert multiple rows in a single insert statement that takes a number of value arrays: insert into Customers (Name, City, Phone) values (‘Customer #1’, ‘Jerusalem’, ‘2343245’), (‘Customer #2’, ‘Tel Aviv’, ‘0987345’), (‘Customer #3’, ‘Haifa’, ‘275466’) Source link

Continue Reading