Comparing two databases

Hi,
I would like to compare two sql server databases.
I need to find the difference in the two database(i.e table, colums,
data type)
Is there any free utility available to compare sql server database
I'm using sql server 2000.
Help me out to compare databases

Thanks & Regards,
ManiIt's not free, but you can download a 14-day trial version;
http://www.redgate.com/products/SQL_Compare/index.htm
Markus|||Another one is AlfaAlfa's SQL Server Comparison Tool (SCT)

http://www.sql-server-tool.com
- also not free, but it has 30 days of free evaluation period, which
can be extended to 90 days. Comparisons can be fully automated
through the usage of command line parameters.

SCT works with SQL Server 2005, 2000 and 7.0 and between these
versions.

Dariusz Dziewialtowski.

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Restlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
aren´t.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
> to
>> do this? Please provide any code if you have it or any links regarding
> this
>> problem.
>> Thanks in advance...
>>
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> aren´t.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> > Two text fields in the same record or in related tables? If you are
going
> > to
> > be comparing a large number of records, then you may want to speed
things
> > up
> > by first determing those records where the length of the text values are
> > not
> > the same. Those are obviosly different. Perhaps store their primary key
> > IDs
> > in a temporary table. Once done, you can then perform the full test
> > comparison against the remaining few that are the same size.
> >
> > "helpful sql" <nospam@.stopspam.com> wrote in message
> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> >> Hi all,
> >> What is the fastest and best way to compare two text fields(data
type
> >> TEXT). I just need
> >> to know when the values are different. Are there any functions
available
> > to
> >> do this? Please provide any code if you have it or any links regarding
> > this
> >> problem.
> >>
> >> Thanks in advance...
> >>
> >>
> >
> >
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TEXT).
> I just need
> to know when the values are different. Are there any functions available to
> do this? Please provide any code if you have it or any links regarding this
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available to
>> do this? Please provide any code if you have it or any links regarding this
>> problem.
>> Thanks in advance...
>>
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdata2,1,
Datalength(textdata1))
--
1|||Here's a quick and dirty function that will do the comparison:
Create Function dbo.TextDataAreEqual (@.Text1 Text, @.Text2 Text)
Returns Bit
AS
Begin
Declare @.Len1 Int
Declare @.Len2 Int
Set @.Len1 = DataLength(@.Text1)
Set @.Len2 = DataLength(@.Text2)
If @.Len1 <> @.Len2
Return 0
If @.Len1 <= 8000
If Substring(@.Text1, 1, @.Len1) <> Substring(@.Text2, 1, @.Len2)
Return 0
Declare @.Index Int
Set @.Index = 1
While @.Index < @.Len1
Begin
If Substring(@.Text1, @.Index, 8000) <> Substring(@.Text2, @.Index, 8000)
Return 0
Set @.Index = @.Index + 8000
End
Return 1
End
In essence, it compares on length and then the first 8K and only barring that
does it compare using the chunking methodology that people were talking about in
previous posts. You would get a speed improvment by narrowing the list by
comparing datalength in the main query itself. So something like:
Select *
From (
Select T1.Id, T1.TextField1, T1.TextField2
From dbo.TableName As T1
Where T1.DataLength(T1.TextField1) = T1.DataLength(T1.TextField2)
) As T
Where dbo.TextDataAreEqual(T.TextField1, T.TextField2) = 1
Thomas|||OK that could be done in the query which call´s the function:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
Where Datalength(EmployeeID) <> Datalength(EmployeeID)
Assuming that u don´t have the same column as shown above, just for the
syntax.
HTH, Jens Suessmeyer.
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:el0N7MmVFHA.1508@.tk2msftngp13.phx.gbl...
> Am I correct in assuming that if the text values have different lengths,
> for
> example 2000 vs. 2100, then they are different without performing a text
> comparison? Once we have that list of these candidates in a temporary
> table,
> we can exclude them from the query which performs the text compare.
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
>> Some (bad coded) applications write chunks of data in the database in
> steps
>> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
>> could be a problem because many columns would "seem" to be the same but
> they
>> aren´t.
>> Just a experience and my two cents.
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
>> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
>> > Two text fields in the same record or in related tables? If you are
> going
>> > to
>> > be comparing a large number of records, then you may want to speed
> things
>> > up
>> > by first determing those records where the length of the text values
>> > are
>> > not
>> > the same. Those are obviosly different. Perhaps store their primary key
>> > IDs
>> > in a temporary table. Once done, you can then perform the full test
>> > comparison against the remaining few that are the same size.
>> >
>> > "helpful sql" <nospam@.stopspam.com> wrote in message
>> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> >> Hi all,
>> >> What is the fastest and best way to compare two text fields(data
> type
>> >> TEXT). I just need
>> >> to know when the values are different. Are there any functions
> available
>> > to
>> >> do this? Please provide any code if you have it or any links regarding
>> > this
>> >> problem.
>> >>
>> >> Thanks in advance...
>> >>
>> >>
>> >
>> >
>>
>

