I am trying to import 2 columns from a 3 column file, but I get: C:\temp\>bcp soar.dbo.remarks_temp in remarks.txt -f bcp.fmt SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file Here is the template file: 8.0 3 1 SQLCH...more >>

In my application, I have an ADO.NET DataTable whose schema is the same as this table: CREATE TABLE dbo.CMRPhone ( CLMRKey INT NOT NULL, PhoneNumber NVARCHAR(40) NOT NULL ) The PK of the table is a composite of both columns. The items in the ADO.NET DataTable may or may not hav...more >>

Hi all, How would I order the Months, from June 2004 tell now? The code below works fine but doesn't order the Month the way I want. Select DATENAME(MONTH, examdate) as [Month], COUNT(ercpid)as Total from tempercp where Stay ='inpatient/consult' and examdate >= '20040601' ...more >>

Hello, I need to tell MSSQLServer that a where clause must treat lower and upper cases differently. I just need to do that in one query. -- Jorge Luzarraga C Fidens S.A. "I can do it quick. I can do it cheap. I can do it well. Pick any two." ...more >>

I have a field with values "A", "B", or "C", for example. I want to sort the table on this field but in the following order: "B", "C", "A". Is there a way that I can "redefine" the values in this field in my SQL statement so that the table can be sorted in this "unusual" order? TIA, Larry...more >>

Hi, I'm trying to bulk insert a date. I get this error: Server: Msg 4864, Level 16, State 1, Line 1 Bulk insert data conversion error (type mismatch) for row 2, column 14 (date). Here is the BCP format file: 8.0 7 1 SQLCHAR 0 0 "" 0 id SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 0 "" 0 ...more >>

SQL 2K I rolled out a SP (mysp) which includes the create statement and in the last GRANT EXECUTE ON mySP TO myrole GO Stored procedure created successfully and in EM it shows the permissions granted to myrole, BUT my application failed with error message " No permission on object mysp ...more >>

I want to create an empty SQL Server database with nothing in it so that I can add tablesand other stuff to it using sql code. How do I do this? -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ ...more >>

hello there I want to know if I can have sql server C2 login log to a file till it reaches 10 megs and switch to another one. the reason is that I want to ship the logs to a log aggregation software every hour or so ? any ideas ? ...more >>

I was wondering about the nvarchar datatype in sql , if there is a good alternative for a datatype to use. I have an script that submits articles to a blog via asp.net, and I think some of the articles are going to be more than 4000 characters (the nvarchar limit). thanks .Netsports ...more >>

I set up a DTS package that will loop thru a directory and process all excel files in that directory. All the files have the same format. So no problem here. However, when the user is not careful, the column headings can be a little issue: "Cust#" vs "Cust #" or "Cust Nbr", etc. I have been...more >>

Hi, I have same primary key (SEC_ID) in all tables and I have a sequence table. I want set SEC_ID field from T_SEQUENCE table on insert and after insert I want increment SEC_ID (like an IDENTITY). T_SEQUENCE ------------ NEW_ID bigint All tables have some insert trigger; ------------ ...more >>

Hi all, select ISDATE(CAST(100130 AS VARCHAR(100))) returns 1 as opposed to select ISDATE(CAST(100789 AS VARCHAR(100))) which returns 0. How can I make sure that numbers like 100130 when checked against isdate come out as 0? Or how can I check numbers to make sure that they are not dates....more >>

Hi, I need to display text data stored in SQL Server 2000 on Web pages. I have a few nvarchar fields, containing some Unicode characters, and I output their contents in HTML using vb 6 DLLs. Event if, in the SQL tables, the accentuated characters look fine, I can't seem to retrieve them u...more >>

Hi! I'm experiencing the following experience problem with my SQL Server 2000. Explanation a' la example; 1. I insert data using my SP; EXEC dbo.up_DataInsert This is fine, SQL Profiler duration only a few ms. 2. I try to select the data. select * from dbo.tblData where DataNumber = ...more >>

Hi, I am trying to use MS SQL to connect to mutliple mail boxes in Outlook. I am using Outlook 2000 and MS SQL Server 2000, both on the same server. I can connect to the Administrator Inbox that I configured on the server by using xp_startmail, or even by passing the user and password. ...more >>

Hello, all. I'm simply trying to get the most recent run information for a specific job. I'm hitting the sysjobhistory table for this information as follows: select max(h.run_date) as Max_Date, j.job_id, j.name , h.run_date, h.run_status from sysjobs j, sysjobhistory h where j.job_id ...more >>

I'm a bit stumped because I don't really know what to search for in my search. Which is a bit of a problem! I hope someone here can point me in the right direction. I have a table that lists discovery dates for forest fires for 50+ years. The only field I care about in this query is discover...more >>

Hi All, I have a column called OldEndDate which is nvarchar(255) and has values like 1/1/1997 I have a new column called End_Date which is datetime(8). When I run this update Update TempSessions Set TempSessions.End_Date= OldEndDate It gives me an error saying Server: Msg 8115,...more >>

