I'm trying to use the following statement:
IF (IDS2_TXT.FIRST_MODIFIER="HQ" THEN
(IDS2_TXT.UNITS/240))
But an error appears whn i try to run it. Basically i want sql to only divide the amount of unit of a record if it finds a value on one of the fields (an leave the rest like they are) can someone please help me on what is wrong with the statement. Thank you.
using the derived column transformation:
FIRST_MODIFIER=="HQ"?(UNITS/240):UNITS
Frank
Have a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:
At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER
Missing Parameter [Parameter ordinal = 1]
Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!
Dim conn As SqlCeConnection = Nothing
Try
conn = New SqlCeConnection("Data Source =" & _
(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand
cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn.Close()
End Try
Shouldn't it be "INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||Hi ELugoL,
The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').
Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.
Thanks,
Zero Dai - MSFT
|||Hi Zero Dai, have a nice day.
Running the code on debug mode throws the exception in the instruction
cmd.ExecuteNonQuery()
and the exception details are:
HResult: -2147217904
Message: "Parameter missing. [ Parameter ordinal = 1 ]"
NativeError:25950
Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"
StackTrace:
en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()
I will appreciate any help you can provide to me. Thanks in advance.
Edgar Lugo L.
|||Hi Edgar,
Sorry for delaying reply.
After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?
And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.
Thanks,
Zero Dai - MSFT
|||delete the "[" "]" symbols from the query and thats allHave a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:
At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER
Missing Parameter [Parameter ordinal = 1]
Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!
Dim conn As SqlCeConnection = Nothing
Try
conn = New SqlCeConnection("Data Source =" & _
(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand
cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn.Close()
End Try
Shouldn't it be "INSERTINTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||Hi ELugoL,
The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').
Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.
Thanks,
Zero Dai - MSFT
|||Hi Zero Dai, have a nice day.
Running the code on debug mode throws the exception in the instruction
cmd.ExecuteNonQuery()
and the exception details are:
HResult: -2147217904
Message: "Parameter missing. [ Parameter ordinal = 1 ]"
NativeError:25950
Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"
StackTrace:
en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()
I will appreciate any help you can provide to me. Thanks in advance.
Edgar Lugo L.
|||Hi Edgar,
Sorry for delaying reply.
After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?
And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.
Thanks,
Zero Dai - MSFT
quote:|||Thanks a zillion.
> Hi All,
> I have the following table suppliers and product.
> The Supplier table have two columns Supplier_id and Supplier_name.
> Below is my data in the supplier table:
>
> Supplier_Id
> Supplier_Name
> 2
> New Orleans Cajun Delights
> 3
> Grandma Kelly's homestead
> 16
> Bigfoot Breweries
> 19
> New England Seafood Cannery
> 5
> New Mexico Seafood
> 4
> Indian Spices
>
>
> My Products table have 3 columns Product_id, Product_name and supplier_id
> I have the following data in my products table:
>
> Product_Id
> Product_Name
> Supplier_Id
> 4
> Chef Anton's Cajun Seasoning
> 2
> 5
> Chef Anton's Gumbo Mix
> 2
> 65
> Louisiana Fiery Hot Pepper Sauce
> 2
> 66
> Louisiana Hot Spice Okra
> 2
> 6
> Grandma's Boysenberry Spread
> 3
> 7
> Uncle Bob's Organic Dried Pears
> 3
> 8
> Northwood's canaberry sauce
> 3
> 34
> Sasquach Ale
> 16
> 35
> Steeleye Stout
> 16
> 67
> Laughing Lumberjack Lager
> 16
> 40
> Boston Crab Meat
> 19
> 41
> Jack's New England Clam Chowder
> 19
> 75
> Chicago Pizza
> NULL
> 22
> Indian Hot Sauce
> NULL
>
>
> I want to find the supplier_name of the supplier who is supplying maximum
> products.
> Can anybody help me with the query?
> Thanks,
> Vinita
>
Source:
.Net SqlClient Data Provider
Data:
System.Collections.ListDictionaryInternal
Message:
Cannot find the user 'owner', because it does not exist or you do not have permission.
Cannot find the queue 'SqlQueryNotificationService--guid deleted--',
because it does not exist or you do not have permission.
Invalid object name 'SqlQueryNotificationService--guid deleted--'.
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SqlDependencyProcessDispatcher.SqlConnectionContainer.CreateQueueAndService(Boolean restart)
at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults)
at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults)By changing the service logon account from a local one to a domain account I have stopped the above error.
I have the following SELECT statement I am using in asp.net page:
SELECT MonthName(Month([TransEntryDate])) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY MonthName(Month([TransEntryDate]));
I get the following error:
Undefined function 'MonthName' in expression.
This used to work with classic ASP, but I'm not sure how to form this to work with ASP.NET. I am using this because my results are showing months numerically and I'd like them to be returned with the actual month name (january, february, etc.)...
Any help would be greatly appreciated!MonthName is not a SQL Server keyword, its only support in Analysis Services. Is you .net project pointing to the same database as your classic asp?|||Yes pointing to same db... I'm not sure how to get the results I'm looking for though...|||Yes...pointing to same db, but not sure how to make this work for the results I need...|||I think that what I'm looking for is an example of how I could use the CASE Expression within my SELECT statement to return the month name (not the numeric value)... PLEASE HELP!!!!|||ah now why didn't you say so ;)
|||I hate to be a pain...I'm new to this... Could you show me how to use this with my SELECT statement in my first post? Thank you!!!|||
SELECT DATENAME(month, getdate()) AS 'Month Name'
SELECT DateName(month,[TransEntryDate]) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY [TransEntryDate]
NB Simply using "Group By" doesn't guarantee a sorted order.
I've been having problems with the following query. It falls over on the first BEGIN but I can't se why.
Any sugguestions?
Set transaction isolation level read uncommitted
Declare @.Date DateTime
Declare @.Msg VarChar(255)
set @.Date = Getdate()-44
If (Select z.lastamendedby from dbo.intray as z
Where z.lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date))
--and y.ntSuspended = 0)
--and z.lastamended > @.Date
Begin
set @.Msg = "These users have done work since the 44 day period, don't suspend"
Print @.Msg
End
Else
Begin
Select z.lastamendedby from dbo.intray as z
Where lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date
and y.ntSuspended = 0)
Set @.Msg = "These users haven't done any work since the 44 day period, suspend"
Print @.Msg
End
Cheers,
Jim
Because you didn't finish your IF.
If you reduce it down, maybe you'll see:
IF (SELECT lastamendedby)
BEGIN
Just out of curiosity, what company do you work for? Cause I'm definately interested in finding one that only suspends you if you haven't done anything for 44 days ;-p
I 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.
hope this link may help u :http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html|||
Whoops, I forgot to mention that this is SQL 2005, SP1. I will check out the link though.
Any other ideas?
The other thing is that this only stops working when I turn this into a SP, when I run the first part (between but not including the create proc...as and the sp_send_dbmail) it seems to work fine.
I just can't turn this into a SP so that I can have DBMAIL execute it and mail the output.
|||OK, I am going to change the problem a little bit, as I have made some progress. I decided to NOT make this a stored procedure, instead it is just one script now that does all of the work.
However, I still use this part of the code :
EXEC sp_addlinkedserver
@.server = 'SERVER_BK',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
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'))
This works when I run it as myself, who happens to be an administrator in both locations, and the ODBC connection exists. The problem is when I schedule this as a job. I have a job that runs a tsql command, the job succeeds, but I get this in the log:
Line 59 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IFS_BK". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.".
I know that this runs as the SQL Agent account, but I set the account as a db_datareader for that particular database on the other server. The SQL Agent account also is in the local administrators group of the OS on the remote system. Any ideas why this is failing?
|||EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
when u give this statement the current users credentials are used to connect to the remote server also... check remote server have this user and same password
FROM BOL :
Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.
A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server
If remote server is having different user and password then u should use the below statement
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'Server2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SomeLoginInServer2',@.rmtpassword='Password'
Madhu
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||I did as you suggested, so this makes up the beginning of my script:EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2', --This is an ODBC connection that is setup on the machine.
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2'
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
Later in the script, I run this:
OpenQuery([SERVER2], 'select * from TABLENAME')
My expectation is that I would get all of the information from the TABLENAME table on SERVER2. Instead, I get this error message when the script runs:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
I think i'm missing one important thing, but I can't figure out what it is.
My thought process is to have 1 script that:
1. Creates the connection to the other server, preferable through the System DSN so that I don't have to store a username/password in the code.
2. Access a table in the remote server that contains a bunch of rowcounts for that server
3. Compare the table from the remote rowcounts to the local rowcounts to make sure they match.
Maybe i'm just doing it the wrong way.
|||
At least you got me thinking...
I found the problem. I created the connection, but forgot to put "GO" to create the connection before I tried using it.
Now, it's working.
Thank you for your help.
I 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.
hope this link may help u :http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html|||
Whoops, I forgot to mention that this is SQL 2005, SP1. I will check out the link though.
Any other ideas?
The other thing is that this only stops working when I turn this into a SP, when I run the first part (between but not including the create proc...as and the sp_send_dbmail) it seems to work fine.
I just can't turn this into a SP so that I can have DBMAIL execute it and mail the output.
|||OK, I am going to change the problem a little bit, as I have made some progress. I decided to NOT make this a stored procedure, instead it is just one script now that does all of the work.
However, I still use this part of the code :
EXEC sp_addlinkedserver
@.server = 'SERVER_BK',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER_BK'
EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
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'))
This works when I run it as myself, who happens to be an administrator in both locations, and the ODBC connection exists. The problem is when I schedule this as a job. I have a job that runs a tsql command, the job succeeds, but I get this in the log:
Line 59 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IFS_BK". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).". OLE DB provider "MSDASQL" for linked server "IFS_BK" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.".
I know that this runs as the SQL Agent account, but I set the account as a db_datareader for that particular database on the other server. The SQL Agent account also is in the local administrators group of the OS on the remote system. Any ideas why this is failing?
|||EXEC sp_addlinkedsrvlogin 'SERVER_BK', 'true'
when u give this statement the current users credentials are used to connect to the remote server also... check remote server have this user and same password
FROM BOL :
Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.
A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server
If remote server is having different user and password then u should use the below statement
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'Server2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SomeLoginInServer2',@.rmtpassword='Password'
Madhu
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||Madhu,
Thank you, I think i'm on the right track now. I think I may have confused myself a bit though. The first lines of my script is this:
EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2' --This is an ODBC connection that is setup in the system named SERVER. I was trying to do this to avoid having a username and password in a script.
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
My expectation is that I can now use a command like this further down in the same script:
OpenQuery([SERVER2], 'select * from TABLENAME')
and it would return everything in the TABLENAME table on the Other Server (server2). When I run the script, instead I get an error:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But it's not listing any errors creating the connection. What's the key i'm missing?
|||I did as you suggested, so this makes up the beginning of my script:EXEC master.dbo.sp_addlinkedserver
@.server = 'SERVER2', --This is an ODBC connection that is setup on the machine.
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'SERVER2'
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'SERVER2',
@.useself=N'False',
@.locallogin=NULL,
@.rmtuser=N'USERNAME',
@.rmtpassword='PASSWORD'
Later in the script, I run this:
OpenQuery([SERVER2], 'select * from TABLENAME')
My expectation is that I would get all of the information from the TABLENAME table on SERVER2. Instead, I get this error message when the script runs:
Msg 7202, Level 11, State 2, Line 67
Could not find server 'SERVER2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
I think i'm missing one important thing, but I can't figure out what it is.
My thought process is to have 1 script that:
1. Creates the connection to the other server, preferable through the System DSN so that I don't have to store a username/password in the code.
2. Access a table in the remote server that contains a bunch of rowcounts for that server
3. Compare the table from the remote rowcounts to the local rowcounts to make sure they match.
Maybe i'm just doing it the wrong way.
|||
At least you got me thinking...
I found the problem. I created the connection, but forgot to put "GO" to create the connection before I tried using it.
Now, it's working.
Thank you for your help.
