how to format the date data type input in DD-MM-YY

hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.Originally posted by verybrightstar
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,convert(varchar,'2/6/2004 11:07:46 AM',105))

How to format Field ?

HOw to set the field (Password) in sqlServer database to '*' .
For example :
I key in VB : 123
In SqlServer database should appear : ***
Yoir help will be appreaciated.
daniel.Daniel:
I don't think that this is a datawarehouse question, but nevertheless
In SQLServer it is not possible to individually mask (as in MS Access) or
encrypt a single column as such. You would have to write your own encryption
routine or get one (many are available).
I usually encrypt my passwords and also set proper user rights restrictions
in order to protect the passwords.
Balaji Vasudevan
"Daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:E11D06A0-335A-481D-8A7B-7D32FE0F6E9D@.microsoft.com...

quote:

---

> HOw to set the field (Password) in sqlServer database to '*' .
> For example :
> I key in VB : 123
> In SqlServer database should appear : ***
> Yoir help will be appreaciated.
> daniel.

---

How to force a new page inbetween Subreports

Anyone know how to force a new page inbetween Subreports?
I have for example 10 reports which I want to consolidate into a single area of work as one report (i.e. HR reports for month to date).
So Ive created a blank report and added in the subreports needed.
When I go to repreview the reports they all come across as one page.
What im looking for is each being on a separate page or at least starting on a separate page, and at best when I export to excel then the ports appearing as different tabs on the worksheet and not part of one continuous page.
Anyone have an idea on how to do this?
Anyhelp MUCH appreciated.Rectangles support before and after page breaks:
* Place empty rectangles between the subreports and set page break on the
rectangles.
* Place each subreports in a rectangle and place a page break on the
rectangle
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Johnson" <RyanJohnson@.discussions.microsoft.com> wrote in message
news:801596CC-67F5-4EBF-A848-BF40FD30B185@.microsoft.com...
> Ack.. but get the following when viewed in excel.
> 'Subreports within table/matrix cells are ignored.'
> And no matter how many times I change the force new page option "page
break at end", it resets itself back to blank.
>|||Try running the report that follows this posting. It uses 2 textboxes
separated by a rectangle.
The rectangle has a page break set on it. You should get 2 pages.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"chad" <chad@.discussions.microsoft.com> wrote in message
news:D4446D8B-0122-413E-9B6A-B1C89DFA3D6E@.microsoft.com...
> rectangles do have these properties. however, these properties don't seem
to do anything for me. i selected page break before, but it does not insert
the page break.
> "Bruce Johnson [MSFT]" wrote:
> > Rectangles support before and after page breaks:
> > * Place empty rectangles between the subreports and set page break on
the
> > rectangles.
> > * Place each subreports in a rectangle and place a page break on the
> > rectangle
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Ryan Johnson" <RyanJohnson@.discussions.microsoft.com> wrote in message
> > news:801596CC-67F5-4EBF-A848-BF40FD30B185@.microsoft.com...
> > > Ack.. but get the following when viewed in excel.
> > > 'Subreports within table/matrix cells are ignored.'
> > >
> > > And no matter how many times I change the force new page option "page
> > break at end", it resets itself back to blank.
> > >
> > >
> >
> >
> >
RectangleWithPageBreak.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="CustomerID_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Top>1.875in</Top>
<rd:DefaultName>CustomerID_1</rd:DefaultName>
<Height>0.25in</Height>
<Width>1in</Width>
<CanGrow>true</CanGrow>
<Value>Second Field</Value>
<Left>0.875in</Left>
</Textbox>
<Rectangle Name="rectangle1">
<ZIndex>1</ZIndex>
<Top>0.625in</Top>
<Height>1in</Height>
<Width>2in</Width>
<Style />
<Left>0.875in</Left>
<PageBreakAtStart>true</PageBreakAtStart>
</Rectangle>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.125in</Top>
<rd:DefaultName>CustomerID</rd:DefaultName>
<Height>0.25in</Height>
<Width>1in</Width>
<CanGrow>true</CanGrow>
<Value>First Field</Value>
<Left>0.75in</Left>
</Textbox>
</ReportItems>
<Style />
<Height>2.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<Width>6.62501in</Width>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4f653deb-b9af-49ca-98e1-fe972d395f88</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

