I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies
Hi,
I've been able to make a linked server to an access document as follows and it works perfectly:
EXECUTE sp_addlinkedserver 'North', 'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0','C:Practice FilesNorthwind.mdb'
SELECT * FROM North...Customers
I have 2x sql 2005 servers at home at different machines. I'm logging on both machines by using windows authentication. How can I make a linked server between them?
Thank you.
is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
i want to run a transaction across mulitpule instences of sqlserver with out linked server.
distributed trnasaction can do it with link server , can it do it with out linked server.
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
thanks in advance.
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
Hi,
Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).
Background:
We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.
Problem:
There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).
I cannot get Query Analyzer to display the data. I have tried the following select statements:
select definition_text from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data
… and get the same error for every one:
quote:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?
Thanks,
Laurie
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
View 3 Replies View RelatedHello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
Here is the sample xml
<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
[code]....
how to write a sql query.
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies View Relatedhi,
i am generating report for my project.
my table goes like
ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone
1 25 4 True False False Mail me False True False Visit me
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively..
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
if any one know the solution for the above problem,Please help me
Thanks & Regards,
Currently, I want to get images of an item in my report for illustration purpose.
Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items. Â
SQL Query:
Select top 1 item_picture_mst.picture from item_picture_mst
where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000')
ORDER BY item_picture_mst.picture DESC;
I had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)
thanks
Fede
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....
Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.
Currently I'm making a new connection for every query, and my code looks like this:Public Shared Function executeQueryReturnDataset(ByVal strsql As String) As DataSet
Dim sqlConnection As New SqlConnection(Connstr)
Dim sqlCommand As New SqlCommand(strsql, sqlConnection)
Dim sqlDataSet As New DataSet
Try
sqlConnection.Open()
Dim myDataAdapter As New SqlDataAdapter
myDataAdapter.SelectCommand = sqlCommand
myDataAdapter.Fill(sqlDataSet)
Catch e As Exception
Console.Write(e.ToString())
Finally
sqlConnection.Close()
End Try
Return sqlDataSet
End Function I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?
Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.
How can I retrieve all the roows from 201 to 250?(like the middle limits)
In oracle there is a property called RowID, but is there any such item in SQL server?
hello,guys,
my question is :
how do I query data from the linked foxpro database?
more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.
sample:
select * from fox..sysu
then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'
I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.
Thank you very much
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
View 9 Replies View RelatedI have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
Hello,
Im using Visual Studio 2005 to code ASP.NET
How to query my MS SQL SERVER 2000 to retrieve data from my DataBase?
Ive tried some tutorials, but i kept giving me errorsCan you put a simple source code to retrieve something like "select userID, Name, Age from Users"
Thank you,
I am developing a commercial solution for scheduling problems, using different databases. Several of our clients don't have a database server installed in their systems networks, so we thought we would simply use SQL Server Express as a no-cost solution. However, I have come across a MAJOR difficulty:
When I try to log on using SQL Server Authentication (for example, name: "sa" pass: "sa") instead of Windows Authentication, I am simply unable to view any data that does not come directly from a table! No data from queries(!), no data from stored procedures(!!), no data from functions(!!!), only from tables!!!!
And it's not like the queries don't return a dataset, because they do, for a user logged on using Windows Authentication!
And this is not 'simply' a problem of remotely connecting to SQL Server Express, this is also true locally!
There is nothing wrong with my own system, this problem occurs on at least four different windows platforms, vista included. And it is not related to our program, I am having the same difficulty with the Management Studio Express.
I have tried to use several different users, each with different permissions and roles, to no avail.
This is truly a frustrating problem.
It can be solved in one of two ways:
1. Get a non-windows-authenticated user to see the data (get more than 0 rows in the dataset).
2. Manage to log on remotely using Windows authentication.
I am unable to do either one of those, and not for lack of trying. I have been at this for the past week without results.
PLEASE, PLEASE, PLEASE, Can anyone HELP?!?!?!?
Hello,
I need to retrieve data from a SQL-server table using a stored procedure. I want to retrieve the values from the table and add them to labels on a form. What is the easiest way to do this? Is dataset the solution?
I appreciate any help!
string sCN = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlConnection2 = new SqlConnection(sCN);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@userID", userID);
Int32 rowsAffected;
cmd.CommandText = "get_user"; //Stored procedure to get user data. Takes UserID as parameter
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection2;
sqlConnection2.Open();
rowsAffected = cmd.ExecuteNonQuery();
sqlConnection2.Close();
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
ID
Duplicate_of
State
77637
65702
Duplicate
65702
42217
Duplicate
42217
-
Verified
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
ID
Duplicate_of
State
77637
65702
Duplicate
65702
42217
Duplicate
42217
-
Verified
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
The project I'm currently working on involves combining data from one SQL Server 2000 databases and XML returned from a web service into a 3rd SQL Server 2000 database.
This process must be scheduled to happen once a day. If it weren't for the Web Service, I'd say that this is a no-brainer and I'd use DTS. However, I'm not sure if I can even access a Web Service with DTS. Has anyone done this or have any tips?
I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong?
appreciate for any suggestion!
...mike
Hi,
In SSRS 2005, I have an assembly(dll) which is included in a report in order to retrieve data ( OdbcConnection) from database as special condition while the runtime of report. This assembly works FINE in the preview of VS development tools. But it is NOT working when deploying to server, it ONLY returns default value instead of database's value.
Anyone knows how to solve it ? Many Many thanks
Hi,
I am getting an error
'Server: Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists'
when i run the below code,
SELECT a.* from
OPENROWSET('SQLOLEDB',
'Data Source=SA3KNX;
Integrated Security=SSPI;
Initial Catalog=[Mis Mart];
Persist Security Info=False;
Initial Catalog=Mis Mart;',
'select * from [Mis Mart].dbo.Dim_Cus') as a
Can you please help resolve this. The server SA3KNX uses windows authentication and I run this code in another serer 'SEBDTD3' which uses SQL server authentication. How can i retrieve data from a server that uses windows authentication.
Regards,
Sharan.
Hi everyone,
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
Thanks
Greetings all,
I have successfully been able to use a SELECT query to see tables on a remote (Linked) server. Now, I want to be able to join those records with a WHERE clause to the same tables I have on my local database. The idea here is to be able to see current data on my local database(which is not current data), which resides in the remote database(which is current data). Here is what I have so far:
SELECT top 10 * from LinkServer.MC_Card.webuser.POS_TX
But, I'm not sure how to SELECT from two tables. Would I do a SELECT from the local database WHERE all records = records on the remote database?
Not sure how to do this. Is this what would be considered a Distributed Query? And how would I make this work with joins like the existing joins I have to the tables in my local database?
This is new territory for me. Sorry if this is such a newbie question.
Thanks,
Bruce
I'm using MS SQL Server Express and I've added a linked server. (I created the linked server by right mouse clicking on Server Objects, selecting SQL and entering the SQL2 as the name.) Now I'm trying to query a linked table. The following query works.
SELECT * FROM SQL2.PA.dbo.Counties
This one doesn't:
Select * from OPENQUERY(SQL2,"SELECT * FROM SQL2.PA.dbo.Counties")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT * FROM SQL2.PA.dbo.Counties'.
Can you tell me why?