hi, I have a problem - i hv 2 table - create table predsucc (predecessortask int not null, successortask int not null) create table parentchild (parenttask int not null, childtask int not null) 1 parent can hv many child so 1:M predecesor : successor m:m relatioship if any task ...more >>

I must be missing something in this stored procedure. I tried to comment my intentions, any help is appreciated.... create procedure sp_load as BEGIN SET nocount on -- load the sys_load table initially TRUNCATE TABLE sys_load insert into dbo.sys_load (FirstName, LastName, Address, City, S...more >>

Is it perfectly well and good to return ntext data (up to thousands of characters) from a stored procedure to an application via ADO.NET's ExecuteScalar() method? I don't see why not... just wondering because every time I've seen ExecuteScalar presetned, it is typically being used to return a si...more >>

I have looked in few places for example of paging with sorting using SQL Server and I have not found a definitive direction on how to do it. While in search I have picked up a few pointers and put together a solution that seems to work for my purposes. I created a following example to illustrate...more >>

I am using following connectstring and it works with CSV delimmited file ConnectionString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & = _ "Data Source=3D" & App.Path & ";" & _ "Extended Properties=3D""text;HDR=3DYES;FMT=3DFixedLength""" But when I delimited m...more >>

I am using SQL Server 2000. In a database column (NVARCHAR Column), I would like to store the values in multiple languages like Traditional Chinese, Korean and Japanese. Is it possible? The same column should store all these values based on the user input. How do I achieve this? ...more >>

Inserting a new column, Enterprise Manager sets a default datatype (char) of length 10. I you modify the datatype, the Length property adjust automatically. SQL-DMO has _Column and SystemDatatype2 classes. Which property do you use to get the default Length? Is it possible to get the defaul...more >>

I've applied "Hotfix 8.00.0859", which I downloaded from the Microsoft site, and now I get the "Invalid cursor state" error described here http://support.microsoft.com/?kbid=831997 The article says there is another hotfix that fixes what the previous hotfix screwed up, and one should contact "...more >>

Let's say you have a data schema similar to the following: Master_Data_Table ----------------- Master_Data_Table_ID blah1 blah2 blah3 Sub_Data_table1 --------------- Sub_Data_table1_ID blah1 blah2 blah3 Sub_Data_table2 --------------- Sub_Data_table2_ID blah1 blah2 blah3...more >>

I have a zip code search, if a user enters a zip code not in the db I want the user to get a message that says zip code in not listed. How do I validate a db return when no data is returned? Any Idea's on how to pull this off? thanks. ...more >>

I have a stored procedure (below), that is suppose to get a Reg Number from a table, (Reg_Number), in such a way that every time the stored procedure is called, it will get a different reg number, even if the stored procedure is called simultaneously from two different places, However it is ...more >>

Can some one pls explain the line below. UPDATE webtable SET Active = '0' FROM webtable AS L INNER JOIN tbtemp AS T ON L.Source = T.Source Where flag = '1' ************************ Questions.... 'AS L INNER JOIN ' - What is "L"? What does it reference and how is it linked to my database ...more >>

I've been trying to figure this out for the past couple of hours... without luck. I am using the OpenDataSource approach to import/read an excel spreadsheet. However, if my first sheet is call Sheet1, I have no problems. But, if the sheet is named Excel Ex File.... it fails... -- Thanks for an...more >>

Some .NET code invokes this passing info about a file to import. Because of the way the BULK INSERT statement works, it won't let me use @filename for the 'data_file' paramter even though it's a string, not an identifier! Also, the first_row fails if I give it a paramter! GRRRRR! Has anyone ...more >>

EEI_KEY is a column of type INT. How can I create an execute stored procedure to select where EEI_KEY in (1,2,3) ? Thanks. CREATE PROCEDURE test @m_EEIs varchar(1000) AS select DATA_KEY from DATA where EEI_KEY in (@m_EEIs) If I do EXEC test '1,2,3', I got an error "Syntax error converti...more >>

I am sure there is a way around this, but I can't seem to find it. I have the following scenario set up to test this: SP1 calls SP2 which calls SP3 Each of these stored procedures contain a transaction setup like: begin tran .... do stuff .... if @error_code rollback tran else ...more >>

I have a two-fold question. First, I have a text file (csv) that may contain 20,000 entries (Serial Numbers). Now, I want to select a product and insert the data from the csv file along with the product number into a database. The database has only 2 fields that I need to worry about, the product...more >>

I have a process that has to go against several million records. How can I have the process do 10,000 records at a time and what type of counter etc. do I need to set up to ensure that the next 10,000 start with 10,001 and so on? Is there a white paper or how to document I could look at? ...more >>

I have several scripts that need to run under a specific login, but portions of the script need to run under a different login. Is there a way to have a single script run under different user logons without my having to actually log on and off again for each user? Thanks, Robert *** Sen...more >>

I have a stored procedure that haults sometimes. I do not know how to reproduce it or test that I have fixed it. My problem is this: I have a table that has a field that contains a number. I use this number to create a unique document number. I have to make sure the document numbers are un...more >>

objective: all-at-once generate the create sql for every foreign-key constraint that references a certain key of a certain table ( in my case , it's the primary key ) ( similar to SQL Enterprise Manager's "Generate SQL Script" function ) so, if my database has 1 "parent" and 10 "child" tab...more >>

I am trying to upsize a database front end from Access to ADO. In Access I would have a subquery and then a join with a main query to produce a complete result. The subquery is as follows and named subqryRead: SELECT DISTINCT ArtNum FROM tblDLs WHERE (Util="usernamehere")) The main quer...more >>