How to fix corrupt database?

Hello,

I am trying to run the example program: Coding4Fun: Building a Family History Web Service at this URL

http://msdn.microsoft.com/coding4fun/xmlforfun/familyhistory/default.aspx

The project has an SQL Express database Family.mdf. When I try and open Family.mdf in VS 2005, I get this error:

'FAMILY.MDF' cannot be upgraded because its non-release version (587) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

I can create a new database, but then the stored procedures will be lost.

Any suggestions how to repair the database?

Thanks

That database was based on a beta build. Beta build were not supposed to be supported after the RTM version is launched. í dont′think that there is a upgrade path for this internal version to the RTM version. If you still have a beta SQL Server 2005 (Yukon) you can try to attach the db to that one and script the structure out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hello Jens,

Thanks for taking time to reply to this question. I dont have a beta version of SQL 2005, so I guess I will have to just create a new database.

Thank you,

Tom

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?
JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>
|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
There is no built-in and generally applicable method to determine the
progress of an UPDATE (e.g. to be able to answer how much longer it will take
for the UPDATE to complete). The reason being that UPDATE can be processed in
many different way.
If we are talking about a very large update, you may want to break it into
multiple smaller batches of update, and insert code yourself to report the
progress.
Linchi
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Answered in .programming. Please do not double-post. If you want to post
to multiple newsgroups, post to all relevant groups at once so people will
not waste time trying to answer questions that have already been answered.
"DR" <softwareengineer98037@.yahoo.com> wrote in message
news:uaiJVZ7MIHA.5224@.TK2MSFTNGP02.phx.gbl...
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time,
> is there anyway for me to check the status of that update process?
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specifi
c
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, i
s
> there anyway for me to check the status of that update process?
>
>

how to find out column name with sql server store procedure

Hi
I have table which has Fields A,B,C and D for example. is thier any way to
retrive these field column as inline table function or store procedure.
thanks
some thing like select column_name from xxxx
thanksUSE pubs
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'autho
rs'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
> Hi
> I have table which has Fields A,B,C and D for example. is thier any way to
> retrive these field column as inline table function or store procedure.
> thanks
> some thing like select column_name from xxxx
> thanks
>|||Or get it all in one variable with this technique:
USE pubs
GO
DECLARE @.fields VARCHAR(1000)
SELECT @.fields = ISNULL( @.fields, '' ) + column_name + ', '
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'
-- Trim trailing space and comma
SET @.fields = SUBSTRING( @.fields, 1, LEN( @.fields) -1 )
SELECT @.fields
-- sp_columns gives some useful information too.
EXEC sp_columns 'authors'
"Tibor Karaszi" wrote:

> USE pubs
> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aut
hors'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
>
>

How to find Dependencies ?

