I have a numeric data in YYYYMMDD format and i would like to convert it to a datetime. For example the data is 20050115. How can I convert it to a datetime value? Thanks ...more >>

We have a database with approx 120 stored procedures in it and no access to the original scripts. Is there any way to search a database's stored procedures for a certain code text ie: a search for "select datediff" code in all the stored procedures. Thanks...more >>

Hello, Please help!!! I've been stuck on this issue for months. I just wonder if there is a way to programmatically evaluate expression strings such as ( ( 3 + 5 ) / 2 ) > 4 --> this should return 0 or false( ( 3 + 6 ) / 3 ) > ( ( 5 + 3 ) / 4 ) --> this should return 1 or trueThan...more >>

Hi all, I am currently using the sqlmaint command from a job to run a backup of a database everynight, the command line is as follows: sqlmaint -S AUGUSTUS\KWSSQL1 -D ParasolIT -Rpt D:\Backups\Clients\ParasolIT\SQLServer\Reports\ParasolITDB.txt -WriteHistory -BkUpDB D:\Backups\Clients\Para...more >>

I need to loop through all the user SPs in a DB and output the SQL into seperate text files. Anyway I can find out dynamically all the user SPs in a DB? thanks, chopswil ...more >>

Hi, I am fairly new to MS-SQL and stored procedures. I need to create a unique continuous "rolling" invoice number which is assigned when I create an invoice. The trouble is that I cannot do: read current invoice_no update invoice_no +=1 in a single row table because I cannot guarantee...more >>

Hi, I am fairly new to MS-SQL and stored procedures. I need to create a unique continuous "rolling" invoice number which is assigned when I create an invoice. The trouble is that I cannot do: read current invoice_no update invoice_no +=1 in a single row table because I cannot guarante...more >>

Hi, Want to know more details about SQL Server Error Msg 8124, "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expr...more >>

rt ...more >>

I am trying to delete a column which has a default value and I get an error message like "The object DF_xxx_yyy_zzz is dependent on column <the name of my column>". When I removed the Default I was able to Drop the column. Is there a way to remove a Default from a column using SQL? Thanks Yanni...more >>

Hi All, My company's network guy (who claims to be a DBA) set up a server where he migrated all the SQL server 2000 databases throughout the company including Active Director etc. This has shown some performance hit in my application and I am not able to give him a good reasoning why he sho...more >>

Hi, I am facing a problem. i am having one text field, and i need to update it. I can use both Update and WriteText statements if i pass the new value as an argument. But wants to know which one is better Aneesh ...more >>

Hi, I would like to know how i can relate 2 tables knowing that their relation is a one-to-one relationship. It is something like: A department HAS A manager. Department and manager have different PK and then I cannot have the table sharing to same PK to model my one-to-one relationship. But...more >>

Seeking comments regarding the benefits of taking the time to learn ORM using Visio which I understand will spit out a relational schema normalized to the 5th form (assuming the ORM has been constructed logically). Would those who have done so say it takes away from the learning experience of de...more >>

Wanted to know which among these options is better and why? Or if their could be scenarios where we could opt for one of these. a) flags passed from code to control the execution of queries within a stored procedure i.e. - where queries within a single stored procedure are controlled by flag...more >>

Hi all. I have a stored procedure that updates multiple tables. One of the columns in the table is defined as a datetime and is populated by a varchar thru a convert(datetime,@value). My problem is, if someone enters a non-date field on the front-end, my proc false and does not go through my error c...more >>

I am actually using MSAccess but I am sure the same will apply with SQL Server. I want to insert a record in a table, using values selected from 2 other tables, but cannot find the right syntax. This is as close as I have come: INSERT INTO TblStuCourseDet (SCDet_CourseID,SCDet_StuID) VA...more >>

what is the difference between checking "is not null" and "<> null" ??? declare @myInt as integer set @myInt = 1 if @myInt is not null print 'is not null' if @myInt <> null print 'not equal to null' tia jt ...more >>

Hi Is it possible to write a single query so it also returns the total* sum of each column in addition to the sum of values by each grouped criteria. E.g. Buildings 2000 Contents 2000 Total* 4000 Thanks in advance. ...more >>

Hi How do i display the Date and only the Date working with DatePart? ch Tom ...more >>

