I have already created Tran. Replication for one database and it is working fine. But i have added 3 tables to the database and i want to do replication for that tables with same publications . I mean i can go to Publishers Properties and add articles but the problem is:
1)When i run the snapshot agent,it creates the snapshot whole database which is bigger in size .
2)Is there a way i can run snapshot agent for remaining articles in same publication.
3)How to do it step by step
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:
This version of the Web Services Description Language (WSDL) is not supported.
We have a datawarehouse that's built nightly through a number of Integration Services packages. I have several packages that compile and aggreage data used by a set of reports constructed and presented using Reporting Services. I would like to augment the packages to incorporate a step that will create a snapshot for each report. Can anyone provide a set of pointers or sample code to accomplish this?
The data is static and this method would allow the reports to be constructed before the first user runs them (caching would require them to be run once). I would also like to delete the snapshot if a report extract needed to be run again. This can occur in some occassions. I'd like to automate this as much as possible.
I imagine I'de be able to do this using a script task in SSIS that interactes with the reporting services web service in some way.
Dear All- i am facing a problem with them creation of the execution Snapshot for my report
simply when i try to create a one and after choose the schedule setting and i press OK to finish an error appear mention "The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting) (Report Services SOAP Proxy Source)"
i like to mention also i choose the datasource credentials is the redio button Windos integrated security and also the reporting services and the database are exit on the same machine
i hope someone help soon as our work is affected due to this issue
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005. It's all done by RMO.
Following Scenario: 1. PublicationDB was created by User1(sysadmin) ... successful 2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner) 2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful 2.3 Add Articles to publication ... successful 2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:2007-09-12 12:05:46.58 --------------------------------------2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest2007-09-12 12:05:46.60 -ReplicationType 22007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard2007-09-12 12:05:46.60 -DistributorSecurityMode 12007-09-12 12:05:46.60 -PublisherSecurityMode 12007-09-12 12:05:46.60 --------------------------------------2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'2007-09-12 12:05:46.96 The replication agent had encountered an exception.2007-09-12 12:05:46.96 Source: Replication2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command. Contact your system administrator.2007-09-12 12:05:46.96 Message Code: 142602007-09-12 12:05:46.96
Configurations: -All Users have rights for read and write on the snapshotfolder including the Agent -All users are defined in the same windows domain -the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"! But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem? I appreciate any support. MariJo
After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?
We have transactional replication running with a seperate publisher/distributor/subscriber. I want to add a couple of articles to the publication, and then initialise them. I have added the articles and run sp_refreshsubscriptions
I now want to refresh the subscriptions. I have selected not to lock the tables on the snaphot tab of the publication properties, but whenever I run the snapshot agent it locks the application solid! Its odd, as soon as I run the snaphot agent, the phones start ringing within minutes. The application is Great Plains and I have set the snapshot agent to run nightly anyway.
Is there any way I can run the snapshot agent during working hours to refresh this one article? Once I have successfully done this, I have a number of articles want to add - but I can't lock the tables when refreshing the initial snaphot.
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
HI, I want to know if there's an easier way to do a calculation on the remaining total of the months. For example: month 09 would be (total *3 ) because 9+3 = 12 and there are 12 months in a year. month total 01 2217.3700 02 2187.6600 03 2243.6500 04 2216.1000 05 2374.4200 06 2296.2200 07 2266.6000 08 2164.2600 09 2085.2400
recently i noticed that my trans Log size is not increasing(it is constant) , but the databse size is increasing at high speed. why is it so? earlier the reverse used to happen i.e databse size increased only after a certain period. and log size increased at const. rate.
I need to pull records from single table such that I get a subset defined like this:
( acctcode = 'xh364' and product = 'T&E' )
And return all of the rest of the records:
( acctcode = '%' and product = '%' )
Can I do this within a WHERE clause, or will this require CASE / ELSE? There will be other specific acctcode/product rules that will be added later. I could do this with a UNION, but I need to avoid that if possible.
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
I have seen the threads already in here about connections and SQL Server but I too am having a connection pool problem.
I am explicitly making a connection, creating a command object, executing a datareader then closing the command and then disposing the connection. The connection just prior to the dispose is definately closed.
However when I do an sp_who in SQL Server the connection is still listed. I have a connection timeout of 30 seconds, so I am considering reducing this to 5 seconds but dont wish to. So therefore when a process is called repeatedly instead of using an existing connection in the pool, it thinks that this connection is still active and therefore creates a new connection. Because all of this is in an ascx, I cant code for one connection to stay open for all 30 instances of the control.
This is a problem because I am creating controls at run time and using page.databind and at that point, I exceed the connection pool and therefore the site crashes out to the "SQL Server Timeout" error.
My replication application need to be able to skip the from-last-stop remaining logs (that means to skip the logs generated from the last stop time of my replication application), how can i realize it? thanks.
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution: - I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem: This configuration gives me only the first erroneous column in the row being processed.
Question: Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank FROM RPL ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I my requirement I need to read all the files from folder (one by one) and insert data from those files into the database table . I am facing one issue here . If suppose while executing if any of the file fails its not executing the other files. Package stops execution.
I cannot use Redirect rows option beacuse as per requirment if file has some Data problem , I am suppose to ignore the file instead of Data Rows.
Is there is any property in for each file task .....kindly suggest
I am a serious NT sql NEWBIE. I have installed v7 on my NT/MIIS. I want to allow another party now to create tables on their SQL db they have runnning on my server (their own domain)
What is the best way -? Do they send me the scripts or is there a way to run them remotely and create them.
Hi! I am using VS 2003 (v 1.1) I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net. How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation. Give me the complete code with can example (if possible)
I was just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.
Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel
