what mean the tables are truncated...more >>

my mdf and ldf are in 1 drive only. i am facing query timeout problem for this server which is mainly used for employees if i separate ldf into another drive will there will be any perf benefit can i add another log file how to clean up my log file...more >>

Hi, If I have two tables A and B and I am trying to insert rows, update rows and delete rows in table B based on table A is it possible to do it one SQL statement with a full outer Join?. I have written the SQL for getting the data using a full outer join but want to achieve Inserting , up...more >>

Can someone explain why this sql statement does NOT work. DECLARE @test varchar(30) SELECT @test = 'TEST' SELECT @test, count(*) from @test Assuming that table TEST is in the database. I would expect to get something like this from the script, should I: ------------------------------ ----...more >>

I've never been great at DB Design, I try though. Here is my situation. We manufacture hardware devices(startup Co.). I write the firmware for the devices and generally all other software issues. As we are growing, we were having more and more customers wanting "customized" versions of the...more >>

Hello say you have data in one record that is semicolon delimited example: 231232;test;1321;dsfwe;wefdw;www;0 i want to be able to split this data in seprate fields using a stored procedure: In vb i would use the spliter = Split("231232;test;1321;dsfwe;wefdw;www;0", ";") transi...more >>

I'm sure this is well-known to all you experts out there. I found the following on the Net: A frequent question posted on here is something like "How do I list all of my customers along with their most recent order". You can achieve this using a correlated subquery. A correlated subquery...more >>