Hi all, I'm trying to do an update in a stored procedure that assigns an ID to the next available records. The problem is I can't figure out how to take a parameter value and use that as the count for the TOP clause. Here's what I'm trying to do... UPDATE PrepaidBatch SET RoleID = @R...more >>

Folks, I have the following problem. I have two tables - Calls and Notes. I'd love to be able to use a query to combine the two. For example, let's say Calls table contains something like this CallID CallDate CallNotes 1 12/20/04 Call 1 2 12/25/04 Call 2 Notes ...more >>

How do I query the top 10 customers for each salesman? If I use the select top(10), then I only get 10 results. For a basic structure, say I had 3 tables: customer, salesman, and sales. Customer - custid, custname, etc Salesman - salesid, salesname, etc Sales - id, custid, salesid,...more >>

$query_Recordset1 = 'SELECT first_name AS name, option_selection1 as showname, '; $query_Recordset1 .= 'DATE_FORMAT( payment_date, \'%b-%e\' ) AS date, '; $query_Recordset1 .= 'CONCAT(\'$\',SUM(mc_gross)) AS amt '; $query_Recordset1 .= ' FROM transactions' . ' WHERE ( transactions.paymen...more >>

Hi all, I need to create a view in sql7, The table look like this: field1 field2 field3 A abc/xyz 500.00 B mnc/opq 255.00 C xyz/abc 550.00 D opq/mnc 123.00 E hnc/yun 25.00 F inm/mnb...more >>

I am not sure why I cannot post this message! I've tried many times! Original message: Okay, let me first start with the abridged table structures: Student Personal Details: (Table 1) Student ID: Eg. 1111 Name: Eg. Abc Student Sale Details (Table 2) Student ID: Eg. Abc Invoice Number...more >>

Hello, Trying to display some statistical information from SQL2000 on web using asp. We are trying to list out participants who have completed survey. while I am able to get the completed number using count(*) for "COmplete" Status code, how would the total number of participants be calcula...more >>

Hi all, I'm building a stored procedure that takes a few input parameters where each of the parameters represent columns from different tables in the database. The parameter's can be null or populated. I can build the WHERE clause of the SELECT statement dynamically, not a problem. The que...more >>

I have a large import through BCP, I get the error message "Primary filegroup is full" after a long while. I looked in the properties of the database and it is set to automatically grow file by 10% and unrestricted growth. Why is it hitting this error then? Thanks Chris ...more >>

I want to use sql statment to insert a field with blank datetime, Does anyone know the how to write this sql ?? Thanks a lot ...more >>

I have a query with 12 union statements , when i execute it i get the following error Could not allocate a work table while processing views. The total number of work tables generated by the query exceeds the limit (14) of work tables. Simplify the query or the views referenced in the que...more >>

I've got a situation where I need to retrieve data between a begin and end date for each KeyID. I also need to find the 1st occurence of an event prior to the begin date for each KeyID. I can get this to work by doing a cursor through the KeyID's and then doing a Union All. But with a table...more >>

in our lan we have 2 sql 2000 servers ( "A" & "B") I'm trying to retrieve data from sql "A", while log in on a session of sql server "B" . I use the "sp_addserver" to add the procedure and I got this message: Server 'server_B' is not configured for DATA ACCESS. any help would be most appre...more >>

Hi I have a table with a field "qty" that should contain a number. Some of the values is today NULL. Is there a script to run on the table to set all records with qty = null to qty = 0 TIRislaa ...more >>

I have a table that looks like this... employee_id,email_address,manager_id 12345,12345@wherever.com,77777 77777,77777@wherevere.com,88888 88888,88888@wherever.com,11111 66666,66666@wherever.com,88888 45678,45678@wherever.com,77777 78910,78910@wherever.com,77777 From the table we know th...more >>

Hello, I'm not an expert in SQL, if you could help me for that little problem: I had tree simple tables with their fields: [Client] IdClient, Param [Sale] IdSale, IdClient, Param [Param] IdParam, Value How can I retrieve a recordset with this columns ? IdClient, IdSale, ValueOfParamC...more >>

Is there any way that i can keep track of record deletion. because after delete, the record will go away. Help Thank ...more >>

When we script a a database, we get a lot of CREATE VIEW statements. Unfortunately, they are sometimes "out of order", i.e. the Enterprise Manager seems not to care about dependencies. Did we miss an option or is there a (not too expensive) tool that can arrange the views so that we don't have...more >>

