|
Are your two databases on the same server? If so, you can reference tables in the other database using the syntax database.owner.mytable .
If the two databases are on different servers, you can set up one of the servers as a linked server in your other server.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Paul,
Thanks for the quick response. I gave it a try on some test tables I created and executed the SP and had a return value of '0' which is good. The only problem is that the information I tried to insert was not in either table
Below is what I created, could you take a look and see if I am missing something.
-- Insert statements for procedure here
BEGIN TRANSACTION
INSERT INTO ff_test(TestInput, TestInputCode)
VALUES('INSERTED','1')
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN 2
END
INSERT INTO [FEDEX].[dbo].ff_test(TestInput, TestInputCode)
VALUES('INSERTED','1')
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN 3
END
COMMIT TRANSACTION
RETURN 0
|
|
|
|
|
Your code looks OK, so I'm not sure what the problem is. I would try running the SQL Profiler to determine which statements in your stored procedure are actually being executed.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Not sure what was going on there but the information is now in the tables, and representative of each time I tried to execute the program. Weird but now ok. I will futz around some more to clean it up but again "THANKS" for the pointer. It works.
F
|
|
|
|
|
I would like to import my HOST file into a DB Table and the periodically run a script to verify if all the entries are the same, changed, deleted, etc.
|
|
|
|
|
solutionsville wrote: I would like to import my HOST file into a DB Table and the periodically run a script to verify if all the entries are the same, changed, deleted, etc.
Good for you.
Did you have a question that you wanted to ask?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello Paul.
I guess I was kinda vauge... Mind is elsewhere. Funeral today.
I want to write an SQL script that looks at the Windows HOSTS File and reads the IP / host name into two columns of a table.
I next want to write a SQL Script that I can run as a job and validate the entries in the hosts file and if they have changed then modify the table data.
I will also send a diff email to myself noting any changes(the email piece I have figured out).
Thanks,
|
|
|
|
|
solutionsville wrote: I guess I was kinda vauge... Mind is elsewhere. Funeral today.
Sorry to hear that.
solutionsville wrote: I want to write an SQL script that looks at the Windows HOSTS File and reads the IP / host name into two columns of a table.
You could write a small .NET program to read the contents of your HOSTS file into SQL Server. Use the System.IO.File.ReadAllLines() to load the contents of the file into an array. Then do some string manipulation to separate the IP address and host name for each entry. Then use ADO.NET to load the data into your database.
solutionsville wrote: I next want to write a SQL Script that I can run as a job and validate the entries in the hosts file and if they have changed then modify the table data.
You could achieve this by using SQL Server AGent to set up a job that runs periodically.
solutionsville wrote: I will also send a diff email to myself noting any changes(the email piece I have figured out).
You can write code in T-SQL to send mail. Have a look at this article[^]. You could invoke this from your SQL Server Agent job.
Hope this helps.
-- modified at 10:12 Tuesday 27th November, 2007
I forgot to include the link to the email article. I've put it in now.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
I need to update a field in a record in a table how can I access it to change it.
ie: i have a grid view and a form view on the same page I want to put the highest value in the grid view in a field in the formview and save it to the data base.
Thank you
BiG RaLpH
|
|
|
|
|
How can i insert breakpoints in stored procedures(SQL SERVER 2000) using SQL Debugger in Query Analyzer.
Exec sp_sdidebug 'legacy_on' is not working.
Thanx in advance
|
|
|
|
|
|
yes to some extent but i m not using windows or any other application.i just want to debug my sp from query analyzer.The problem is that Step Into button is disabled in my T-SQL Debugger
|
|
|
|
|
Hi ,
I can restore my db from SQL server Management with this script;
RESTORE DATABASE [LIDER] FROM DISK = N'E:\OyunTakip\database\LIDER.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
but i cant do the same thing from C#.
SqlConnection baglantim = new SqlConnection("data source=THORN\\SQLEXPRESS; database=LIDER; integrated security=SSPI");
baglantim.Open();
string cmdText = "RESTORE DATABASE" + " LIDER" + " FROM DISK = 'E:\\OyunTakip\\database\\LIDER.BAK'" + " WITH MOVE 'LIDER' TO 'e:\\Oyuntakip\\database\\LIDER.mdf' " + ",MOVE 'LIDER_Log' TO 'E:\\Oyuntakip\\database\\LIDER_log.ldf'" + ",REPLACE";
SqlCommand komut = new SqlCommand(cmdText,baglantim);
komut.ExecuteNonQuery();
Getting this error ;
RESTORE cannot process database 'LIDER' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally.
i cant solve it help pleasEEEe ;(
|
|
|
|
|
tr_thorn wrote: SqlConnection baglantim = new SqlConnection("data source=THORN\\SQLEXPRESS; database=LIDER; integrated security=SSPI");
Change it to
SqlConnection baglantim = new SqlConnection("data source=THORN\\SQLEXPRESS; database=Master; integrated security=SSPI");
That should work.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Getting this error after change LIDER to Master ;
Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.
|
|
|
|
|
Surely you restore into a new database. If the database already exists the restore process cannot work. Try restoring to a database name that does not already exist on the server.
|
|
|
|
|
Execute this command after setting the connection and before restoring the DB
ALTER DATABASE LIDER SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Of course, the ramifications being that any user connected to the DB will lost connection and any open transactions will be rolled back. It's pertinent for you to check if there are any existing users first, any running transactions first and then do the recovery.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
how can i restore database to exists database.?
|
|
|
|
|
tr_thorn wrote: how can i restore database to exists database.?
Using these[^] command
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Can anybody guide me as to how do we
call a cursor having rows more than 10000?
Manish Ganguly
|
|
|
|
|
I am afraid that you might be running under performance issues when you are working with cursors on a table with more than 10000 rows.
Best bet will be to try populating your records onto a temporary table or table variable.
Once you have mastered the ability to turn yourself invisible at will, and to deflect bullets with your hair, you will be ready to come back here to the lounge and post programming questions.Only at this point will you be able to dodge the fireballs that are going to come your way. - Pete O'Hanlon
|
|
|
|
|
HI CS,
I GOT WHAT U SAID.
THIS IS MY SCENARIO:
1. PICK UP DATA FROM A TEMPORARY TABLE ROW BY ROW......VALIDATE WHETHER THEY FOLLOW BUSINESS LOGIC OR NOT.
2. IF THE DATA IS ERRONEOUS UPDATE A FLAG IN THE SAME TABLE TO F, ELSE UPDATE IT TO P.
Manish Ganguly
|
|
|
|
|
Please don't shout. First of all, can you perform your business logic in SQL? If you can, you can possibly do this without having to use a cursor at all. It's always best to use SET based updates wherever possible, so you could use something like:
INSERT INTO myTable (myCondition1, myValue1, myOtherValue, myValue) VALUES ('...', 1, 2, 'F')
UPDATE myTable mt
SET myValue = 'P'
WHERE myCondition1 = 'Hello' AND myValue1 > 30 AND myOtherValue >
(Select SUM(oValue) FROM otherTable ot WHERE mt.PrimaryKey = ot.ForeignKey) As you can see, the initial insert creates the record and sets it's validity to false. The update is then used later on in your processing to update the records to P that have passed the test.
|
|
|
|
|
Hi Manish
What database product are you using?
Note that some databases (particularly SQL-Server), cursors are considerably slower set-based operations. Cursors would only normally be considered for reeeeeeally complex business rules).
Under SQL-Server, if you really have to use a cursor, it would be something along the lines of:
declare c1 cursor for
select field1, field2, etc
from YourTable
for update of MyFlag
open c1
fetch c1 into @Field1, @Field2, @Etc
while (@@FETCH_STATUS <> 0) begin
set @MyFlag = 'P'
--Do business logic checks here - if error then set @MyFlag to 'F'.
update YourTable set MyFlag = @MyFlag
where current of c1
fetch c1 into @Field1, @Field2, @Etc
end
close c1
deallocate c1 The code is similar in other types of databases too.
Regards
Andy
|
|
|
|