comparing getdate()

hi I have done this before but can't seem to recall now ...I want to compare a field in my table with getdate() the field type short date which also has the time stamp...doing field_date > getdate() does not work to get data of today...can someone help!if a time is not provided when the date is entered, it defaults to midnight (when it first becomes "today"), so sometimes that can throw these types of queries off.

curtis

Comparing a real datetime to a constructed datetime

I have the following SQL:

select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a

where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

The SQL above returns

2004-04-20 00:00:00.000 Deposit ...

Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

I get the following error:

Syntax error converting datetime from character string.

Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

If I do:

SELECT top 10 right(term,4) FROM dbo.status_view

I get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

But if I do:

SELECT top 10 right(term,4) FROM dbo.status_view
WHERE
isnumeric(right(term,4)) =0 and

It returns no rows! I think you're on to something. But the weirdest part is if I do:

SELECT top 10 cast(right(term,4) as int) FROM dbo.status_view

I still get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

Ack!|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

Oh wait, I was doing one and not zero. If I do:

SELECT count(1) FROM dbo.status_view
WHERE
and isnumeric(right(term,4)) = 0

it returns zero. So I guess thats not it...|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784

This works fine. It returns no rows, but no errors. This is driving me insane!|||This is your problem, right?

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

Which is syntactically incorrect...

what is a.submit_date? I assume it's from select *

Plus you need ro give the derived table a name

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) AS XXX
where dt >= submit_date

Does that run?

Compare datetime with a string

