Re: SMO get backup file to store on another pc - Database Discussion Boards

First Prev Next

Re: Correlated sub query issue

AJ du Plessis14-May-08 2:43

AJ du Plessis

14-May-08 2:43

I'm using a MS SQL 2000 database. I would like to return a different field than the one i'm doing the MIN on.

Re: Correlated sub query issue

SomeGuyThatIsMe14-May-08 3:09

SomeGuyThatIsMe

14-May-08 3:09

You'll probably have to return two fields with the second in a group by clause, or you could use a sub query

SELECT Tap_IsInPunch
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT = (SELECT MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime)

this way would not be the fastest, but it should work

SELECT Tap_IsInPunch, MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime
GROUP BY Tap_IsInPunch

should also work and run faster you'll just have to have your code ignore the second column.

Hope this helps

Re: Correlated sub query issue

Ashfield14-May-08 3:10

Ashfield

14-May-08 3:10

Try something like this

SELECT <br />
Tap_IsInPunch<br />
FROM TimeAtendancePunches tp1<br />
WHERE Tap_IsInPunch IS NOT NULL<br />
and Tap_DateAndTime_DT = (<br />
SELECT <br />
MIN(Tap_DateAndTime_DT)<br />
FROM TimeAtendancePunches tp2<br />
WHERE Tap_DateAndTime_DT >= @fromDateTime AND <br />
Tap_DateAndTime_DT <= @toDateTime AND <br />
Tap_IsInPunch IS NOT NULL<br />
and tp2.Tap_IsInPunch  = tp1.Tap_IsInPunch )

Bob
Ashfield Consultants Ltd

Re: Correlated sub query issue

AJ du Plessis14-May-08 4:20

AJ du Plessis

14-May-08 4:20

Thank you Ashfield

That works perfectly. What you did there is called an alias correct? I have added a join to this query
to check a value in a different table. Thanks again Ashfield

Re: Correlated sub query issue

Ashfield14-May-08 21:09

Ashfield

14-May-08 21:09

You are welcome. Some would call it an alias, personally I would call it a correlated sub query.

BTW, just be warned if using SQL Server 2000, ther is a bug that means if you nest more than 4 deep with sub queries it doesn't work - and before I get a load of comments to the effect that you should never nest sub queries that deep, it was an experiment to see if you could Smile | :)

Bob
Ashfield Consultants Ltd

SMO get backup file to store on another pc

JoZ CaVaLLo13-May-08 23:33

JoZ CaVaLLo

13-May-08 23:33

Hi.

I'm creating backup files of my database using SMO. The problem is that I can only store those files on the pc running the SQL Server.

Is there a way to get SMO to store the backup file on the pc running my SMO application?

Here is my code:

<br />
Backup backup = new Backup();<br />
backup.Devices.AddDevice("C:\\temp\\myBackup.bak", DeviceType.File);<br />
backup.Database = "MyDatabase";<br />
backup.SqlBackup(server);<br />

Thank you for any help!

Life is not short... the problem is only how you organize yourself

Re: SMO get backup file to store on another pc

Mycroft Holmes14-May-08 20:24

Mycroft Holmes

14-May-08 20:24

You have to consider that running backup is not done by your PC, it is done by the server. So think like a server, you need to have your drive mapped to the server, when it looks at C: it is the SERVERS local drive.

Never underestimate the power of human stupidity
RAH

Re: SMO get backup file to store on another pc

JoZ CaVaLLo14-May-08 22:42

JoZ CaVaLLo

14-May-08 22:42

That's Ok... My question was if somebody knows enough SMO to solve what I need...

The user may work on different pc's... I have to give him the possibility to create a backup file and store it on the pc where he is working (CD, USB drive,...) ...

Same way I need to send a backup file to the server from whichever pc in order to execute the backup.

Is it possible? Or is there any other solution?

Life is not short... the problem is only how you organize yourself

Re: SMO get backup file to store on another pc

Mycroft Holmes14-May-08 22:53

Mycroft Holmes

14-May-08 22:53

AFAIK you are going to have to have a mapped drive from the server to the client, if the client changes you have a problem. Sorry, no solution from here.

Ahh
Have SQL create the backup to a shared server folder and then use a UNC path to copy to the local client using your UI. Sigh | :sigh:

Never underestimate the power of human stupidity
RAH

Adding a out put parameter in parameter array for OracleHelper class

shames(Sam)13-May-08 22:12

shames(Sam)

13-May-08 22:12

i am trying to add a out put parameter for parameter array like this

string SpName = "USP_SHIPPINGMETHOD";//store procedure name
OracleParameter[] param = {
new OracleParameter("STORESHIPPINGMETHODID",this.StoreShipId),
new OracleParameter("STOREID", this.StoreId),
new OracleParameter("PRICE",this.Price),
new OracleParameter("ONLINESHIPPINGMETHOD",this.OnLineShippingMethodID),
new OracleParameter("ACTIVE",this.Active),
new OracleParameter("PERCENT",this.percent),
new OracleParameter("SHIPPING_METHOD_NAME",this.ShippingMethodName),
new OracleParameter("METHOD_TYPE",this.MethodType),
new OracleParameter("CUSTOMCRITERIAID",this.CriteriaID),
new OracleParameter("QTY",this.Qty),
new OracleParameter("ADDITIONALCOST",this.AdditionalCost),
new OracleParameter("ADDITIONALQTY",this.AdditionalQty),
new OracleParameter("TEMPID",this.AdditionalQty).Direction=ParameterDirection.Output,

};
int r = OracleHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, SpName, param);
r = int.Parse(param["TEMPID"].Value.ToString());