Is it possible to write T-SQL that executes on server1 that enumerates and kills processes attached to a database on server2 ? ...more >>

Scenario: I create a new table called Test. I create a column called TestSerialNo which is the primary key. I save the table. In Enterprise Manager, I right-click on the table and select "Generate SQL Script". When I look at the script it has generated the script has added the WITH NOCHE...more >>

Sorry I forgot to mention so I am reposting: This is for an ASP-based website (told you I was a newbie :) I am building a web-based (ASP) reservation system. When a user creates a new reservation, I check to ensure that the requested dates are available. If so, I create the record. Howe...more >>

how can i handling exception in SQL Server? -- Posted using the http://www.dbforumz.com interface, at author's request Articles individually checked for conformance to usenet standards Topic URL: http://www.dbforumz.com/Programming-handling-exception-ftopict234378.html Visit Topic URL to con...more >>

I've got some tables that have no Indexes on them and I want to find out how many rows exist in those type of table. I can write the query to retreive the correct tables, but dont know how to get the count of rows of data in each of those tables without the use of a cursor. CREATE TABLE [db...more >>

Hi, I'm investigating the use of triggers to manage a simple audit trail for a small table named tblSTOCK in my database. Currently, I have an INSERT trigger that inserts an audit record when a record is inserted into tblSTOCK. This works effectively enough. However, I want a trigger th...more >>

I am building a reservation system. When a user creates a new reservation, I check to ensure that the requested dates are available. If so, I create the record. However, I am concerned that between the time I check to ensure the dates are available, and the time I actually create the new re...more >>

Hi all ! I have to make a Task Job, with a SP that transfers rows from some tables to other tables in other server. I want that when one insert fails.. the all SP stop, and roll back the transaction. is that ok to use SET XACT_ABORT ON befor the BEGIN TRANS ?, that s ensures me to have all...more >>

Hi, I have a procedure (see partly below) that takes a stored procedure name and a list of id's as parameters and that then executes the proc for each id. This all works, but when the procedure raises an error RAISERROR('%s : adjustment_id [%d] does not exist in table t_adj_data_trade.'...more >>

