Hi
Can anyone explain me as to how to map my local_userid to the linked server.
Under the security tab of the linked server properties what options are to be used..any help in this regard is greatly appreciated
Thanks
VENU
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
Thanks
VENU
Hi
I could add a server with the following statement..
USE MASTER
GO
EXEC sp_addlinkedserver 'SiebInt','SQL Server'
GO
In the enterprise manager I could see the server under liked server ..But when I try to access tables under the
server it gives me an error and the error is
GENERAL NETWORK ERROR, CHECK YOUR NETWORK DOCUMENTATION
Can any one tell me what is the reason for the above error and how to get around the problem.It's very urgent
HOW DO WE MAP THE LOGINS USING EM AFTER ADDING THE SERVER USING SP_ADDLINKEDSERVER
Thanks
VENU
Hey guys and gals,
I got asked a question yesterday about queries that JOIN between two databases on a single server... Simple enough
SELECT e1.employee_number
FROM Database1.dbo.employees As e1
INNER
JOIN Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But what about joining tables from two separate servers?
I figured that this would use full 4 part naming
SELECT e1.employee_number
FROM Server1.Database1.dbo.employees As e1
INNER
JOIN Server2.Database2.dbo.employees As e2
ON e1.employee_number = e2.employee_number
But I got the following error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
So off I went to trusty BoL and Google to see what sp_addlinkedserver was all about and came up with the following
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
Is this syntax correct?
I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
Is this process reversible?
I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
Does the link affect all databases on a single server, or just the one I'm playing with?
How do I connect to a specific database on a specific server?
Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
EDIT: highlighted in pink
Our Production server is Windows 2003 and SQL 2000 Enterprise. The server name is VB-GP.
I dumped 2 of the databases from the production server and restored them to server name SQL2K5-Pub (a TEST server) which was a fresh install of Windows 2003 and SQL 2000 Enterprise. I then upgraded this "TEST" server to SQL 2005 Enterprise. All is fine so far.
Now I'm setting up Transactional Replication in my test environment. I configured SQL2K5-Pub as a Distributor which went well. I created a a Publication of 1 database which went well. I attempted to create a Publication of the second database and received the following error.
Error Message
*********************************************************************
Could not find server 'VB-GP' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Could not use view or function '[dbo].ContractBillingStatus_
*********************************************************************
Remember - server name VB-GP is the production server but I have restored to a test server with a different server name. I do not want the production server to have any part of my test environment.
Any ideas?
Thanks,
Terry
I have used this command
sp_addlinkedserver 'linkedserver', 'Access', 'Microsoft.Jet.OLEDB.4.0','C:CATS_MDBintrax_1_db.mdb'
to link an .mdb databse. the trouble is, I do not know how to query tbl_user which is inside. When I try
Code Snippetselect * from [linkedserver].catalogs.dbo.tbl_user
it throws this error:
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linkedserver". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
HI!
i m using sp_ADDLINKEDSERVER for getting data from the other server name 'db est'.
And when i write
'
EXEC SP_ADDLINKEDSERVER 'db est', 'SQL SERVER'
GO
EXEC sp_addlinkedsrvlogin 'db est', FALSE ,NULL ,'sa','sa'
GO
SELECT * FROM db est.Practice.DBO.Employees
'
And then run it in query analyzer. it gives following error due to '' in the server name.
Incorrect syntax near ''.
how to resolve this issue?
Regards,
Shabber Abbas Rizvi.
Hi,
Iam trying to use database in different server. i have searched for that i got to know there is something called
"sp_addlinkedserver" & "sp_addlinkedsrvlogin " stored procs exist. i have tried using this code block to register server to sys servers.
Code Block
exec sp_addlinkedserver @server='ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname='ServerName', @useself=false, @rmtuser='testUser', @rmtpassword='pwd'
when i login using Domain user it is throwing an error as "Login Failed for the User(Domain user)", but when i use "sa" user it works fine.
Is there any settings which i have to take care while using domain user.
can anyone help on this.
Thanks,
-Badri
I'm trying to link access table and execute in Query analyzer or query window.
but I'm pretty sure that I connect properly....
1)
sp_addlinkedserver 'linkedserver', 'Access 97', 'Microsoft.Jet.OLEDB.4.0','C:Mag.mdb'
after i ran this it displays....
(1 row(s) affected)
(1 row(s) affected)
Server added.
2)
sp_addlinkedsrvlogin 'linkedserver', true, 'sa', 'sqluser','sqlpassword'
(0 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
3)
after that when i try to run the sql statement like
Select * from linkedserver..tablename
I GOT THIS ERROR MESSAGE:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'linkedserver..tablename'.
I appreciate if anybody have idea about that.
Thankx a lot
QUESTION 1
Is it possible to run a JOIN on tables that are on seperate servers and youre not able to link the servers using "sp_addlinkedserver?"
QUESTION 2
I try to run
"exec sp_addlinkedserver 'sqlserver1'"
and I get the following:
"Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36
User does not have permission to perform this action."
I try to then run
"SELECT *
FROM server1.database1.schema1.table1 AS a
JOIN server2.database2.schema2.table2 AS b
ON a.[column1] = b.[column2]"
and I get the following:
"OLE DB provider "SQLNCLI" for linked server "ITCMSARPT" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
It would appear the the server is now linked, yet it does't accept my credentials. I do have the ability to log into the server on a seperate query.
Can anyone provide a solution to my issue?
I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
View 1 Replies View RelatedI wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database.
--Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins
if exists (select * from dbo.sysobjects where id = object_id(N'sp_compare_table_record_counts') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_compare_table_record_counts
go
create proc sp_compare_table_record_counts as
----Creates the connection to the Remote Database. We need to make sure that the
----ODBC connections on each of the Production and Development Servers
----are named the same, so that we can use the same script and it will refer to the correct server
EXEC sp_addlinkedserver
@server = 'SERVER_BK',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
set quoted_identifier off
select substring(remote_table.tablename,1,40) as Tablename, local_table.tablerowcount as L_SERVER,
remote_table.tablerowcount as R_SERVER,
case when remote_table.tablerowcount = local_table.tablerowcount then 'Counts Match'
else 'Counts Do Not Match'
end as Result_of_Compare
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table, tablecounts local_table
where remote_table.tablename = local_table.tablename
order by Result_of_Compare, Tablename
-- table name on R_SERVER but not on L_SERVER
select substring(local_table.tablename,1,40) as 'Tables not on R_SERVER'
from tablecounts local_table where local_table.tablename not in (select * from OpenQuery([SERVER_BK], 'select tablename from tablecounts'))
-- table name on L_SERVER but not on R_SERVER
select substring(remote_table.tablename,1,40) as 'Tables not on L_SERVER'
from OpenQuery([SERVER_BK], 'select * from tablecounts') remote_table where remote_table.tablename not in (select tablename from tablecounts)
----Deletes the connection to the Remote Database to free up resources. This is just cleanup.
EXEC sp_dropserver 'SERVER_BK', 'droplogins'
I tested, and the stored procedure works OK when it is run as a New Query (i.e. I copy the guts and run it). When I execute the following, it fails:
exec msdb..sp_send_dbmail
@profile_name = 'DB Mail Profile',
@recipients = 'Recipient List',
@query_no_truncate = 25,
@query_result_separator = ' ',
@subject = 'Record Counts',
@query = 'exec db..sp_compare_table_record_counts', <--db is the appropriate db where the sp is
@body_format = 'text'
Here is the error I get...any ideas would be greatly appreciated. I'm kind of new at this:
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 7399, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
The OLE DB provider "MSDASQL" for linked server "SERVER_BK" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Server L_SERVER, Procedure sp_compare_table_record_counts, Line 28
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER_BK".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'CONVERTSDB', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'F:Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';
a different scenario leads to the same problem, the error message:
Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"
I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server.
this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away.
I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution?
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
HiI am getting the above error message when I try and bind a stored procedure to a gridview:string connectionString2 = ConfigurationManager.ConnectionStrings[project].ConnectionString; SqlConnection con_room_code2 = new SqlConnection(connectionString2); SqlCommand cmd_sofa = new SqlCommand("report", con_room_code2); cmd_sofa.CommandType = CommandType.StoredProcedure; cmd_sofa.Parameters.Add(new SqlParameter("@code", SqlDbType.VarChar)); cmd_sofa.Parameters["@code"].Value = Session["code"].ToString(); SqlDataSource1.ConnectionString = connectionString2; SqlDataSource1.SelectCommand = cmd_sofa.ToString(); plreport.Visible = false; plreport.Visible = true; test.Text = Session["code"].ToString();does any1 know why this is? thanks
View 2 Replies View Relatedhi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
Hi everybody,
I have a batch which needs to be run every day night at 2:00 am.I Using At command but it is not working. if u have idea, please let me know
I AM USING THE FOLLOWING COMMAND :
AT 2:00AM C:SCHED.BAT
Thanks in advance
Krishna
hi all
its urgent really urgent
please reply soon
I am getting error in sysindexes when i run dbcc checkdb on a production db.
the error is Server: Msg 8928, Level 16, State 1, Line 1
please help me to remove this
all the options of dbcc checkdb as well as table are not helping me
thanks in advance
Hi Guys
i write a thread before asking for help with reading an uploaded csv file,
i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db.
Heres the code<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If (IsPostBack) Then
Grid1.Visible = True
Else
Grid1.Visible = False
End If
End Sub
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
' Creating an "Uploads" directory isolates uploaded
' files in a separate directory. This helps prevent
' users from overwriting existing application files by
' uploading files with names like "Web.config".
Dim saveDir As String = "Data"
' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the
' uploaded file to the specified path.
' This example does not perform all
' the necessary error checking.
' If a file with the same name
' already exists in the specified path,
' the uploaded file overwrites it.
FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "csv.txt")
Grid1.DataSource = objDataset1.Tables(0).DefaultView
Grid1.DataBind()
objConn.Close()
End Sub
</script>
My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode
I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app.
Thanks for your help
Hello
I'm trying to execute the following
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
but its giving me an error becuase of @dbName which is a variable decleared as the followingDECLARE @dbName as varchar(100)
the Database name is assigned to @dbName
but i can't take the value of @dbName to combine it to the rest of the statment
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
what should i do ! please help :(
Hi, I am trying to make a backup of my database using Sql-DMO
When I do the backup on my server, my code runs fine. But when I backup from across the network from another computer, the database is saved on the server and not on the computer from where I am executing my application. How set SQLl-DMO to backup on the computer from where I am?
here is my code (found on the net)... Any suggestions greatly appreciated!
Cursor = Cursors.WaitCursor
'create an instance of a server class
Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass
'connect to the server
my_srv.Connect("servername", "userid", "password")
'create a backup class instance
Dim my_backup As SQLDMO.Backup = New SQLDMO.BackupClass
'set the backup device = files property
my_backup.Devices = my_backup.Files
'set the files property to the File Name text box
my_backup.Files = Me.txtFilePath.Text
'set the database to the chosen database
my_backup.Database = "MYDB"
'perform the backup
my_backup.SQLBackup(my_srv)
MsgBox("Database successfully backed up.", MsgBoxStyle.Information)
Cursor = Cursors.Default
How can i insert a word document or an a image file into a column of image datatype using T-SQL.
View 2 Replies View RelatedHello,
I've a bactch file that is ran by the user and it looks for files and if it doesnot exist it waits for it and when it exsits, through isql it truncates the table and bcp in the file. In bcp files the loginname used in isql has dbo rights to truncate the table and the batch file also has password. The user who runs the script can also view the batch file and can know the password, Is there any way I could rewrite it so that the user cann't view the password or other way like SQL DMO or DTS package
Any help is appreciated
Thanks
Rea
I need to FTP files to another drive on the network. Do I need to setup an FTP server on the other computer where I will send file to?
Thanks!!
Ravi.
Greetings!!!
Can any one tell me what is the database log size for a datafiles (tables and Indexes 500MB)
On What basis database log size is estimated for the Database Creation of 500MB ?
On What percentage will be the dump file size and log file size with respect to Database file size??
Can any body help!!!!!!!!!!
Thanks in Advance
A Junior DBA
Hello,
I've a table which has clustered index on one column and I tried to run begin transaction and then 2000 insert statements and then commit transaction, the transaction took 15 min and then it hung up the table, I could not access the table I had to stop the process, the only info I received was the table had 4517 locks, I've no clue how it got so many locks, there was db backup running as I had scheduled 15 min backup. could anyone help me that inside each open transaction how many insert statement is advisible and how many maximum locks does sql server works on a table and what could have gone wrong in my situation.
Thanks
Rea
Hello,
Can anyone guide me how many insert statements can be written in one transaction?
Thanks
Rea
I get following result set for blocked process: wait_type 14, wait_resource TAB: 5:1485248346 [[COMPILE]] . what does it mean?
View 1 Replies View RelatedHi,
I am trying to do DTS for transfering data from one server to other server,both are on T1 link,after some time DTS failed by giving error message saying that
" Error at destination for Row number 3357844,Errors encountered so far in this test:1,,,Invalid charector value for cast Specification "
that table have 24 million rows..
How can i solve this problem,Please help me in this.this is urgent please
Mark A
I have a field 'slnumber' defined as int.Values are like 70000,70001,70002 etc.I need to export this field using bcp to a text file and corresponding field in the text file should have 10 characters and should read like 0000070000,0000070001,0000070002 etc.How do we achieve this format? Any help is greatly appreciated.
Thanks.
Reddy.