It took me a while to finally figured what what went wrong to fix the above error. The problem is my bcp.fmt needs to have an empty line at the last row.
The first screen capture is the one before I added the empty line and the second screen capture is the one after I added the empty line. Take note of the cursor.
After you have added the empty line, it should works.
Let’s try this out. First, open your SQL Query Analyzer. Then, run these three lines of code. (I assume your SQL Server 2000 has default Pubs database).
Use Pubs
SET NOCOUNT ON
SELECT au_lname FROM authors
Then, change from SET NOCOUNT ON to SET NOCOUNT OFF and run them.
Use Pubs
SET NOCOUNT OFF
SELECT au_lname FROM authors
Spot the difference?
Okay, if you didn’t, the hint is at the left bottom tab. Click Messages tab. Got it? The difference is
Why we need to set it ON or OFF? Answer is performance. If set it ON, it has a better performance.
You can execute a stored procedure from another stored procedure. This means that you can create a common stored procedure so that any stored procedure which needs this common stored procedure can execute it.
Example, you have these three stored procedures. One is a common one. The other two are normal one.
Inside sp_update, you have
UPDATE mytable SET status ‘N’
Inside sp_delete, you have
@ID int
AS
DELETE FROM mytable WHERE ID = @ID
Inside sp_common, you have
@ID int,
@desc varchar(3000),
@submit_by varchar(50),
@submit_by varchar(50)AS
INSERT INTO mytable (id, desc, submitby, submitdt, status)
VALUES (@ID, @desc, @submit_by, @submit_dt, ‘N’)return 1
If sp_update and sp_delete need sp_common, just add one more line (if there is no return value).
Inside sp_update, add the line in bold
UPDATE mytable SET status ‘N’
EXEC sp_common
Note: You can use either EXEC or EXECUTE. They are the same. But EXEC() is a different thing.
If there is return value, then inside sp_update, add a declaration line and edit the existing execution line (in bold):
DECLARE @status int
UPDATE mytable SET status ‘N’
EXEC @status = sp_common
Somehow, null will always exists in the database.
Without doing some null checking at your code, you will get an error.
One way to fix it is by using isNull at your query. Basically this check, if counter is null, then a No value string will be assigned to it.
SELECT isnull(counter,’No value’) AS counter FROM test
So, the output will becomes,
| counter |
| 99 |
| 12 |
| 34 |
| No value |
(This example was done using SQL Query Analyzer in SQL Server 2000)
Say you have this table below,
| product_type | log_datetime |
| xbox | 2008-06-11 16:26:46.000 |
| xbox | 2008-06-13 16:30:09.653 |
| psp | 2008-06-14 16:28:08.000 |
| ps | 2008-06-16 16:21:40.000 |
And you would like to display log_datetime in different format, how do you do it? One way is to do the formating at the query side. Example, if you want the date format to be dd mmm yyyy format, use CONVERT to format log_datetime like below
SELECT product_type, convert(varchar(11),log_datetime,106) AS log_date FROM test ORDER BY log_datetime
The output becomes,
| product_type | log_datetime |
| xbox | 11 Jun 2008 |
| xbox | 13 Jun 2008 |
| psp | 14 Jun 2008 |
| ps | 16 Jun 2008 |
If you want to just show the time only, just change the 106 to 108 to become,
SELECT product_type, convert(varchar(11),log_datetime,108) AS log_date FROM test ORDER BY log_datetime
The output becomes,
| product_type | log_datetime |
| xbox | 16:26:46 |
| xbox | 16:30:09 |
| psp | 16:28:08 |
| ps | 16:21:40 |
For more reference, http://doc.ddart.net/mssql/sql70/ca-co_1.htm