I am looking for code or a solution to find all
dependencies in a .db fast and simple (or code). Any
suggestions would be great!
Example:
Here is the dependencies among these 4 tables:
1) Publishers (base table): does not depend on other
tables.
2) Authors (base table): does not depend on other tables.
3) Titles: has foreign key which depend on publishers
table.
4) Titleauthor: has 2 foreign keys which depend on
authors and publishers titleswhy?
is it because you want to make sure your new code doesn't
break any existing code?
check out DB Ghost @. www.dbghost.com - it can guarentee
your code doesn't break anything and make deploying your
changes a breeze.
>--Original Message--
>I am looking for code or a solution to find all
>dependencies in a .db fast and simple (or code). Any
>suggestions would be great!
>Example:
>Here is the dependencies among these 4 tables:
>1) Publishers (base table): does not depend on other
>tables.
>2) Authors (base table): does not depend on other tables.
>3) Titles: has foreign key which depend on publishers
>table.
>4) Titleauthor: has 2 foreign keys which depend on
>authors and publishers titles
>.
>|||Have you looked into the stored procedure sp_depends?
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all > dependencies in a .db fast and simple (or code). Any > suggestions would be great!
> > Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other > tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers > table.
> 4) Titleauthor: has 2 foreign keys which depend on > authors and publishers titles
>|||Yes, sp_depends does not solve the issue. It shows
dependencies on sp.
>--Original Message--
>Have you looked into the stored procedure sp_depends?
>--
>Keith, SQL Server MVP
>"Mike A" <mike@.countrymedic.com> wrote in message
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
>> I am looking for code or a solution to find all
>> dependencies in a .db fast and simple (or code). Any
>> suggestions would be great!
>> Example:
>> Here is the dependencies among these 4 tables:
>> 1) Publishers (base table): does not depend on other
>> tables.
>> 2) Authors (base table): does not depend on other
tables.
>> 3) Titles: has foreign key which depend on publishers
>> table.
>> 4) Titleauthor: has 2 foreign keys which depend on
>> authors and publishers titles
>.
>|||No trying to determine population order of the .db built
in another state.
>--Original Message--
>why?
>is it because you want to make sure your new code
doesn't
>break any existing code?
>check out DB Ghost @. www.dbghost.com - it can guarentee
>your code doesn't break anything and make deploying your
>changes a breeze.
>>--Original Message--
>>I am looking for code or a solution to find all
>>dependencies in a .db fast and simple (or code). Any
>>suggestions would be great!
>>Example:
>>Here is the dependencies among these 4 tables:
>>1) Publishers (base table): does not depend on other
>>tables.
>>2) Authors (base table): does not depend on other
tables.
>>3) Titles: has foreign key which depend on publishers
>>table.
>>4) Titleauthor: has 2 foreign keys which depend on
>>authors and publishers titles
>>.
>.
>|||hi Mike,
"Mike A" <mike@.countrymedic.com> ha scritto nel messaggio
news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> I am looking for code or a solution to find all
> dependencies in a .db fast and simple (or code). Any
> suggestions would be great!
> Example:
> Here is the dependencies among these 4 tables:
> 1) Publishers (base table): does not depend on other
> tables.
> 2) Authors (base table): does not depend on other tables.
> 3) Titles: has foreign key which depend on publishers
> table.
> 4) Titleauthor: has 2 foreign keys which depend on
> authors and publishers titles
You can have a look at the UNDOCUMENTED (unsupported) stored procedure
sp_MSforeachtable..
sp_MSforeachtable can execute the command you pass as a parameter for each
table present in your database and you can try using sp_depends, that
returns all dependencies for the specified ('?') table object
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
you'll get an output similar to
ObjName
--
dbo.tableXXXXX
no object refers to this object....
or
ObjName
--
dbo.tableXXXXX
In the current database, the specified object is referenced by the
following:
name type
xxxxx xxxxx
to list FK constraint you can use a select which actually includes some
joins....
mixing the 2 command you can give this a try...
----
EXEC sp_MSforeachtable 'SELECT ''?'' AS ObjName EXEC sp_depends ''?'''
DECLARE @.MasterCMD VARCHAR (1000)
SET @.MasterCMD ='SELECT ''?'' AS ObjName
PRINT ''sp_depends''
PRINT ''--''
PRINT ''fk_scan''
PRINT ''--''
SELECT SO3.NAME FK_NAME, ''['' + SU.NAME + ''].['' + SO.NAME + '']''
FROM SYSFOREIGNKEYS SYSFK
--Foreign Key Constraint - Table info
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO
ON SYSFK.FKEYID = SO.ID
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO2
ON SYSFK.RKEYID = SO2.ID
--Name and ID of the FOREIGN KEY constraint
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS) SO3 ON SYSFK.CONSTID = SO3.ID
--Foreign Key Constraint - Owner info
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID
--Referenced in the FOREIGN KEY constraint - Owner info
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID
WHERE SU.NAME + ''.'' + SO.NAME = ''?'''
EXEC sp_MSforeachtable @.MasterCMD
----
you must be warned that Microsot does not recommend the use of UNDOCUMENTED
stored procedures that are unsupported and system objects direct
manipulation
hth
Andrea Montanari
montanari_andrea@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||It also shows dependencies on a table...
USE pubs
GO
exec sp_depends 'authors'
-- Keith, SQL Server MVP
"Mike A" <mike@.countrymedic.com> wrote in message =news:0a3001c34a22$5c817f80$a501280a@.phx.gbl...
> Yes, sp_depends does not solve the issue. It shows > dependencies on sp.
> > > >--Original Message--
> >Have you looked into the stored procedure sp_depends?
> >
> >-- > >Keith, SQL Server MVP
> > > >"Mike A" <mike@.countrymedic.com> wrote in message > news:000301c34a11$6be5af20$a101280a@.phx.gbl...
> >> I am looking for code or a solution to find all > >> dependencies in a .db fast and simple (or code). Any > >> suggestions would be great!
> >> > >> Example:
> >> Here is the dependencies among these 4 tables:
> >> 1) Publishers (base table): does not depend on other > >> tables.
> >> 2) Authors (base table): does not depend on other > tables.
> >> 3) Titles: has foreign key which depend on publishers > >> table.
> >> 4) Titleauthor: has 2 foreign keys which depend on > >> authors and publishers titles
> >> > >.
> >

how to find data mining 's example

how to find data mining 's example from sql2005

how to apply data mining in project

This might be a good starting point...

http://msdn2.microsoft.com/en-us/library/ms175595(SQL.90).aspx

|||

There is a Data Mining forum and I am sure you will find relevant information there.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=81&SiteID=1

How to find an MS SQL Server DBA

My ISP runs MS SQL server for me and does not have experience managing this type of database. For example, the database occassionally crashes and looses data. Our site is not live yet, but once we go live, this is unacceptable.

I need somebody to consult on the database configuration, tuning, security and maintenance plan. How would I find a person with the correct qualifications?do you need someone for occasional consulting or
you are looking for full time sql server dba ?

How to find "best" matches

How do I search a table for the best match. For example... I have a name,
zip, birthday and gender column. How do I search for name = smith, zip =
30001, birthday = 19501201 and gender = m and list those records matching 4
of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
--
David"dtw" <dtw@.discussions.microsoft.com> wrote in message
news:B6971108-5543-4069-931A-DFC88E1A49A4@.microsoft.com...
> How do I search a table for the best match. For example... I have a name,
> zip, birthday and gender column. How do I search for name = smith, zip =
> 30001, birthday = 19501201 and gender = m and list those records matching
> 4
> of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
> --
> David
Here's one way... maybe not the best:
select ...,
case name when 'smith' then 1 else 0 end
+ case zip when '30001' then 1 else 0 end
+ case birthday when '19501201' then 1 else 0 end
+ case gender when 'm' then 1 else 0 end AS match
from tableName
order by match desc
You can filter the results to return only records where there is at least
one match.|||You can implement a calculated column called [score] by adding the result of
several case functions like so:
select
*,
(
case when name = 'smith' then 1 else 0 end +
case when zip = 30001 then 1 else 0 end +
case when birthday = '19501201' then 1 else 0 end +
case when gender = 'm' then 1 else 0 end
) as score
from
mytable
This will result in each row being assigned a score from 0 - 4.
Also, if you want to filter, group or order a query based on a complex
expression (and I'm sure you will), then you can avoid repeating the
expression within the [where], [group by], and [order by] clause, by
implementing a sub-query like so:
select
score,
count(*) as cnt
from
(
select
(
case when name = 'smith' then 1 else 0 end +
case when zip = 30001 then 1 else 0 end +
case when birthday = '19501201' then 1 else 0 end +
case when gender = 'm' then 1 else 0 end
) as score
from
mytable
) as x
where
score >= 2
group by
score
order by
score desc
An alternative to the sub-query is to implement a temporary table.
"dtw" <dtw@.discussions.microsoft.com> wrote in message
news:B6971108-5543-4069-931A-DFC88E1A49A4@.microsoft.com...
> How do I search a table for the best match. For example... I have a name,
> zip, birthday and gender column. How do I search for name = smith, zip =
> 30001, birthday = 19501201 and gender = m and list those records matching
> 4
> of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
> --
> David

How to filter reports user logon info

I am new to reporting services
Is there an example of how to obtain user logon information to use as input
for queries before report renders. We will deploy reports to a portal and
want to use the user logon info as a filter for initial queries ( for example
to restrict information viewed by client ). Thanks.You can use the User!UserID global, which is the logged on username. Pass
it into a query parameter. Of course, you will need to have data in your
source database tables that includes the UserID values, to join and filter
your data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>I am new to reporting services
> Is there an example of how to obtain user logon information to use as
> input
> for queries before report renders. We will deploy reports to a portal and
> want to use the user logon info as a filter for initial queries ( for
> example
> to restrict information viewed by client ). Thanks.|||Where is it picking up the global value from? When I was working with
Cleverpath portal before (CA's portal product) the reports were published to
the portal and the logon id was obtained from the portal signon. In the case
of RS, the reports are deployed to the report server - are there tables
associated with the report server? Is it getting the value from the browser
session?
"Jeff A. Stucker" wrote:
> You can use the User!UserID global, which is the logged on username. Pass
> it into a query parameter. Of course, you will need to have data in your
> source database tables that includes the UserID values, to join and filter
> your data.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
> >I am new to reporting services
> > Is there an example of how to obtain user logon information to use as
> > input
> > for queries before report renders. We will deploy reports to a portal and
> > want to use the user logon info as a filter for initial queries ( for
> > example
> > to restrict information viewed by client ). Thanks.
>
>|||Yes, it's getting the user name from the session. If you have Windows
Authentication, it's getting it from the OS. If you have custom security
and forms auth, then it's getting it from the forms auth cookie.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:A5CD5045-9C76-4F0E-B092-F1AB2B372A1E@.microsoft.com...
> Where is it picking up the global value from? When I was working with
> Cleverpath portal before (CA's portal product) the reports were published
> to
> the portal and the logon id was obtained from the portal signon. In the
> case
> of RS, the reports are deployed to the report server - are there tables
> associated with the report server? Is it getting the value from the
> browser
> session?
> "Jeff A. Stucker" wrote:
>> You can use the User!UserID global, which is the logged on username.
>> Pass
>> it into a query parameter. Of course, you will need to have data in your
>> source database tables that includes the UserID values, to join and
>> filter
>> your data.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>> >I am new to reporting services
>> > Is there an example of how to obtain user logon information to use as
>> > input
>> > for queries before report renders. We will deploy reports to a portal
>> > and
>> > want to use the user logon info as a filter for initial queries ( for
>> > example
>> > to restrict information viewed by client ). Thanks.
>>

how to filter element by attribute to be imported using SQLXML schema annotation in XML Bu

[question 1]
does anyone know how to filter the element by attribute to be imported
using SQLXML annotation?
for example, in the following xml, I only want to import the value in
the element with attribute Type="shipping" only, not the one with
attribute Type="billing". is it possible to use annotation to filter
that?
<Addresses>
<Address Type="shipping">1234 shipping avenue</Address>
<Address Type="billing">5678 billing street</Address>
</Addresses>
[question 2]
This question is kind of related too. I'd like to write schema
annotaion that's both "element-centric" and "attribute-centric".
for example, in the the following xml, I need to upload both the value
in attribute CustID and also the value in element Customer.
<Customers>
<Customer CustID="1">Customer One</Customer>
<Customer CustID="2">Customer Two</Customer>
</Customers>
I've thought about using other approaches but XML Bulk Load should be
the ideal way for performance reason because our XML files are all
very big, more than 100MB. But we're stuck on the schema annotaion.
any idea you may have is greatly appreciated.
The short answer to Q1 is "no" I'm afraid. You could however use an XSLT
stylesheet to filter the data first, and then import the filtered data. The
XPath expression you need in the stylesheet is something like
"/Addresses[Address/@.Type='shipping']"
As for Q2, if the element/attribute names match the table/column names you
can actually get away without using a schema and both element-centric and
attribute-centric mappings will work by default. If the names are different
however, this won't work. Again, an XSLT stylesheet prior to bulk loading
would be the way to go here.
There's an example of using an XSLT stylesheet on sqlxml.org at
http://sqlxml.org/faqs.aspx?faq=49
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Evangeline" <evangeli@.yahoo.com> wrote in message
news:df3f63cc.0403282343.883270d@.posting.google.co m...
> [question 1]
> does anyone know how to filter the element by attribute to be imported
> using SQLXML annotation?
> for example, in the following xml, I only want to import the value in
> the element with attribute Type="shipping" only, not the one with
> attribute Type="billing". is it possible to use annotation to filter
> that?
> <Addresses>
> <Address Type="shipping">1234 shipping avenue</Address>
> <Address Type="billing">5678 billing street</Address>
> </Addresses>
>
> [question 2]
> This question is kind of related too. I'd like to write schema
> annotaion that's both "element-centric" and "attribute-centric".
>
> for example, in the the following xml, I need to upload both the value
> in attribute CustID and also the value in element Customer.
> <Customers>
> <Customer CustID="1">Customer One</Customer>
> <Customer CustID="2">Customer Two</Customer>
> </Customers>
>
>
> I've thought about using other approaches but XML Bulk Load should be
> the ideal way for performance reason because our XML files are all
> very big, more than 100MB. But we're stuck on the schema annotaion.
>
> any idea you may have is greatly appreciated.
|||1) No, I'm afraid you can't do that. As otherwise suggested, you could run
an XSLT first to remove the data you don't want.
2) You should be able to map this by annotating the element with the name of
the column you want it to go to and the attribute with the name of the
column you want it to go to.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Evangeline" <evangeli@.yahoo.com> wrote in message
news:df3f63cc.0403282343.883270d@.posting.google.co m...
> [question 1]
> does anyone know how to filter the element by attribute to be imported
> using SQLXML annotation?
> for example, in the following xml, I only want to import the value in
> the element with attribute Type="shipping" only, not the one with
> attribute Type="billing". is it possible to use annotation to filter
> that?
> <Addresses>
> <Address Type="shipping">1234 shipping avenue</Address>
> <Address Type="billing">5678 billing street</Address>
> </Addresses>
>
> [question 2]
> This question is kind of related too. I'd like to write schema
> annotaion that's both "element-centric" and "attribute-centric".
>
> for example, in the the following xml, I need to upload both the value
> in attribute CustID and also the value in element Customer.
> <Customers>
> <Customer CustID="1">Customer One</Customer>
> <Customer CustID="2">Customer Two</Customer>
> </Customers>
>
>
> I've thought about using other approaches but XML Bulk Load should be
> the ideal way for performance reason because our XML files are all
> very big, more than 100MB. But we're stuck on the schema annotaion.
>
> any idea you may have is greatly appreciated.
|||Thank you very much for your help.
I was trying to avoid using xslt because it adds too much overhead to the process.
for example, I'm using MSXML3 object to transform one of my file, the xslt process takes 70 seconds and the bulk load with translated xml only takes 50 seconds. It would be great if we could save that 70 seconds in xslt.
|||Can you move to MSXML4? The performance of XSLT there was greatly improved.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Evangeline" <anonymous@.discussions.microsoft.com> wrote in message
news:BAAC30DF-5073-48EC-9318-AFF09406224A@.microsoft.com...
> Thank you very much for your help.
> I was trying to avoid using xslt because it adds too much overhead to the
process.
> for example, I'm using MSXML3 object to transform one of my file, the xslt
process takes 70 seconds and the bulk load with translated xml only takes 50
seconds. It would be great if we could save that 70 seconds in xslt.
|||Thanks a lot. MSXML4 does cut the XSLT translation time to 40% less than MSXML3.
Just being curious, is there any other XML/XSLT component that has better performance than MSXML4? maybe some Java component?