Hello!
In the database I have a column of type datetime called skip_start_time.
If I want to compare this skip_start_time with a character string for
example "2008-01-04 11:32:49" how
do I write this?
//TonyTony
CAST your character for DATETIME
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ub8681xYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello!
> In the database I have a column of type datetime called skip_start_time.
> If I want to compare this skip_start_time with a character string for
> example "2008-01-04 11:32:49" how
> do I write this?
> //Tony
>|||declare @.startDate CHAR(20)
SET @.startDate ='2008-02-04 11:32:49'
IF (CAST(@.startDate as datetime) > GETDATE())
PRINT 'yes'
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ub8681xYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello!
> In the database I have a column of type datetime called skip_start_time.
> If I want to compare this skip_start_time with a character string for
> example "2008-01-04 11:32:49" how
> do I write this?
> //Tony
>|||Hello!
How do I write this in pure sql?
//Tony
"Jack Vamvas" <DEL_TO_REPLY@.del.com> skrev i meddelandet
news:es2dnWkCUeWm2j3anZ2dnUVZ8qGdnZ2d@.bt.com...
> declare @.startDate CHAR(20)
> SET @.startDate ='2008-02-04 11:32:49'
> IF (CAST(@.startDate as datetime) > GETDATE())
> PRINT 'yes'
>
>
> --
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
> "TonyJ" <johansson.andersson@.telia.com> wrote in message
> news:ub8681xYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> > Hello!
> >
> > In the database I have a column of type datetime called skip_start_time.
> > If I want to compare this skip_start_time with a character string for
> > example "2008-01-04 11:32:49" how
> > do I write this?
> >
> > //Tony
> >
> >
>|||On Jan 30, 2:55=A0pm, "TonyJ" <johansson.anders...@.telia.com> wrote:
> Hello!
> In the database I have a column of type datetime called skip_start_time.
> If I want to compare this skip_start_time with a character string for
> example "2008-01-04 11:32:49" how
> do I write this?
> //Tony
select convert(varchar(50),skil_start_time,120)|||Tony
Jack's script is pure SQL
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ecgMXhyYIHA.4160@.TK2MSFTNGP03.phx.gbl...
> Hello!
> How do I write this in pure sql?
> //Tony
> "Jack Vamvas" <DEL_TO_REPLY@.del.com> skrev i meddelandet
> news:es2dnWkCUeWm2j3anZ2dnUVZ8qGdnZ2d@.bt.com...
>> declare @.startDate CHAR(20)
>> SET @.startDate ='2008-02-04 11:32:49'
>> IF (CAST(@.startDate as datetime) > GETDATE())
>> PRINT 'yes'
>>
>>
>> --
>> Jack Vamvas
>> ___________________________________
>> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>>
>>
>> "TonyJ" <johansson.andersson@.telia.com> wrote in message
>> news:ub8681xYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> > Hello!
>> >
>> > In the database I have a column of type datetime called
>> > skip_start_time.
>> > If I want to compare this skip_start_time with a character string for
>> > example "2008-01-04 11:32:49" how
>> > do I write this?
>> >
>> > //Tony
>> >
>> >
>>
>|||SQL Server will implicitly covert a character string to a datetime when you
compare it to a datetime column. This is because datetime has a higher data
type precedence than character types:
SELECT *
FROM dbo.MyTable
WHERE MyDateColumn = '2008-01-04 11:32:49'
The sample datetime string you specified will work with most, but not all
date formats. You can use a date format independent string like
'2008-01-04T11:32:49' or '20080104 11:32:49' to ensure the string values is
converted as you expect regardless sessions settings. For example
SET LANGUAGE 'English'
SELECT CAST('2008-01-04 11:32:49' AS datetime)
SELECT CAST('2008-01-04T11:32:49' AS datetime)
SELECT CAST('20080104 11:32:49' AS datetime)
SET LANGUAGE 'French'
SELECT CAST('2008-01-04 11:32:49' AS datetime) --unexpected date returned
SELECT CAST('2008-01-04T11:32:49' AS datetime)
SELECT CAST('20080104 11:32:49' AS datetime)
Hope this helps.
Dan Guzman
SQL Server MVP
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ub8681xYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello!
> In the database I have a column of type datetime called skip_start_time.
> If I want to compare this skip_start_time with a character string for
> example "2008-01-04 11:32:49" how
> do I write this?
> //Tony
>

commit /rollback problem

the exeception as followAn unhandled exception of type 'System.NullReferenceException' occurred in system.windows.forms.dll

Additional information: Object reference not set to an instance of an object.

my code as followDim meetError As Boolean = False
Dim conn As Odbc.OdbcConnection
Dim trans As Odbc.OdbcTransaction
Dim cmd As Odbc.OdbcCommand
Dim da As System.Data.ODBC.OdbcDataAdapter
Dim ds As DataSet = New DataSet("dsTable")
Dim dt As DataTable
Dim i As Integer
'
Try
conn = New Odbc.OdbcConnection(connStr)
conn.Open()
cmd = conn.CreateCommand
cmd.Transaction = trans
'
Catch e As Odbc.OdbcException
MsgBox("open error " & e.Message)
meetError = True
Return False
End Try
'
'exec sql
Try
For i = 0 To in_stmt.GetUpperBound(0)
cmd.CommandText = in_stmt(i).value
da = New Odbc.OdbcDataAdapter(cmd.CommandText, conn)
cmd.ExecuteNonQuery()
'
Try
dt = New DataTable(i.ToString)
da.Fill(dt)
If dt.Columns.Count > 0 Then
ds.Merge(dt)
End If
Catch ex As Exception
MsgBox("merge error " & ex.Message)
End Try
Next
Catch ex As Odbc.OdbcException
MsgBox("execute error " & ex.Message)
meetError = True
End Try
'
If meetError Then
trans.Rollback()
Else
trans.Commit()
End If
'
'close connectoin
'
grd.DataSource = ds
Try
conn.Close()
conn = Nothing
Catch e As Odbc.OdbcException
MsgBox("close error " & e.Message)
End Try
Return True
thanks for help.I think the problem is
can da.fill(ds) after trans.begin but before trans.commit.
thanks

