|
hi,
i have two databases which are used for different projects. but i need to update a column of one database with the data from another database.
is there a way to do that using SPs? or any other way?
thanks.
regards.
|
|
|
|
|
You can do this job by these statement.
1)Declare a Variable like v
2)Select the record you want use by SELECT Statement and assign it to the v by = operator
3)Update the record you want by UPDATE Statement and set it to the v.
I hope this solution is useful
|
|
|
|
|
Here we go!!
Use joins (normal, left or right) between tables across the databases. You can call different databases in TSQL with dot operators (..).
Eg:-
Select n.name from northwind..authors n, pubs..aurhors p
where p.authorid = n.authorid
|
|
|
|
|
You need to prefix the schema.tablename with the name of the database. For instance:
Update database1.myschema.table1 set value = 10 where id = 20
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
i want to see an example that how to use trigger in sql server 2005,thanks ..
|
|
|
|
|
Now, repeat after me:
Google is my friend. Google is my friend.
Clickety[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I use SQL Server 2005 and Visual Basic 2005.
I write these Statement in SQL Server but it executes these with error and the record dose not inserted into table Project.
CREATE PROCEDURE Insert_Project
(@pNumber_M int,
@pRow_Agreement int,
@pRow_Machine int,
@pRow_Language int)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
DECLARE @Number_O int;
SELECT @Number_O=Number-sum(Number_A) FROM Machine_View WHERE Row=@pRow_Machine GROUP BY Number
SET @Number_O=@Number_O-@pNumber_M;
INSERT INTO Project
(Number_A,Number_D,Row_Agreement,Row_Machine,Row_Language)
VALUES
(@pNumber_M,@Number_O,@pRow_Agreement,@pRow_Machine,@pRow_Language)
UPDATE Project
SET Number_D=@Number_O
WHERE Row_Machine=@pRow_Machine
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH
END
GO
Please help me.
Thank you.
|
|
|
|
|
mghiassi wrote: it executes these with error
What error?
|
|
|
|
|
Hi
The statement below return another code and not zero
SELECT ErrorProcedure AS ERRORPROCEDURE
Thanks for your help
|
|
|
|
|
mghiassi wrote: The statement below return another code and not zero
That is hardly helpful information. What code? If you remove the TRY/CATCH, what error is given (That should give you a textual description of the error)
|
|
|
|
|
Hi guys ! I have installed windows vista and i also installed microsoft sql sever 2005 express edition, and also I have downloaded the requierd update ! but when i want to create a database i get this message :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
The server principal "MRK\Mr.K" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)
|
|
|
|
|
hi,
i need a help in an automation of data fetch from db to excel.
I am having 2 tables in sql server db. at present i will type hundreds of number in excel sheet then i will import that to sql server as a table then executing that table and the primary table using some sql statement i used to fetch the required information. now the problem is when we are doing it for multiple records its too difficult to repeat the process repeatedly.so i want to automate this process.
for example:
primary table:
slno eno,ename, eaddress, ecity
exporting data through excel is eno
comparing this eno in excel with the primary table i can fetch the remining details to the specified set of eno.
how to automate if anyone have idea please share it. so that i can begin my process as soon as possible.
Tech_spidy
|
|
|
|
|
I would used DTS. It comes with SQL Server.
If you have sql server 2000 go to where you start enterprise manager. There is an icon that says import export. If you are using sql 2005 you need to go to the database you want to export the table. Right click the database go to tasks then export.
Pick your sql server name and database to export from.
Pick Excel from the drop down and set the output file name.
Then pick the table / table names or do a query.
I think that could work pretty nice for you process.
I hope that helps.
Ben
|
|
|
|
|
This is what i am already doing. I need a solution like by clicking a single button it has to export the data by executing the query in db.
Tech_spidy
|
|
|
|
|
i want copy of sql table on lan computre can i copy paste
how???
|
|
|
|
|
You can do this job by this solution
Generate the script code for the table by right click on the table and select script table as and
select Create To File and then run this file on the another computer
|
|
|
|
|
You can do this with "Copy" option of right clicking that particular table and paste in Query Analyzer - simple!!
|
|
|
|
|
Is this way a good solution in SQL Server 2005
|
|
|
|
|
hi all
can any body guide me please the exzact difference between a stored procedure,view and a simple aql query what is the major difference based on functionality between these three ,,,
thanks in advance
hello
|
|
|
|
|
A stored procedure is a cached pre compiled sql statement that exists on the database. It can have parameters or not, it can return result sets or not.
A Sql Query is a sql statement that you pass to the database.
A view is a subset or whole table or joined tables that you want to expose. Often if you don't want to do the same join over and over again you can create a view that does the join and only has the columns you are interested in. Some views are updateable, normally when they are only of a single table. Some people use views to control what other users have access to. So the user does not have access to the table, but the do have access to the view.
You can always do some searching on the net to get more info:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx[^]
Hope that helps.
Ben
|
|
|
|
|
Hi, I am working on this master data repeater and got almost everything working, i have 3 levels of categories that show correct
i believe the problem is on the data that i provide through stored ptocedures
then i have the items under the first level almost fine, they are showing fine under their first level category, but the second and third go all under the first instead of group nicelly under their second like
<table><br />
<tr><br />
<td>correct:</td><td>how it is:</td><br />
<td>1.</td><td>1.</td><br />
<td>1.1</td><td>1.1</td><br />
<td>2.</td><td> 2.1 </td><br />
<td>2.1</td><td>3.2</td><br />
<td>3.</td><td>2.</td><br />
<td>3.1 </td><td>3.</td><br />
</tr><br />
</table>
here it is how the relation goes:
DataRelation relation1 = new DataRelation("STOCK_CAT_1", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Items"].Columns["CAT_LEVEL_1"]);<br />
ds.Relations.Add(relation1);<br />
<br />
DataRelation relation2 = new DataRelation("STOCK_CAT_2", ds.Tables["Cat1"].Columns["LEVEL1"], ds.Tables["Items"].Columns["CAT_LEVEL_2"], false);<br />
ds.Relations.Add(relation2);<br />
<br />
DataRelation relation3 = new DataRelation("STOCK_CAT_3", ds.Tables["Cat1"].Columns["LEVEL2"], ds.Tables["Items"].Columns["CAT_LEVEL_3"], false);<br />
ds.Relations.Add(relation3);<br />
<br />
<br />
DataRelation relation4 = new DataRelation("CAT_1toCAT2", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL1"]);<br />
ds.Relations.Add(relation4);<br />
<br />
DataRelation relation5 = new DataRelation("CAT_2toCAT3", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL2"]);<br />
ds.Relations.Add(relation5);
then when i remove the false from ["CAT_LEVEL_3"], false); to create a relation, ig throws a "These columns don't currently have unique values"
here is the data, can anyone spot what is wrong?:
Items | Items | Items | | Cat1 | Cat1 | Cat1 | CAT_LEVEL_1 | CAT_LEVEL_2 | CAT_LEVEL_3 | | STOCK_CAT_ID | Leve1 | Level2 | 62 | 0 | 0 | | 174 | 0 | 0 | 62 | 0 | 0 | | 65 | 0 | 0 | 62 | 0 | 0 | | 176 | 0 | 0 | 62 | 0 | 0 | | 177 | 0 | 0 | 62 | 0 | 0 | | 178 | 0 | 0 | 62 | 0 | 0 | | 175 | 0 | 0 | 62 | 0 | 0 | | 179 | 0 | 0 | 62 | 0 | 0 | | 180 | 0 | 0 | 62 | 0 | 0 | | 181 | 0 | 0 | 62 | 0 | 0 | | 182 | 0 | 0 | 62 | 0 | 0 | | 213 | 182 | 0 | 62 | 0 | 0 | | 214 | 182 | 0 | 62 | 0 | 0 | | 215 | 182 | 0 | 62 | 0 | 0 | | 216 | 182 | 213 | 62 | 0 | 0 | | 217 | 182 | 213 | 62 | 0 | 0 | | 218 | 182 | 213 | 65 | 0 | 0 | | 219 | 182 | 213 | 65 | 0 | 0 | | 220 | 182 | 213 | 176 | 0 | 0 | | 221 | 182 | 213 | 176 | 0 | 0 | | 222 | 182 | 213 | 176 | 0 | 0 | | 223 | 182 | 213 | 176 | 0 | 0 | | 224 | 182 | 213 | 176 | 0 | 0 | | 225 | 182 | 213 | 176 | 0 | 0 | | 226 | 182 | 214 | 176 | 0 | 0 | | 227 | 182 | 214 | 176 | 0 | 0 | | 228 | 182 | 214 | 176 | 0 | 0 | | 229 | 182 | 214 | 176 | 0 | 0 | | 230 | 182 | 214 | 176 | 0 | 0 | | 231 | 182 | 214 | 176 | 0 | 0 | | 232 | 182 | 214 | 176 | 0 | 0 | | 233 | 182 | 214 | 176 | 0 | 0 | | 234 | 182 | 215 | 176 | 0 | 0 | | 235 | 182 | 215 | 176 | 0 | 0 | | 236 | 182 | 215 | 176 | 0 | 0 | | 237 | 182 | 215 | 176 | 0 | 0 | | 238 | 182 | 215 | 176 | 0 | 0 | | 239 | 182 | 215 | 176 | 0 | 0 | | 240 | 182 | 215 | 176 | 0 | 0 | | 241 | 182 | 215 | 176 | 0 | 0 | | 242 | 182 | 215 | 176 | 0 | 0 | | 243 | 182 | 0 | 176 | 0 | 0 | | 244 | 182 | 243 | 176 | 0 | 0 | | 62 | 0 | 0 | 176 | 0 | 0 | | 63 | 0 | 0 | 176 | 0 | 0 | | 246 | 182 | 243 | 176 | 0 | 0 | | 247 | 182 | 243 | 177 | 0 | 0 | | 248 | 182 | 243 | 177 | 0 | 0 | | 245 | 182 | 243 | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 181 | 0 | 0 | | 181 | 0 | 0 | | 182 | 213 | 216 | | 182 | 213 | 216 | | 182 | 213 | 216 | | 182 | 213 | 217 | | 182 | 213 | 217 | | 182 | 213 | 217 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 214 | 222 | | 182 | 214 | 222 | | 182 | 214 | 222 | | 182 | 214 | 223 | | 182 | 214 | 232 | | 182 | 214 | 232 | | 182 | 214 | 232 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 243 | 244 | | 182 | 243 | 245 | | 182 | 243 | 245 | | 182 | 243 | 246 | | 182 | 243 | 247 | | 182 | 243 | 248 | | | | | | | | |
|
|
|
|
|
Did you read the posting guidelines?
Did you take note of rule 4: Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
Did you even notice the warning before you posted that your message was very long? You must have, because you would have had to acknowledge it. I don't supposed you really cared. That shows a complete disregard and disrespect for other people here.
|
|
|
|
|
I have a stored proc which takes as input a user name and a password, and checks against a table if the user name and password exist. If it is a valid username-password,there is a return code of 0 followed by the customer name,address1,city,state etc(these are the fields in the table), if it is an invalid combination of username and password i get a return code of 1....how do i get the values from the db. here is the code i have written...i manage to get only the return code.
imports Microsoft.applicationblocks.data
'-----------below is the code in my function
Dim oPar() As SqlParameter = New SqlParameter(2) {}
'Set Parameters Up
oPar(0) = New SqlParameter("@UserID", Data.SqlDbType.VarChar, 40)
oPar(0).Value = userid
oPar(1) = New SqlParameter("@PW", Data.SqlDbType.VarChar, 20)
oPar(1).Value = password
oPar(2) = New SqlParameter("@Return", Data.SqlDbType.Int)
oPar(2).Direction = Data.ParameterDirection.Output
SqlHelper.ExecuteNonQuery(SetupProperties.ApplicationsDBConnection, Data.CommandType.StoredProcedure, _
"WebUserProc", oPar)
If oPar(2).Value Is DBNull.Value Then
Return ""
Else
Return (oPar(2).Value)
End If
Please help !!!!
RH
|
|
|
|
|
Because you are using ExecuteNonQuery the stored procedure select statements will not be returned. You will need to executereader or something like that to return data.
Hope that helps.
Ben
|
|
|
|
|
I am running SQL Server 2005 and I'm using Sql Server Management Studio Express. Is there an easy way that I can quickly generate creation scripts for my database (including table!)? If not with what I have is there a tool I can grab to do this? All I'd like to do is just right click the database and select the 'Generate Creation Scripts' and get all I need to build a new database with my tables from scratch.
Thanks,
Michael
-- modified at 13:14 Friday 16th March, 2007
|
|
|
|