Database Discussion Boards

First Prev Next

Re: Lookup Columns In Access via Linked Data?

Matt U.25-Jun-11 13:26

Matt U.

25-Jun-11 13:26

Alright, that sounds reasonable. I will look into it more. Until then I will use my current "report" generation. Smile | :)

Problem with TIMESTAMP WITH LOCAL TIMEZONE datatype in Oracle 9i

coder21k21-Jun-11 9:42

coder21k

21-Jun-11 9:42

I am seeing a weird behavior in reading a column(of type TIMESTAMP WITH LOCAL TIME ZONE) from Oracle 9i version as compared to othe versions of Oracle (10g and 11g). I have developed a windows client which connects to the foriegn database(ORACLE) and do the transactions(WRITE and READ). Though the value which is written(storage value) is same in all the versions, the value which I read from the database is different wih Oracle 9i. If anyone has faced this issue, please help me resolving this. Thanks in advance.

Re: Problem with TIMESTAMP WITH LOCAL TIMEZONE datatype in Oracle 9i

Eddy Vluggen21-Jun-11 12:23

Eddy Vluggen

21-Jun-11 12:23

..and what's the timezone set to? Checked "Daylight Saving Time"?

Bastard Programmer from Hell Suspicious | :suss:

Re: Problem with TIMESTAMP WITH LOCAL TIMEZONE datatype in Oracle 9i

coder21k22-Jun-11 3:22

coder21k

22-Jun-11 3:22

No, the Daylight savings time is not checked. The timezone is set to 'US/Canada' "-5:00 Hrs".

Re: Problem with TIMESTAMP WITH LOCAL TIMEZONE datatype in Oracle 9i

jschell22-Jun-11 9:26

jschell

22-Jun-11 9:26

Certainly the following is going to have some sort of impact.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#CACFFHCJ[^]

Retrieve stored procedures. Each Procedure in separate file. SQL 2008 R2

MecAlex21-Jun-11 3:07

MecAlex

21-Jun-11 3:07

How to retrieve stored procedures from exist database each of them into separate file?
I know how to retrieve all in one file, but I do not need it.

Do I have to save each of them in separate file manually? All ~1500... Is there any tool? Any suggestions?

Confused | :confused:

We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic

Re: Retrieve stored procedures. Each Procedure in separate file. SQL 2008 R2

UNCRushFan21-Jun-11 5:26

UNCRushFan

21-Jun-11 5:26

From SQL Server Management Studio 2008 R2: you can RT-click the database in question, choose Tasks -> Generate Scripts and select the objects you'd like to script out that way. There is an option to save each object to a separate file ("single file per object" option, I believe).

My Ox is Flummed!

TheComputerMan20-Jun-11 15:18

TheComputerMan

20-Jun-11 15:18

I have an SQL Server 2005 Express instance which has a database on it and no problems - all the data is visible and everything works.

I have just created another database and in the Visual Studio environment everything is fine and I can 'see' the data.

If I compile the setup and install the program on the same machine the program sees no data at all. It has to be something to do with these User instances I am thinking, yet I have not (knowingly) invoked one.

Both have an identical connection format:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & strDatabase & ";Data Source=" & strServer & ";Type System Version=SQL Server 2005;MultipleActiveResultSets=True

In the development environment there are 'jobs' set up but it does not see them in the 'non-development' environment.

I have checked the properties of both databases and I cannot see any differences between them.

I am stumped and could do with someone to come up with the magic solution!! Smile | :)

Re: My Ox is Flummed!

jschell21-Jun-11 8:49

jschell

21-Jun-11 8:49

Rephrasing your question to be sure I understand it.

You have a SQL server installed on your local box (no other.)
You have data in tables in that database.
When you use SQL Server Managment Studio you can see the data in the tables.
You have a program which when run in Visual Studio produces data from the tables.

When you run the same program from the command line, on the local box, you do not see the data.

If all of that is true...

Are you catching exceptions and ignoring them?

Re: My Ox is Flummed!

TheComputerMan21-Jun-11 9:00

TheComputerMan

21-Jun-11 9:00

Yes pretty much true. The program is a windows exe. No I am not ignoring exceptions, they are written to the Event log - and there are none.

The program runs just fine, but there is no data visible, in other words when I go to the configuration screen which draws its data from the SQL Express database there is no data, yet it is there running inside Visual Studio. It is as if the database is fresh with no data in it.

Re: My Ox is Flummed!

jschell22-Jun-11 9:15

jschell

22-Jun-11 9:15

Then you are making an assumption which is not true.

