Extracting Sql: extracting

Showing posts with label extracting. Show all posts

Friday, February 24, 2012

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?
Check out www.lumigent.com.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

Extracting XSD Collection

I've got a really quick - and I'm sure easy - question...

I'm wondering is there any way to extract an XSD Schema collection so that it appears as a well formed XML document?

The only thing I've seen is if you right click on a sche,a collection, you can either script it as a CREATE or a DROP. The CREATE script has no formatting whatsoever - it's all one one line.

If anyone has done this or knows of an effective way to accomplish this, your help would be greatly appreciated!

Thanks!

SELECT xml_schema_namespace('db_SchemaName','XmlSchemaCollectionName')

e.g.: SELECT xml_schema_namespace('dbo','MyXmlSchema')

|||Thanks Phe....!!

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
--
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningfu
l
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, bu
t
> when they aren't, your going to have a hard time shaping them to a meaning
ful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?
I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>
|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningful
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, but
> when they aren't, your going to have a hard time shaping them to a meaningful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract all
the data into a table. Can anyone give me some tips on the easiest way to do
this. The data is written to the table and will be extracted in real time.
Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.
|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column in
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract al
l
the data into a table. Can anyone give me some tips on the easiest way to d
o
this. The data is written to the table and will be extracted in real time.Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column i
n
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting XML data from SQL 2000 to SQL 2005 database table.

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

VDeevi wrote:
Hi,
We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.
Thanks

What methods have you tried so far and why do they fail?

-Jamie|||Hi Jamie,

Thanks for your reply, We tried with OPENXML, sp_xml_prepareddocument etc. but they are all round about things we want to know is there any simple technique in SSIS in order to retrieve the data from XML stored in SQL Server 2000 TEXT field.

Thanks,|||I've just had a go at this...

When you select the data a TEXT field appears in the SSIS pipeline as a DT_TEXT. To insert it into a XML field use a derived column transform to convert it to DT_NTEXT.
If you don't like DT_NTEXTs in your pipeline then convert it to DT_WSTR and then to DT_STR which can also be inserted into an XML field.

So yes, SSIS can do this very very well.

-Jamie

Extracting words from strings