Error Cannot implicitly convert type 'System.Data.ParameterDirection' to 'System.Data.OracleClient.OracleParameter' (For TempId);

thanks
sam Wink | ;)

Web Developer

Slow DTS

Member 290588913-May-08 21:08

Member 2905889

13-May-08 21:08

Hi we have been running the DTS package for Direct Deposits, for a while now and suddenly is very slow and data is not updated on applications for users.We get data from ftp files to the tables.

Thank you

Re: Slow DTS

Ashfield13-May-08 21:16

Ashfield

13-May-08 21:16

This is more of a comment than a question! Not a lot to go on is there? To help we need to know a bit about the dts package, the tables etc etc.

A guess? Try checking your error messages fromt the dts steps.

Bob
Ashfield Consultants Ltd

Validating if a varchar column contains int values

JacquesDP13-May-08 20:20

JacquesDP

13-May-08 20:20

Hi,

I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid.

What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int.

something like if value is int, is there a way that I can do this?

So lets say the column contains the following values:
abcdef123321
abcdef78998745
abcdef4568547
abcdef123548/

it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end

Thanks in advance

No matter how long he who laughs last laughs, he who laughs first has a head start!

Re: Validating if a varchar column contains int values

Ashfield13-May-08 21:11

Ashfield

13-May-08 21:11

You can use the sql ISNUMERIC

declare @variable varchar(100)<br />
set @variable = 'abcdef78998745'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
<br />
set @variable = 'abcdef4568547'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
set @variable = 'abcdef123548/'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 0 as not numeric

Hope this helps

Bob
Ashfield Consultants Ltd

Re: Validating if a varchar column contains int values

JacquesDP13-May-08 21:13

JacquesDP

13-May-08 21:13

I think this will do the trick, thanks a million.

No matter how long he who laughs last laughs, he who laughs first has a head start!

Re: Validating if a varchar column contains int values

Ashfield13-May-08 21:27

Ashfield

13-May-08 21:27

No problem

Bob
Ashfield Consultants Ltd

Re: Validating if a varchar column contains int values

Blue_Boy13-May-08 21:15

Blue_Boy

13-May-08 21:15

here it is

select *,substring(value,7,len(value)) as intValue from myTable<br />
where charindex('/',value) =0

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

To get output of Sysdate in a variable

megha_gharote13-May-08 20:03

megha_gharote

13-May-08 20:03

Dear Friends,
I m working on VC++ application using Oracle with ODatabase.
I m able to execute the query "SELECT SYSDATE FROM DUAL"...
but how to take this data in a Date variable in my application...

Can anybody help me out???

Megha

MFC application crashed in the dll's function ConnectTransact

Arun Kuriyakkotte Ramachandran13-May-08 15:49

Arun Kuriyakkotte Ramachandran

13-May-08 15:49

I've an MFC application which uses SQL connection using Namedpipes. I'm getting crash in the application log as follows.

Fault address: 7331293E 01:0000193E C:\bin\DBNMPNTW.DLL
Call stack:
Address Frame
7331293E 013D68DC ConnectionTransact+1AE

I'm not getting any clue of why the crash is happening at this point. After opening for a long time, it just crashes at this point !!!!!

Appreciate if some body can shed a light on the reason, what all could be the reason.

Arun K Ramachandran

Expense of network traffic

Member 391904913-May-08 11:40

Member 3919049

13-May-08 11:40

I am trying to understand the impact of network traffic on data performance in my environment.

For example, if I am inserting 2 million rows into a database then how can I determine how much time is spent on network traffic? And how can I determine the savings of performing a batch update?

Re: Expense of network traffic

Mark J. Miller14-May-08 4:42

Mark J. Miller

14-May-08 4:42

I don't know what to tell you about HOW to measure the network overhead as I am not a network professional, I imagine you'd have to inspect the packets going over the wire. But I CAN tell you that batches get exponentially faster than RBAR (row by agonizing row) transfers the more data you're working with. Imagine what the network overhead is for one round trip to your database. When you perform batches that overhead becomes a constant. But when you perform RBAR operations over the network that overhead is added for EVERY row. If you are working with that much data then it shouldn't even be a question of wither or not to batch update. Use BULK INSERT or SSIS/DTS but don't do it RBAR.

Mark's blog: developMENTALmadness.blogspot.com

Oracle message, error that makes no sense

eggsovereasy13-May-08 8:27

eggsovereasy

13-May-08 8:27

I have a stored procedure in oracle that looks like this:

<br />
PROCEDURE validate_master_account_id (v_master_account_id IN NUMBER, v_is_valid OUT NUMBER);      <br />

And a procedure that looks like this:

<br />
public static bool TestProcedure()<br />
{<br />
	  string procedureName = "SMT.CPR_MASTER_ACCOUNT_PKG.validate_master_account_id";<br />
	  using (OracleConnection connection = new OracleConnection(AppSettings.ConnectionString))<br />
	  {<br />
		    connection.Open();<br />
<br />
		    using (OracleCommand command = new OracleCommand(procedureName, connection))<br />
		    {<br />
			      command.Parameters.Add("v_master_account_id", OracleType.Number).Value = 16793;<br />
			      command.Parameters.Add("v_is_valid", OracleType.Number).Direction = ParameterDirection.Output;<br />
<br />
			      command.ExecuteNonQuery();<br />
			      bool result = ((int) command.Parameters["v_is_valid"].Value) == 1;<br />
			      return result;<br />
		    }<br />
	  }<br />
}<br />