The following are possible assumptions. There could be others.
- You are connecting to different databases.
- You are connecting to different servers.
- The data in VS is not commited.

Re: My Ox is Flummed!

TheComputerMan22-Jun-11 9:23

TheComputerMan

22-Jun-11 9:23

Sorry what assumption am I making?

There is only one database
There is only one server which is the machine the program, VS and data are on.
The data in VS is not committed because it is already in the database. I am reading it not writing it.

Thanks for your time on this one Smile | :)

but I have decided to change over to MySql. I can't be doing with the vagaries of Microsoft.

Re: My Ox is Flummed!

jschell22-Jun-11 9:29

jschell

22-Jun-11 9:29

TheComputerMan wrote:
Sorry what assumption am I making?

It would of course be your assumption, so there is no way for me to know what it is.

You have an impossible situation.
Since computers are deterministic that means that you made an assumption which is wrong.
You can't find assumptions by looking at code (although code might have been written with an assumption in mind.)

Trying to get the output of this code down to 1 line vs 1886 it currently displays. Can anyone help?

SQL Ed20-Jun-11 9:49

SQL Ed

20-Jun-11 9:49

Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help?

/* This template is an aging report for private pay open balances*/

USE AR

SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR,
--CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE,
--CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE,
--DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE,

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [0-30 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [31-60 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [61-90 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [91-180 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [181-360 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [OVER 360 DAYS]

FROM dbo.tblAROPEN AS ARO

INNER JOIN dbo.tblCLMHDR AS CLM
ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH
AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH

WHERE ARO.INS_CO_AR IN ('0','000')
AND ARO.TOTALAMOUNT >0

GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH

--ORDER BY ARO.TOTALAMOUNT DESC

Re: Trying to get the output of this code down to 1 line vs 1886 it currently displays. Can anyone help?

Tim Carmichael20-Jun-11 10:25

Tim Carmichael

20-Jun-11 10:25

Assuming the fields you ultimately want are:
SUM_OF_TOTALAMOUNT
INS_CO_AR
[0-30 DAYS]
[31-60 DAYS]
[61-90 DAYS]
[91-180 DAYS]
[181-360 DAYS]
[OVER 360 DAYS]...

Then, use your query as a sub-select and do a select from there.
SELECT sum(SUM_OF_TOTALAMOUNT) as TotalAmount,
sum([0-30 DAYS]),
sum([31-60 DAYS]),
sum([61-90 DAYS]),
sum([91-180 DAYS]),
sum([181-360 DAYS]),
sum([OVER 360 DAYS]),
INS_CO_AR
from ( your query ) as DT
group by INS_CO_AR

Or something to that effect...

Re: Trying to get the output of this code down to 1 line vs 1886 it currently displays. Can anyone help?

SQL Ed21-Jun-11 6:24

SQL Ed

21-Jun-11 6:24

Do you mean to put all the code I have written into a sub query ????

Re: Trying to get the output of this code down to 1 line vs 1886 it currently displays. Can anyone help? [modified]

TrustTheMath21-Jun-11 9:00

TrustTheMath

21-Jun-11 9:00

I'd probably do it like this. The main difference is making each [xx-yy] field a subquery, and using BETWEEN rather than doubling up the datediff's.

select SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,
[0-30 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) <= 30),
[31-60 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 31 and 60),
[61-90 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 61 and 90),
[91-180 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 91 and 180),
[181-360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 181 and 360),
[over 360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) >= 361)
from <your table joins>
group by <your grouping>
order by <your ordering> desc

modified on Tuesday, June 21, 2011 3:23 PM

SQL Update Statement, OleDbDataAdapter, and ms access 2007

VonHagNDaz17-Jun-11 8:05

VonHagNDaz

17-Jun-11 8:05

Hi guys,

I'm trying to understand an Update query generated from vs2010. I'm new to sql and all of the msdn answers are hard-coded, and I need some help to do this with variable data.

I've figured out my insert statement

DataSet dataChanges = new UserData();
                dataChanges.DataSetName = "dataChanges";

                dataChanges = this._userDataSet.GetChanges(DataRowState.Modified | DataRowState.Added | DataRowState.Deleted);

                this._dbConnection.ConnectionString = this._connectionString;
                this._dbConnection.Open();

                //insert new rows
                OleDbCommand command = new OleDbCommand("INSERT INTO tblUsers(ID_NAME, ID_PASSWORD, ID_RIGHTS, ID_JOBS)VALUES(?, ?, ?, ?)", this._dbConnection);
                command.Parameters.Add(this._userDataSet.tblUsers.ID_NAMEColumn.ColumnName,
                                        OleDbType.VarChar,
                                        this._userDataSet.tblUsers.ID_NAMEColumn.MaxLength, 
                                        "ID_NAME");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_PASSWORDColumn.ColumnName,
                                        OleDbType.VarChar,
                                        this._userDataSet.tblUsers.ID_PASSWORDColumn.MaxLength,
                                        "ID_PASSWORD");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_RIGHTSColumn.ColumnName,
                                        OleDbType.Integer,
                                        this._userDataSet.tblUsers.ID_RIGHTSColumn.MaxLength, 
                                        "ID_RIGHTS");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_JOBSColumn.ColumnName,
                                        OleDbType.Integer,
                                        this._userDataSet.tblUsers.ID_JOBSColumn.MaxLength,
                                        "ID_JOBS");
                this._dbAdapter.InsertCommand = command;