I've done up this script which I'll turn into a user-defined function for
extracting words from text. However it seems a little over-complicated.
Anyone got any ideas of how to simplify it?
Thanks
Damien
SET NOCOUNT ON
DECLARE @.word TINYINT
DECLARE @.search_string VARCHAR( 2000 )
DECLARE @.delimiter CHAR( 1 )
-- Initialise variables
SET @.word = 1
SET @.search_string = 'This is a test function'
SET @.delimiter = ' '
SET @.search_string = @.search_string
DECLARE @.return VARCHAR( 25 )
DECLARE @.string CHAR( 1 )
DECLARE @.pos SMALLINT
DECLARE @.old_pos SMALLINT
DECLARE @.start SMALLINT
DECLARE @.total_len SMALLINT
DECLARE @.words SMALLINT
DECLARE @.len SMALLINT
-- Initialise variables
SET @.start = 1
SET @.old_pos = 1
SET @.pos = 1
SET @.words = 1
SET @.len = 0
SET @.total_len = LEN( @.search_string )
-- Check for errors
IF @.total_len = 0
BEGIN
RAISERROR( 'Invalid search string: %s.', 16, 1, @.search_string )
END
-- SELECT @.search_string AS '12345678901234'
-- Loop thru string one character at a time
WHILE @.pos <= @.total_len
BEGIN
SET @.string = SUBSTRING( @.search_string, @.pos, 1 )
-- Check we're not at the end of the string
IF @.word > @.words BREAK
IF @.pos = @.total_len
BEGIN
SET @.start = @.old_pos
SET @.len = @.pos - @.old_pos + 1
BREAK
END
-- Found a space; any previous text is the first word, any further text
is further word
IF @.string = @.delimiter
BEGIN
IF @.words = @.word
BEGIN
-- Current word is same as requested word
SET @.start = @.old_pos
SET @.len = @.pos - @.old_pos
BREAK
END
ELSE
BEGIN
SET @.old_pos = @.pos + 1
END
SET @.words = @.words + 1
END
-- Debuggin
--SELECT @.pos AS '@.pos', @.string AS '@.string', @.start AS '@.start',
@.old_pos AS '@.old_pos', @.word AS '@.word', @.words AS '@.words', @.len AS '@.len'
-- Increment current position
SET @.pos = @.pos + 1
END
-- Set return value
SET @.return = SUBSTRING( @.search_string, @.start, @.len )
function_exit:
-- RETURN @.return
SELECT @.return, LEN( @.return )
SET NOCOUNT OFFTry this....
if exists (select 'Y' from dbo.sysobjects where id =
object_id(N'[dbo].[fn_Split]'))
BEGIN
Drop Function fn_Split
END
GO
Create Function fn_Split
(
@.SplitString text,
@.Delimiter VARCHAR(1) = ' '
) RETURNS @.Dictionary TABLE (word varchar(8000)) AS
BEGIN
DECLARE
@.ParsedString VARCHAR(8000),
@.element VARCHAR(8000),
@.start_pos int,
@.end_pos int,
@.DataLength int,
@.BytesProcessed int,
@.length int
SELECT
@.BytesProcessed = 0,
@.DataLength = DataLength(@.SplitString)
SELECT @.ParsedString = Substring(@.SplitString, @.BytesProcessed + 1, 8000)
WHILE @.BytesProcessed < @.DataLength BEGIN
SELECT
@.BytesProcessed = @.BytesProcessed + DataLength(@.ParsedString)
SELECT
@.start_pos = 0
SELECT @.end_pos = CHARINDEX(@.Delimiter, @.ParsedString, @.start_pos + 1)
WHILE @.end_pos <> 0 BEGIN
SELECT @.length = (@.end_pos - @.start_pos) - 1
SELECT
@.element = SUBSTRING(@.ParsedString, @.start_pos + 1, @.length),
@.start_pos = @.end_pos
INSERT INTO @.Dictionary
VALUES (@.element)
SELECT @.end_pos = CHARINDEX(@.Delimiter, @.ParsedString, @.start_pos + 1)
END
SELECT @.ParsedString = SUBSTRING(@.ParsedString, @.start_pos + 1, 8000)
SELECT @.ParsedString = @.ParsedString + Substring(@.SplitString,
@.BytesProcessed + 1, 8000 - DataLength(@.ParsedString))
END
SELECT @.element = @.ParsedString
IF @.element IS NOT NULL
INSERT INTO @.Dictionary
VALUES (@.element)
RETURN
END
GO
select * from dbo.fn_Split('This is a test function',' ')|||I can't follow exactly what you're trying to do in that code but it looks
like you're trying to return the string of searched text from a string being
searched. For example, searching for "B" in "ABC".
In that case you could do something like:
DECLARE
@.string VARCHAR(10)
, @.search VARCHAR(10)
SELECT
@.string = 'ABC'
, @.search = 'B'
SELECT SUBSTRING( @.string, CHARINDEX(@.search, @.string), LEN(@.search) )
That code will return "B"; if you searched for "D" it would return an empty
string.
But doing that doesn't really make sense, because you already know the
string you're searching for. Couldn't you just use CHARINDEX(), and if it
returns > 0 reuse the string that is the text you're searching for?
Only caveat in all that is that it is not case sensitive for
case-insentitive collations.
"Damien" wrote:

> I've done up this script which I'll turn into a user-defined function for
> extracting words from text. However it seems a little over-complicated.
> Anyone got any ideas of how to simplify it?
> Thanks
>
> Damien
>
> SET NOCOUNT ON
> DECLARE @.word TINYINT
> DECLARE @.search_string VARCHAR( 2000 )
> DECLARE @.delimiter CHAR( 1 )
> -- Initialise variables
> SET @.word = 1
> SET @.search_string = 'This is a test function'
> SET @.delimiter = ' '
> SET @.search_string = @.search_string
> DECLARE @.return VARCHAR( 25 )
> DECLARE @.string CHAR( 1 )
> DECLARE @.pos SMALLINT
> DECLARE @.old_pos SMALLINT
> DECLARE @.start SMALLINT
> DECLARE @.total_len SMALLINT
> DECLARE @.words SMALLINT
> DECLARE @.len SMALLINT
> -- Initialise variables
> SET @.start = 1
> SET @.old_pos = 1
> SET @.pos = 1
> SET @.words = 1
> SET @.len = 0
> SET @.total_len = LEN( @.search_string )
> -- Check for errors
> IF @.total_len = 0
> BEGIN
> RAISERROR( 'Invalid search string: %s.', 16, 1, @.search_string )
> END
> -- SELECT @.search_string AS '12345678901234'
> -- Loop thru string one character at a time
> WHILE @.pos <= @.total_len
> BEGIN
> SET @.string = SUBSTRING( @.search_string, @.pos, 1 )
> -- Check we're not at the end of the string
> IF @.word > @.words BREAK
> IF @.pos = @.total_len
> BEGIN
> SET @.start = @.old_pos
> SET @.len = @.pos - @.old_pos + 1
> BREAK
> END
> -- Found a space; any previous text is the first word, any further tex
t
> is further word
> IF @.string = @.delimiter
> BEGIN
> IF @.words = @.word
> BEGIN
> -- Current word is same as requested word
> SET @.start = @.old_pos
> SET @.len = @.pos - @.old_pos
> BREAK
> END
> ELSE
> BEGIN
> SET @.old_pos = @.pos + 1
> END
> SET @.words = @.words + 1
> END
> -- Debuggin
> --SELECT @.pos AS '@.pos', @.string AS '@.string', @.start AS '@.start',
> @.old_pos AS '@.old_pos', @.word AS '@.word', @.words AS '@.words', @.len AS '@.le
n'
> -- Increment current position
> SET @.pos = @.pos + 1
> END
>
> -- Set return value
> SET @.return = SUBSTRING( @.search_string, @.start, @.len )
>
> function_exit:
> -- RETURN @.return
> SELECT @.return, LEN( @.return )
> SET NOCOUNT OFF
>|||Use a table of sequential numbers for this purpose instead of loop routines.
You can create one easily using the hack:
SELECT TOP 2000 IDENTITY ( INT ) n INTO Nbrs
FROM sysobjects s1, sysobjects s2 ;
Now your requirement is simpler:
DECLARE @.search_string VARCHAR( 2000 )
DECLARE @.delimiter CHAR( 1 )
SET @.search_string = 'This is a test function'
SET @.delimiter = ' '
SELECT SUBSTRING( @.search_string, n, CHARINDEX( @.delimiter,
@.search_string + @.delimiter, n ) - n )
FROM Nbrs
WHERE SUBSTRING( @.delimiter + @.search_string, n, 1 ) = @.delimiter
AND n < LEN( @.search_string ) + 1 ;
Anith

Extracting Users for restore

