How to save performance counters values to SQL Server database tab

I am monitoring sql server and windows performance counters.
My computer has "Microsoft windows XP professional". I am monitoring sql
servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
For historical analysis I want to store the values of various counters in
sql server database(table). I have created a a DSN for the sql server 2005
installed on my PC for data repository. Still I can save counter values as
report(.tsv) ot web (.html). I want to save the values to SQL server TABLE.
hOW DO i DO THAT'
--
ontario, canadaI am using microsoft management console 2.0 version 5.1.
--
ontario, canada
"db" wrote:
> I am monitoring sql server and windows performance counters.
> My computer has "Microsoft windows XP professional". I am monitoring sql
> servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
> For historical analysis I want to store the values of various counters in
> sql server database(table). I have created a a DSN for the sql server 2005
> installed on my PC for data repository. Still I can save counter values as
> report(.tsv) ot web (.html). I want to save the values to SQL server TABLE.
> hOW DO i DO THAT'
> --
> ontario, canada|||db
How about?
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
OR counter_name = 'Target Server Memory (KB)';
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name = 'SQLServer:Buffer Manager';
"db" <db@.discussions.microsoft.com> wrote in message
news:F027E65B-CADE-41AE-97E2-DD3079D42481@.microsoft.com...
> I am monitoring sql server and windows performance counters.
> My computer has "Microsoft windows XP professional". I am monitoring sql
> servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
> For historical analysis I want to store the values of various counters in
> sql server database(table). I have created a a DSN for the sql server 2005
> installed on my PC for data repository. Still I can save counter values as
> report(.tsv) ot web (.html). I want to save the values to SQL server
> TABLE.
> hOW DO i DO THAT'
> --
> ontario, canada|||Thanks Uri. Two questions:
1. It works for SQL server 2005 by in sql server 2000 I am getting this
error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_performance_counters'.
2. Parameter like sql Server: Buffer Manager (Buffer cache hit ratio) has a
value 3310 by this select statement. I was expecting something like (99%).
3. Many counters that available through MMC are not present in this table.
Should I be looking at some other tables.
--
ontario, canada
"Uri Dimant" wrote:
> db
> How about?
> SELECT * FROM sys.dm_os_performance_counters
> WHERE counter_name = 'Total Server Memory (KB)'
> OR counter_name = 'Target Server Memory (KB)';
> SELECT * FROM sys.dm_os_performance_counters
> WHERE counter_name = 'Page life expectancy'
> AND object_name = 'SQLServer:Buffer Manager';
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:F027E65B-CADE-41AE-97E2-DD3079D42481@.microsoft.com...
> >
> > I am monitoring sql server and windows performance counters.
> >
> > My computer has "Microsoft windows XP professional". I am monitoring sql
> > servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
> >
> > For historical analysis I want to store the values of various counters in
> > sql server database(table). I have created a a DSN for the sql server 2005
> > installed on my PC for data repository. Still I can save counter values as
> > report(.tsv) ot web (.html). I want to save the values to SQL server
> > TABLE.
> > hOW DO i DO THAT'
> >
> > --
> > ontario, canada
>
>|||1. Table in sql server 2000 looks like "sysperfinfo".
4. I need to append everyday all the counter values to the table with a
field for datetime at which counter values were captured.
ontario, canada
"db" wrote:
> Thanks Uri. Two questions:
> 1. It works for SQL server 2005 by in sql server 2000 I am getting this
> error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'sys.dm_os_performance_counters'.
> 2. Parameter like sql Server: Buffer Manager (Buffer cache hit ratio) has a
> value 3310 by this select statement. I was expecting something like (99%).
> 3. Many counters that available through MMC are not present in this table.
> Should I be looking at some other tables.
> --
> ontario, canada
>
> "Uri Dimant" wrote:
> > db
> > How about?
> > SELECT * FROM sys.dm_os_performance_counters
> >
> > WHERE counter_name = 'Total Server Memory (KB)'
> >
> > OR counter_name = 'Target Server Memory (KB)';
> >
> > SELECT * FROM sys.dm_os_performance_counters
> >
> > WHERE counter_name = 'Page life expectancy'
> >
> > AND object_name = 'SQLServer:Buffer Manager';
> >
> >
> > "db" <db@.discussions.microsoft.com> wrote in message
> > news:F027E65B-CADE-41AE-97E2-DD3079D42481@.microsoft.com...
> > >
> > > I am monitoring sql server and windows performance counters.
> > >
> > > My computer has "Microsoft windows XP professional". I am monitoring sql
> > > servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
> > >
> > > For historical analysis I want to store the values of various counters in
> > > sql server database(table). I have created a a DSN for the sql server 2005
> > > installed on my PC for data repository. Still I can save counter values as
> > > report(.tsv) ot web (.html). I want to save the values to SQL server
> > > TABLE.
> > > hOW DO i DO THAT'
> > >
> > > --
> > > ontario, canada
> >
> >
> >|||I was able to save it in sql server table through MMC perfmon in the way i
want to do it.
--
ontario, canada
"db" wrote:
> 1. Table in sql server 2000 looks like "sysperfinfo".
> 4. I need to append everyday all the counter values to the table with a
> field for datetime at which counter values were captured.
> ontario, canada
>
> "db" wrote:
> > Thanks Uri. Two questions:
> >
> > 1. It works for SQL server 2005 by in sql server 2000 I am getting this
> > error message:
> >
> > Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'sys.dm_os_performance_counters'.
> >
> > 2. Parameter like sql Server: Buffer Manager (Buffer cache hit ratio) has a
> > value 3310 by this select statement. I was expecting something like (99%).
> >
> > 3. Many counters that available through MMC are not present in this table.
> > Should I be looking at some other tables.
> >
> > --
> > ontario, canada
> >
> >
> > "Uri Dimant" wrote:
> >
> > > db
> > > How about?
> > > SELECT * FROM sys.dm_os_performance_counters
> > >
> > > WHERE counter_name = 'Total Server Memory (KB)'
> > >
> > > OR counter_name = 'Target Server Memory (KB)';
> > >
> > > SELECT * FROM sys.dm_os_performance_counters
> > >
> > > WHERE counter_name = 'Page life expectancy'
> > >
> > > AND object_name = 'SQLServer:Buffer Manager';
> > >
> > >
> > > "db" <db@.discussions.microsoft.com> wrote in message
> > > news:F027E65B-CADE-41AE-97E2-DD3079D42481@.microsoft.com...
> > > >
> > > > I am monitoring sql server and windows performance counters.
> > > >
> > > > My computer has "Microsoft windows XP professional". I am monitoring sql
> > > > servers (2000 (sp3 and sp4) and 2005) running on windows NT 4 and 5.2.
> > > >
> > > > For historical analysis I want to store the values of various counters in
> > > > sql server database(table). I have created a a DSN for the sql server 2005
> > > > installed on my PC for data repository. Still I can save counter values as
> > > > report(.tsv) ot web (.html). I want to save the values to SQL server
> > > > TABLE.
> > > > hOW DO i DO THAT'
> > > >
> > > > --
> > > > ontario, canada
> > >
> > >
> > >sql