_userDataSet is a typed dataset.
The vs generated UPDATE query looks like this:

//update rows;
                command = new OleDbCommand("UPDATE tblUsers SET ID_NAME = ?, ID_PASSWORD = ?," +
                                            "ID_RIGHTS = ?, ID_JOBS = ? WHERE ((ID_NAME = ?) AND" + 
                                            "((? = 1 AND ID_PASSWORD IS NULL) OR (ID_PASSWORD = ?))" +
                                            "AND ((? = 1 AND ID_RIGHTS IS NULL) OR (ID_RIGHTS = ?))" +
                                            "AND ((? = 1 AND ID_JOBS IS NULL) OR (ID_JOBS = ?)))", this._dbConnection);

All the msdn examples are hard coded, ...SET ID_NAME = "some static text string"...

1) I know the "?" are placeholders for the parameters, but which values go where?
2) After I build my Add, Update, and Delete commands, I call

this._dbAdapter.Update(dataChanges, "tblUsers");

which should execute all three commands based on the changes to the dataset, so I'd like to know how to do this programatically.

Kind of a big question, but I've been banging my head on the wall for the better part of a day.

[Insert Witty Sig Here]

Re: SQL Update Statement, OleDbDataAdapter, and ms access 2007

Matt U.19-Jun-11 6:40

Matt U.

19-Jun-11 6:40

In order to fill in the parameters you would use the same type of code from the INSERT statement generation:

<br />
command.Parameters.Add(this._userDataSet.tblUsers.ID_NAMEColumn.ColumnName,<br />
                                        OleDbType.VarChar,<br />
                                        this._userDataSet.tblUsers.ID_NAMEColumn.MaxLength, <br />
                                        "ID_NAME");<br />
 <br />
                command.Parameters.Add(this._userDataSet.tblUsers.ID_PASSWORDColumn.ColumnName,<br />
                                        OleDbType.VarChar,<br />
                                        this._userDataSet.tblUsers.ID_PASSWORDColumn.MaxLength,<br />
                                        "ID_PASSWORD");<br />
<br />
// Etc., etc.<br />

You would change the OleDbType to the appropriate type for the parameter. And you change the last parameter to the name of the field. Does that make sense?

Hopefully this helps. I answered based on what I could gather from your questions.

Keep history table [modified]

Rahul.RK14-Jun-11 20:05

Rahul.RK

14-Jun-11 20:05

Hello,

I need to insert the newly inserted/Updated/Deleted records into History table in following format.

ID - Field Name - Old Value - New Value - Date

So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure?

Demo code would help more.

As described above,

There would be a table names "History" having columns,
ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field]

Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value.

So all the database table would have single History table.

Hope this made more clear. Thanks.

Thanks in advance.

modified on Thursday, June 16, 2011 12:46 AM

Re: Keep history table

_Damian S_14-Jun-11 20:32

_Damian S_

14-Jun-11 20:32

It all depends on what database you are using. You can use a trigger, or you can simply update your code to call another stored procedure to put an entry into the History table.

Clearly if you are deleting a record, you will need to make a copy of it before you actually delete it!!

The power is in your hands!!

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Re: Keep history table

FeatherstoneBC15-Jun-11 7:37

FeatherstoneBC

15-Jun-11 7:37

What are the business requirements?

Will end-users see/use the data, a silent audit log,.. ?

What will it be used for?

Brad

Re: Keep history table

jschell15-Jun-11 9:08

jschell

15-Jun-11 9:08

Depends on other information.

Also you might want to verify if the user that originated the change must be tracked.

Are you sure you want to keep a record by column versus just keeping the entire row? Your solution adds complexity and your actual use cases and retention might not make it worthwhile.