I'm refreshing (restoring) a database and would like to keep the users from
the original database. Is there a efficient way to extract users, restore
the database and then re-apply the users. I'm not talking about logins as
I'm familiar with sp_help_revlogin.
Thanks.
RonThe Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||Ron,
You can get the information from the database prior to the restore and save
it somewhere for scripting back into the database later.
For SQL 2000:
sp_helpuser
sp_helprotect
Although these still work in SQL 2005, they miss some securables. You can
also look at the new dynamic management views. Start by examining:
sys.database_permissions
sys.database_principals
sys.database_role_members
Here are a couple of queries that may help you get started.
-- Permissions
select u.name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
-- Role Memberships
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u
on m.role_principal_id = u.principal_id
join sys.database_principals u2
on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from a
database, then 2) have that database refreshed from another databases, then
3) re-apply the users from step 1.
"Andrew J. Kelly" wrote:
> The Users stay with the db when you back it up and will be there after the
> restore.You may need to remap the login to the users an can do that with
> sp_change_users_login. Here is a bunch of info related to that topic.
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
> system dbs 2005
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> > I'm refreshing (restoring) a database and would like to keep the users
> > from
> > the original database. Is there a efficient way to extract users, restore
> > the database and then re-apply the users. I'm not talking about logins as
> > I'm familiar with sp_help_revlogin.
> >
> > Thanks.
> >
> > Ron
>
>|||You can script the users with Enterprise Manager. Right click on the db and
choose All tasks - Generate SQL Scripts. One of the tabs has an option to
script users and permissions for the db.
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
> No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> a
> database, then 2) have that database refreshed from another databases,
> then
> 3) re-apply the users from step 1.
> "Andrew J. Kelly" wrote:
>> The Users stay with the db when you back it up and will be there after
>> the
>> restore.You may need to remap the login to the users an can do that with
>> sp_change_users_login. Here is a bunch of info related to that topic.
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
>> Moving
>> system dbs 2005
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's 2000
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
>> Moving Users
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after
>> a Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery
>> Articles
>> for SQL Server
>> http://www.support.microsoft.com/?id=320125 Moving a Diagram
>> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
>> 2000
>> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
>> Roles and Permissions
>> --
>> Andrew J. Kelly SQL MVP
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
>> > I'm refreshing (restoring) a database and would like to keep the users
>> > from
>> > the original database. Is there a efficient way to extract users,
>> > restore
>> > the database and then re-apply the users. I'm not talking about logins
>> > as
>> > I'm familiar with sp_help_revlogin.
>> >
>> > Thanks.
>> >
>> > Ron
>>
>>|||Thanks Andrew - it seems to work fine in SQL2000. However in SQL2005, I
don't see where the database roles get extracted.
For example here's the output from SQL2000 extracting users and database
roles:
if not exists (select * from dbo.sysusers where name = N'abrown')
EXEC sp_grantdbaccess N'abrown'
GO
exec sp_addrolemember N'db_datareader', N'abrown'
GO
exec sp_addrolemember N'db_datawriter', N'abrown'
GO
Here's the output from SQL2005 extracting users and schemas (no mention of
extracint db roles):
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'abrown')
EXEC sys.sp_executesql N'CREATE SCHEMA [abrown] AUTHORIZATION [abrown]'
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'abrown')
CREATE USER [abrown] FOR LOGIN [abrown] WITH DEFAULT_SCHEMA=[abrown]
GO
I don't see how in SQL2005 users get added to datareader?
Thanks
Ron
"Andrew J. Kelly" wrote:
> You can script the users with Enterprise Manager. Right click on the db and
> choose All tasks - Generate SQL Scripts. One of the tabs has an option to
> script users and permissions for the db.
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
> > No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> > a
> > database, then 2) have that database refreshed from another databases,
> > then
> > 3) re-apply the users from step 1.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The Users stay with the db when you back it up and will be there after
> >> the
> >> restore.You may need to remap the login to the users an can do that with
> >> sp_change_users_login. Here is a bunch of info related to that topic.
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
> >> Moving
> >> system dbs 2005
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's 2000
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> >> Moving Users
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after
> >> a Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >> http://www.support.microsoft.com/?id=307775 Disaster Recovery
> >> Articles
> >> for SQL Server
> >> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> >> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
> >> 2000
> >>
> >> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> >> Roles and Permissions
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> >> > I'm refreshing (restoring) a database and would like to keep the users
> >> > from
> >> > the original database. Is there a efficient way to extract users,
> >> > restore
> >> > the database and then re-apply the users. I'm not talking about logins
> >> > as
> >> > I'm familiar with sp_help_revlogin.
> >> >
> >> > Thanks.
> >> >
> >> > Ron
> >>
> >>
> >>
> >>
>
>

Extracting Users for restore

Extracting Time from the SQL DateTime field.

Any help on extracting the time part from the datetime field in SQL
database.

even though i had entered only the time part in the database when i
extract the field it gives me only the date part. im using Vb.net
datagrid as a front end.

