Select * from ServerName.DatabaseName.OwnerOrSchema.Objectname
I tyied the way given above.But when im going to run it on query analizer it gives an error massege saying that
"Server 'JOY' is not configured for DATA ACCESS.'
How can i configure the particuler sever.
Thanks
Set the server configuration to allow remote connections.Hello,
I would like to know whether is it possible to administer a database on a hosted site - i.e inserting some lines, executing a query. I normally access the files via ftp connection. And so far the only way I figured I can mess around with the database is to download it and change it on my desktop PC.
Thank You.
Tomá? Pajonk
they would need to open a port (which is not advisable)
port 1433 by default.
the best solution is to create a vpn.
|||Thank You very much. How can You create VPN for this purpose ? - I believe the hosting company would have to allow it and configure it.
How do people access their ASPNETDB file when their projects are hosted ? It is easy to do in the VWD but when the site is local.
Do I need to create my own "administrative pages" for this prupose ?
Thank You again for such a swift reply.
|||well. . .depends on your hoster.
Yes it is up to him to let you vpn to the machine.
So you have rented diskspace and they hang your mdf on their server tree?
Do you have telnet capabilities as well as ftp? you could possibly telnet in and use the osql command line utility to administer the database on their side. it is a little cumbersome but once you get the hang of it, its not too hard. you can write your scripts locally, move them over and execute via a batch file.
can you remote desktop into the machine?
other than that, yeah, a set of adminstrative pages is about the only way.
|||yes I have rented diskspace. They support ASP.NET 2.0, and SQL Server 2000 and SQL Server Express.
They do not provide telnet capabilities. I am trying the remote connection - that could work. But'll have to ask them for a password. I am not sure which password should I use. I am sorry if I sound like a total newbie - but that's the way it is when it comes to hosting and so on.
I guess I'll have to work on those administrative web pages once I understand the ASPNETDB database.
thanks for all the hints.
|||I can't comment on your particular provider but GoDaddy has an administrative interface that is pretty easy to use. The ASPNETDF database is created by issuing the sql scripts on the administrative interface.Hi
Please let me how to put a specific query to a database in the task and get the values from the db.
I have this code:
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(
ComponentMetaData.RuntimeConnectionCollection["conRun"].ConnectionManager);
cm.Name = "TestConn";
cm.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;" +
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" +
"Auto Translate=False;";
After doing this, if i need to write a specific query to a table. How to do it.
Thanks
Vipul
Hi Vipul:
Not sure if this is exactly what you're trying to do, but I think this might be close to what you need.
I'm using the following code to extract a stored flat file date/time stamp from a SQL Server table.
Notice that I'm using a DataAdapter to populate a DataTable with the rows returned by the in-line
SQL statement. This function returns just the first column of the first row of the DataTable, but could
just as easily return the entire DataTable, or just one row from the table:
Private Function GetPreviousDateTimeStamp(ByVal pstrFileName As String) As Date
Dim cn As Data.SqlClient.SqlConnection
Dim cm As Data.SqlClient.SqlCommand
Dim da As Data.SqlClient.SqlDataAdapter
Dim dt As DataTable
Dim strSQL As String
Try
cn = New SqlConnection
'Set up a Connection object:
cn = CType(Dts.Connections("My_Connection_ADO" _
).AcquireConnection(Nothing), Data.SqlClient.SqlConnection)
strSQL = "SELECT PreviousDateTimeStamp " _
& "FROM dbo._FileTimeStamps " _
& "WHERE ImportFileName = '" & pstrFileName & "'"
'Establish a Command object with this SQL statement:
cm = New Data.SqlClient.SqlCommand(strSQL)
'Set Command object's Connection property:
cm.Connection = cn
'Set up a SqlDataAdapter:
da = New SqlDataAdapter(cm)
'Instantiate a DataTable:
dt = New DataTable
'Populate the DataTable:
da.Fill(dt)
'Return the first cell of the first row:
Return CType(dt.Rows(0).Item(0), Date)
Catch ex As Exception
Throw ex
End Try
End Function
Hope this helps! BTW: I could only get this code to work with an ADO connection,
and could not get it to work with an OLEDB connection. I'm sure someone here knows
why I couldn't use OLEDB, but it's working for me and I'm okay with that.
- Mike
|||Hi Mike:
Thanks for the reply. I was able to do it. This is my piece of code and i have made use of oledb only.
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(
ComponentMetaData.RuntimeConnectionCollection["Source"].ConnectionManager);
ConnectionManagerOleDb cmoledb = cm.InnerObject as ConnectionManagerOleDb;
OleDbCommand oledbCommand;
String connMgrString = cm.ConnectionString;
OleDbConnection conn = new OleDbConnection(connMgrString);
conn.Open();
String query = "INSERT INTO Tab1 (First, Second) " + " VALUES ('TEST', 'TEST')";
oledbCommand = new OleDbCommand(query, conn);
oledbCommand.ExecuteNonQuery();
conn.Close();
You can check it if this helps.
Thanks
Vipul
|||Hi Vipul:
Is your code working now? It looks okay to me.
- Mike
|||Yes it works..
Thanks
Vipul
hi,
i've currently created a linked server using sql server management studio express (it connects to an oracle database to draw data), i can query it all fine using the query analyzer with sql server etc.
how can i start using the linked server data with visual studio 2005? i can't seem to find the linked server anywhere inside the server explorer, am i suppose to import it / connect to it any specific way so i can start using the data retreived from the linked server?
thanks in advance!
create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,
Hope this helps
|||
Ramzi.Aynati:
create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,
Hope this helps
thanks, but how do i create a view?
|||if you would like to hit linked server in you code just add database name to each object your would like to access. You can run query on server to which you are connected and server will forward your query to linked server. for example if your SQLServer2 is linked server in SQlServer1 you can run query like this om SQLServer1 ( if user you used to link servers have rights to objects in your query)
select * from [SQLserver2].[database].dbo.[tablename]
you can connect results from multiple servers
select * from [SQLserver2].[database].dbo.[tablename]
left join [SQLserver1].[database1].dbo.[tablename1]
ON tablename.aa=tablename1.bb
If you only use linked server in your query you can use OpenQuery which will process data on the linked server and returns only results to you.
SELECT * from OPENQUERY([SQLServer2,' select * from [database].dbo.[tablename]')
Thanks
|||
In your SQL, go to ur query analyzer
and type
Create View View_name as
SELECT *
FROM TEST1.Northwind.dbo.Orders
//where test is ur linked server
This is it, u got urself a view accessible from ur server explorer
|||I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.
Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)
Thanks
|||
I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.
Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)
Thanks
thanks, i might try both options.. would you happen to know of any tutorials that would put me to the right direction in creating a stored procedure to do this? thanks
|||Dear Frank
As long as the table ur querying is less than 10000 records, u wont feel a pinch, the difference will be in milli seconds,
The topic jpazgier is raising is for really advanced SQL programming and for huge amount of records over 1 million
|||thanks, have given it a go and i get this error message, any idea what i'm doing wrong?
OLE DB error trace [Non-interface error].
Msg 7312, Level 16, State 1, Procedure OracleComDir, Line 3
Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
|||nevermind, worked it out, thanks :)|||Dear Frank
Please dont forget to mark the post that helped u the most as answered for sake of future readers
Glad to be of help my friend
|||
I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.
Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)
Thanks
turns out there's about 500,000 records in the database, so results are generating really slow.
i've been attempting to create a stored procedure, but it's still taking 20 odd seconds to bring up the results, here's a basic summary of my stored procedure:
ALTER PROCEDURE GetOracleData
AS
SELECT
oracle_table1.field1,
oracle_table1.field2,
oracle_table2.field1,
oracle_table2.field2
from
OracleCD..oracle_database1.table1,
OracleCD..oracle_database2.table2
WHERE
(oracle_table1.field1 = oracle_table2.field1) AND
(oracle_table1.field2 = oracle_table2.field2)
OracleCD = name of the linked server i created, i'm thinking the fact that i'm calling the data from the linked server is the reason why it's taking soo long for the data to load? what's the best way to call upon data from different databases in a stored procedure?
thanks in advance!
SELECT break_time, break_rep_no, break_count, break_update_count, break_type, break_group
FROM breaks
WHERE (break_date = @.p1) AND (break_group = @.p2) AND (break_time > @.p3) OR
(break_time < @.P4)
ORDER BY break_time
When I insert this parameters:
@.p1= 7/21/2004
@.p2= 0
@.p3= 9:00 AM
@.p4= 3:00 PM
I get these results:
Break_time rep_no count ucount break_type break_group
9:00:00 AM____2____2_____0________2_________0
9:00:00 AM____2____2_____1________1_________0
10:00:00 AM___1____2_____0________2_________2
11:20:00 AM___2____1_____0________2_________2
1:00:00 PM____2____2_____1________1_________0
Which if you notice not the result I expect look at the (break_group) Column you will find that it brings 0's in addition to 2 which is not expected to appears?
I'm using this query in ASP.Net page Under VB.Net
Me.SqlComm_Chk_Group.Parameters(0).Value = System.DateTime.Today
Me.SqlComm_Chk_Group.Parameters(1).Value = 0
Me.SqlComm_Chk_Group.Parameters(2).Value = tFrom.AddMinutes(59).ToShortTimeString()
Me.SqlComm_Chk_Group.Parameters(3).Value = tTo.AddMinutes(-59).ToShortTimeString()
dr = Me.SqlComm_Chk_Group.ExecuteReader
While dr.Read
tBreakTime = dr(0)
iRepNo = dr(1)
iBreakCount = dr(2)
iBreakUCount = dr(3)
iBreakType = dr(4)
iBreakGreoup = dr(5)
Call Engine(tBreakTime, tFrom, iBreakType, False, iRepNo)
End While
dr.Close()
Where is the problem PLAES?
Thank youParenthesize.
SELECT break_time, break_rep_no, break_count, break_update_count, break_type, break_group
FROM breaks
WHERE (break_date = @.p1) AND (break_group = @.p2) AND ((break_time > @.p3) OR
(break_time < @.P4))
ORDER BY break_time
Even now, I am not sure the code will do what you are looking to do.|||Why I did it before and it works I even try it with OleDB with MSAccess DB But I'm not understand why its not working here every thing seems to be correct|||Depending upon the data in the db and the times you pass, it might APPEAR to work for some data.
I want to use SQL Server to query an Access Database with about 40,000 rows of data. If possible, I don't want to upsize the database because others need acess to it in the ACC97 format. Is there a way to use ODBC to connect to the ACC97 database so that I can use the SQL query capability of SQL server to query the database.
I know access allows you to write some SQL queries but I need the power of the SQL server and now it is a matter of curiosity because I've been searching for this answer for about 8 hours.
WHat do you mean by power of SQL Server ? If you do some queries on the data and you will not pass the query to access as a pass through query, SQL Server will always pull out the whole data to do the query.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I've tried to use the exec sql connect to ... in query analyzer, but could not
get it to parse.
Can someone show me how in a script, say the middle, I can connect to another
database and execute the remaining script lines there.
This way I can have a script update two separate db's at once.
Thanks
Jeff
Jeff KishJeff,
You can specify the from clause of SQL statements as
DATABASE.OWNER.TABLE, as long as they are on the same SQL Server. You
may also utilize the USE DATABASE command to change which database you
script will execute in. But, if you have different servers you want to
access, then that will not work unless you have set up Linked Servers.
In which case you would add the servername to the beginning of your
sting: SERVERNAME.DATABASE.OWNER.TABLE.
Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
T1.ReportID = T2ReportID
use DB2 --This Example will use both Databases to update the column
Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
T1 where T4.ReportID = T1.ReportID
Steve|||On 23 Mar 2006 13:16:41 -0800, "sdyckes" <stephendyckes@.gmail.com> wrote:
>Jeff,
> You can specify the from clause of SQL statements as
>DATABASE.OWNER.TABLE, as long as they are on the same SQL Server. You
>may also utilize the USE DATABASE command to change which database you
>script will execute in. But, if you have different servers you want to
>access, then that will not work unless you have set up Linked Servers.
>In which case you would add the servername to the beginning of your
>sting: SERVERNAME.DATABASE.OWNER.TABLE.
>Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
>T1.ReportID = T2ReportID
>use DB2 --This Example will use both Databases to update the column
>Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
>T1 where T4.ReportID = T1.ReportID
>Steve
Thanks Steve. They will be on the same SQL Server, so I can run with this.
Regards
Jeff Kish
