This query runs ok: SELECT Mission FROM tblProviders WHERE ProviderID = 'rnpo' When I pass the @providerID and @WhichOne values dynamically to this stored procedure: DECLARE @ProviderID varchar(15) SET @ProviderID = 'rnpo' DECLARE @WhichOne int SET @WhichOne = 1 DECLARE @QRY varchar(1...more >>

I consulted the SQL2K help for adding a table and copied the code and substituted my own variables, but I get an error message about incorrect syntax regarding the AS in line 1. I'm a newbie and am bummed that the code provided in the help doesn't work--I'm assuming there's something I don't ...more >>

Hi all, I have data in the table myTable like following: myTable_oid, myTable_Time 1, 2005-10-21 10:58:34.531 And I want to perform the SQL query in java code using JTDS, like: SELECT myTable_oid, myTable_Time FROM MyTable WHERE (myTable_oid=? AND myTable_Time>=?) ...more >>

Running the script below will result in this error in SQL2K SP4 environment: create view dbo.vw_INTERNAL_ERROR as select top 100 percent * from (select top 100 percent [DateField]=getdate() order by 1) x1 go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else prin...more >>

I have a table with time values in one field. The field is datetime data type with no date entered. During some processing, I want to offset the time in the schedule by a number of minutes, but keep the date value part "empty", even if the offset causes the time to cross a date boundary. ...more >>

Hi All How do i create a table which is as big as 1GB? I have tried some thing below, but it it taking too long. Is there any quick way to do it? Here is my sample i have tried and it's taking too long: CREATE TABLE TableD ( X text) GO insert into tableD values (replicate('h', 5000...more >>

To All Gurus: I am trying to log some information to another table when @@Error returns an error. Below is the sample code to see how @@error works. In this sample code, I am purposely failing INSERT INTO TEST command and want to log informaiton in TestLog. Even though there is a error but ...more >>

I have two ATL Dlls that have multiple interfaces and run under two COM+ Applications. Each DLL accesses SQL Server db via ADO calls and one of the components also interacts with a 3rd party report server system. We run into intermittent issues with the COM+ Application hanging. Our clien...more >>

I am trying to learn SQLDMO for working with our SQL Server 2000 servers. I grabbed a demo program from: http://www.csharphelp.com/archives2/archive342.html called SQLDMO for C# from Kevin Goss I built the program successfully in VS 2003 and it shows me a list of servers (mine being on the li...more >>

Hi there, I'm using MS SQL Server 2000 on Server 2003 One table emp and one empArchive I wanted every time that one record in the emp table will be inserted updated or deleted. Same thing will happend in the empArchive table. So I created three triggers look like this CREATE TRIGGER my...more >>

I have a badly designed legacy database. The product number column has the format 'xxx -yyy', where x is the style and y is the colour. Both style and colour have a variable length. They are always separated by 3 blamks and a '-'. I need to be able to isolate the colour part in order to joi...more >>

DBCC INPUTBUFFER(12) - This command returns what spid #12 is doing. (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. I am 99% confident the output above "DBCC execution completed..." is choking my VB.NET client on th...more >>

Hi, I have a Table with reservations for Northwind Products: ReservationID ProductID StartDate EndDate Quantity I need to tell how much Reservations are still possible in a given TimeFrame. Is this possible without Cursor? Here is a sample: ID StartDate EndDate Quanti...more >>

I have fields that are listed as nvarchar in a table. Field1 contains the month, Field2 contains the day, Field3 contains the year. I need to pick stuff off this table with a "date" >= 12/10/2004. How do you construct the "date" from the 3 fields above? I've tried cast & converts on it, ...more >>

Hi all, Just wondering, is their a system stored procedure i can use to grant permission to all objects in a specified db to a specified user ? Cheers, Adam ...more >>

Here comes the table: Id_Prices | Shop | Product | Price | tStamp -------------------------------------------------------------------------------------- 1 eMall Pen 10 20.5.2005 2 eMall Pen ...more >>

I'm a bit unclear on the syntax to add a composite (multiple columns) primary key to a table. The columns already exist. Thanks....more >>

I work on a project where advertisers on a classifieds Web site have the possibility to place a certain number of ads per month. The difficulty comes from the fact that a month period starts at each "monthversary", meaning that if somebody created his account on June 15, I would need to tak...more >>

Hey, I have a table that records all my bank transactions. So in it there is one entry per transaction. This entry has the date the transaction occured. All I want to do is perform a query that returns how many transactions I had for each month. Ie. jan : 24 transactions feb : 19 t...more >>

Hi When I run the query below in QA, it works fine and gives me some results. When I try to run the same query in Microsoft Query from an Excel sheet, it fails. I get the error "No column was specified for column 1 of 'FSS'. Statements could not be prepared". The query I try to run is th...more >>

Is the following statement documented/supported? I can't find it documented in BOL the results for Aggregate functions when there is no input data. If a Aggregate function gets 0 records input, it returns null. eg. SELECT MAX( Field ) AS ReturnsNull FROM ( SELECT TOP 0 1 AS Fiel...more >>

Hello! I am posting this problem again. I have gotten somebody's help with the right solution but I have one more case to work on. I would appreciate your time and help on this. create table #temp (ID int, Cust varchar(80)) insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1...more >>

We have a float (length = 8) field in the table. We want to store values out ot 4 decimal places. In some cases, where a value ended in 0 or 00, the places are probably truncated (e.g., 38.9560 = 38.956 or 38.6500 = 38.65). I would like for us to see the field value out to 4 places. In other...more >>

Hi all, I am needing to export the data contained in my database as series of sql insert statements? How do i do this in SQL Server? Cheers, Adam ...more >>

I am have an application that uses a full-text catalog for word searching. I need to dsitribute this application so is there a way to distribute the full-text catalog with a script like I do for the database, sp's and triggers? Thanks ...more >>

Here comes the table: Id_Prices | Shop | Product | Price | tStamp -------------------------------------------------------------------------------------- 1 eMall Pen 10 20.5.2005 2 eMall Pen ...more >>

Hi, I want to automate some administrative activities (Check Integrity, Shrink DB Log Space after backup, rebuild indexes...). I'm using Database Maintenance Plan, is this ok? After I run these jobs, in the job history and in the log generated I just see that the activity succeded, but not w...more >>

Is there a script to query a table that will tell me the user that added the Indexing or the user that changed on table on a specific date? It appears to me this is not an option in SQL 2000. Any help is appreciated. Thanks....more >>

I will be inserting large numbers of records into a table with an Identity column. I need the Identity value back to use for subsequent inserts to other tables which link to 1st table on the ID. I can insert to "parent" table one-by-one, retrieving the ID each time. INSERT INTO MyTable (M...more >>

Hello, I am not so experienced in database design and in my job now I have to design a database for publications. The problem is the design should be worked out as soon as possible.:( The publication database should contain the following details: Title Subtitle Authors Place of public...more >>

create proc SQLweird as select * into #temp from employees exec('select * into #temp from employees') select * from #temp where is the result of or how can i access the result of the exec() statement -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mc...more >>

Hello, We're trying to come up with a solution for loading large EDI (X12) batch files into a SQL Server 2005 system. We tried using Altova Mapforce which now has a code generation feature which can be compiled and run, but it does not seem to scale well for large data loads. Any one hav...more >>

How do I know the current Date Format of the Database? Madhivanan ...more >>

Hello, We're trying to come up with a solution for loading large EDI (X12) batch files into a SQL Server 2005 system. We tried using Altova Mapforce which now has a code generation feature which can be compiled and run, but it does not seem to scale well for large data loads. Any one hav...more >>

Hi All, i do have two table a Master and a Detail which has a primary key on the Master Table and Foreign key on the Detail Table How do i insist that when ever there is a entry in the Master table there should atleast exist one record in the Detail Table. Any sort of condition can...more >>

Hi, I created a table in a wrong filegroup. Is it possible to move it to another filegroup? If yes how? Do I need to drop the table and create it in the new filegroup or this can be done online (like oracle's alter table t move ....). Thanks in advance, Tarek...more >>

I traced my app for some perf related issues and found that a proc is giving Sort warnings (EventClass) and Multi Pass Sort (EventSubClass). Where in the query should I start looking into the problem? when do we get these warnings - BOL/msdn doesn't explain in dtl; it just says that - The Sort...more >>

Hi, I want to write the second field from customer table (cm_name) to another table to act as a lookup. This table LICENCINGLOOKUP has two columns (LookupType,LookupValue) For this insertion the first is set to 'CUSTOMER' but it appears all records from the customer table are trying to ...more >>

i am facing timeout error problem on my server using dot net applications how to solve this will no lock option causes this...more >>

Well Actually I have this situation I have a table with AccCode, AccMovement, AccYear, and AccPeriod and my data looks like this AccCode AccYear AccPeriod movement 110401 1998 02 541870.00 110401 1998 03 1210000.00 110401 1998 04 2687330.00 110401 1998 0...more >>

Hi. I'm using ASP.NET Membership and Role Management (Forms authentication) to secure my web application (accessible to a restricted user base via the Internet). This is such a great feature of ASP.NET 2.0. My app accesses data in a Sql Server database which is running on the same box as I...more >>

Hi, Does anyone know the T-SQL code I need to set the collation of a column to the database default? Following books online, I have tried: ALTER <TABLENAME> ALTER <COLUMNNAME> COLLATE DEFAULT GO but this doesn't seem to be correct. I'd be grateful for any assistance! Many thanks....more >>

Hello, I have this Store Procedure and i am new in "Cursor" programming The store Procedure is this CREATE PROCEDURE Testing_Cursor AS DECLARE @AccCode varchar(12) DECLARE @YEAR varchar(4) Declare CODE CURSOR FOR SELECT DISTINCT(NLAcc_Code) FROM NLMovement_TMP OPEN CODE BEGIN FETCH ...more >>

Dear all, I want to know all the possible values for the status field bring up for sysprocesses table. Values such 'running' or 'sleeping' seems very evident but there is one so-called 'DEF-WK...' or something like that which I haven't idea. In this occasion I am not be able to find it in...more >>

I am working on an assignment that was due at midnight. The question is = using the "Northwind database write the following SQL statements to be = executed against on of the tables within the "Northwind database" a.) Alter a table and add a column b.) Alter a table and add a constraint c.) A...more >>

Hi can any one tell me about the use of NOLOCK. Whrere can we use it and where dont ? pros and cons of the above ? rgards lara ...more >>

Hi, I want to create a set of procedures through my application on SQL = Server, through ADO. My Scipts is something of the form: Use DB1 Go create procedure <procedurename> as < procedure text> Go Create Procedure <procedurename> as <procedure text> Go But when I execute thi...more >>

I am using Enterprise Manager to create a relationship between two tables (Bookings and BookingTypes). The relationship on column BookingTypeSerialNo (int) in both tables with no cascading actions. BookingTypes contains a column called OfficeCopies which is a tinyint. I've created a check ...more >>

I have a stored procedure that consists 4 set of statements (delete rows and then insert rows from excel files with openrowset). It returns error when execute the 4th insert statement. The error message is: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider...more >>

Hi, Anybody tells me about any sample application available for ADOMD.NET. Regards Stephen ...more >>