|
Hi
here is what I have
1- I have 2 different tables in 2 different databases
2- I need to write a stored procedure to start a transaction to add data in both tables so if any error occured then rollback
so I want to access the table in the other database to add data to it
any help
thanks
Shaimmaa
|
|
|
|
|
Get the data from one database table to anothter databse
select * from databasename.dbo.tablename
smile 
|
|
|
|
|
Assumes the DBs are on the same server (valid assumprion from the ?)
If the DBs are on different servers you will need to look into Linked servers (nasty things linked servers)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
tran log of my database is now hge in size and my disc space is almost full.
what do i do now? should i take backup of tran log or change the path of transaction log file to another disc location..
please suggest..
|
|
|
|
|
I'm assuming you aren't backing up your transaction log at all. If that is the case then change the recovery model of your database to Simple. Then detach your database, delete the transaction log file and re-attach your database. A new log file will be created for you.
To set your recovery model to simple use:
ALTER DATABASE [databasename] SET RECOVERY SIMPLE
Do NOT do this while users are logged in. Wait for a time when there is no activity on your database. If you don't have a maintenance window which allows you to do this you will need to truncate your log and then shrink it.
To shrink your transaction log file, first get the logical name of your transaction log by running sp_helpdb 'your database name'. There will be 2 resultsets, the second one will have a column "name", for the row that refers to your log file, that is the logical name.
Then you can run the following script:
<br />
BACKUP LOG [Database Name] WITH TRUNCATE_ONLY<br />
GO<br />
<br />
DBCC SHRINKFILE([LOGICAL FILE NAME], [SIZE]) <br />
GO<br />
where [size] is an integer value in megabytes.
Again, this is only if you are not using your transaction log. The commands will be different if you need to backup your log file. Refer to BOL.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
thanx a lot... i just did as per ur procedure..
|
|
|
|
|
In answer to your question:
sohne wrote: change the path of transaction log file to another disc location..
It's a good idea to keep the transaction log on a different disk to the database - doing this will speed up SQL Server.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
I have a column in my table that is both telephone numbers and text. I have made the field type text as I want to be able to use both in my database. However when I import it into my database, it wants to import that field type as double?! so when it enters my database, all the text entries are null.
Please can someone tell me how to get both the numbers and the text in the database?
|
|
|
|
|
Create the table first in SQL
CREATE TABLE tblCust(RowId int IDENTITY(1,1) PRIMARY KEY, Phone nvarchar(20) NULL)
Then import and use column mapping.
|
|
|
|
|
I've just done that, but even when I am on the mapping form(hopefully you've used the import tool with SQL 2005) although the type field says nvarchar, at the bottom of the page it says this:
Source Column Destination Telephone No Double(15)
But that makes no sense because in my excell spreadsheet the field is a text field, I changed it from a general field to a text field?????
|
|
|
|
|
Hey all, Please help!
I have a delete statement that relys on data not being in another table, it is as follows:
delete MyTable
where substring(MyColumn, 1, charindex('/', MyColumn) - 1) not in (select MyOtherColumn from MyOtherTable)
The problem is that not all the rows contain a '/' so the statement will error as no int value is found for the length parameter coming from char index.
This needs to be done as a case statement I think, but I just can't find the correct syntax, I'm trying to avoid looping through each row as theres lots of them!
Any help would be greatly appreciated.
Many thanks in advance
Mark
Many divs make for very hard work
|
|
|
|
|
You could try this:
delete MyTable
where substring(MyColumn, 1, charindex('/', MyColumn + '/') - 1) not in (select MyOtherColumn from MyOtherTable)
which will just give you the full value from MyColumn
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
wow, I love simple solutions, I just never seem to be able to spot them myself!!
Thank you very much
mark
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi all,
I am changing a sql Server 2000 query to sql server 2005.
That query contains =*.I changed it to Outer join in 2005.But the query returns only 1000 rows and in 2000 it returns 120000 rows.
here is the query in 2000
<br />
select p.planno,e.element,I.Invhst from plan_ p,Element e,Invhst I<br />
where p.Planno=e.Planno<br />
and I.Planno=*p.Planno
here is the query in 2005
select p.planno,e.element,I.Invhst from plan_ p<br />
inner join element E on P.Planno=E.Planno<br />
Left outer join Invhst on I.Planno=p.Planno
Please give me ur suggestion and help to solve this problem.
thanks in advance,
Sangeet
|
|
|
|
|
is it supposed to return 120000 rows, you could be duplicating data in sqlserver 2k. crossproducts of tables typically do that in my experience, which is why i dont use them.
|
|
|
|
|
ramyasangeet wrote: here is the query in 2005
select p.planno,e.element,I.Invhst from plan_ p
inner join element E on P.Planno=E.Planno
Left outer join Invhst on I.Planno=p.Planno
Have you tried a RIGHT join?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Everyone,
I have one problem that is i dont know how to connect ms access database in VC++ program.please tell me the procedure for connection.
please help me.
Thanks In Advance,
savitri
|
|
|
|
|
Please ask in VC++ forum!
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hello Friends,
I've deleted my Table1's records and now i want to retrieve all of deleted records using Rollback.......
Can anybody tell me how can i do it using rollback or any other way.....
|
|
|
|
|
If you did a BEGIN TRAN before the delete then issuing a ROLLBACK TRAN will undo your delete and restore all your records, otherwise they have gone. You could restore from your latest backup - assuming you have one.
Hope this helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi there,
I am trying to encrypt my sotred procedures with "WITH ENCRYPTION" clause like this ALTER PROC myProcedure WITH ENCRYPTION. It works well for the stored procedure that has not parameters but for a stored procedure with parameter list it generates the following error.
"Incorrect syntax near '@p_param1'." where @p_param1 is the first parameter. Note that this SP is giving the proper results and runs well.
Thanx in advance
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Try adding the WITH clause after your parameters list like
create procedure testproc<br />
@param1 int<br />
with encryption <br />
as<br />
begin<br />
[your stmts here]<br />
end<br />
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Well I wonder why this idea didn't struck me. that was too simple.
Thanx for help SimulationofSai.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
What is the proper syntax of an if statement ina stored procedure in MS SQL please?
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
