Database

Eddy Vluggen1-Sep-12 22:57

1-Sep-12 22:57

1.fcb1913 wrote:
What I have to do to connect from outside?

I suggest you run a little webserver, and use that to verify whether you can connect. If you can't serve a page from the machine, then you won't be able to use a database remotely.

The logical steps would be something like
- verify connectivity
- verify db-server is running
- verify local connectivity
- verify remote connectivity

If your PC is "at home", chances are your router has it's own firewall. Check the manual to verify - the router-setup varies per product. I changed something called "NAT", pointing from the outside to the static IP on the inside-network.

(The numbers inside the network should be different from those outside, and you'd be using the outside-number to connect)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

jschell2-Sep-12 7:51

jschell

2-Sep-12 7:51

1.fcb1913 wrote:
server has IPv4 address 10.10.11.93. I

That is a private IP address.

It is NOT possible to address a private IP address on the internet. There is absolutely no way to do it.

Your options are
- Figure out your public IP address, which your ISP has given you. There is NO guarantee that you have a public IP address. However if you have one then your cable modem would be the one that has it. You would need to set up your modem to redirect requests to your box.
- Buy a public IP from your ISP. Then do the above.

1.fcb19134-Sep-12 6:59

1.fcb1913

4-Sep-12 6:59

Thanks.
I have public IP address.
On a chimney we have white cubic transmitter-receiver and one cable ended with a switch from witch the computers are connected.
I don't know if it is router or modem.
I tried typed my public IP address, IPv4 address, gate IPv4 and server DNS IPv4 address into web browser to set up connection,
but always connect error is occured. I have contacted my internet provider, I hope he'll resolve this.

Paul Conrad4-Sep-12 7:14

Paul Conrad

4-Sep-12 7:14

Have you tried using port forwarding as Bernhard has suggested?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Bernhard Hiller2-Sep-12 21:09

Bernhard Hiller

2-Sep-12 21:09

Depending on the hardware for connecting your local intranet to the internet, it may be possible to set-up a "port forwarding": that means, you send the request to your public ip address with a port specified, and the router then forwards the request to an internal computer and that port. To get your public ip address, you can use tools like dyndns.

Re: Please help the problem

Paul Conrad4-Sep-12 7:13

Paul Conrad

4-Sep-12 7:13

Yes, port forwarding and using a dyndns tool should be able to work.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Please help the problem

kathytran29-Aug-12 7:40

kathytran

29-Aug-12 7:40

Hi,
I got the following error on test server when I tried to connect to IBM.DB2

Method not found: 'Void IBM.Data.DB2.DB2Connection.set_ParameterStreaming(Boolean)

It worked fine on my desktop with Visual Studio 2010.

I'm appreciate if someone can help.

Thanks,

R. Giskard Reventlov29-Aug-12 8:31

R. Giskard Reventlov

29-Aug-12 8:31

Does the test server have everything installed that your desktop has? The same versions? Have you installed your application correctly? With everything it needs?

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Import excel data in SSRS

Arul R Ece29-Aug-12 1:21

Arul R Ece

29-Aug-12 1:21

 Hi Friends,
 
Good noon,
 
Can we have upload data from excel into SSRS in SQl server,
 

if any one help me 
 

Regards

Arul.R

Re: Import excel data in SSRS

Niju129-Aug-12 22:40

Niju1

29-Aug-12 22:40

check this link

http://weblogs.sqlteam.com/joew/archive/2008/08/22/60695.aspx[^]

I need your help to solve a problem.

uspatel28-Aug-12 1:52

uspatel

28-Aug-12 1:52

Problem: I developed a website with ASP.Net and SQL Server.All data I am accessing from SQL Server.
This site will hosted in public domain as internet website and it is a kind of CRM portal.My Sql server datbase has 11 tables.

My company uses Oracle ERP.Now I want to update data from oracle erp to SQL Server.I have created views in Oracle as data needed in SQL Server.
Means when a customer created/information updated ,it should be updated in SQL Server database.
Is there any solution?
I tried google but there is no suitable answers. Please reply me.

Eddy Vluggen28-Aug-12 2:04

28-Aug-12 2:04

Uma Shankar Patel wrote:
I tried google but there is no suitable answers. Please reply me.

Google for "linked server Sql"; it's a few Sql-statements, allow you to access the Oracle-tables as if they were on Sql Server.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

uspatel28-Aug-12 2:12

uspatel

28-Aug-12 2:12

Yes I tried this,
but how can i update my database in SQL server in real time means when I update data in oracle ERP it should be updated in SQL Server.
and
does Hosting provide provides Linked server facility?

David Mujica28-Aug-12 2:27

David Mujica

28-Aug-12 2:27

I doubt very much that a hosting provider will allow you to configure a linked server; this needs to be done by a system administrator of the SQL Server. You should email the tech support folks at your hosting provider and ask them directly.

An alternate solution would be to create a webservice on your hosted site which would process datachanges from your Oracle system. You could create a folder somwwhere on your server and write an XML file which represents the data change, then have a Windows service monitor that folder for new files and when it sees one, call the webservice to update the SQL database.

It is not a real-time solution, but it would be pretty close.
Good luck. Thumbs Up | :thumbsup:

uspatel28-Aug-12 23:36

uspatel

28-Aug-12 23:36

Thanks David........
I used Linked Server,and My system administrator tell me that we are not hosting this in public domain,we host it on local IIS and we buy public IP and connect local IP to public IP.
Is this possible?
Is this secure?

David Mujica29-Aug-12 8:31

David Mujica

29-Aug-12 8:31

Ah ha. I see.
From my understanding if the SQL server and the Oracle Server are local, then the communication between the two should be pretty secure. I would ensure that the user accounts that you are using from your web server has the minimum privilege necessary to get the job done. You don't want to be connecting as "sa" incase your application gets hacked.

During your application development, make sure you use parameterized queries to limit the exposure to SQL injection.

Eddy Vluggen28-Aug-12 2:36

28-Aug-12 2:36

Uma Shankar Patel wrote:
Yes I tried this,

If you did, than why did you not mention that with your question? What else did you try?

Uma Shankar Patel wrote:
does Hosting provide provides Linked server facility?

No. It's something that's built in to Sql Server, and it requires a database-connection to the remote database-server. The remote host will not allow remote connections, to prevent people from abusing them as a cheap alternative for databases.

You could fetch the latest info from the remote host itself; generate Xml using PHP locally on the MySql machine, read it from your code by fetching the webpage, and updating your database.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Shameel28-Aug-12 2:37

Shameel

28-Aug-12 2:37

If your hosting provider allows, you can create a DB Link to SQL Server from your Oracle database. Write triggers in the Oracle tables and update the SQL Server tables through the DB Link.

Please help me for this qusetion

Karthik J, Coimbatore28-Aug-12 23:28

Karthik J, Coimbatore

28-Aug-12 23:28

There are two posibilties

Use linked server concept to link two different databse.

1. http://www.c-sharpcorner.com/uploadfile/suthish_nair/linked-servers-in-sql-server-2008/[^]

2. http://www.jensbits.com/2010/11/10/create-linked-server-sql-server-2008/[^]

(or)

synchronize tool to sync two database at specific time(there are many tool available at affordable price)..

1. http://www.red-gate.com/products/sql-development/sql-data-compare/[^]

2. http://dbconvert.com/convert-oracle-to-mssql-sync.php[^]

Robymon27-Aug-12 2:57

Robymon

27-Aug-12 2:57

I have two tables "CM_Master" and "CM_Details" and the sample records are below, i want to make a report like under the result. How can i join two tables and create table heading like the shift names in CM_Master table.

CM_Master

ShiftID ShiftName
1 Day
2 Night

CM_Details

CM_ID Site_ID Level_ID ShiftID Number
1 1 1 1 5
2 1 2 1 4
3 1 1 2 2
4 2 2 2 8

Result

Site Level Day Night
1 1 5 2
1 2 4 0
2 2 0 8

__TR__27-Aug-12 3:31

__TR__

27-Aug-12 3:31

Try the below approach

SQL

CREATE TABLE #CM_Master
(
	ShiftID INT, ShiftName VARCHAR(50)
)

INSERT INTO #CM_Master
SELECT 1, 'Day' UNION
SELECT 2, 'Night'
 


CREATE TABLE #CM_Detail
(
	CM_ID INT, Site_ID INT, Level_ID INT, ShiftID INT, Number INT
)

INSERT INTO #CM_Detail
SELECT 1, 1, 1, 1, 5 UNION
SELECT 2, 1, 2, 1, 4 UNION
SELECT 3, 1, 1, 2, 2 UNION
SELECT 4, 2, 2, 2, 8 



--SELECT * FROM #CM_Detail

SELECT DISTINCT CD.Site_ID, CD.Level_ID, ISNULL(A.Day,0) AS [Day], ISNULL(B.Night,0) AS Night
FROM #CM_Detail CD
LEFT JOIN
(
SELECT Site_ID, Level_ID, 
SUM(Number) [Day]
FROM #CM_Detail
WHERE ShiftID = 1
GROUP BY Site_ID, Level_ID
) A ON CD.Site_ID = A.Site_ID AND CD.Level_ID = A.Level_ID
LEFT JOIN 
(
SELECT Site_ID, Level_ID, 
SUM(Number) AS [Night]
FROM #CM_Detail
WHERE ShiftID = 2
GROUP BY Site_ID, Level_ID
) B ON CD.Site_ID = B.Site_ID AND CD.Level_ID = B.Level_ID

and here is a generic approach where shift names are not hard coded.

SQL

DECLARE @PivotColumnHeader VARCHAR(MAX)
SELECT @PivotColumnHeader = COALESCE(@PivotColumnHeader + ', [' + ShiftName + ']', '[' + ShiftName + ']')
FROM #CM_Master


DECLARE @SQL VARCHAR(MAX)
SET @SQL = N'SELECT * FROM
(
SELECT Site_ID, Level_ID, ShiftName, ISNULL(Number,0) AS Number  FROM #CM_Detail CD
INNER JOIN #CM_Master CM ON CD.ShiftID = CM.ShiftID
) P
PIVOT
(
    SUM(Number) FOR ShiftName IN (' + @PivotColumnHeader + ')
) AS PivotTable'

EXECUTE (@SQL)

modified 28-Aug-12 4:05am.

Robymon27-Aug-12 3:51

Robymon

27-Aug-12 3:51

Is it possible to make the sql dynamic, instead of hard code.

eg: Day, Night some time the master table have more records.

Eddy Vluggen27-Aug-12 4:50

27-Aug-12 4:50

Robymon wrote:
Is it possible to

Yes, that's possible. The correct response to the answer would have been "thank you".

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]