how to save listbox multiple select values

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germanoshouldn't lbAttendees have .SelectedItems?

Next, you should be passing the values to the database through sql or other dataaccess means.|||>>Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there.

I would strongly suggest option 2 (Normalize).

>>I'm not sure how to loop through each value and write it to the database table.

Well you have some options.

i) Loop through your items on the client and perform inserts (one row at a time).

ii) Package up the values as an xml chunk and use openxml to insert (set based method, less chatty)

ii) Package up the values as a delimited string and perform parsing and insert at the server.

And there are others (using OO: collections, persistance frameworks mechanisms, etc..)sql

How to run a report from webform ........URGENT

Hi,

I am using SSRS 2000 and VS2003.I am new to SSRS.I have to generate a report on button click event.I have to pass parameters(values from textbox,dropdown) to the report.I am a little confused like how to call report from webpage and how to pass web page control values as parameters.Please guide me.Thank you.

hi use reportviewer control. Its easy. For more details see abt this control in MSDN

|||

Hi,

I serached in MSDN.I didn't get how to get REPORTVIEWER control in webform.I don't see any built in reportviewer in VS2003.Can you please guide me how to add that control to my webpage (TOOLBOX).Thank you

|||

hi sure,

But I have office only on Monday. So will let u know then... my id issivaatzenith@.gmail.com. Pls ping to this id... I will let u know

|||

Hi,

Sure I will mail you.I have few more doubts.Thank you.

|||

hi hope this link will solve ur problem

http://www.codeproject.com/sqlrs/SQLRSViewer.asp

sql

How to return xml datatype column values ?

Hi ,

I want to return the xml datatype values in the output of my query.

I am querrying on one table which has xml datatype by selelcting the column in select clause and would like to get the values in output.

How do i get output with out/with including the column name in group by clause?

Can some one help me here.