Is there a way to set up a linked server for a unix box running DB2? I have been able to connect, but always end up crashing SQL SERVER. Thanks ...more >>

I am reposting because I had a misspell in the previous post. I have a transactions table that has an intensive use all day (24 hours) For better performance at 5 AM, I move all transactions of previous day from the production table to a history table. The problem is that while moving (2...more >>

Can someone help me. Ihave been trying to work on this query for a day now and can't get it to work in sql server. I know that i have to use a case statement but can't seem to duplicate this statement. Can anyone convert it to a valid sql statement. SELECT DealerLocations.MasterID, Dea...more >>

I need to run a report per database that has for each user the permissions that they have down to the table level to include stored procs and user defined functions. Does anyone know a query I can use to generate such a report or can you tell which system tables I can query against? Thanks...more >>

Is there a metadata table which can be queried to get at the parameters of a stored proc? -- Galen Boyer...more >>

Does anyone know of an id that uniquely identifies a database or some way this can be accomplished?...more >>

I've been asked to create some web pages with select parameters to pull data from a remote sql 2000 database where I have basic select permissions but not the ability to create/save stored procedures/views/tables. I understand that stored procs are best (?) - faster, easier. What's my best c...more >>

What is the difference between the Login used to Run the SQL Server Agenct Service, and the SQL Server Connection login under the Connection tab of the SQL Server Agnet properties? -- Thanks, ThomasLL...more >>

We have a table of values (tbl_estimates), each row has a "version" column (est_version), to allow multiple revisions. A seperate table stores the current "open" version (tbl_Current_Version). ALTER trigger tg_Current_Version on tbl_Estimates for insert,update,delete as declare @VERSION...more >>

I have a table ‘Test’ with two columns, a datetime column called ‘Last_Used’ and an int column of user ID’s called ‘User_ID’ . I want to write a sproc that gets the max Last_Used datetime of each user ID and if the max datetime for a user ID is less than the current time minus o...more >>

I am trying to export data from SQL Server 7.0 to SQL Server 2000 using DTS Package Data in SQL Server 7.0 Hôtels touristiques avec restaurant Data in SQL Server 2000 after exporting H“tels touristiques avec restaurant The second character in my input data is getting changed to some ...more >>

Hi guys.... In Oracle, I would use the Function TRUNC(Sysdate) to truncate the current date. In SQL Server, I am currently using this: Convert(DateTime, Floor(Convert(Numeric(38, 5), GetDate()))) Is there a better way? Thanks, Forch...more >>

I have a couple of stored procedures and they need to both accept multiple values and return multiple values. Accepting multiple values is fine I know how to do that. Returning multiple values is the question. I want to return a key value from a table and an error code (either set to -1 or ...more >>

Hey, I am not sure how to really explain this, but I'll give it a try. I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement: SELECT Column1, Column2, Column3 FROM ...more >>

This code is run within a DTS package in SQL server. There are about 80,000 rows in the recordset... At first this script would error out with a timeout error after exactly 51 rows were processed, then I added the commandtimeout = 0 line... now it acts like it is running but stops doing an...more >>

Hi I saw mention somewhere in this group that although you can have two fields in a single table that are both FK to the same PK in a parent table, that you are not able to have both with cascaded updates.. Okay, this seems problematic - I think I would like both fields to change if the PK i...more >>

HI, I am using a procedure that inserts data from a database to another. This insert is divided into many parts because of the large amount of data. When the two databases are on the same server, all data are correctly inserted. When the two databases are located on different servers, only...more >>

Is there a way that I can reverse a string, so that 'peter' is returned as 'retep' ? - Craig...more >>

I have an update trigger that uses a Cursor to look through the inserted table and run a complex process for each record returned. For example, if I update 5 records using an UPDATE query, the trigger runs once but the cursor then runs the same process 5 times. The process that runs calls a...more >>

How to use an UPDATE statement to renumber a column? I have a table that stores these data Item Rank --------------------------- Banana 5 Apple 10 Grape 5 Jackfruit 3 Papaya 2 Mango 1 I want to renumber the rank column...more >>

I have a problem converting nvarchar into numeric I've tryed: select @ispID=convert(numeric,SUBSTRING(@isprstr1, @j+1,@i-1)) and also select @ispID=cast(SUBSTRING(@isprstr1, @j+1,@i-1) as numeric) but I always get the message "Error converting data type nvarchar to numeric." I've checked...more >>