How can I read/create a file from t-sql?

I need to create/read files from t-sql? Does anyone know how can I do it?
Thanksbcp?

bulk insert?

DTS?

You probably mean as device the you read sequentially through...

Nope, and you wouldn't want to.|||Thanks Brett. I know the tools you suggested me, but I think they cant help me so much.

What I need to do is process the information. The scenario is: I have two dbs one exporting data, A, and the other, B, importing them, communication is not assured, and moreover, depending on the incoming data and data on B db, I should update records on B or insert them, so, I think, I cant use bcp or bulk insert.

I have been working with oracle and pl-sql has system defined functions and procedures to access files from stored functions or procedures. I have look for the equivalent functions/procedures on T-sql and I havent found any information, dont they exist?

If they dont exist I want to know if I can program DTS task to read data from files and process them.

Thanks in advance|||You basically want to do delta processing right?

If it's in A and not in B, add to b
If it's in A and in B, update B
If it's not in A and in B delete B..

something like that?

I would just compare the tables...

have a look

Delta Process (http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx)

How can I programmatically tell if agents are running?

Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.

This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.

Any suggestions?Query the status column in the history tables in the distribution database. there should be MSsnapshot_history, MSlogreader_history, MSdistribution_history.|||

Thanks for your response Greg.

I've spent some time looking at these tables and can't quite figure how to determine if one of the agents are down or not. I've induced some failures, took some of the agents offline, and generally tried to muck up the environment but couldn't find a reliable way to indicate if any of the darned things were actually running. I suppose I can look for the max timestamp of a 'started' process that doesn't have a 'error' or 'completed' record whose timestamp is more recent, but that doesn't feel to be a reliable method.

|||

Books Online explains each column for each table, as well as the various values for column runstatus.

For example, MSdistribution_history (this is cut/paste from books online):

runstatus

int

The Running status:

1 = Start.

2 = Succeed.

3 = In progress.

4 = Idle.

5 = Retry.

6 = Fail.

|||

Yup, this is a great example of what I was saying about the table not being a reliable indicator of the actual status of the agent. When I manually stopped the Sql Agent, I noticed rows in MSdistribution_history that looked like the following:

agent_id runstatus start_time time duration comments

5 2 <time> <time> 88854 The process was successfully stopped.

It seems that the runstatus 'Success' really means stopped in this case. It was immediately followed by a 'Starting agent' row when I turned it back on again. This led me to wonder if there are any other conditions I'd have to look for in order to see if things are enabled.

Maybe that's the confusion here... I don't care if a particular job is running, just that the agent is started / enabled / turned on / in a state where it can be used, etc. I know I can start looking for the most recent status log that indicates the agent is operable, but I've been in this business too long not to be lazy and just look for a darned enabled / disabled flag.

|||

Replication agents can be invoked numerous ways - from command line, ActiveX, or SQL jobs. The history tables will give you the status of the agent at its current state. For example, if your agent is set to run in continuous mode, and status = 3, then all is good. If status = 6, then your agent has failed. If your agent is scheduled to run at regular intervals, and you query it one hour later, and status = 2, that means the agent job ran successful. IF it's 6, then it failed. If it's 3, then the agent is still running.

Now, how the agent is invoked is totally up to you. HOw you determine if it's in a state where it can be used, well, I'm not sure how to define that for you. YOu need to be more specific with what you're trying to accomplish. Otherwise the replication history tables will give you the status of the current/last agent run.

How can I insert a column into a table before another column via T-SQL?

Please, who can help me to resolve this problem?
My database is MS SQL 2000...
Thank you very much!!There is only one way to do this - recreate table. If your table is very big - just use DTS to export and import data.

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_t2
(
id int NULL,
newcolumn int NULL,
code varchar(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.t2)
EXEC('INSERT INTO dbo.Tmp_t2 (id, code)
SELECT id, code FROM dbo.t2 TABLOCKX')
GO
DROP TABLE dbo.t2
GO
EXECUTE sp_rename 'dbo.Tmp_t2', 't2', 'OBJECT'
GO
COMMIT|||Thanks for your reply, first!!

This way I had think before.

But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

So.....Maybe somthing can help us to complete this task.

I hope so.....|||Open your table in design mode through Enterprise Manager, then make the change you want but do not close the window or save your changes.

Instead, click on the scripting icon (3rd from left on my toolbar), and then copy the script it creates.

Close your table without saving the changes, and then you can adapt the script you copied to whatever you need.

blindman|||Originally posted by Richard Chen
Thanks for your reply, first!!

This way I had think before.

But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

So.....Maybe somthing can help us to complete this task.

I hope so.....

Enterprise Manager does the same way. To be honest I took script from EI - in design table click on icon 'Save change script' (third from the left) after some changes done.|||Plagiarist!|||Originally posted by blindman
Plagiarist!

Idea was in air!|||Originally posted by blindman
:D

I just moved from KY (I had an offer from OH, but in MD is much better).|||Now I understand what you said.

It's so kind of you.

I'II try it right now!!