--Smita.

You have a XML column and you want to break the XML as columns ? is this what you want ?|||

Rick,

I have a coulmn with xml datatype and want to get entire column value in one output.

-Smitha.

|||

Something like this ?

declare @.x xml

set @.x='<Root>

<Node>

<Node name="S.No">1</Node>

<Node name="S.Level">1</Node>

<Node name="S.FName">Data1</Node>

<Node name="S.LName">Data2</Node>

<Node name="Sl #" />

</Node>

<Node>

<Node name="S.No">2</Node>

<Node name="S.Level">2</Node>

<Node name="S.FName">Data22</Node>

<Node name="S.LName">Data33</Node>

<Node name="Sl #" />

</Node>

</Root>'

select cast(r.query('for $data in ./Node return data($data) ') as varchar(max)) as [Column]

from @.x.nodes('/Root/Node') as X(r)

How to return sets by filtering those tuples with null values in a MDX statement?

Hi, dear friends,

How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).

Hope it is clear for your help.

And I am looking forward to hearing from you shortly for your help.

Thanks a lot.

With kind regards,

Yours sincerely,

It depends what you mean by "filter" - do you want the null values included or excluded?

And what does B,C mean? Do you mean if either is null or both?

The following is one solution if you want to return members from DimA where both measures are empty (null).

Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)

but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.

INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))

|||

Hi, Darren,

Thanks a lot for your help.

With kind regards,

Yours sincerely,

How to return sets by filtering those tuples with null values in a MDX statement?

Hi, dear friends,

How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).

Hope it is clear for your help.

And I am looking forward to hearing from you shortly for your help.

Thanks a lot.

With kind regards,

Yours sincerely,

It depends what you mean by "filter" - do you want the null values included or excluded?

And what does B,C mean? Do you mean if either is null or both?

The following is one solution if you want to return members from DimA where both measures are empty (null).

Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)

but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.

INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))

|||

Hi, Darren,

Thanks a lot for your help.

With kind regards,

Yours sincerely,

How to return multiple values from stored procedures

How to return multiple values from stored procedures to reports in sql server 2005

Hello,

Do you mean multiple resultsets from a single stored procedure? This is not supported, Reporting Services will only use the first one. You could break up your stored procedure so that each resultset would populate it's own dataset in the report.

Hope this helps.

Jarret

|||Not possible, one resultset only.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||thanx

How To Return Multiple Table Values From A Function

Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.

Create Function F_clusters()
Returns @.ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @.ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End

This Works Fine :-
And Gives The Reqd. Results.

But,

If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.

Create Function F_clusters()
Returns @.ki Table(names Nvarchar(200),total Int),@.k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @.cnt Int
Set @.cnt = 1
While @.cnt <= 2
If @.cnt =1
Begin
Insert @.ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @.cnt = @.cnt + 1
End
If @.cnt =2
Begin
Insert @.k2
Select @.naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @.cnt2 = @.cnt2 + 1
End
End
Return
End

Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.

Thanks.I don't believe it's possible, and if it is then it's not a good idea.

One of the big advantages of UDF table functions is that you can join them directly into SQL statements.

select * from my table inner join myfunction on mytable.pk = myfunction.pk

Now, what would happen to this if myfunction returned more than one data set?

On a side note, it's nOT nECSSARY tO cAPITALIZE tHE fIRST lETTER oF eACH wORD. In English, just capitalize the first letter of each sentence, and all proper nouns. ;)|||HI,

THANKS FOR THE TIP ON ENGLISH, BUT IS IT POSSIBLE TO GET MORE THAN 1 DATA SET FROM A FUNCTION ... AS I AM REALLY HAVING A PROBLEM WITH IT ?.

THANKS FOR THE HELP ON FUNCTIONS

BUT COULD YOU TELL ME ABOUT HOW TO GET DIFFERENT SET OF DATA FROM THE SAME FUNTIONS.

LETS'S A SIMPLE ONE :-

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Not Null

Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Null

)

I WILL GET IT RIGHT IF I GIVE THE FIRST SELECT STATEMENT ONLY

BUT IF I USE THE SECOND SELECT STATEMENTS IN THE FUNCTION IT WILL NOT EXECUTE.

SO,IS IT POSSIBLE TO GET TWO DIFFERENT SETS OF DATA FROM A FUNCTION.

IF YES,HOW ?

