Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
I know there are already several bug submissions on this error, and I am sure the cryptoAPI team is working to resolve this issue.. However... Is it possible that the CAPICOM update (KB931906) could have contributed to this issue? In my instance, I installed SQLServer 2005 Express w/ Adv Tools SP2 after a freash XP install. I checked it out, and everything worked fine. I updated windows (online) and now it gives this error. In fact, I cannot even run the un-install because the setup utility also hits the following error (as taken from the log file):
Microsoft SQL Server 2005 Setup beginning at Wed Jan 16 12:47:35 2008 Process ID : 576 d:97352a1d146f8bca5542016500a05210setup.exe Version: 2005.90.3042.0 Running: LoadResourcesAction at: 2008/0/16 12:47:35 Complete: LoadResourcesAction at: 2008/0/16 12:47:35, returned true Running: ParseBootstrapOptionsAction at: 2008/0/16 12:47:35 Loaded DLL:97352a1d146f8bca5542016500a05210xmlrw.dll Version:2.0.3609.0 Complete: ParseBootstrapOptionsAction at: 2008/0/16 12:47:35, returned false Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run: Could not parse command line due to datastore exception. Source File Name: utillibpersisthelpers.cpp Compiler Timestamp: Wed Jun 14 16:30:14 2006 Function Name: writeEncryptedString Source Line Number: 124 ---------------------------------------------------------- writeEncryptedString() failed Source File Name: utillibpersisthelpers.cpp Compiler Timestamp: Wed Jun 14 16:30:14 2006 Function Name: writeEncryptedString Source Line Number: 123 ---------------------------------------------------------- Error Code: 0x80070002 (2) Windows Error Text: The system cannot find the file specified.
Source File Name: cryptohelpercryptsameusersamemachine.cpp Compiler Timestamp: Wed Jun 14 16:28:04 2006 Function Name: sqls::CryptSameUserSameMachine:rotectData Source Line Number: 50
2 Could not skip Component update due to datastore exception. Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:27:59 2006 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "InstallMediaPath" {"SetupBootstrapOptionsScope", "", "576"} in cache Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:28:01 2006 Function Name: SetupBootstrapOptionsScope.InstallMediaPath Source Line Number: 44 ---------------------------------------------------------- No collector registered for scope: "SetupBootstrapOptionsScope" Running: ValidateWinNTAction at: 2008/0/16 12:47:35 Complete: ValidateWinNTAction at: 2008/0/16 12:47:35, returned true Running: ValidateMinOSAction at: 2008/0/16 12:47:35 Complete: ValidateMinOSAction at: 2008/0/16 12:47:35, returned true Running: PerformSCCAction at: 2008/0/16 12:47:35 Complete: PerformSCCAction at: 2008/0/16 12:47:35, returned true Running: ActivateLoggingAction at: 2008/0/16 12:47:35 Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run: Datastore exception while trying to write logging properties. Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:27:59 2006 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:28:01 2006 Function Name: SetupStateScope.primaryLogFiles Source Line Number: 44 ---------------------------------------------------------- No collector registered for scope: "SetupStateScope" 00E2CFC0Unable to proceed with setup, there was a command line parsing error. : 2 Error Code: 0x80070002 (2) Windows Error Text: The system cannot find the file specified.
Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:28:01 2006 Function Name: SetupBootstrapOptionsScope.InstallMediaPath Source Line Number: 44
Failed to create CAB file due to datastore exception Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:27:59 2006 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "576"} in cache Source File Name: datastorepropertycollection.cpp Compiler Timestamp: Wed Jun 14 16:28:01 2006 Function Name: SetupBootstrapOptionsScope.HostSetup Source Line Number: 44 ---------------------------------------------------------- No collector registered for scope: "SetupBootstrapOptionsScope" Message pump returning: 2
Since the Microsoft.SqlServer.Express.SQLEditors utilizes a crypto wrapper, givin the error above... would the CAPICOM update have affected things?
I have a SQL express database which I need to access from a shared hosting plan. I can create an ODBC connection through the hosting provider's control panel for SQL Server, but it won't connect. I tested this locally and discovered that the SQL Native Client connects fine, but the previous SQL Server driver does not. This seems to only happen with SQL 2005 Express edition; it works with the Developer Edition. Does SQL Express only use the Native SQL Client??
I am using .NET v2.0 and SqlServer2005 SB in 90 compatibility mode.
From my brief experience it appears that if I try to subscribe to SB with incorrect SQL or wrong names .NET will immediately throw an exception. However, if my code is correct but there is an error on the server (for example incorrect options as per http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=883992&SiteID=1) then SB will immediately fire the notification message but with €œerror€? instead of €œupdate€? content. How do I detect this?
My code is based on the http://msdn2.microsoft.com/en-us/library/3ht3391b(vs.80).aspx example. (I had too much trouble with the SqlDependency class, including overflowing error logs. So unlike the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903713&SiteID=1 I am NOT receiving an SqlNotificationEventArgs object. I wish I were getting one!) I€™ve included my Listen() method below.
The debug portion of this code gives me message body that looks like the following when everything is good. What should it look like, if for example my SET OPTIONS (first link above) were wrong?
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
Would anyone really use ASPNET2 Membership Services with its built in Sql Server Express database for a major, heavy traffic website? How well would sql server express handle 1 million members? Can anyone point me to an article comparing performance of full blow Sql Server with (watered down?) Sql Server Express?
I recently purchased a copy of Visual Studio 2005 Professional last month and it came with SQL Server Developer Edition. However, I noticed it also installs the express edition. If I choose not to install the Developer Edition (Im quite new to SQL) can I deploy a database I develop in the express edition on a regular SQL server?
Good Morning All, I had SQL Server Express installed for a few months now just for testing and learning without any problems. Yesterday, I found this learning video and decided to install “SQL Server Reporting Services" as shown in this lesson 10 video. http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx
When I was trying to install Reporting Services along with, something called Web Components and Backward compatibility were not being installed. So I decided to go to my control panel and uninstall everything for SQL server 2005 and then reinstall it.
This worked and Reporting Services Configuration was installed, but I cannot find the "Management Studio Express" feature in the ALL Programs area.
Whenever I try to reinstall it again the install does not go through and I get a message saying. quote: TITLE: Microsoft SQL Server Setup None of the selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine. To continue, click Back and then select features to install. To exit SQL Server Setup, click Cancel.
I'm using RMO to set up MergePullSubscription to a SQL Express database, the server is running SS2k5.
The subscription gets created just fine, and web sync seems to be working, going to replisapi.dll?diag in my new virtual directory returns no errors.
When creating the publication on the server (using management studio for this) the snapshot is created, and stored in the snapshot share. Creating the publication seems to go without any issues. When I open the "Configure Web Synchronization" wizard, and point it to my new snapshot share, it ALWAYS says that the snapshot folder appears to be empty, which it isn't.
I get an error when I attempt to sync the databases that I believe is caused by Web Sync not being able to find the snapshot. Here's the output from triggering the sync via RMO.
2007-11-12 14:45:49.225 The upload message to be sent to Publisher 'SandBoxWebApp' is being generated 2007-11-12 14:45:49.265 The merge process is using Exchange ID 'DF8FE565-F6BC-40B3-8AEA-7B6FA5EC225D' for this web synchronization session. 2007-11-12 14:45:49.928 No data needed to be merged. 2007-11-12 14:45:49.940 Request message generated, now making it ready for upload. 2007-11-12 14:45:49.952 Upload request size is 1290 bytes. 2007-11-12 14:45:49.988 Uploaded a total of 1 chunks. 2007-11-12 14:45:50.000 The request message was sent to 'https://SandBoxWebApp/WebSync/replisapi.dll' 2007-11-12 14:45:50.064 Downloaded a total of 3 chunks. 2007-11-12 14:45:50.076 The response message was received from 'https://SandBoxWebApp/WebSync/replisapi.dll' and is being processed. 2007-11-12 14:45:50.109 The process could not connect to Distributor 'SandBoxWebApp'. A first chance exception of type 'System.Runtime.InteropServices.SEHException' occurred in Microsoft.SqlServer.Replication.dll A first chance exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred in Microsoft.SqlServer.Replication.dll A first chance exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred in Microsoft.SqlServer.Replication.dll
That's it, always end up with "Cannot connect to distributor".
I'm using a free cert for testing, hence the non-FQDN name SandBox, but I have added the root CA to my machine, and don't get any cert errors when visiting the ?diag page in my browser.
Any ideas? I've been beating my head against the same wall for 2 days now!
I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.
My question is,how do i select a record from the database if I don't know the table it comes from?
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources() { DAL.CreateCommand("sp_GetLatestFiveTrainingResources"); DAL.ExecuteReader(); SqlDataReader dr = DAL.DataReader; ..... } Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005
We are needing to script to find out if our servers have a full version of SQL Server installed or if it is just SQL Express that is used by one of our backup software vendors. How can we do this? Currently, we are having to go into each server and look at the SQL console. Anything that someone can suggest would be greatly appreciated as we have hundreds of servers to go through. Thanks much in advance. Cathy
I have read a lot of topics about execution plan for query, but I got little. Please give me some help with examples for comparing different select statements to find the best efficient select statement.
I was using the SQL 2000 stored procedure wizard to create storedprocedures. I can create insert, delete and update SPs however I amnot able to create the select SP. The wizard does not offer the optionof selecting "select" type SP. thank you for your replyscompraj
I dont find sql management express in the program folder of sql server 2005. i find configuration manager but dont find the sql server management express. If the program is deleted also then the shortcut is deleted but not the whole program. i can find the files in the respective folders of program files -> microsoft sql server 2005. but dont know which files start the sql managment express
Windows 2000 Server SP4 + SQL Server 2000 Enterprise Editon SP3,RAID5.
the windows event log give the following error information:
I/O error 2(The system cannot find the file specified) detected during write at offset 0x0000010c6c4000 in file 'D:Program FilesMicrosoft SQL ServerMSSQLdataGJSZBANK_Data.MDF'.
I have searched microsoft knowledge base and got this article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339
but don't understand some contents in the topic:
For example, if you encounter the following error message in the SQL Server Errorlog file, SQL Server encountered operating system error 2 when it uses a Windows API call to write to the tempdb primary database file:
Error: 823, Severity: 24, State: 4 I/O error 2(The system cannot find the file specified.) detected during write at offset 0x00000000284000 in file 'D:Program FilesMicrosoft SQL ServerMSSQLdata empdb.mdf'
Because SQL Server has already successfully opened the file and did not receive an “Invalid Handle” error, the error is likely being raised in a lower-level kernel software component, such as the file system or a device driver. This problem does not indicate a problem in SQL Server, and it must be investigated as an issue with the file system or a device driver that is associated with the file.
Does that mean this is not a SQL Server error? Does that mean something wrong with my operating system? or something wrong with my hard disk?
In the registry in Windows 10, I looked inside HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server. Is LoginMode still in some subfolder of that, or has it been moved to another place? Perhaps a differently named key is now used. Or perhaps it's configured in a config file for SQL Sever 2012 express.
I'm getting a specific error when i try to run a .sql script for a Custom TableProfileProvider ,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following error. It will produce the table but with "Errors" which dosn't do anyone any good to have errors in the table. I even used the Sql Server Surface Area Configuration to add a new Administrator with the name listed below (WYATT-PCWyatt), that didnt help either..Here is part of the Sample Scheme the problem lies in the [YOURMACHINENAMEASPNET] Server = WYATT-PCSQLEXPRESS Computer Name = Wyatt-PC OS: Windows Vista Home Premium =========================================================== use TableProfileProviders go -- --grants on ASP.NET stored procedures and tables used by the custom providers -- grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [YOURMACHINENAMEASPNET] grant EXECUTE on dbo.aspnet_Users_CreateUser to [YOURMACHINENAMEASPNET] grant SELECT on dbo.aspnet_Users to [YOURMACHINENAMEASPNET] grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [YOURMACHINENAMEASPNET] go --drop table dbo.ProfileTable_1 --go create table dbo.ProfileTable_1 ( UserId uniqueidentifier not null Primary Key, FirstName nvarchar(50) null, LastName nvarchar(50) null, Age int null, LastUpdatedDate datetime not null) go grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [YOURMACHINENAMEASPNET] go ================================================================================ Msg 15151, Level 16, State 1, Line 5 Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder). The SQL Server Express error log is clean and indicates full functionality. Everything is running locally. No VWD installation errors.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error: here is my web.config file:<?xml version="1.0"?><!-- Note: As an alternative to hand editing this file you can use the web admin tool to configure settings for your application. Use the Website->Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in WindowsMicrosoft.NetFrameworkv2.xConfig --><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings><add key="EmailFrom" value="webmaster@reaganpower.com"/> <add key="EmailSubject" value="File Ready for Download!"/><add key="SmtpServer" value=""/> <add key="MailUser" value=""/><add key="MailPassword" value=""/> <add key="MailPort" value="25"/><add key="EmailFormatSelected" value="Text"/> <add key="PageTitle" value="Send It Now!"/><add key="ShareLocalFolderPath" value="H:MIS DepartmentIntranetSendItNowFileStorage"/> <add key="httpDownloadPath" value="http://misfs/SendItNow/ContentFiles/"/> <!-- <add key="CurrentTheme" value="CleanBlue" />--> <add key="CurrentTheme" value="CleanOrange"/></appSettings> <connectionStrings> <add name="ConnectionString" connectionString="Data Source=(local)SqlExpress;AttachDbFilename=|DataDirectory|FileShareDB.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/> </connectionStrings><system.web><!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. Visual Basic options: Set strict="true" to disallow all data type conversions where data loss can occur. Set explicit="true" to force declaration of all variables.--> <identity impersonate="false"/> <roleManager enabled="true"/><compilation debug="true" strict="false" explicit="true"> <assemblies><add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation> <pages><namespaces> <clear/><add namespace="System"/> <add namespace="System.Collections"/><add namespace="System.Collections.Specialized"/> <add namespace="System.Configuration"/><add namespace="System.Text"/> <add namespace="System.Text.RegularExpressions"/><add namespace="System.Web"/> <add namespace="System.Web.Caching"/><add namespace="System.Web.SessionState"/> <add namespace="System.Web.Security"/><add namespace="System.Web.Profile"/> <add namespace="System.Web.UI"/><add namespace="System.Web.UI.WebControls"/> <add namespace="System.Web.UI.WebControls.WebParts"/><add namespace="System.Web.UI.HtmlControls"/> </namespaces></pages> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms"/> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors>--> </system.web> </configuration>
Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatacartracker.mdf". Operating system error 32: "32(error not found)". An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatacartracker.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I am using Visual Basic 2005 Express Edition along with SQL Server 2005 Express Edition.
Now here's what I did:
Rather than use the Database Explorer within VB2005 EE to create my database, I thought I would try creating my database externally using the downloadable "SQL Server Management Studio Express" tool. (I figured that if I had a stable data model for my database, I could side-step the copying/overwriting issues that come with creating a database as part of a VB solution.)
So having gotten my database setup and populated with some sample data, I am now trying to connect to it from Visual Basic. In VB (in the context of working on a VB Solution), I do the following:
Select Add New Data Source... from the Data menu to bring up the Data Source Configuration Wizard. Choose "Database" for the data source type and click Next. Click "New Connection..." to bring up the Add Connection dialogue.
For data source I choose "Microsoft SQL Server Database File", with ".NET Framework Data Provider..." as the data provider. Navigate to my existing database file (in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" folder). Use Windows Authentication.
When I click "Test Connection" or just click "OK", i get the Microsoft Visual Basic 2005 Express Edition error shown at the top of this message.
Is this occuring because I'm trying to access my database as an .MDF file rather than through Named Pipes / Shared Memory / TCP/IP? If this is the case, is there any way that I can tell VB2005 EE to use Named Pipes / Shared Memory / TCP/IP? Is this a fundamental limitation of VB2005 EE?
Thanks in advance to the guru who is able to explain this to me...
I'm getting an error while trying to install SQL Server Express 2005 Express Edition. I'm attaching the log of the installation process here. Please throw some light on the same.
Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is (3) The system cannot find the path specified.
Setting status of unmanaged components and removing unmanaged resources
Error Code: 3
MSI (s) (D0!64) [21:39:17:257]: Product: Microsoft SQL Server 2005 -- Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is (3) The system cannot find the path specified.
.
Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is (3) The system cannot find the path specified.
So i am getting an error of "An error has occurred while establishing a connection to the server. When connecting to SQL Server 205, this faliure may be caused by the fact that under the defualt setting SQL Server does not allow remote connections.(ProviderQL Net work Interfaces, error: 26- Eror Locating Server?Instance Specified)
So i have Windows Xp SP2,750 mb Ram, 1.6 Ghz, Uninstalled Everything (visual basic express,SQL express) then reinstalled everthing all over again. Same problem . Any Ideas???
Msg 102, Level 15, State 1, Line 6 which is around the comment line where is shows monthly and other. Getting message incorrect systax near '/'.
@Frequencyvarchar(1), -- 'M' = Monthly, 'O' = Other @FiscalPeriod smallint, @FiscalYearsmallint as
set nocount on
DECLARE@DateFromdatetime, @DateTodatetime
SET @DateFrom = CASE @Frequency WHEN 'M' THEN convert(varchar,(SELECT efsc_from_date FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_to_date = (SELECT max(efsc_to_date) FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_to_date < getdate())),101) + ' 00:00:00' WHEN 'O' THEN convert(varchar,(SELECT efsc_from_date FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_fiscal_year = @FiscalYear and efsc_fiscal_period = @FiscalPeriod),101) + ' 00:00:00' ELSE convert(varchar,getdate()-1,101) END
SET @DateTo = Case @Frequency WHEN 'M' THEN convert(varchar,(SELECT efsc_to_date FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_to_date = (SELECT max(efsc_to_date) FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_to_date < getdate())),101) + ' 23:59:59' WHEN 'O' THEN convert(varchar,(SELECT efsc_to_date FROM rs3_reporting.dbo.rs_entity_fiscal (NOLOCK) WHERE efsc_fiscal_year = @FiscalYear and efsc_fiscal_period = @FiscalPeriod),101) + ' 00:00:00' ELSE convert(varchar,getdate()-1,101) END
I installed SQL7 on my windows 95 desktop for testing. When I try to open the tables for the Northwind database, I get the message box "ado could not find the specified driver". No records are displayed.
SQL2000 publisher , acting as it's own distributor (v8.0.760)
Transactional Replication with updateable subscriptions
SQL2005 subscriber (v9.00.3042)
Push subscription with immediate updating from distributor to subscriber and queued updating from subscriber to distributor
The set-up went smoothly and changes in the publication db flow down to the subscriber as expected. However, changes at the subscriber fail to flow up to the publisher.
The Queue Reader agent reports the error "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.sp_MSrepl_ins_mytable_1"
The SP 'sp_MSrepl_ins_mytable_1' exists in the publisher db but the owner is 'dbo' not 'upd' (there is no such login as 'upd' on the server)
The only useful article I have found on the net suggests that this could be caused by a bug (jn SP3?) but the situation described in that article and the fix/workaround for it don't seem to apply for me.
The problem and fix in that article was: table MSsubscription agents in the subscriber db had a queue_id value of 'mssqlqueue' and the fix was to set it to 'mssqlqueuev2'. However, it is already ''mssqlqueuev2' in my subscription db so I'm at a loss to explain why it's still generating calls looking for SPs owned by 'upd'.
other info: ------------------------------ Just to see if it would work around my immediate peoblem I tried creating a user called 'upd' and duplicating the SPs. This worked up to a point. Changes replicated up to my publisher ok. However, when I created a conflict (changed same row on publisher and subscriber at same time) the Queue Reader fell over with error: "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.1". -------------------------------
--------------------------------- Investigated the stored procdure sys.sp_replqueuemonitor in my subscription database and found statements like this: select publisher, publisher_db, publication, queue_server, queue_id from dbo.MSsubscription_agents where publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND publication = case when @publication is NULL then publication else @publication end AND update_mode IN (2,3) AND queue_id != N'mssqlqueue' note that the queue_id is is looking for is 'mssqlqueue'. If I manually run sys.sp_replqueuemonitor I get no results (unsurprising since the data in dbo.MSsubscription_agents has queue_id='mssqlqueuev2'.
It looks to me as if some of the SPs created when the publication and/or subscription was set up are incorrect...??? --------------------------------
Any advice or suggestions on how to approach this problem?
Hi all, i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is: String was not recognized as a valid Boolean. how can i find the exact line causing the error? thank you -shane
