I am trying to connect my laptopname,which has various SQL server 2005 databases on it by using Query Analyzer & Mgt. Studio but it gives me an error as follws: An error has occurred while establishing a connection to the server. When "connecting to SQL Server 2005, this failure may b...more >>

Hi All! I have a SP like this. CREATE PROCEDURE [dbo].[GetNumWinners AS BEGIN DECLARE @WinNum numeric(10) DECLARE @Total numeric(20) DECLARE @Win numeric(20) DECLARE @UserID nvarchar DECLARE @House numeric(20) DECLARE @Guest numeric(20) SET @WinNum = (SELECT COUNT(*) FROM TblUserID WHE...more >>

Is there any way in SSMS to have the query editor automatically convert all keywords to upper case? For example I type in this: create procedure MyProcedure .... and the query editor automatically converts it to this: CREATE PROCEDURE MyProcedure I'm thinking that - if it can turn keywo...more >>

I am using Small Business Server 2003. I think it come with SQL server 2000. The transaction log is large and getting larger, moreover, a new project will be add to the existing database in the near future. I am afraid the hard disk is not large enough to hold the database + log. So I think ad...more >>

I am very new to programing and serves ect and just learning. have just set up a business with a freind but we both work fro home. I have built an access database that we enter in ourcustomer details and any orders they place with us. As we both work from home we would like a way of sharin...more >>

I was working originally with what turned out to be a very unreliable Offshore IT firm in India. I had to finally sever our working relationship with them. They were about 70% complete even though they demanded 100% payment upfront. WON'T EVER DO THAT AGAIN! My problem is they site me all the ...more >>

Hello We will migrate from SQL Server 2000 to 2005 , side by side installation so will keep both instances running at first , Is there an easy way of transferring all logins from 2000 to 2005 AND associate them with database users in the database that will be manually attached to 2005 ? ...more >>

I am looking for some software that will clean up my SQL statements. It doesn't need to change any of the syntax - just make it look pretty. I recently converted dozens of ReportSmith reports to Crystal Reports. When I pasted the SQL statements into the Crystal Reports command, they were no...more >>

I am having some trouble. I am doing some db search tests before moving from access to mysql/sql server. Before I get into the Full Text Search battle..... If I try to do the following and there are multiple stores who have the same book, all values are returned, which make duplicate entries...more >>

I need to create a query for SQL 2005 that will return all the rows in a table, but only allow a maximum of x # of recurring entries of a single varchar field. So, if 'dog' and 'cat' were values of a varchar field in the table, and my limit was 3 recurring values, I would see a maximum of 3 ro...more >>

I am currently working on boosting the performance of a database used to store music information. As it turns out the insertions of tuples into a table storing distances between songs seems to be the worst bottleneck to overcome.. For that reason I choose to use stored procedures for insertion...more >>

Hello, Is there a way to migrate my Reportserver project for SQL 2000 Report services developed with VS2003 so that I can use and modify them in VS 2005 or have I realy to install VS 2003 on my new machine? Greets Jean Paul ...more >>

Trying to do this in a Stored Proceduce not sure if it's possible, I'm new to Stored Procedures. Could do it in .net but would like to avoid it if possible. In it's simplest form select Number, price from sometable Returns 1, 100 1, 150 1, 100 2, 100 2, 200 I'd like to create a...more >>

Take the following query: SELECT * FROM Table1 JOIN Table2 ON Table1.SomeID = Table2.SomeOtherID WHERE Table1.SomeData = 123 Table1 contains 45million rows, Table2 has just over 2 million. The query takes several minutes to run and correctly returns zero records. It takes so long becaus...more >>

Is there a way to suppress column headers in the results grid? I need to create a list of records from a specific table in multiple databases, but I don't want the column header to appear between the data from each table. For example: use Coronado Select Property FROM tblProperty use Co...more >>

I am using SQLServer 2005 and classic ASP. When I do a: sql = "select * from my_table" cmd.CommandText = sql cmd.CommandType = 1 Set cmd.ActiveConnection = conn rs.Open cmd, , 1, 3 .... rs("a") = "abcd" .... rs.update I get: Microsoft OLE DB Provider for ODBC Drivers error '80004005' ...more >>

I'm trying to find a way to determine which database a table belongs to in MSSQL 2000. I looked at sysobjects, sysdatabases and the INFORMATION_SCHEMA views, but to no avail. Does anyone know how to do that? Thank you, Alain Quesnel ...more >>

hi, I have written a select cursor which just seems to run and run. I was wondering if someone could point out where I'm going wrong. DECLARE @PROP_REF as varchar(15) DECLARE @ROTA_INTERVAL as varchar(1) DECLARE @ROTA_DAY as int DECLARE @ROTA_WEEK as int DECLARE @WEEK1_DATE as smalldate...more >>

I am tryng to write a stored procedure with a parameter that can except a list of values to build an In clause. I have: CREATE PROCEDURE usp_retrievenames @tnames varchar(100) AS SELECT * FROM tbltest WHERE tname IN(@tnames) I create the procedure fine but when I try to execute for more...more >>

Hello, SQL Server 2005 STD edition SP1 Trying to call the following from a stored Proc. Yes XP_CmdShell is enabled Declare @Server_Instance sysname ,@cmd varchar(1000) set @Server_Instance = 'Server\Instance' set @cmd = 'SQLCMD -S<Host server> -i\\<UNC path>\restore.sql -o\\<UNC ...more >>

THIS IS THE SAMPLE DATE I want to see supplierID, sum of amount shipped and sum of amount not shipped and group them by suppier, total no of orders accpeted by the vendor, total no of orders declined CREATE TABLE #OrdersShipped (OrderID int, [Shipped Date] datetime) INSERT #OrdersShipped ...more >>

I am trying to suppress Null when a column with type datetime with the following code: declare @c as datetime set @c = null select isnull(@c, '') The result is 1900-01-01 00:00:00.000, not blank. Please advise. Thanks. Peter...more >>

Having some trouble with a SQL statement and I would appreicate some guidance. Basicly this is what I am trying to do: Insert into a table records that exist in Active_PTPs but not Active_PTPs_Temp This is what I have for a statement but I am getting this error: Incorrect syntax near the...more >>

Hi! I'm looking for a way to retrieve Licence Info from SQL 2005 Server with VBScript From a remote location I connect to the server and grab those info with my administrative rights, but I'm not accessing the DB with a SA account... With SQL 2000 Server I retrieved those info from regi...more >>

Help please! I've implemented a calender table as per: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html and have a question concerning its use. I need to show every date as a set in between the start date INCLUDING days preceding the sta...more >>

Is there a reason why SQL would chose not to use an existing index on a table, when the data is being request in a UNION statement? Here is the type of statement I am using: Select * from Archive..report where cn = '12345' UNION select * from production..report where cn = '12345' There is ...more >>

I have a VB 6 application using access that I'm converting to VB.net. VB.net has a conversion tool that will allow me to use the existing access db in the access 2000 file format BUT.... I know I need to go to SQL server. I'm completely new to sql server and hope there is a conversion tool to ...more >>

I need some help with this. When I run this code on my test server it works fine. When I run it directly on the production server it runs fine. When it is scheduled on the test server it runs fine. But when I schedule it on the production server (SQL Server 2000 sp4) I obtain the error: Lin...more >>

I am looking for a SQL script that will list the rights assigned to a user in a database. for example, I want to know if a particular has rights to select, execute, update, etc objects in the database. Thanks Emma...more >>

I am trying to create a CLR procedure with the following signatures tc_newStuff @StuffID int, @DT datetime = null Sadly, changing the signature is not an option. I tried creating the procedure public static void NewStuff(int id,DateTime? dt) Howerver, SQL Server tells me that types of @DT par...more >>

I have user table named TBL_USER which has userid and username fields. I have another table named TBL_PRODUCT which need user info in it Which way you offer me to follow . 1. Having Userid Field in TBL_Product or 2. Having Username field in TBL_PRODUCT to have relationship between 2 ...more >>

Hello, I have created this table CREATE TABLE [dbo].[mytable]( [id] [int] IDENTITY(1,1) NOT NULL, [value] [int] NULL, CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED ( [ad_id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END My questions are: a) ...more >>

Hi, All system procedures like sp_help, etc. have SET NOCOUNT ON as the first statement but none of the SP end with SET NOCOUNT OFF. Normally in my SP, I used to add both these statements. Can you explain why SET NOCOUNT OFF is not used? what are the issue with using NOCOUNT OFF? -- ...more >>

Hi, I use (SELECT *) in a view. But it doesn't bring the new columns added to table. When I use SELECT * it means every column! How can I get this behavior? Thanks in advance, Leila ...more >>

Hi, What is better to use when making a relation inside a query. I.e. you are selecting data using three tables. You can do this: where... table1.id = table2.id and table2.name = table3.name and table3.name = 'x' Or you can use an inner join. Whats best? ... if there is a difference...more >>

Hi, I use SELECT * in a view. But it doesn't bring the new columns added to table. When I use SELECT * it means every column! How can I get this behavior? Thanks in advance, Leila ...more >>

Hi, I want to be able to format a string so that it puts it as a decimal figure. e.g. If the parameter = 6 then I want to be able to format this to 6.00. If it's 6.25 then I want it to stay as 6.25. ANy help on this would be appreciated. Thanks Damon ...more >>

Hi Does anyone know how I can get the PRINT statement to output immediately. By default it seems to wait for a buffer to get full. I would like it to flush the buffer immediately Thanks Craig ...more >>

Hello I would like to do this: select seqno, (CASE status WHEN 0 THEN (declare @myString varchar(100) set @myString='' select @myString=@myString+coalesce(w.POSITION,'')+'/ ' FROM table1 w) END) as test from myTable but I get an error "Incorrect syn...more >>

Hi, We are uploading data from one table to another table. Table size is 430 million rows. We are using INSERT INTO .. for bulkloading. The problem we are facing is transaction log becomes too huge and unmanageable once we complete this operation. I want to know is there a way we could turn...more >>

Hi, In a sp I have multiple selects. How can I combine the result sets to one? Thanks! Arjen ...more >>

I have three tables: Process_Log, Process_A AND Process_B . The log table is used to create logs for data inserted in the other two tbls. The two tbls have the Process_Log ID as an FK and for each of the two a unique process file ID exists in the Log . I am using these update statements to upd...more >>

Hi, I have a server running 2005. I have a SP that dumps data into a table. When it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but 18.5 is just space. When I shrink the DB I goes back down to 500mb? If I run the SP again, I get the same problem Thanks for the help! ...more >>

I'm getting the warning message: Warning: Null value is eliminated by an aggregate or other SET operation. When running the SP listed below. Count is returning a valid number greater than zero so I don't understand where the null value in the message is coming from. Is this anything I ...more >>

I am connecting to a remote SQL Server database from Visual Studio .NET 2005. I have an sql script located on my machine containing the sql code I want to use to create several tables, and want to execute it. How can I do this from Visual Studio .NET 2005? I do not like designing the database ...more >>

Hello! I remember reading a while ago that index on INT column could be used for bitwise operations efficiently. For example, Index on dwRights_Public could be useful: select dwPlayerIx from FTE_TABLE_SIT where (dwRights_Public&4) > 0 I created an index (which is highly selective) but Qu...more >>

Can you edit the result set from Studio Manager Sql Server 2005 like you could in EnterpriseManager sql server 2000? I loved being able to go directly into a table in sql server 2000 enterprise manager, return a result set and just edit inline right there. In sql server 2005, I always see a n...more >>

In a stored procedure if a variable supplied is '' eg @variable varchar(25) is supplied as '' how can i make that variable NULL for a query ? ...more >>

I have a table like OrderID Orderdate City SalepersonID 11 1/2/2005 NYC 205 12 2/5/2006 CHG 206 13 2/5/2003 NYC 207 14 3/5/2006 CHG 205 15 4/5/2006 NYC 206 There are only 2 cities I want to write a query to find out SalespersonID, count(orders by salespersonid), overall % of ord...more >>

Ladies / Gentlemen I am looking for software that will read in Old SQL / TSQL and will reformat it and make performance improvements to the generated SQL Output. Mark Moss ...more >>