I have a simple SQL Insert statement that is causing the error: -2147217900: Syntax error or access violation If I run the statement in Query Analyzer it runs fine. Here is the statement: INSERT AccessItem (AccessKey,Grantee,GroupKey,Permission,RoleKey,UserKey) VALUES ('{6A431912-3F6D-...more >>

Hi All, If a user login to sql and create a table, the table owner is under their name. If I give the admin permissions the table owner is dbo. I don't want to give then admin permissions but I want table owner to be dbo after their create a table. What can I do to fix this? Maybe it's a par...more >>

I have a calendar table with all dates in a month which I am trying to link to a transactional table. Although, there is 07/01/2004 present in the transactional table, but the result of the join is starting from 07/02/2004 and interestingly ending one day before the end of transactional table ...more >>

I have question abt varchar and nvarchar. I do understand that if i use varchar, i can store character set which is set during SQL Server Setup And if i use nvarchar i can store all of the characters defined in the various character sets. I have created one sampletable create table #test...more >>

I have an Access 2003 application that has SQL Server 2000 back-end tables linked via ODBC. On one table, I am getting a Write Conflict error every time I try to edit a varchar (1000) text field, even when I am the only person in the application/database. I can edit all other text fields in ...more >>

Okay, I created a view in my database. This view references tables that are not available until a later time but I wanted the view for use later. I generated a sql script for the view that will be deployed with the rest of our database update scripts. The view references tables that are create...more >>

Hi, Suppose my Product table has 2 rows Product TransactionType Amount A Cost 5 A Sale 8 B Cost ...more >>

I'm trying to determine status of a job within a stored procedure... I know that I use sp_help_job @job_name, @job_step and it returns a record set that contains "current_execution_status". I've tried a temporary table that matched the record set but get: Server: Msg 8164, Level 16, State...more >>

Here is a problem I have come across in one of my tables. Hope someone here can help...I currently have a varchar column that accepts strings of date timestamp information. I have been tasked with converting the column from Varchar to a small datetime. The data in the column is variable F...more >>

I am trying to execute a statement as shown below select top 10 * from employees this is giving me an error Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '10'. Can anyone help me on this....more >>

Can I set a variable to the results of a select statement and then use the variable as the IN clause of another SELECT statement. psuedo code... DECLARE @LIST as varchar(16) SET @LIST = (SELECT Account from AccountGroups WHERE .....) Variable would then be equal to 'A,B,C' SELECT * FROM ...more >>

I run a query against table A that returns Name values. The query returns anything from 1 to 5 names each time. Each name in the result set has to go into a separate column in a new table. There are 5 name columns and between 1-5 of them will be populated using the result set. Is there a wa...more >>

Hi, First of all, apologies for the subject - I'm not entirely sure how to explain my problem clearly. I'm going to use the pubs database to demonstrate what I'm trying to do. The titles table contains all the books in the database. Each book has a title and a type. I'm trying to write a qu...more >>

Is it possible to write a user-defined aggregate function ? I try a simple thing, but can't find a solution. A user-defined-aggregate- function could solve this. I'd like to concatenate the values of the tuples of my resultset, i know how it works with local variables, but not without, and th...more >>

I have been trying to do something like this :- declare @fieldLength int set @fieldLength = (select max(len(somefield)) from sometable) select cast(somefield as char(@fieldLength)) into newtable from sometable The above doesn't work. Is it posssible to do anything like this with MSQL...more >>

Hello, Sorry for asking stupid questions here. I never administred SQL. I need to execute on standalone SQL server a batch written and tested under MSDE. Batch contains just couple of calls to OSQL.EXE like: OSQL -U Administrator -i CreateDB.sql On every call I'm getting: ...more >>

Hi all, I need to make sure that all dollar values in a certain table are evenly divisable by 3. Does anyone know how to check for a remainder? Thanks, Joe...more >>

I have the following code segment in a Stored Proc: IF exists (Select distinct customer from CustomerMaster where ID = @varID ) BEGIN set @varcustomer = (Select distinct customer from CustStore where ID = ...more >>

hi all, I have a db with text in Spanish. I'm creating a web application with an form for it. The data displays correctly, however, when i pass the request.form to the stored procedure, the umlauts and other non-ascii characters are changed to gibrish - for instance,niño is stored in the db ...more >>

While returning ntext from a SQL SP I get this error. "Error 409: The assignment operator operation could not take a text data type as an argument Please help. Thanks, Jagriti...more >>

Is it any way to take back up on Mapped Network Drive and is i feasible ? with or without any external software -- gheman ----------------------------------------------------------------------- ghemant's Profile: http://www.msusenet.com/member.php?userid=234 View this thread: http://www...more >>

Hi, I am getting the following error "Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint." when inserting a records into a table from the view which has 230 columns in it. I am selecting 70 columns from view and inserting into the ta...more >>

I have the following code but I am not getting the result I expect from my output parameter. My variable @TotDue should hold the value 146 but it appears is not being set. BEGIN SET @SqlString = N'SELECT SUM([Due to pay]) FROM ' + @NewRenewalAnalysisbyRenType SET @Params = N'@TotDueOUT i...more >>

i have two SP one is used to update(insert) in master table, the other is used to update(insert) in detail table. the relation has an insert constraint. meaning the PK in master table must exist in as FK in the detail table. what should i do (or configure) to be able to execute this two SP ...more >>

I have a bit of code that I have written that works well is each bit is run individually. However when it is all run together it trips up as a result of a sequence that looks as follows Select into x Alter Table x, Add newcol select newcol from x Running each line individually works fine...more >>

can you compare view against SP when executing select statements. im using my selects to fill up my datatable and bind it to a true grid. (a form with a list) -- rad...more >>

Hi and TIA! I have an ASP.Net page that when the user clicks a button a recordset is passed to a stored procedure(this is what I'm trying to do). I then want to take this recordset and insert all the records from the recordset into a table on SQL Server. Is this possible and if so where can...more >>

Dear all, I have a table in SQL server, e.g. "myTable", it looks like the following: Custr_ID Order_Qty Order_Date --------------- ----------- ------------- Cust1 10 20/03/05 Cust1 ...more >>

Can someone give me a simple example of a stored proceudre using on input and one output parameter. Thanks in advanc ...more >>

Hi, Recently i installed SQL server 2000(EE)on my remote dedicated server( win 2003). I haven't installed ant Service packs. Now the problem is i am not able to connect to the SQL server either thru Query Analyzer or thru the application. I have disabled the firewall, still no use. Can anyone he...more >>

Hi I am trying to replace some text in a database field with some other text, is there a query which will check a field in a table for a specific string of text and then replace it with another string of text, there is other text in the field which I want to keep as it is. The field is a type...more >>

hi! I have developed an Accounting Software using SQL Server 2000 and VB6. In it I have a Table ChartOfAccounts.In this table there are certain columns like AccountNo, OpeningBalance, ClosingBalance etc. Now it was working fine since last 2 years. But now unexpectidly sometimes OpeningBa...more >>

Hello , Is there a way to implement wildcard search like using LIKE operator to implement in FullText Search using FREETEXT or CONTAINS? For example Giving a key word, 'Mexico' should fetch the records which contains 'Mexicocity' Thanks in advance. Regards Sathian ...more >>

Is there a way to select CUMULATIVE result? For example, if there is a very simple table with only one field A, and the data is 1, 2, 3,4,5.... A --- 1 2 3 4 5 Now I would like to select comulative result of field a as followed, A ComulativeOfA -- ----------------------- ...more >>

Hi, I want write some application that will read the content of trace file without help of SQL Server. I don't want to connect to SQL Server and fn_trace_gettable function. Any idea about how can we get to know about the trace file format . Is there already any tool or application exist t...more >>