Creat a replica of sql table using query
I need a query to create a table which is the exact replica but with different table name and without any data from the source table using a sql query! ...
sql-server
Using Access 2003 ADP/Project files to access SQL Server 2005 database
We use Access Project files to connect with SQL Server. Our SQL Server 2000 has been upgraded to SQL Server 2005 and now my project files are reporting an incompatibility and the error "This recordset is not updatable" when trying to modify data. So: 1) Will upgrading to Access 2007 and then creating project files solve this problem? ...
Optimize SQL Query
Dear Friends i m using the following which Execute against 1'500'000 rows My SP is as follows: CREATE Procedure USP_12(@AuditMasterID as varchar(10),@TABLE as Varchar(50)) as BEGIN Declare @SQLStatement varchar(2000) Declare @PrefixNo varchar(20) Declare @PrefixLen varchar(20) Declare @AfterPrefixLen varchar(20) DECLARE Cur_Prefix CUR...
pessimistic locking in sql server 2008 using NHibernate
SQL Server doesn't support the "SELECT FOR UPDATE" syntax, which is used by NHibernate for pessimistic locking. I read here on StackOverflow descriptions of other alternatives (I liked the "SELECT WITH (...)" because it's quite close). however NHibernate doesn't seem to support this syntax. do you have a workaround for this? I believe ...
Top 10 SQL Server tools for DBAs
I need to submit a requisition for SQL DBA tools before we run out of budget. From the top of my head, I can only think of RED GATE. Can you please suggest tools that you might have used or reviewed? ...
How can I insert binary file data into a binary SQL field using a simple insert statement?
I have a MSSQL 2000 server with a table containing an image field. How do I insert the binary data of a file into that field by specifying the path of the file? CREATE TABLE Files ( FileId int, FileData image ) ...
sql server count of max values without subquery
I want to write a T-SQL query which returns not only the maximum value, but the number of rows having the maximum value. There must be a better way than what I have come up with --wrong way select LibraryBranchId, max(daysCheckedOut), count(daysCheckedOut) from books group by LibraryBranchId LibraryBranchId Expr1 Expr2 -...
Changing default collation of a SQL Server 2008 instance
Hello, When I try to change the default collation of a SQL Server instance using this commandline: setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=********* /SQLCOLLECTION=SQL_Latin1_General_CP1_CI_AS I get the following message: Microsoft (R) SQL Server 2008 Setup 10.00.1600.22 Copyright (c) Microsoft Corpo...
SQL nvarchar Performance
I'm storing localized strings in a single datatable using MS Sql (2008 or whatever). Most of the strings are short and can be represented with varchar(200), while about 10% are much longer require something like varchar(5000). My question is, is there a performance advantage when retrieving shorter strings if I break this into two tables...
Question about SQL Server users on shared Godaddy hosting accounts
I have a client who needs a report created using data retrieved from a SQL Server database on a shared Godaddy hosting account. I do not need full access to this database, so I have asked the hosting account owner (supposedly a web site developer) to create a user for me that has only select privileges on the tables I need access to. H...
Pull Data from Text File Before Appending it to Table in SQL-Server 2005 DTS
I have to work in a DTS environment in 2005 (too complicated to explain) and I have a comma delimited text file that is to be appended to the main table. I'd like to pull the last column in the text file out for the first record and use it as select criteria for a delete command. But how can I do this in the older DTS environment? Her...
Determining Formula Field
Hi all, Guys, I am using SQL Server 2000 and executing the sp_columns stored procedure to get a layout of my table. One of my fields is a formula field and my question is, how can I determine this through sp_columns? sp_columns does not seem to show this information. THanks in advance ...
Help me with this SQL: 'DO THIS for ALL ROWS in TABLE'
[using SQL Server 2005] I have a table full of users, I want to assign every single user in the table (16,000+) to a course by creating a new entry in the assignment table as well as a new entry in the course tracking table so their data can be tracked. The problem is I do no know how to do a loop in SQL because I don't think you can bu...
How to determine SQL Server stored procedure parmeters at run time in .NET?
Is there any way for an application to determine the parameters and paramter types of a stored procedure at run time? ...
Multiple insert problem
I am doing a multiple insert in vba access query as below: Private Sub btnSubmit_Enter() DoCmd.RunSQL ("insert into tblAutonumber (Dummy)values ('DummyValue')") Dim lastAutonumber As Long lastAutonumber = DMax("Autonumber", "tblAutonumber") txtAutoNumber.Value = lastAutonumber DoCmd.RunSQL ("insert into tbltesting " & _ "(Empid, Te...
How do I copy a CLOB from Oracle into SQL Server
The CLOB is XML data that is > 8k (sometimes > 32k). Any suggestions? ...
How can a stored proc retrieve the name of the database it's running in?
The title pretty much says it all, I guess. I have a stored procedure which can be run in a number of databases, and the functioning of the stored procedure needs to vary slightly depending on the database. I've been all over books online and looked in the system tables to see if this might be somewhere in there, but so far no joy. Th...
SQL Server GROUP BY error
Here is the query I am trying to run: SELECT TOP 5 PageComment.ID FROM PageComment WHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage) GROUP BY PageComment.ParentID What I want to get as a result, is one comment per project however, this query gives this error: "[Microsoft][SQL Native Client][SQL Server] Column PageCo...
How to implement database access control on row basis
Hi, I'm currently developing a small business database application for which we plan to go towards multi-user access in the next time. The database mainly contains projects (in a project table) with a couple of joined tables containing additional information. One requirement of our customers regarding multi-user operations will be a f...
SQL Server 2008 - Case / If statements in SELECT Clause.
Hi, I have a Query that's supposed to run like this - If(var = xyz) SELECT col1, col2 ELSE IF(var = zyx) SELECT col2, col3 ELSE SELECT col7,col8 FROM . . . How do I achieve this in T-SQL without writing separate queries for each clause? Currently I'm running it as IF (var = xyz) { Query1 } ELSE IF (var = zyx) { ...