any assistance appreciated!! :?:

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict254266.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989"v0lcan0" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_877989_087c9582517dd6a1f7813c2a746cac72@.dbf orumz.com...
> Any help on extracting the time part from the datetime field in SQL
> database.
> even though i had entered only the time part in the database when i
> extract the field it gives me only the date part. i'm using Vb.net
> datagrid as a front end.
> any assistance appreciated!! :?:
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/General-Dis...pict254266.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=877989

See CONVERT() in Books Online:

select convert(char(8), getdate(), 108)

For more general information about working with datetime data, see here:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon

Extracting the sql query in the stored procedure in asp.net

Hi,

I have set programmaticaly as follows for sql dataadapter

Commandtext="name of stored proc "

commandtype="stored proc"

now I want the query in the stored proc which i will store in the string .is there any way to get the query from sp progrmmaticaly?

Swati

Usesp_helptext

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id
|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

for extract customers betweeen 10000 and 11000 based on customer_id|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

Extracting the parameter values

I have a report application where I am using Microsoft AxBrowser and url acess to view the reports. I have a report with multivalued parameter. I need to extract the user selected parameter value for that rpeort for some other purpose. How to extract the parameter value?

Please help

Do you have access to the cookies collection of the AxBrowser control? If so, then you can try to get the RS session ID and call the SOAP API GetExecutionInfo() on that session. It will return the effective parameters for the current session. Assuming you haven't changed them then it should be the parameters that were used to render the report.

Question: Why aren't you using the viewer control?

|||Thanks for the reply John.
I am passing the default parameters to the report and getting it.
I needed to extract the parameter values when the user inputs (selects) some other value.
I could able to retrieve the parameter values by capturing the url after navigation and used some string functions. I used the Navigate2 event of AXBroweser. This works for me becuase I had to extract values for only two reports and I know the names of the parameters
we have SQL2000 and Reporting Services2000. I guess we can use report viewr control only with Reporting Services 2005. Correct me if I am wrong.
Thanks,
Siri..
|||

Yes the viewer control only works with the 2005 endpoints.

Your solution will only work in the case where parameters are passed on the URL string, which in general is not the case. I would recommend trying to get the sessionId from the AxBrowser and calling GetExecutionInfo() as it should work regardless of how the parameters are set.

Extracting the duplicates using Fuzzy Grouping

Hi,

I have an Oracle table called "Party" which contains Party_Id as primary key and have Party_Name, Party_Addr etc., as fields. We have lot more duplicate party details such as (party_name and party_addr) in this table. We are trying to aviod duplicates using FUZZY logic of SSIS.

1. Is any body suggest me how to create package to avoid duplicates using Fuzzy logic for this scenario(Step by step instructions are good for me to understand SSIS).

2. Could you please provide me some samples for FUZZY(Please send me a sample to my email)

Ever heard of Google? http://www.google.ie/search?hl=en&q=ssis+fuzzy&meta=

Emailing defeats the object of having online forums and it is far easier for people to reply here than to send an email. Perhaps you could do the legwork yourself instead of asking people to do it for you?

-Jamie

extracting the duplicate values

hi all
i want to extract only the duplicate values from a table
can any one know the sql syntax for that
thnking u
jagselect foo from yourtable
group by foo having count(*) > 1|||thnk u rudy thak u very much
i got it!!!!!!!!!!!!!!!

Friday, February 24, 2012

extraction of data from transaction log of SQL Server 2000

extraction of data from transaction log of SQL Server 2000

extraction of data from transaction log of SQL Server 2000

Extracting XSD Collection

Extracting XML data to columns during query

Extracting XML data to columns during query

Extracting XML data stored in a LONGTEXT column

Extracting XML data stored in a LONGTEXT column

Extracting XML data from SQL 2000 to SQL 2005 database table.

Extracting words from strings

Extracting Users for restore

Extracting Users for restore

Extracting Users for restore

Extracting Time from the SQL DateTime field.

Extracting the sql query in the stored procedure in asp.net

extracting the specified record no of records from database table

extracting the specified record no of records from database table

Extracting the parameter values

Extracting the duplicates using Fuzzy Grouping

extracting the duplicate values

Extracting Sql

Blog Archive

About Me