CAN WE DECLARE TWO TABLES WHICH CAN RETURN VALUES ?
IF YES,HOW?|||//What about a subselect?
//The subselect returns two columns, one with the Null count
//and one with the Not Null count

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct c1.Chremail) As CNTS
(Select count(*)
//The disctinct does not work with a Null Column, its like counting nothing
From CLEANCustomer As c2
Where c2.Chremail Is Null) As CNTSNull

From CLEANCustomer As c1
Where c1.Chremail Is Not Null
)

//A second way is the Union:
//The Result of the Union are two rows with two fields, one as the count,
//one as the the type
Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct Chremail) As CNTS,
'Not Null' As Nulltype
From CLEANCustomer
Where Chremail Is Not Null
UNION
Select count(*)
'Not Null'
From CLEANCustomer
Where Chremail Is Null
)

/*
This is only SQL, try to take some SQL lessons

Sneaky Pie
*/|||No. You can only return one dataset from a function. Rewrite you code as a stored procedure, which can return multiple datasets:

Create Procedure F_clusters()
AS
Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Not Null

Select count(distinct Chremail) As CNTS From CLEANCustomer
Where Chremail Is Null

...but this sort of thing is not much use in application development. You rethink what you are doing.|||HI,

THANKS FOR THE HELP BY BOTH OF YOU,BUT STILL FACING THE PROBLEM ,

THE subselect FUNCTION IS NOT WORKING :-
I TRIED SEVERAL PERMUTATIONS AND COMBINATIONS

Create Function F_clusters()
Returns TABLE
AS
RETURN
(
Select count(distinct c1.Chremail) As CNTS
(Select count(*)
//The disctinct does not work with a Null Column, its like counting nothing
From CLEANCustomer As c2
Where c2.Chremail Is Null) As CNTSNull

From CLEANCustomer As c1
Where c1.Chremail Is Not Null
)

AND YES YOU ARE RIGHT ABOUT USING STORED PROCS TO GET MULTIPLE DATASETS :-

BUT YOU ARE FORGETTING THOUGH WE HAVE PROCS WHICH GIVES US THE RESULTS , WE ARE TRYING TO CONVERT THEM INTO FUNCTIONS

AND THEREFORE FACING PROBLEMS WITH MULTIPLE DATASETS,

FUNCTION CAN RETURN A TABLE , BUT NOT TWO MULTIPLE DATA SETS
IS THERE A WAYOUT OF IT ?

OR ELSE WE MIGHT HAVE TO WRITE SEVERAL FUNCTIONS TO REPLICATE WHAT 1 PROC RESULTS WAS REFLECTING WHICH WOULD BE A REAL PAIN.

IS THERE A WAY OUT ?|||Why are you so hot to convert your stored procs into functions? Functions are not necessarily better than stored procedures.

I can't shake the feeling that there are underlying problems with your data architecture, application design, or project goals, and unless you resolve these you are not going to be any better off than you were.|||Hi,

We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions And Use Them As An I/p To Other Procs Or Functions.

But,if We Are To Use Them As Procs Then We Have The Limitation Of Not Using Insert..exec Statement More Than Once.

So, There Fore Functions Are Necessary ,but It Now Seems That They Cannot Give Up More Than 1 Datasets ,which Is Again Causing Us Problems.

Out We Are Trying To Automate The Process.

Can You Give Something Better Option To Work ?

As , It Seems We Are Working Round And Round .|||sorry
I have no idea|||I think that the problem lies in the fact that the routine (stored procedure or function) is trying to return more than one result set.

Any given routine should do exactly one thing. If a routine manipulates data (INSERT/UPDATE/DELETE) it should do that one thing, and nothing more. You may have a few stored procedures that are "wrappers" that call a sequence of other routines, but even they do only one thing, wrap the other routines. Designing your code so that a routine intentionally does a dozen things is a receipe for disaster in my opinion. Any complex thing that works is only a collection of simple things that work!

Your biggest problem in my opinion is that you need to adopt a more disciplined methodology. It appears that you are used to writing "spaghetti code", and are having a hard time breaking that mindset. Although it is considered "old fashioned" by internet standards, I think the simplest way out of the problems you are facing right now is to embrace "structured programming", although you need to keep in mind that there are many other methodologies that offer even more benefits at the cost of more complexity.

I'd suggest that you read Software Tools (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=ZYw1fKKpGI&isbn=020103669X&itm=1) and try to adopt its principles as best you can.