Commercial Application using MSDE or SQL Server 2005 Express

Hi,

I'm currently developing a commercial application in VS2005 that will catalogue hobbyist type data such as Music and Movies and I am considering using SQL Server 2005 Express for the client database.

My question is how popular is MSDE and/or SQL Server 2005 Express in commercial software products? I can't think of any application that I could buy off the shelf that uses the light-weight version of SQL Server whether that be MSDE or SQL Server 2005 Express.

I love what has been done with VS2005 and SQL2005 and I would love to roll this out but I would like to know that i'm not the only person that is going to implement SQL Server 2005 Express into a commercial product.

Other alternatives that I have looked at is VistaDB.NET, Microsoft Access of course and even looked at DB4O... these are all light-weight in comparision.

Geoff.Great question Geoff. Thousands of companies have signed the redistribution agreement allowing them to redistribute MSDE and/or SQL Server Express. MSDE is installed with many familiar applications, many times silently so users may not know they have it.

Ultimately SQL Server Express will replace MSDE, so building on SQL Server Express is the right thing to do.

The redistribution agreement is available at http://www.microsoft.com/sql/editions/express/redistregister.mspx.

Regards, MJ|||Hello, I am also using Sql express for a commercial application, so dont worry.|||Thanks Luis, This helps, Can you tell me which application it is?

Geoff.|||I know of one that uses MSDE (app was developed prior to SQL Express availability):
REA 9 Real Estate Contact Manager (www.gorea.com/about.html ). It is a client-server app intended to track contacts and properties in a commercial real estate venue.

Command Type for dataset using Custom Data Processing Extension

Hi,
I'm writing a custom data processing extension, but I cannot get
anything other than a command type of "Text" in the dataset for the
report I have setup to use the Data extension I have written.
Does anyone know what or where the available command types are derived
from?
GregI still haven't had any luck resolving this, hoping someone can help :)

command type = Stored procedure

Hi,
does anyone know an URL with an example of using "StoredProcedure" as
"Command type" in reporting services. Normally it defaults to "Text" instead
of "StoredProcedure"
thanks,
Ludowell its the same as the text only u write the name of the sp(no need exec..).
if the sp has input parameters the RS will add them by default as Report
parameters.
I think it's the best way 2 use RS...
"Ludo Van Dun" wrote:
> Hi,
> does anyone know an URL with an example of using "StoredProcedure" as
> "Command type" in reporting services. Normally it defaults to "Text" instead
> of "StoredProcedure"
> thanks,
> Ludo
>
>

Command Prompt problems when installing MSDE

