|
Query the sys.columns table for @TbleName and add if the CreatedBy, ModifiedBy columns exist on that table add that in to the dynamic SQL
|
|
|
|
|
Hi All.
Firstly.I use Database link from my SQL to Oracle to get Data.In The Oracle the field sta_datetime is date type.
Now I use
convert(varchar(10),sta_datetime,111) as Sdate,
convert(time,sta_datetime) as S_Time,
To get data to Sdate field date type and S_Time field is time(0)type.
Now I show them in the gridview and then get them back when user lick on the row to show on another child gridview.
So many convert time.So it is so difficult to get them back.
|
|
|
|
|
Your question is also confusing. So much so that I'm not sure what your question is!
However, Don't convert date, datetime or time to varchar(anything) ... use the appropriate types. It is only in the final presentation of the data that you need to worry about how it looks
|
|
|
|
|
Original type of field in Oracle Database is date.Now I select this and insert to SQL server.Then show in gridview.
The question is
How to get exactly data from oracle database link?
And How to show exactly data to the user?
|
|
|
|
|
I'm moving my sql server db to oracle. But oracle seems not to support Date Name with date data type.
Looking for solution as changing name from Date to Vdate or bDate requires lot of changes in app code.
|
|
|
|
|
"Date" will probably be a reserved word which is why you get this issue.
Outside of changing the column name,which I strongly recommend doing(calling a column "date" is asking for trouble), which will involve quite a bit of work, the only workaround is to use whatever Oracle uses to delimit column names ([],"" etc)
My experience is that it is best to fix these sorts of issues early i.e. rename the column as the problems you currently experience will be magnified in a few months time when you have more that depends on a column called "date". The amount of work it will take you to fix this issue now will be far less than later.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Guy is spot on on all accounts, Date isn't just a reserved word in Oracle but also by ANSI, you have a list here.
As a delimiter in Oracle you use the ANSI standard "". But also here I agree with Guy, don't do it, it'll save you a lot of trouble later.
|
|
|
|
|
Another benefit of changing the column name will be the opportunity to give it a semantically meaningful name. Just "Date" could be any sort of date anyone might ever think of. Calling it what it actually is (like "CreatedDate", "PurchaseDate", etc.) will make much more sense.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Hello All,
I would like to convert reports in the format .rdlc into format .rdl using Visual Studio 2015.
What I did: I've created a Reporting project(existent under Visual C# templates) where I added two reports. Those reports come with the extension .rdlc. After creating the project I checked its properties and the type of project is windows application. Further I decided to use SQl Server Reporting Services to do my reports. I installed Data Tool preview for Visual Studio 2015 and created a Reporting Services project under Business Intelligence template.
Issue:
The reports I initially created using the Reporting project have the extension .rdlc and the ones created with Reporting Services .rdl. I imported the .rdlc ones into my Reporting Services project. However if I change the extension from .rdlc to .rdl i can design but not preview. When trying to preview, Visual Studios says "an error occurred during local report processing. error in the application. Object reference not set to an instance of an object".
I would not want to loose those two reports as the design took me one day to do.If anyone knows a solution that I could use to import/convert .rdlc reports into .rdl I would appreciate.
|
|
|
|
|
|
jonatan_556 wrote: I would not want to loose those two reports as the design took me one day to do.If anyone knows a solution that I could use to import/convert .rdlc reports into .rdl I would appreciate.
This advice may help you - use some form of version control.
You need never find yourself in the position of losing any previous work if you set up the correct protocols for changes and use version control.
My person suggestion is to use GIT[^].
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi,
Sorry the delay in answering.
Many thanks on this.
|
|
|
|
|
You're welcome - version control is your friend in these sorts of situations  
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi Friends,
I have a database on which I have enabled CDC on some tables. As CDC captures auditing data on transactions basis, I am accessing that data and putting in flat file by using a Store Procedure
Now is there any way in T-SQL so that, we can wait until a transaction finishes to capture the data. Because if I capture the information half way we may miss some of the data.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Can't you use AFTER INSERT , AFTER UPDATE T-SQL (triggers) in this scenario? Am I missing something that you are not explaining in detail?
|
|
|
|
|
Sure, a trigger works on the Table level, where as I want it on the transaction level, on transaction may be using multiple tables. The other interesting and more complex thing is Change Data Capture works on log file asynchronously to write the values into Audit tables. But luckily CDC writes information as Transaction based, like it gives same start_lsn id for same transaction, which I can use.
But for example when I retrieving data from CDC Audit tables and putting it in the Flat tables then I want to wait if CDC is in between writing a Transaction on to the CDC Audit tables. Can I do it?
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
My suggestion - give each 'transaction' a GUID.
Create a table that holds the GUIDs of 'transactions' that have been completed. Write the relevant GUID into this table when a 'transaction' is completed.
Then when it comes to determining whether the data is ready to be sent to flat files just look to see if the GUID exists in your completed 'transaction' GUID table.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
Hello
I need some advice... Let's have two network : Nprod and Ndev.
The production server is on Nprod and is connected to 80 customers databases through 80 VPN, all is fine here.
But... we need also to connect the Ndev network to the customers databases, for easy (and sometimes emergency) debugging. Sometimes we have a VPN between Ndev and away DBs, sometimes not, and in this case we have to connect to a remote desktop on the Nprod network, open a connection server, pass queries one by one, and try to understand what's going wrong  = not very convenient and lost of times to react to the bug.
So my question is : Is there a way to connect my dev desktop to the Nprod, and then using the VPNs Nprod <-> Customers DB, something like a connector installed on the Nprod network which could pass queries and return results to my dev network ? I don't know where and what to search so if you have some tips or tricks to orient my search, it should be great
|
|
|
|
|
Hi All,
I have a database in which most of the tables have Created by, modified by, Create Date and Modified date columns, for some child tables we don't have. The problem is developers are performing DML operations on those tables like insert with some inappropriate values or update without using modified by column. Then when they update it incorrectly we are unable to track who modified it.
Is there any mechanism for me from Sql Scripts or any other who made those changes, so that I can track that out and let him/her know what incorrect change they made.
Any help a code snippet, a link, a small script or even suggestion helps me. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
A quick Google for "sql audit trail" came up with Audit Trail Generator for Microsoft SQL[^] right here on CP. If you need to know who's messing with your data, how and when, an audit trail is the way to go.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
You should enforce INSERT/UPDATE triggers for these auditing purposes. You cannot reply on the developers to implement this.
|
|
|
|
|
Hi All,
I have a parent table and multiple child tables I want to create a resultant Flat table from all result columns, is there any way to create all those columns in a table to put the result into a flat table?
If the Object Ids are going to be different I am fine to put them also into it.
Here is the script I am trying to use, and understandably it is giving me the error "
Column names in each table must be unique. Column name 'ApplicationId' in table 'TempDelta' is specified more than once.
"
Any help is much appreciated thanks in advance friends.
select * into TempDelta from [Application] a
inner join ApplicationComponent b on a.ApplicationId=b.ApplicationId
inner join ApplicationPage c on a.ApplicationId=c.ApplicationId
inner join Audit d on a.ApplicationId=d.ApplicationId
inner join OrgEntityApplication e on a.ApplicationId=e.ApplicationId
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Don't use select *
List the columns you actually want. If you have two columns with the same name in two separate tables (i.e. the data contained in the column is different) then you can use something similar to
select A.Col1, B.Col1 AS BCol1, A.Col2, B.Col2 as BCol2 ...
Specifically listing the columns you want protects code from subsequent schema changes - imagine you have a carefully crafted gridview that is populated from the database with a query that does a SELECT * FROM Table1...for a specific business reason Table1 gets an extra column that has absolutely nothing to do with your carefully-crafted-gridview ... you're going to end up with an extra column that you were not expecting.
|
|
|
|
|
Yeah thanks buddy, but actually some tables have 20 column also and I have to all the column as flat to write into Date Warehouse table. May be I have to do like you said but a lot of writing though
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
