Hi,
After deleting the Merge Publication from Replication, Publications,
there are still remains of the publication in the database. (the
rowguid column in every table and the shared sign on the database
icon) We would like to remove them without creating a new database. It
looks like running the sp_removedbreplication is the best option
here. We are trying to run the procedure but cannot find how to. Books
online not very helpful.
Please help!
Martin
Martin,
the syntax is exec sp_removedbreplication 'yourdatabasename'. The only thing
you need to be careful of is that it will remove all traces of replication -
if this database is used as a publisher for several publications and a
subscriber also, all this will be removed. Sometimes this procedure doesn't
remove all system objects, and Hilary Cotter has previously posted up his
own script (on http://www.ava.co.uk) that will do the extra bits, but I'd
try sp_removedbreplication first.
HTH,
Paul Ibison
|||in your publication database issue a sp_replicationdboption 'merge','false'
This will get rid of most of the merge replication metadata and unshare your
database is you don't have any transactional or snapshot publications
present there. To get rid of the transactional publications issue a
sp_replicationdboption 'PublicationDatabaseName', 'publish', 'false'
sp_replicationdboption 'PublicationDatabaseName', 'merge publish', 'false'
Getting rid of the Rowguid columns is more complex.
Try this
select 'alter table ['+sysusers.name+'].'+sysobjects.name +' drop column
rowguid'+char(13)+'GO' from sysobjects, sysusers, syscolumns where
sysobjects.type='u'
and sysobjects.uid=sysusers.uid
and syscolumns.name='rowguid'
and syscolumns.id=sysobjects.id
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:3hc9j09ufhe2v7nonkpmnrbafr4i5qk011@.4ax.com...
> Hi,
> After deleting the Merge Publication from Replication, Publications,
> there are still remains of the publication in the database. (the
> rowguid column in every table and the shared sign on the database
> icon) We would like to remove them without creating a new database. It
> looks like running the sp_removedbreplication is the best option
> here. We are trying to run the procedure but cannot find how to. Books
> online not very helpful.
> Please help!
> Martin
>
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Friday, March 23, 2012
How to run query on system tables of MS Jet
I am new to use Microsoft.Jet.OLEDB.4.0.
I need to run query (Select * from TABLE...) on system tables of my DB.
I can see data in those table from MS Access. But anytime I run query programatically, I got error saying
exception 80040E09, Record(s) cannot be read; no read permission on 'MSysRelationships'. (80040E09)
Can anyone tell me how to run query on sytem tables?
I can do this type of things in MS SQL Server.
Really appreciate your help!
/HannaI have exactly same problem. PLease help !
I need to run query (Select * from TABLE...) on system tables of my DB.
I can see data in those table from MS Access. But anytime I run query programatically, I got error saying
exception 80040E09, Record(s) cannot be read; no read permission on 'MSysRelationships'. (80040E09)
Can anyone tell me how to run query on sytem tables?
I can do this type of things in MS SQL Server.
Really appreciate your help!
/HannaI have exactly same problem. PLease help !
Wednesday, March 21, 2012
How to run a package using T-SQL?
The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.
So I wonder if there's any other ways to run the package using SQL?
Why why why, still no answer?|||From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "
Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.
|||Thank you, jwelch.
Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.
|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.
If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.
|||Thanks for your suggestion, thanks a lot! Problem resolved!How to run a package using T-SQL?
The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.
So I wonder if there's any other ways to run the package using SQL?
Why why why, still no answer?|||From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "
Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.
|||Thank you, jwelch.
Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.
|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.
If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.
|||Thanks for your suggestion, thanks a lot! Problem resolved!Monday, March 12, 2012
How to return current hour of week?
One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:
DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME
--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)
This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.
The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...
(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}
Is it correct?|||You need another set of parenthesis for proper order of operations:
declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.
These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.
-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)
Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.
Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167
etc.
Ideas??This should be a good start:
DECLARE @.startDate SMALLDATETIME
DECLARE @.endDate SMALLDATETIME
--Set Test value for Thursday Feb 22nd
SET @.endDate = ('02/22/2004 15:21:20')
--Set the start date to first day of the week
SET @.startDate = DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)
--Remove the time component of the start date
SET @.startDate = CONVERT(VARCHAR(10), @.startDate, 101)
--Count the number of hours from start of week (Mon) to @.endDate
SELECT DATEDIFF(Hour, @.startDate, @.endDate)
This could be converted to run as part of a select where @.endDate is provided by a column with Date data. If you use this verbatim you will want to test boundary conditions to assure they meet your requirements and I'm sure it could be optimized.
The key is in the setting of @.startDate [DATEADD(Day, -(DATEPART(WeekDay, @.endDate) - 1), @.endDate)].
Evaluation:
DATEPART(WeekDay, @.EndDate) = Day Code for Thursday (4)
(4) - 1 = 3*
-(3) = -3
DATEADD(Day, (-3), @.endDate) = Sets Date To Monday (*determined by the 1 subtracted from the original day code)|||One more try...
(24 * DatePart("dw",'2004-03-8 1:00:00')-1) + {fn Hour('2004-03-8 1:00:00')}
Is it correct?|||You need another set of parenthesis for proper order of operations:
declare @.TimeStamp datetime
set @.TimeStamp = getdate()
select (24 * (DatePart(dw,@.TimeStamp)-1)) + {fn Hour(@.TimeStamp)}|||set datefirst 1
declare @.timestamp datetime
set @.timestamp = getdate()
select (datepart(dd,@.timestamp)-1)*24+datepart(hh,@.timestamp)|||Just an observation that may not affect you, but there aren't always 168 hours in the week. If you observe Daylight Savings Time (http://webexhibits.org/daylightsaving/) then one week has 167 and one has 169 each year. If you need to keep Sidereal (http://tycho.usno.navy.mil/sidereal.html) time, then a different week each year can have a smidgeon more or less than 168.
These don't affect everyone, but they are the basis behind my always using date functions instead of "roll your own" functions like these. I'm not exactly sure how I'd handle this case, but I just wanted to raise the issue before folks go merrily trooping off with a solution that might not always work for them.
-PatP|||declare @.dt datetime
set @.dt = '20040309 1:00'
select DATEDIFF(hh,DATEADD(d,-(DATEPART(w,@.dt)+@.@.DATEFIRST-2)%7,LEFT(@.dt,13)),@.dt)
Hans.|||Just like skinning a cat, there is more than one way to do this and whatever you choose, the result is not very attractive.
Wednesday, March 7, 2012
How to retrieve system oledb Provider list
In my app I have to list of "oledb provider" to let the user choose the connection method. As follow:
1.Jet 4.0 OLE DB Provider
2.OLE DB Provider for DTS Packages
3.OLEDB Provider for Indexing Service
4.OLEDB Provider for ODBC Drivers
5.OLEDB Provider for OLAP Services
6.OLEDB Provider for Olap Services 8.0
7.OLEDB Provider for Oracle
8.OLEDB Provider for SQL Server
9.OLEDB Simple Provide
Which doesn't not support must be disabled.
Use which funciton can retrieve the Provider list of system or something like that.
Thanks.
--Master..xp_enum_oledb_providers
If you want to keep the resule,Create a table with the column returned by the above query.And
insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||en ... I still do not know how ..
where is the master..xp_enum_oledb_providers
is that a function?
can show me a sample ?
many thanks.
Originally posted by ClaireHsu
Master..xp_enum_oledb_providers
If you want to keep the resule,Create a table with the column returned by the above query.And
insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||Can show me a sample code to list the oledb providers of current system.
Thanks.|||I really want to know does the system support:
1.SQLOLEDB
2.DTSPackageDSO
3.SQLReplication.OLEDB
4.MSDataShape
5.VSEE Versioning Enlistment Manager Proxy Data Source
6.ADsDSOObject
7.MSOLAP
8.MSDAIPP.DSO
9.MSDASQL
10.MSDASQL Enumerator
11.Microsoft.Jet.OLEDB.4.0
12.Microsoft.Jet.OLEDB.3.51
13.SQLOLEDB Enumerator
14.MSDAOSP
15.MSDAORA
...
How to perform this check/text of the user's system in my app.
In my app the unsupport oledb provider must be visable.
--
Thanks.
1.Jet 4.0 OLE DB Provider
2.OLE DB Provider for DTS Packages
3.OLEDB Provider for Indexing Service
4.OLEDB Provider for ODBC Drivers
5.OLEDB Provider for OLAP Services
6.OLEDB Provider for Olap Services 8.0
7.OLEDB Provider for Oracle
8.OLEDB Provider for SQL Server
9.OLEDB Simple Provide
Which doesn't not support must be disabled.
Use which funciton can retrieve the Provider list of system or something like that.
Thanks.
--Master..xp_enum_oledb_providers
If you want to keep the resule,Create a table with the column returned by the above query.And
insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||en ... I still do not know how ..
where is the master..xp_enum_oledb_providers
is that a function?
can show me a sample ?
many thanks.
Originally posted by ClaireHsu
Master..xp_enum_oledb_providers
If you want to keep the resule,Create a table with the column returned by the above query.And
insert into OLEDB_Providers exec Master..xp_enum_oledb_providers|||Can show me a sample code to list the oledb providers of current system.
Thanks.|||I really want to know does the system support:
1.SQLOLEDB
2.DTSPackageDSO
3.SQLReplication.OLEDB
4.MSDataShape
5.VSEE Versioning Enlistment Manager Proxy Data Source
6.ADsDSOObject
7.MSOLAP
8.MSDAIPP.DSO
9.MSDASQL
10.MSDASQL Enumerator
11.Microsoft.Jet.OLEDB.4.0
12.Microsoft.Jet.OLEDB.3.51
13.SQLOLEDB Enumerator
14.MSDAOSP
15.MSDAORA
...
How to perform this check/text of the user's system in my app.
In my app the unsupport oledb provider must be visable.
--
Thanks.
Friday, February 24, 2012
How to retrieve data as XML from SQL database
Hi,
I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:
User searches for all entries with Forename = John; Results are listed in a gridview as per expected.
User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format
<records>
<record>
<Forename>John</Forename>
<Surname>Smith</Surname>
<Email>j.smith@.blah.com</Email>
<Ext>1234</Ext>
<DeptList>History</DeptList>
</record>
</records>
I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:
CREATE
PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML@.Forename
varchar(50),@.Surname
varchar(50),@.Ext
varchar(4),@.DeptList
varchar(50)AS
SELECT
Forename, Surname, Email, Ext, DeptListFROM
dbo.recordWHERE
ForenameLIKECOALESCE(@.Forename,Forename)ANDSurname
LIKECOALESCE(@.Surname,Surname)ANDExt
LIKECOALESCE(@.Ext,Ext)ANDDeptList
LIKECOALESCE(@.DeptList,DeptList)FOR
XMLAUTO,ELEMENTSIf someone could be kind enough to help me out with this, I'd be really grateful.
Many thanks,
Tom
Hi tomosap,
Yes, this is a good way to get that.
Another way is to fill everything to run the query and fill data into a DataSet. You will be able to get the DataSet's data in XML format by calling DataSet.WriteXml() method.
Both these methods are good.
HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!
|||Hi,
Thanks for your reply - I'm afraid that my needs have slightly changed since this was posted. I now have an XML file which I transform using XSL so it displays all the data in a gridview. What I want to do is to have a search form so that I can search the data that's in the gridview. I.e.
Name: [INPUT]
Results in Gridview...
User enters name in input, clicks search, then all matches in Name column on the gridview is displayed. I've successfully managed to do this with a basic SQL database using the object source control and passing paramaters, but I don't know how to do this when the data source is XML.
All help would be appreciated.
Thanks,
Tom
Sunday, February 19, 2012
How to restore system messages to master.dbo.sysmessages
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix DurnDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix Durn
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix DurnDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Flix Durn
How to restore system messages to master.dbo.sysmessages
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix DuránDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix Durán
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix DuránDo you happen to have another SQL Server with similar setup? If so, you
could copy the messages across. Look up sp_addmessage in Books Online too.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Felix D." <Felix.Duran@.segundamano.es> wrote in message
news:eRvxjbS2DHA.2032@.TK2MSFTNGP09.phx.gbl...
Hi,
I suspect that after a blackout here at the office (time enough to loose the
UPS's) I have lost some messages from the sysmessages table in the master
database.
I have discovered this when trying to construct a snapshot replication in
one server, and discovering, after diving into SP code for hours, that the
SP that configure the replication uses some messages to name the job step of
the agents that perform replication, and also discovering that the messages
are not in my database.
So, what can I do?. Do I have to perform a restore of the master database?.
What happen if I have the replication active in the server?.
Thanks
Félix Durán
Subscribe to:
Posts (Atom)