Hi,
I cannot seem to create a server instance when installing the MSDE.
step 1. I launch my cmd. It pops up with (C:\Documents and Settings\Billy
I type setup instancename="netsdk" sapwd="sa"
I immediately get a status message saying go to the control planel and
install and configure systems components. I am stuck here. If anyone can shed
some light with this problem I would appreciate it.
I am installing the MSDE onto my laptop(XP OS). I went to my control
panel->administrative tools->Local Security Policy-> but I don't what to look
for?
I am lost.
hi Billy,
Billy wrote:
> Hi,
> I cannot seem to create a server instance when installing the MSDE.
> step 1. I launch my cmd. It pops up with (C:\Documents and
> Settings\Billy
> I type setup instancename="netsdk" sapwd="sa"
> I immediately get a status message saying go to the control planel and
> install and configure systems components. I am stuck here. If anyone
> can shed some light with this problem I would appreciate it.
>
when opening the command prompt window, please navigate to the folder
containing the MSDE setup.exe boostrap installer, typically C:\MSDErelA\ or
C:\sql2kspX\MSDE\ (where X stands for the service pack level)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Forgive for not knowing, but how I do navigate to the root directory?
I tried the following commands you posted early,but cannot get to specified
location. The downloaded folder is under the C:\ dir. However, when my cmd is
invoked I cannot get to it from C:\Documents and Settings. One more time,
please. I even tried to copying the folder to the documents folder and still
no go.
Thanks
"Andrea Montanari" wrote:

> hi Billy,
> Billy wrote:
> when opening the command prompt window, please navigate to the folder
> containing the MSDE setup.exe boostrap installer, typically C:\MSDErelA\ or
> C:\sql2kspX\MSDE\ (where X stands for the service pack level)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
Recopied it to the Billy folder and create an instance to the server,
automatically starting installing. Error on my end. Thanks for the help.
"Billy" wrote:
[vbcol=seagreen]
> Andrea,
> Forgive for not knowing, but how I do navigate to the root directory?
> I tried the following commands you posted early,but cannot get to specified
> location. The downloaded folder is under the C:\ dir. However, when my cmd is
> invoked I cannot get to it from C:\Documents and Settings. One more time,
> please. I even tried to copying the folder to the documents folder and still
> no go.
> Thanks
> "Andrea Montanari" wrote:
|||Billy wrote:
> Andrea,
> Recopied it to the Billy folder and create an instance to the server,
> automatically starting installing. Error on my end. Thanks for the
> help.
?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.
Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to get
> an xml document into Sql Server.
> Thank you for any help provided.
>
|||Hello,
Could you please send me the schema and the data file so i can give it a try?
Thanks,
Monica Frintu
"AshleyT" wrote:
[vbcol=seagreen]
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to g
et
> an xml document into Sql Server.
> Thank you for any help provided.
>|||Hello,
Could you please send me the schema and the data file so i can give it a try
?
Thanks,
Monica Frintu
"AshleyT" wrote:
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:
>

Combining XQuery and data paging in SQL Server 2005

Folks,
I have a table in SQL Server 2005 that contains a row of type "XML" (for now
untyped - no schema behind it).
I can easily query that table and retrieve values from the XML - way !
I can also easily query that table and use the data paging mechanism based
on the ROW_NUMBER() function - way , too!
But I can't seem to combine the two...
Imagine I have books stored in my XML column:
<books>
<book id='123'>
<author>Smith</author>
<yearpublished>1999</yearpublished>
</book>
<book id='177'>
<author>Black</author>
<yearpublished>2002</yearpublished>
</book>
... (lots more books) .....
</books>
To query the table, I use something like:
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished'
FROM BooksTable
Works fine. Now what I'd like to do is add a ROW_NUMBER() function over the
e.g. "YearPublished" attribute (stored in the XML) to get my books by year
published, in batches of 10 or whatever:
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished',
ROW_NUMBER() OVER(ORDER BY YearPublished) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Trouble is - this won't work, since MgmtStudio complains it doesn't know
about the "YearPublished" column.....
What am I missing? Can I still achieve this goal somehow?
Any hints are most welcome !!
Marc
(mscheuner -* AT *- gmail.com)Untested but try this
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as
'YearPublished',
ROW_NUMBER() OVER(ORDER BY
BooksXML.value('(/book/yearpublished)[1]', 'int')) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20|||... or :-)
WITH x AS
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') AS Author,
BooksXML.value('(/book/yearpublished)[1]', 'int') AS YearPublished,
FROM BooksTable
), BooksList AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY YearPublished) AS RowNum
FROM x
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Also, please consider not using single quotes (') for naming aliases, you
should use double quotes (") as that is the ANSI standard and using single
quotes will be debrecated.
HTH
/ Tobias