-PatP|||The definition of a FUNCTION is to return ONE THING. That thing can be a value (scalar) or a table (inline or table-valued). Data connectivity (ODBC API's and SQLOLEDB) for SQL offer the ability to retrieve multiple resultsets from a statement execution against a connection object/handle. This is a flexibility feature, not a standard.

We all understand that you "HAVE TO CONVERT BLAH-BLAH-BLAH...", but you have to understand that what everybody tries to offer here is tricks and techniques that are based on specific well known rules that IT industry lives by. Complexity and/or brilliance of solutions varies, but the concept remains. Unless in the future there will be a break-through in technology that would alow sending rockets to the moon with a simple SELECT, or returning more than "ONE THING" from a function, - you'll have to learn how to abide by these industry rules.

And one last thing, - no matter how many more times you choose to post the same question (HOW TO MAKE A FUNCTION RETURN MORE THAN ONE RESULTSET), you will NOT get any more elaborate answers, other than "YOU CAN'T!"|||"We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions" - A tautological argument

"... And Use Them As An I/p To Other Procs Or Functions." - And how would you use them as input if they returned more than one result set? The problem here is with your development process, and not due to a limitation of SQL.|||Hi,
Thanks Pat ,BLINDMAN & RDJABAROV FOR THE HELP. and HAS YOU SAID ALL ,I Think I Have To Bring In More Discipline In My Programming
AND YES MAYBE GET BACK TO STRUCTURED PROGRAMMING

And Ok !!! Think,it's Time To Break Down Most Of The Routines Into Simple Steps.

BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.|||BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.If you find a way out, please publish it. The book will make you a very rich person, and the rest of us very happy people!

-PatP

how to return more than two values from a stored procedure..

Hi,

I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.

How can I do that. I'm able to get only 1 value if i use the return key word.

Eg:

create proc test1 as

Begin

Declare scount int

Declare scount2 int

-- statements in stored procedure

return scount

return scount2

End

create proc test2

Declare variables...

Exec Test1

// here i want the values (scount and scount2 ), processed in stored procedure Test1 .

How can i get this.. please let me know..

Thanks,

srikanth

I would suggest using a function rather than a stored procedure; however, it is also possible to do what you want using a stored procedure, but you will not be able to use the RETURN verb to return the values. Give a look to CREATE PROCEDURE in books online.

An inline function to return 2 values would look something like:

Code Snippet

create function dbo.test1 ()
returns table
as
return
( select 1 as scount, -- or whatever values
3 as scount2
)

The function would be invoked with something like:

Code Snippet

select scount, scount2

from test1()

Give a look to the CREATE function article in books online.

To doctor up your procedure, try something like:

Code Snippet

create proc test1
@.scount integer output,
@.scount2 integer output
as

-- statements in stored procedure

set @.scount = 14 -- or the appropriate value

set @.scount2 = 21 -- or the appropriate value

return

To invoke the stored procedure, try something like:

Code Snippet

declare @.out1 integer, out2 integer

exec @.test1 @.out1 = @.scount output,
@.out2 = @.sctount2 output

select @.out1 = [@.out1],
@.out2 = [@.out2]

|||

during the process i have to modify data in database.so i cant use functions as they cant update the data.

hence i hav chosen stored procedure.

let me know if there is a way in stored procedure itself..

thanks for ur reply..

|||

Which stored procedure is updating the data -- the stored procedure that invokes the other stored procedure or the stored procedure that gets invoked?

|||

You can use OUTPUT parameters.

In SQL Server 2005 perhaps you may use OUTPUT CLAUSE http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1642175&SiteID=1 for your purpose.

|||

hi Kent,

thanks for your information in your previous reply.

Its working the way as you said.

Thanks,

srikanth

How to retrive a value using a query string?

Hello,

I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.

This is what I have:

<script runat="server"> Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @.Password) AND (users.username = @.Username)" conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@.Username", SqlDbType.VarChar, 50) cmd.Parameters("@.Username").Value = Me.Login1.UserName cmd.Parameters.Add("@.Password", SqlDbType.VarChar, 50) cmd.Parameters("@.Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else 'Response.Write("Invalid credentials") End If myReader.Close() End Sub</script>

I would like to know how can I get now the "user.FirstName" and pass it to a session variable?
how should I code it?

thanks,

Inside your read have

Session["FirstName"]=myReader.getString(2)

Is there any reason that you're getting 7(I think) fields from the database and throwing them away?

Also, it's poor form to store unencrypted passwords in the database. At least use xor or something.

How to retrieve values from one db to another db

Hi
Could someone please give me the correct TSQL statement, for the below
"pseudo".
USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
given that tbl_1 and tbl_2 is identical.
Any hints appreciated
Regrds.
Mr. SmithAssuming both tables are owned by dbo:
INSERT INTO db_a.dbo.tbl_1
SELECT * FROM db_b.dbo.tbl_2
Jacco Schalkwijk
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Hi,
No need of USE Database command here. you could use:-
Insert into database1.tableowner.tablename select * from
database2.tableowner.tablename
Thanks
Hari
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||INSERT INTO DB1.tbl_1
SELECT * from DB2.tbl_2
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Thanks all of you! Hari, Jacco and Jens for a quick and easy answer.
Regards
Mr. Smith
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Sorry, forgot the owner
INSERT INTO DB1.dbo.tbl_1
SELECT * from DB2.dbo.tbl_2
Jens Smeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:ur9bJ6ARFHA.3288@.TK2MSFTNGP14.phx.gbl...
> INSERT INTO DB1.tbl_1
> SELECT * from DB2.tbl_2
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
> news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
Try using OpenRowSet.
User db_a
INSERT INTO tbl('col1','col2')
SELECT a.col1,a.col2
FROM OPENROWSET('SQLOLEDB','servername';'user
id';'password','SELECT
col1,col2 FROM db_b.dbo.tbl_2') a
Hope this helps.
Regards,
Sambath
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>

how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

How to retrieve timestamp

Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanksCan any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.

Is there anything I need to set it up.

thanks

I don't believe the timestamp datatype actually contains a Date/Time, but instead, a unique binary number which represents an update to the row.

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.|||If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.
=============================================

System is in production now. Even though I create a column with datetime the program will not update the column. So I thought setting up a column which is being automatically updated.

I like to delete some records based on the date last modified.

Thanks|||This is right out of the SQL Server help guide:

timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @.@.DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

You'll never get actual date/time values from the timestamp datatype. You're going to have to change something if you want to know exactly when a row was last touched.

how to retrieve random values from a database table

how to retrieve randomly rows from a sqlserver database tableHi,

Use TABLESAMPLE

Example.

SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT) ;

OR

SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 ROWS) ;