I have the stored proc below that is not returning the new identity field. Can someone help? Thanks. David CREATE PROCEDURE [ms_insRepairOrderTasksDup] (@TaskID [int], @TaskIDNew [int] OUTPUT) AS INSERT INTO [dbo].[RepairOrderTasks] SELECT [IDfromABS], [EmployeeCode], [Repa...more >>

Hi everybody I was looking for some tool that exports the structure of the tables of my SQL Server database. I've found some, but noone that exports also the description of the fields in the tables. Anyone knows about a tool that does it? giovanni ...more >>

What is the sqlserver equivalent of MS Access INT datatype?...more >>

Okay, I'm having an issue with an outer join that is just confounding me! Can somebody help explain. I started out with a large query that was not returning all of the data that I wanted, so i paired it down to this simple outer join: SELECT E.EMP_RNG_CDE FROM DATA_TABLE B LEFT OUTER JOIN ...more >>

I have a query that works, until I try to also grab one of the fields that is set to a 'text' datatype. I can grab any field, and it works fine, but once I try to grab the data from the 'text' field, I get the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and...more >>

Hello all, I am importing from a text file a field that needs to end up in a sql table as decimal type. The field has numbers and then a trailing sign (+ or -) to indicate pos or neg.. How can I conver that filed to numeric with the sign in front of the number? e.g.: 000032897+ should be...more >>

I have 'Table1' with two fields: - RecID - POID I want to allow records to be entered in Table1 where the same RecID and POID can exist across multiple records in Table1, but I don't want to allow a POID to exist in Table1 twice with different RecIDs. This is OK RecID | POID 1 ...more >>

I have built 20+ stored procedures using an ADP front end. The stored procedure are constructed in a similar fashion, the call an outside procedure with certain parameteres including a date parameter. an example of a stored procedure: ALTER PROCEDURE sp22_IRRSinceInceptionByInvestorStrategy...more >>

Hi, I have the following update statement but using a linked server Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a inner join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and a.ChargSubType = b.ChargSubType I am not sure why it doesn't work. Can I no...more >>

My math skills are not up to it so I have to ask for it! I need a procedure which accepts one lat, lon, and Distance(in feet? miles?) and returns the lat's and lon's which describe a square around the given lat/lon with 2 * Distance on a side. This will be used to search a database for o...more >>

Jeez, Just can't remember the command to display the database percent recovery complete via query analyzer? TIA...more >>

Hello, I'm facing a Multirow-subquery issue: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I can't figure out what I'm doing wrong. Anybody any c...more >>

Ok , I'm new in stored procedures, I created one, so I want to test it without going and coding a program in C# or VB.NET, is it any way to run it where it will ask my parameters and get the result using the SQL Enterprise Manager ? Thanks ...more >>

I have two tables as: table1 tablename tablecount a 1 b 2 c 3 d 4 .. .. table2 tablename tablecount a 1 b 2 c 10 d 11 .. .. .. How d...more >>

Hi, I have a query which simply returns a count of a particular field by week number. My query as it works fine however for weeks where there are no data it obviously doesn't return a row. My problem is that is I wish for the statement to return a zero for those weeks with no data - I'm jus...more >>

I have some difficulties with import xls file to sql server. The problem is I try to import excel-html file but not ordinary excel-binary file. I use to use query like that : select 1,2,3,4,5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; DATABASE=c:\zeszyt1.xls;HDR=YES', 'Select *...more >>

Some of our sales data is a week/year format like this... SELECT DATEPART(wk, GETDATE()) AS 'Week', DATEPART(yy, GETDATE()) AS 'Year' I would lie to convert it to mm/dd/yy Start - mm/dd/yy End date. Thanks! ...more >>

Hi All, I just wanted to get some clarification on the ROWCOUNT statement in T-SQL. I'm presently using it to limit the number of rows returned in SELECT based SPs. However I'm just curious as the effect of this keyword on the global scope of SQL operations. Does setting ROWCOUNT to 25 i...more >>

In a table or 'shifts' for employees i'm trying to test a proposed new shift for conflicts, the employee's shifts must have 12 hours between them, This makes sense to me but, is it going to be efficiant considering the table could have ten's of thousands of shifts. IF EXISTS ( SELECT R....more >>

I am getting very confused of why COALESCE doesnt report NULL as NULL. Can somebody please explain the behavoiour described below??? Create these tables: create table dbo.Warrants ( WarrantId bigint not null constraint WarrantsPKCO primary key nonclustered (Warran...more >>

Hello. I was wondering what is the best way to kick off multiple procs trapping the ones that had an error. Here's an example of what I came up with. alter procedure dbo.testError @problem int = 0 OUTPUT AS set nocount on print 'start' Declare @error_msg int set @error_msg = 0 set @...more >>

I'm brand new to SQL statements. How do I set ANSI_NULLS to ON for all databases on a SQL 2000 server to ON. SQL Best Practices analyzer recommended changing to this value. I know how to do SET ANSI_NULLS ON, but how do I apply to every database and/or table? ...more >>

Recently I found rather a nasty consequence by using parameterised queries on a partitioned view. I have a .net app that sends parameterised queries to SQL Server 2000. SQL Server obliges by caching the query plan to increase efficiency of subsequent queries. In my case the query is runn...more >>

Hi folks, I have a table which is of 500 GB in size. I need to change the datatype of a column from INT to BIGINT. When I tried making this change from Enterprise Manager, it was throwing log space is full. I also truncated the log and tried again, eventhen I face the same problem. Also I ha...more >>

Using Visual Basic can you assign a resultant recordset to an array without looping through the number or returned records and fields. Dim strArray() as String Dim rs As ADODB.Recordset rs.Open SQLStatement, Connection strArray = rs Many thanks for any assistance....more >>

I have a daily transactions with 40 updates in one table and 200 updates in another table. Once a week this transaction hangs and causes a lot of problems. When it hangs next time, how can I find the cause of this problem? I am on SQL 7.0 and will upgrade to SQL 2000 shortly. I am using ADO.net...more >>

We have exec sp_reset_connection executing about every 2 secs. Howver there are 5 databases on this server. How much is too much? -- JP ..NET Software Develper...more >>

Dear all I got a problem that I would like to use a dynamic statement to query data from a linked server (it is not guaranteed that the used database remains on this server or the Test-Database would be renamed). I tried to use the OPENQUERY-Functionality or to set up a SP which uses Par...more >>

I'm having an issue with selecting data from an Excel 2002 spreadsheet using OpenDataSource (or DTS for that matter). Excel will sometimes identify the column type as numeric and ignore all character data in the column, returning nulls instead of the data (known issue - http://support.micro...more >>

Hi - I would like to know what sql to run in Query Analyzer to add two fields to an existing table (I know you can do this in Enterprise Manager - but I'd like to be able to send a script to someone to let it happen automatically). Column Name: IDCreated DataType: DateTime Length: 8 Allow N...more >>

I have two databases and I am trying to do something with the Merge table in one database and am having a little difficulty writing the logic. Basically in DB1 I have a table which is full of records outling the merges of URNs. Example below. Database1 Table: MergeList RecNo FromURN ...more >>

i added a new user group to sql server. all the users in that group should be able to access the database.i check the data-reader,data-write and public options.for some reason the users are not able to access the database. they are able to access the database only if i turn on system admins pr...more >>

i have some issues with permissions. i added a new user group to sql server.i want to give them permissions to access the database but i don't want them to be the database admins. for some reason if i don't make them system admins they are not able to access the database.Am i missing something...more >>

In our database, there are lots of procedures who have obsolete parameters defined. For example, in the procedure defined as: create procedure proc_MyProc @myparam1 int, @myparam2 int as begin blah-blah-blah end ....let's say that @myparam2 has gone out of use, so we want to re...more >>

I am learning, how can i use sp_msforeachdb to see what nt user has access on a database and the rights they have? or can you help me with the code to do so.. -- Eager to Learn...more >>

I have a WinForms application in which I am wrapping the backing up of a sql database. In order to back up the database I am using the BACKUP DATABASE t-sql and the STATS=5 in order to get the progress back from sql server. Here is the exact script: BACKUP DATABASE test_dvpt TO test_Backup...more >>

Hi All, one of my database is suddenly crashed with power fluctuation and when i started the server it is showing "not able to connect database". I have taken backup and i was trying to attach database in other system. It has given me the following error. --------------------------- Mic...more >>

select count(*) from Table(nolock) -- 11972198 select count(*) from Table(nolock) where Vh_SummVeh_id is null -- 11894085 select count(*) from Table(nolock) where Vh_SummVeh_id is not null -- 9546220 ....doesn't quite make sense, the collation of the field is NULL and datatype is...more >>

I have a question about foreign keys and indexes that I am unsure about. If a table has a foreign key does this column have a non clustered index assigned to it as default (that is hidden)? or does it make sense to add a non clustered index to the foreign key column in the foreign key table? ...more >>

When I use OpenRowset method to export data to Excel file, only 255 characters of columns are allowed to be copied. I get this error when data is more than 255 characters Server: Msg 8152, Level 16, State 4, Line 6 String or binary data would be truncated. The statement has been terminate...more >>

I need a script for changing user user permissions on each database, how do i achieve this? -- Eager to Learn...more >>

As much as i would prefer not to..it appears the only way i can acheive something is to use dynamic sql. I have constructed the test query below, but only recieve invalid column name errors. Can someone tell me where i am going wrong? I am obvsiouly missing something quite simple?? D...more >>

Hello, Is it possiable to call a exe file from a sql stored procedure, and what do i need to look into to do this. also Is it possiable to read a text file from a sql stored statement? ...more >>

I've got a database that has most of it's data in one table, say 90%, and the other 10% across 20 or so tables. I'm finding that performance with this table is slowing down to unacceptable levels when inserting data. I've managed to get good enough performance when reading data from the table....more >>

Hi, Probably an obvious question but I'm havign some real difficulty getting this to work. I have the following query: SELECT datepart(wk, datein) AS WeekNo , count(movementno) as StockIn FROM stock WHERE YEAR(datein) = '2005' AND GROUP BY datepart(wk, datein) Now I want to ensure ...more >>

What is the normal procedure for enclosing string variables in Dynamic TSQL. I've tried using: SET QUOTED_IDENTIFIER ON SET @sqlquery = 'SELECT TOP ' + CAST(@oqs As varchar) + ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' + 'FROM ' + 'asmt_v2_questions ' +...more >>

I have SQL server 2000 and access 2003 adp file always disconnect Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection run-time error 429 ActveX component can't create object How repair this without reinstall all?? ...more >>

Hi, I have this simple SP.. CREATE Procedure stp_SaySo_Increment_Hits @RestaurantID int as declare @CurrentHits int select @CurrentHits = RestaurantHits from dbo.tbl_SaySo_Restaurants Where RestaurantID = @RestaurantID Update dbo.tbl_SaySo_Restaurants Set RestaurantHits = (@Curr...more >>