Why Virtual Suscriber_db Value In Distribution.dbo.MSsubscriptions Table?
May 10, 2007
I'm using SQL Server 2005 version 9.0.3054.
In the distribution.dbo.MSsubscriptions table, the field subscriber_db has a value of "virtual".
This is only occurring for one of my transactional publications.
There are 2 rows with "virtual" and they have subscriber_id's of -1 and -2.
There is a third and last row for that transactional publication that points to the correct db.
What does "virtual" mean and why does it appear?
More info:
I have 3 transactional subscriptions. The first two were created via SMO. The third one, with the "virtual" subscriber_id, was originally created manually via SSMS. I then generated scripts from SSMS in order to recreate it again later. Those stands out from the other two with differences in these columns:
snapshot_seqno_flag
value is 0 rather than 1;
independent_agent
value is 1 rather than 0;
article_id
value is 3 for all three rows listed for the subscription in that table;
status
values for the three rows are 1,2,1 respectively. Whereas my other subscriptions have status 2.
agent_id
values for the three rows are each different (10,11,12) whereas the agent_id for each of the rows in the other two publications is consistently the same.
publisher_seqno
ss_cplt_seqno
are displayed as equal for each row whereas the values for those two fields for rows for the other 2 publications are not equal; The value formats are different too. Example:
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
:confused: Please forgive this elementary question. I have database which has a view that produces the desired records and fields from multiple tables. As I understand it a view is a Virtual Table. My problem is I need to export these results periodically to deliver to a customer. I am running this database on SQL Server 2000 if I right click on a table I have the option to dts the data to a text file but when I right click on the view I do not have this option. How can I make this virtual table an actual table. PLEASE Help Thank You, Ed
Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.
Hello all,my question might be trivial but since my background isn't DB i'll dareto ask it any way:how to create a real (not virtual) table from view1 & view2 ni sqlserver?what should i do specificly in the sql server application & what is thesyntax for that?i thank all of you,groupy.
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
I have merge replication between sql 2005 SP1, and when I disable distribution from my publisher, my tables with identity columns change the property "Not for replication" to "NO", does somebody knows if this is a known problem ?
Other information : the compatibilty level for database is 2000.
I have a SSIS package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.
I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.
Getting Error: [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked
I have written an installer that creates an MS SQL Database, creates the necessary data tables and then imports the data into the data tables. All this works fine. I now need to copy the web pages to a directory on the users machine and then make the chosen directory a virtual directory so the user can call the pages in the usual way (http://localhost/mytestsite/index.html)
Would anyone know how to create a virtual directory using dos command line parameters. I have seen something using "IIsVdir.vbs" but I do not have this script file.
What is a virtual server?In my new job,my boss mentioned about moving the websites from a physical server to a virtual server.Why is this done or needed? Also he insists that I buy and install SQL server 2000 enterprise edition in my laptop and use the enterprise manager to access their server.Can't I just download the client tools from their SQL package and be able to access their server?Do I need a CAL or is it just enough to have a permission in their network for my laptop?
I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.
I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.
Getting Error: [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked
using lookup component to split record if exist start update else insert as new record every time I start execute get error on lookup component low virtual memory
Kindly if anyone have suggest I will be appreciated
I have a sbs 2003 sp2 server running exchange 2003 sp2 it was running fine until one day I got this low virtual memory error, I checked the memory usage in the task manager and it is using 7 gigs of virtual memory, I increased the max size from 6 gigs to 8 gigs just to see what happens and now it is using 9 gigs of virtual memory. I don't have any clue as to what the problem is! Please Help!!!
I really need some help. This is what I'm trying to do: first of all, i have a PC running Vista. Now on that PC, i have a virtual machine using VMWare Workstation 6, which has Win XP with SP2 installed on it. On the virtual machine, i have Visual Studio 2005 and SQL Server 2005 Express Edition with SP2; now, the thing is, i have a program in C#, which is supposed to connect to a network, but since I'm running on just one PC which is not connected to a network, i instead connect to 127.0.0.1 via port 1433. Now here's where my problems start. At first, i could not connect because i kept receiving an error which said "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. (provedier: TCP provider, error: 0 - No connection could be made because the target machine actively refused it.)". A friend of my mine helped, so i did the following: i went into SQL Server Surface Area Configuration (configuration for localhost) , changed the remote connections to allow for both local and remote, using both TCP/IP and named pipes, restarted the database engine, started the SQL Server Browser; then i went to administrator tools, Data Sources (ODBC), and from System DSN tried to add an SQL Server. I gave it a name, and on the server textbox i tried typing "localhost", but doing so, i could not get past the next screen, (i did specify the port as 1433) where i have to chose between win nt and sql server authentication, and neither would work (even though i know the sa password and can log on to SQL Management Studio using that password). Then i tried copying the server name from the management studio, as the server when trying to add the system DSN. That seems to have worked, and i finished ading the SQL Server to the System DSN, using the database which i wanted. But after all these steps, i still get the exact same error as before - "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. (provedier: TCP provider, error: 0 - No connection could be made because the target machine actively refused it.)".If someone can please help - i've tried everything i can think of - i can access the internet from the virtual machine, i even tried disabling the firewall, but no luck. Is it because of the virtual machine? If so, can anyone suggest a solution? I actually doubt it, because i've even tried to install it on my pc, not the virtual machine, so on vista - and also installed SP1 for VS 2005 and SP1 for Vista, but still the same thing. Sorry for making this post so long, but if anyone can help, i'd really appreciate it. Thanks!
One of our clients controls data for about 150 companies. Each company has the same schema and is running SQL Server 7. The maintenence task is horrible. He would like to put all the data in one large database, but needs to control the access, so a user at one company selecting data in a table cannot have access to another company's data in the same table. I thought about using views for each company but that is 150 companies times 100 tables. With Oracle8i you can make virtual private databases. Can this be done in SQL Server 7? Thanks in advance
We have a Server that is set to use 80mb of a 128mb machine. The machine is also set to use up to 300mb of Virtual memory as needed.
After running for 10 days, we got the message "Low on Virtual memory". We did some looking and found SQLServer was using 300mb between real and virtual memory.
So the question: Is there a manual method to get SQLServer to release extra memory with out stopping and restarting the service?
We are using sql server 2008 r2 standard with sp2. I have a 12 GB database in production server, log file was set to 150MB with increment of 10 percent, in last 4 years database size has gone from 2gb to 12 gb. I ran following command
DBCC LOGINFO
And found I have 150 plus rows(which means 150 plus virtual log files)
I increased the size of log file to 25 percent of data file which comes to be approx 3gb and also set auto growth to 20 percent ...
Additional info: we have a log shipping environment in production, i am taking log backups every 15 mins.. still number of virtual log files is same , why is that , how to bring them between number of 25-50 as thats the recommended.
I need to have a way to intercept the SQL queries from an application written in VB and using a ODBC driver and modify the SQL queries before it goes through the ODBC driver and then to the database. This I need to do without modifying the original application.
The solution I have in mind is to write a ODBC virtual driver and configure my application to use my virtual ODBC driver. The ODBC virtual driver in turn will use the actual ODBC driver to the database. The virtual driver will basically intercept the SQL queries, modify it and then give it to the real ODBC driver.
My question is
1. Is this a feasible solution? 2. What should I do in order to implement the vitrtual ODBC driver. 3. Any pointer will be appreciated.
I am looking for some first-hand experiences from fellow DBA's where they had SQL Server running on a Win2K3 VM. What sort of issues (or successes) did you find re: resource sharing, swap files, etc? Are there any experiences where using a VM negatively affected your environment?
I have two instances of SQL Server running on my Development machine.I am having some performance problems and while investigating theproblem I saw with the Process Explorer form Sysinternals that bothinstances consume each 800 Mbytes of memory!I experimented with sp_configure and by giving both instances a fixedmemory size. Both methods do not seem to have any effect.Can anybody explain me why SQL Server is using so much memory?Thanks for any information.Evert WiesenekkerPSBesides the northwind database I only have one simple extra database(70 Mb in size) installed.
I've been discussing here a SQL 7 view which scrolls slowly when linked toan Access 2000 MDB. After trying various things, I've distilled it down tothe following: when the linked view has a virtual primary key, it is slow;when there is no virtual primary key, it scrolls quickly.Looking at Profiler, I see that when there is no virtual primary key, Accesssends a simple select command to SQL Server. However, when there is avirtual primary key, Access gets a list of primary key values, and then usesthose values to get records from SQL Server in groups of 10.This may account for why the scrolling is slow when there is a virtualprimary key. However, I wonder if there's any way to improve performance orotherwise work around this apparent bottleneck.The MS literature says that creating a virtual primary key will not affectperformance. However, since different methodologies for obtaining data areused with and without the virtual primary key, it seems that it does affectperformance, and certainly does in this situation.Thanks,Neil
I have 2 clusters I am reducing to 1 cluster with 2 instances. In order to save programming time I need to rename the sql instances to use the old names after the old servers are removed from the network. I plan on changing the IP address to match the old servers. Can this be done ?
We have a bunch of SQL databases that are used for a similar number of IIS sites (we are talking of close to 500 sites), dealing with a .NET e-learning application
While we manage to create a virtual folder for the e-learning content, we do have one database for each site. So, when it comes to update the whole platform, we are talking about running one script per site, which may take about 5 seconds per user per site. With a total of 300.000 users and sites, it may come to more than 17 days running the update script.
Is there a way similar to that on IIS to create a symbolic link from one table in one database to another table in a diferent database in a diferent or same server?
I am wondering if there is a way to solve a virtual memory error? We randomly get the following error when trying to run sycn over the http websync. Some clients have 512 MB running sql express, others are full instances that have 1.5 GB.
The merge process could not allocate memory for an operation; your system may be running low on virtual memory. Restart the Merge Agent.
for the last six months or so my pc has been shutting down all applications for no apparent reason when a 'low virtual memory' bubble appears. I have removed dozens of items, such as games, image editos; all programmes that require a lot of memory but it is no good. Every 40mins or so the pc decides to shut everything down and where it is impossible to start any further applications, unless I log off and on or shut down the pc myself. I really am fed up with this, its so annoying. Is it because of a virus or do I still have too much on my pc?
I am running a transactional replication on two servers. The distribution agent is reporting a timeout, eventhough the distribution database and the replicated database is on the same server.
Folks, We have just finished an application that requires every client PC to have a system DSN that reflects the new datasource. Is there an easier way to distribute this system DSN other than logging on to each pc and creating the datasource?? Thanks!