how to retrieve distinct values

hi friends

i want to retrieve distinct values from 2 columns at the same times eg.

mrn NameInEng NameInFrch

11 rockey rrrrrrr
22 ruxy rxrxrx
33 rockey rrrrrrr
44 ruxy rxrxrx
55 ruxy rxrxrx
66 tony dummy
77 sony
88 tony

column NameInEng is not null and NameInFrch may be null or may contain blanks or some dummy values

output should be like:-

NameInEng NameInFrch

rockey rrrrrrr
ruxy rxrxrx

NOTE:- query should display even exists single time but with the relevant script(eng/frch),
as i mentioned column NameInEng is not null and NameInFrch may be null or may contain blanks or some dummy values.
the result should ignore any DUMMY or BLANKS/NULL values of column NameInFrch.

i mean the result should be in unique values which have only relevant script Eng/Frch(ommiting dummy/null/blanks)
e.g. not required the following

tony dummy
sony
tony

please help me

with warm regards

rahmanselect distinct NameInEng, NameInFrch
from yourtable
where NameInFrch is not null
and NameInFrch > ''
and NameInFrch <> 'DUMMY'|||hi Boss,
how r u?
thanx for ur immidiate reply,
i tried but no row returned though huge rows are exist
1 SELECT DISTINCT fname,afname from mpi
2 where afname is not null and
3 afname > '' and
4* afname<>''
SQL> /

no rows selected

please guide me again

thanks in advance|||how r i? i r ok, how r u?

does this produce any results?

select distinct fname,afname from mpi
where afname is not null
and afname > ''|||Originally posted by r937
select distinct fname,afname from mpi
where afname is not null
and afname > ''

select distinct NameInEng, NameInFrch
from yourtable
where NameInFrch is not null
and NameInFrch > ''
and NameInFrch <> 'DUMMY'


Hi boss,
great....your both the query conditionally working fine but due to incorrect input by the clerk the output is wrong...

anyway, thanks a lot n with warm regards

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comHi
I am not totally sure what you require, as it sounds like a simple update
statement would do what you require e.g.
UPDATE B
SET [Three] = A.[Three]
FROM TABLEB B
JOIN TABLEA A ON A.id = B.id and B.id in ( 11, 12, 13 )
If you do choose to BCP the data out then your would probably need to load
the data into a staging table.
You may want to show what the final states of table a and b are.
John
"vinoth@.gsdindia.com" wrote:

> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility
]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.com
You don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegr oups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>
|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth
|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegr oups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>
|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com
|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegro ups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>