Hi, I wish to execute stored procedures automatically on reboot using sp_procoption. My problem is that I cannot pass a parameter - even one that is constant. I could wrap the stored procedure into another one but then I get into messy ownership issues. Isn't there a graceful way t...more >>

I have one sql server (2000 sp3a) with multiple databases. I need help in creating a scipt that will query two dbs. The two dbs have identical table structures. My desired results are where; the value in field1 in table1 in db1 is also in field2 in db2. [ex: inv master table in db has item...more >>

Hi all, Trying to restore master database, need some help. ALTER DATABASE Master SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE Master FROM DISK = 'E:\SQLBackups\master\master_db_200406280200.BAK' trying this in Query analyzer. get error: Server: Msg 5058, Level 16, Stat...more >>

Hi, Excerpt from BOL: [@attachments =] 'attachments [;...n]' Is a semicolon-separated list of files to attach to the mail message. Question: Is it possible to make two files with @query parameter? Or description above is just for files from disk? I'm using SS2000 Enterprise edition...more >>

Hi Guys, I placed a post last night asking for help with regards to importing data from an Informix server, Thank You I received the required advise and can now import my data, this has now lead to a second problem for me, I am using the below Statement to import data each morning for the prev...more >>

Hi, I have some questions on the importance of using fully qualified database object names in the given scenario: There is a single user who is going to access the database, this user has dbo privileges. Now if my SQL statements have unqualified names then do I lose out on reuse of query...more >>

Hi, I have the sp listed below. It works fine when I use only one field in the openquery. If I use two fileds I get the error A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. There muse be something simple I am forgetting. This is t...more >>

Hi, Using PATINDEX, you are able to search for a range of letters or numbers, ie. PATINDEX('%[a-z]%', notes). Are you able to do something similar for non-alphanumeric characters? Khurram ...more >>

SQL 2000 ent ed on a W2k3 Ent Server. installed outlook 20002 client and configured the profile to point to a SMTP server. SQL Mail test with the profile works and SQL Server Agent test works with the profile. We see these 2 tests hit the SMTP server. When I try xp_sendmail or try sending...more >>

Hey all, What is the best way to automate the function of switching to single user mode? Some maintenance operations require that SQL be in single user mode. This can be accomplished very easily through the command prompt. This requires a body to shut down SQL, bring it up in singl...more >>

How can I create SQL Server stored procedure for the following VB sql code (with multiple if statements)? Thank you. sql = "select DATA_KEY from DATA where DATA_KEY in (" & skeys & ")" If sCondition1 <> "" Then sql = sql & " and DATA_COL1 = " & sCondition1 If sCondition2 <> "" Then sql = sql ...more >>

Hi all; I need your suggestion (with an example if possible) to accomplish the following. The data / contacts that we will work with, is coming from an Excel spreadsheet. Do we need to load this spreadsheet into a database table first? Basically what we need to accomplish is a procedural che...more >>

Consider the following table and stored procedure: CREATE TABLE ShowTime..Transactions ( ShowID SMALLINT NOT NULL, TransactionID INTEGER IDENTITY(1,1) NOT NULL, ShowNumber SMALLINT NOT NULL, ToShowNumber SMALLINT NOT NULL, ClassID SMALLINT NOT NULL, Type TINYINT NOT NULL, Cha...more >>

I want to perform a function on each value that is palced into a column, how would i do this? ive seen the function property of a column but have never used it... what i have is a funciton that strips the time off the datetime value and returns a date with a midnight value only then returns it...more >>

I am trying to differentiate between Views, Stored Procedures and User Defined Functions. Are the following statements true / best practice. 1. Never define a View that is sorted. Sorting of Views is carried out on the client machine not the server? 2. Stored Procedures are best used for co...more >>

I would like to set a default value for a field in a table based on data looked up from another table. In the example below the tblPet table contains one record where the Pet_Description="Dog". When a new record is created in tblName I want the Pet_Identifier for Dog to be entered in the Name...more >>

I have an SP that is passing a WHERE value: exec cbe_getordersbyfilter 'orderid=54 and (canceldate is null) and (receiveddate is null)' This works if I only include ONE of the "xxx is null" criteria. I can use EITHER one and it will work, but if I use BOTH of them I get (also tried it with...more >>

How can I change a column from NULL to NON NULL with a default value? create table temp(name1 varchar(30) not null default '', name2 varchar(30) null) How can I [programmatically] change column name2 to have varchar(30) not null default '' ? Ideally I woul...more >>

I have an SP that accepts a "WHERE" value: ...more >>

I want to set my development SQL server to automatically run something like the following: sp_procoption @ProcName = 'sp_sdidebug' , @OptionName = 'option' , @OptionValue = 'LEGACY_ON' but I don't know what to put for @OptionName. Does anybody know how I can do this or do I have to...more >>

Hey all, We are using SQL 2k on Win 2k Server. Our system here uses Access forms for the front end, and SQL Server Linked tables for the backend. There is a form that derives its' data from a view, and everything works fine. There is this one record though, that wont let a field be u...more >>

I am having trouble figuring out what will cause a stored procedure to hault in the middle of a loop: SET @counter = 1 WHILE counter < 25 BEGIN BEGIN TRANSACTION SET @newNumber = SELECT Number1 FROM ATest UPDATE ATest SET ...more >>

Hallo together! I have the problem, that i want to change the password of some SQL Server logins. Therefore a few code lines are necessary, listed below. With SetPassword it is possible to give a new password. The problem is, the password won't be set. So i give the logins.add command in addi...more >>

I have a CRUD application with about 70 forms, each with about 30 fields. Our current db (SQL 2000) structure has a table for each form. The data in these tables are processed into a positional flat file (no way around it). We now have the task of storing the position data (start and length)...more >>

Hello, With Access 2000 I can create a link to an excel file and use it as a table in access, I am wondering if that kind of things is possible in SQL Server. I need to import an excel file per day into a table in SQL server automatically. How can I do this? Thanks, Jim. ...more >>

If im using a smallmoney data field, how can i round it up to 2 decimal places ie. If the value = 17.5060 make the value 17.51 ...more >>

Hi, Hope this is the proper newsgroup - there are more newsgroup my issue fints into. Have just installed MSDE 2000 on an internal Windows 2000 Server for evaluation - this W2000 Server has been running for month and are fully patched/updated. The web-interface to MSDE is working fine, a...more >>

Hi chaps. Got a silly question... In query Analyzer is there any way to default the file...open to show details instead of list? (Do I win a prize for biggest nub question to date..?)...more >>

Hello, Is there a way to retrieve all jobs and their scheduled on a database? Thanks, niv...more >>

Without loading up the complete quety and DDl code, can any one tell me how to convert this sections output from in this case JULY or NULL to 1 or 0 ?? COALESCE( (SELECT e.RenewalMonth FROM ClientFees as e WHERE e.RenewalMonth = DateNAME(Month, DATEADD(month, 1,Getdat...more >>

Hi All I have data in a tables that looks as per below: QID Res --- --- 123 Y 123 Y 456 Y 456 Y 789 Y 789 Y 789 Y 000 Y How do I update the table so that the first instance of QID is set to N and all the remaining values are left as Y ie. QID Res --- --- 123 N 1...more >>

I use two SQL servers 2000+sp3a, when one is 64 bits and one 32bits. I have a DISTRIBUTED transaction. The queries are very simple, but the statement is stuck. any idea ? ...more >>

I have a query in SQL server that queries data on the Latin_1_General_CI_AS collation, code page 1252. On that code page the character _ appears after the capital A (as it does in ASCII) (I have also tried this query with case sensitve Latin_1_General_CS_AS with the same results) However when i q...more >>

Hi Everybody, I am having a strange problem, i wonder if anybody knows the solution: I have written the following SP: CREATE PROCEDURE sp_search_results @SID varchar(50), @STypeID int, AS SELECT field1,field2,field3 INTO #tempsearch FROM tbl_cfields ORDER BY field1 insert ...more >>