Convert a sql express to mysql

Hello.

I have created a web site using visual web developer (free download) The website was created using asp.net 2.0. The vwd I have incorporated the cool new roles and membership features of asp.net 2.0. When i did this vwd automatically created a sql express database (aspnetdb.mdf) to hold the login stuff.

I then purchased som e web hosting from a company and found out that they only support MySQL databases.

My questions are;

1. Can anyone tell me the difference between the two types of database (i know what sql express is but am not sure what MySQL is)

2. Secondly, is there a tool or way I can convert the SQL Express database to a MySQL database?

TIA

ICW

MySQL is a rather poor database that lots of people use illegally because they think it's free. If the web hosting company is charging you for hosting, they are using MySQL commercially and either paying for it or breaking the law.

Odds are that the SQL Server database is using stored procs, if this is so, then possibly not. If it's just using tables and dynamic queries, then probably.

|||

Thanks

The SQL Express database does indeed have several stored procs "aspnet_membership...". are you saying that MySQL cannot do stored procedures?

Regards

ICW

|||Hi there,

Yes, MySQL does have stored procedures. It didn't use to (pre version 5.0) but it does now. Please see this link for a write-up: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

If the hosting company you have chosen is using anything before MySQL 5.0 then stored procedures are out of the question. Also, if the stored procedures are using T-SQL specific constructs then you may have to find their equivalents (if they exist) in MySQL. For example, if you use X() in SQL Server the equivalent may be Y() in MySQL and so when you code your MySQL stored procedure you use Y().

However, if your stored procedures are doing something fancy then chances are you won't find a MySQL equivalent (although MySQL is faithful to all the standard SQL constructs for the most part so if you only use standard SQL then there's a chance there that you can migrate (reasonably) painlessly).

I have never had the experience of doing a SQL Server --> MySQL conversion, but here is a page that may help you:
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

A quick browse shows it references a few tools that you might use and also has links to forums and other places where people (like MySQL experts) could offer some good advice.

Sorry I can't be of more help at the mo.
|||

Thanks for your help

regards

ICW

|||

Hi everyone

I too have been struggling to deploy the ASPNETDB database on alternative platforms. From many hours of reading MS documentation it seems that the only answer is to create your own Membership and Role providers that conform to the published API of ASP.NET This means writing a lot of code but it can be done. You also have to tell your application to use your version rather than the default one by altering the settings in the web.config file.

It seems that Microsoft left the door open for this and publish guidance to that effect. It's not a question of converting the tables etc, you can't do it! Its a matter of providing an interface between the objects built-in to the Framework class library and your own database engine and data files.

Now the real question is has anyone out there done this for MySql? Is there a software company selling addons to support it?

Regards

Phil Hall

Convert a sql express to mysql

Hello.

I have created a web site using visual web developer (free download) The website was created using asp.net 2.0. The vwd I have incorporated the cool new roles and membership features of asp.net 2.0. When i did this vwd automatically created a sql express database (aspnetdb.mdf) to hold the login stuff.

I then purchased som e web hosting from a company and found out that they only support MySQL databases.

My questions are;

1. Can anyone tell me the difference between the two types of database (i know what sql express is but am not sure what MySQL is)

2. Secondly, is there a tool or way I can convert the SQL Express database to a MySQL database?

TIA

ICW

MySQL is a rather poor database that lots of people use illegally because they think it's free. If the web hosting company is charging you for hosting, they are using MySQL commercially and either paying for it or breaking the law.

Odds are that the SQL Server database is using stored procs, if this is so, then possibly not. If it's just using tables and dynamic queries, then probably.

|||

Thanks

The SQL Express database does indeed have several stored procs "aspnet_membership...". are you saying that MySQL cannot do stored procedures?

Regards

ICW

|||Hi there,

Yes, MySQL does have stored procedures. It didn't use to (pre version 5.0) but it does now. Please see this link for a write-up: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

If the hosting company you have chosen is using anything before MySQL 5.0 then stored procedures are out of the question. Also, if the stored procedures are using T-SQL specific constructs then you may have to find their equivalents (if they exist) in MySQL. For example, if you use X() in SQL Server the equivalent may be Y() in MySQL and so when you code your MySQL stored procedure you use Y().

However, if your stored procedures are doing something fancy then chances are you won't find a MySQL equivalent (although MySQL is faithful to all the standard SQL constructs for the most part so if you only use standard SQL then there's a chance there that you can migrate (reasonably) painlessly).

I have never had the experience of doing a SQL Server --> MySQL conversion, but here is a page that may help you:
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

A quick browse shows it references a few tools that you might use and also has links to forums and other places where people (like MySQL experts) could offer some good advice.

Sorry I can't be of more help at the mo.
|||

Thanks for your help

regards

ICW

|||

Hi everyone

I too have been struggling to deploy the ASPNETDB database on alternative platforms. From many hours of reading MS documentation it seems that the only answer is to create your own Membership and Role providers that conform to the published API of ASP.NET This means writing a lot of code but it can be done. You also have to tell your application to use your version rather than the default one by altering the settings in the web.config file.

It seems that Microsoft left the door open for this and publish guidance to that effect. It's not a question of converting the tables etc, you can't do it! Its a matter of providing an interface between the objects built-in to the Framework class library and your own database engine and data files.

Now the real question is has anyone out there done this for MySql? Is there a software company selling addons to support it?

Regards

Phil Hall

Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert

Conversion from MSSQL 2000 to MSSQL 2005

We have a membership web site that is heavily database driven using MSSQL 2000. We are looking at converting to MSSQL 2005. What is the level of pain in doing this? Will all database calls have to be rewritten, or does Microsoft provide any kind of automatic conversion routine. Thanks in advance.U need to upgrade the database to the next version.

u dont have to rewrite the procs/functions.

Under the watch ful eye of an able SQL-DBA u can do it with out much hassles.

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

Controlling Security through Web Application

Hello all,

I have some questions hopefully you can help with regarding controlling access through ASP.NET.

We'd like to take advantage of reporting services functionality for reports, but we'd like to use our own security model. We have an extensive database-driven security model that exists independently of active directory and windows permissions. We use the NT Logon of the user, but that is all we use. Everything else is maintained within our database structure. We also have all of our web pages and reports in a large database with individual ID's that are permissioned against th

Currently we need a way to link users to reporting services using our own authentication, but this presents a problem. Currently we have a wrapper page (let's call it ReportAccess.aspx). That page authenticates the user and decides whether or not they have access to the report, and then should deliver the report.

Here is where I'm not sure what to do. Our original component simply threw the report URL into an IFRAME. So in order to make this work, we had to give ALL users permissions to all reports on reporting services, since the credentials get passed through. The problem here is that savvy people could look at the URLs and hack their way into reports they should not be seeing.

Ideally, we'd like to only give access to one account and have the ReportAccess.aspx page control that access, but I am not sure how to pull this off. Is this even possible?

The ugly alternative would be maintaining permissions in our system AND Reporting Services ... which would be a lot of work and juggling. There has to be a better way

Okay, I came across the ReportViewer control in ASP.NET and that may be exactly what I was looking for, but I am having a little bit of trouble getting it to work.

I have setup the component as follows:

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" >
<ServerReport ReportServerUrl="http://rs2k5/Reports/Pages/Folder.aspx" ReportPath="/MyReportFolder/MyReportName" DisplayName="Test Report" />
<LocalReport />
</rsweb:ReportViewer>

For the record, you can access the reports using this URL:
http://rs2k5/Reports/Pages/Report.aspx?ItemPath=%2fMyReportFolder%2fMyReportName

When I use [http://rs2k5/Reports/Pages/] as the ServerURL, I get a 404 file not found. Interestingly enough, when I use [http://rs2k5/Reports/Pages/Folder.aspx], I get something back, but it looks like this:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <script language="JScript" type="text/Javascript" ****snipped - very long HTML here****
|||To get entirely around security using the report viewer, you need to use an rdlc local to your project that does not rely on the report server all.|||

This is a pretty interesting approach. I had no idea the RDLC option existed. I'm going to test some with that.

Currently, I was able to get the ReportViewer working with Remote option. Turns out you can't point at your ReportServer, instead you have to point at your ReportManager. Using impersonation, I was able to successfully mimic a user's security, so that all users could access the reports using one single account (and then I can use my own validation on the ASP.NET page.

Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
James
It is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the
>
|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?[vbcol=seagreen]
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when
> catalog
> the
>
|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in
>
|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
James
It is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the
>
|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?[vbcol=seagreen]
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when
> catalog
> the
>
|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in
>
|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
JamesIt is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||has a full population been done?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > I'm dealing with a company that have 3 web servers a test, staging and
> live.
> > All these servers are sql server 2000 and are situated offsite. I do not
> > have dirrect access to them, I have to send them email with scripts when
> > things need changing.
> >
> > The website works on the test and the staging but for some reason the
> search
> > doesn't work on the live server. The search uses CONTAINS and the
catalog
> > seem to be set up correctly on all three servers. The search works in
the
> > sence that it doesn't error but all it returns is zero rows.
> >
> > Any clues?
> >
> > Cheers
> > James
> >
> >
>|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > has a full population been done?
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > I'm dealing with a company that have 3 web servers a test, staging and
> > live.
> > > All these servers are sql server 2000 and are situated offsite. I do
not
> > > have dirrect access to them, I have to send them email with scripts
when
> > > things need changing.
> > >
> > > The website works on the test and the staging but for some reason the
> > search
> > > doesn't work on the live server. The search uses CONTAINS and the
> catalog
> > > seem to be set up correctly on all three servers. The search works in
> the
> > > sence that it doesn't error but all it returns is zero rows.
> > >
> > > Any clues?
> > >
> > > Cheers
> > > James
> > >
> > >
> >
> >
>|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > I've been assured it has.
> >
> > Is there a system query that will tell me the number of rows in the
> catalog?
> >
> > Cheers
> > James
> >
> > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > has a full population been done?
> > >
> > > --
> > > Hilary Cotter
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > >
> > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > Hi
> > > >
> > > > I'm dealing with a company that have 3 web servers a test, staging
and
> > > live.
> > > > All these servers are sql server 2000 and are situated offsite. I do
> not
> > > > have dirrect access to them, I have to send them email with scripts
> when
> > > > things need changing.
> > > >
> > > > The website works on the test and the staging but for some reason
the
> > > search
> > > > doesn't work on the live server. The search uses CONTAINS and the
> > catalog
> > > > seem to be set up correctly on all three servers. The search works
in
> > the
> > > > sence that it doesn't error but all it returns is zero rows.
> > > >
> > > > Any clues?
> > > >
> > > > Cheers
> > > > James
> > > >
> > > >
> > >
> > >
> >
> >
>|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:
> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > try this
> >
> > select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
> >
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > > I've been assured it has.
> > >
> > > Is there a system query that will tell me the number of rows in the
> > catalog?
> > >
> > > Cheers
> > > James
> > >
> > > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > > has a full population been done?
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > >
> > > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi
> > > > >
> > > > > I'm dealing with a company that have 3 web servers a test, staging
> and
> > > > live.
> > > > > All these servers are sql server 2000 and are situated offsite. I do
> > not
> > > > > have dirrect access to them, I have to send them email with scripts
> > when
> > > > > things need changing.
> > > > >
> > > > > The website works on the test and the staging but for some reason
> the
> > > > search
> > > > > doesn't work on the live server. The search uses CONTAINS and the
> > > catalog
> > > > > seem to be set up correctly on all three servers. The search works
> in
> > > the
> > > > > sence that it doesn't error but all it returns is zero rows.
> > > > >
> > > > > Any clues?
> > > > >
> > > > > Cheers
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
JamesIt is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the[vbcol=seagreen]
>|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when[vbcol=seagreen]
> catalog
> the
>|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

Contains function in sql server 2005

H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
=B7 I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID=3D"Microsoft Search Thesaurus">
<thesaurus xmlns=3D"x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>

_|
</expansion>
<expansion>
₊
_&
</expansion>
<replacement>
<pat> </pat>
_|
</replacement>
<replacement>
<pat>+</pat>
_&
</replacement>
</thesaurus>
</XML>
=B7 I modified my SQLquery like that:
dim requeteMC as string =3D "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.Perhaps you get better responses by posting this to
microsoft.public.sqlserver.fulltext, which by the way, is one of the
newsgroups that you missed as you post-bombed multiple newsgroups.
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"djamila" <djamilabouzid@.gmail.com> wrote in message
news:1164826388.137537.34310@.j72g2000cwa.googlegroups.com...
H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>

_|
</expansion>
<expansion>
₊
_&
</expansion>
<replacement>
<pat> </pat>
_|
</replacement>
<replacement>
<pat>+</pat>
_&
</replacement>
</thesaurus>
</XML>
I modified my SQLquery like that:
dim requeteMC as string = "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.

Contact SQL Serve Database Engine Team

Is there any web sit or weblog for contacting SQL Serve Database Engine Team?

There are team members visiting this forum. What exactly do you want with them?

If you have a suggestion or a bug to file, you can submit one at http://connect.microsoft.com/sqlserver

|||Tahnk for your attention
I want ask them why SQl Server dosn't support load balancing and would it be added in Next versions.

i create a topic but no body answer it ...
Load balancing

agin thanks|||

You can look into scalable shared database.

http://support.microsoft.com/kb/910378

Or peer-to-peer replication. Afterall, sqlserver is a backend datastore. The need for a load balancer does not seem to be a must here. You can already load balance at the midtier.

Contact SQL Serve Database Engine Team

Is there any web sit or weblog for contacting SQL Serve Database Engine Team?

There are team members visiting this forum. What exactly do you want with them?

If you have a suggestion or a bug to file, you can submit one at http://connect.microsoft.com/sqlserver

|||Tahnk for your attention
I want ask them why SQl Server dosn't support load balancing and would it be added in Next versions.

i create a topic but no body answer it ...
Load balancing

agin thanks|||

You can look into scalable shared database.

http://support.microsoft.com/kb/910378

Or peer-to-peer replication. Afterall, sqlserver is a backend datastore. The need for a load balancer does not seem to be a must here. You can already load balance at the midtier.

Consuming Web Services In Sql CLR

I am using SQL Server June CTP and I have a CLR stored procedure that is consuming a web service that has 3 methods:
1) GetXml - returns xml data as string.
2) GetXsd - returns xsd as string.
3) GetData - returns a dataset.

I am only using the GetData method to retrieve data and do some processing in the stored procedure. When I try to deploy the assembly, I get the following error:

CREATE ASSEMBLY failed because method "add_GetXmlCompleted" on type "SqlServerAssembly.EsoDataWebService.ESODataSet" in external_access assembly "SqlServerAssembly" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. SqlServerAssembly

Am I trying to do something over here that's not possible or not allowed in Sql CLR? Thanks!

Deploy your assembly as UNSAFE, instead of EXTERNAL_ACCESS. That will take care of that issue.
Niels
|||I tried deploying the assembly as UNSAFE and now I am getting this error:

Could not load file or assembly '1316 bytes loaded from Microsoft.VisualStudio.DataTools, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. An attempt was made to load a program with an incorrect format.|||I guess you have an app.config that is causing this issue. Remove that from your project and deploy again.
To make it work in external access follow the steps in the blog:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

Thanks,
-Vineet.|||Thank you Vineet! I was able to deploy the assembly with 'Unsafe' permission level after deleting the app.config file. But, now when I try executing that stored procedure from Management Studio, I get the following error:

Msg 6522, Level 16, State 1, Procedure TestSproc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'TestSproc':

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)

at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)

at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)

at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)

at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] source

...

System.InvalidOperationException:

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)

at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)

at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)

at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)

at System.Web.Services.Protocols.SoapClientType..ctor(Type type)

at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()

at SqlServ...

|||For XML Serialization (required for calling web services) you need to pregenerate the serializer assembly and register it in the database. You can generate the serialization assembly using a tool called sgen that is shipped with .NET Framework SDK.

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies - myAsm.dll and myAsm.XmlSerializers.dll, you need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE

To automate this in visual studio, follow the instructions in the blog: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

Thanks,
-Vineet.

|||Thank you once again Vineet! Now the stored procedure executes without any errors!

Consuming Web Services from the SQL CLR

Hi there,

I've been following Vineets and David's procedures to consume web

services using SQL CLR to the t. I created my web service in C#.NET

2005, and generated my proxy using this command:

wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx

I added both files to the project, set Generate Serialization Assembly to on and compiled it.
I then generated a strong name key for the assembly and signed my assembly with that key.
Inside my post-build event I added the following script:

"E:\Development\Microsoft

Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force

/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures

$(TargetDir)$(TargetName).dll

This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.

In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my

first assembly with permissions EXTERNAL ACCESS and then the

XmlSerializer assembly with permissions SAFE. I created my stored

procedure and ran it. When I did I got this error which I assumed the

XmlSerializer was supposed to solve for me:

System.InvalidOperationException:

Cannot load dynamically generated serialization assembly. In some

hosting environments assembly load functionality is restricted,

consider using pre-generated serializer. Please see inner exception for

more information. > System.IO.FileLoadException: LoadFrom(),

LoadFile(), Load(byte[]) and LoadModule() have been disabled by the

host.

I have seen alot of posts about this error, but none of them has been able to solve my problem.

Please can you help me?

O'Connor

Hi O'Connor,

As you have experienced, there are a few cases where sgen does not solve the problem of dynamically loading the XmlSerializer assembly.There is usually always a possible workaround, but it can vary depending on exactly what your code is doing.I was intending on writing some more blog posts about solutions to these dynamic assembly loading problems, but haven't had the time to finish them yet.

The problem you're most likely hitting is that the code doing the XmlSerialization uses an XmlSerializer constructor that does not check for the pre-generated (sgened) assemblies.This could be either directly in your code, or in other .NET Framework code that does this, such as in a strongly typed ADO.NET Dataset.This is covered in the msdn documentation for XmlSerializer if you look at the "Dynamically Generated Assemblies" section http://msdn2.microsoft.com/en-us/library/system.xml.serialization.xmlserializer.aspx

If this is indeed the problem, then the easiest way to fix this is to modify your code to instead use one of the constructors that does check for a pre-generated XmlSerializer assembly.This might be more difficult if it is not your code that is directly calling the XmlSerializer constructor.If you can include the full callstack of the error message you shared below and a snippet of the code where the XmlSerialization is happening, I should be able to help you fix it.

Steven

|||Hi Steven,

Thanks for getting back to me in such a short time.
I was playing with it last night and finally got it working.
My types aren't complex, I only use strings within my assembly (as it is a sms service)
What I did eventually do to fix it was to remove the post build script and set the "Generate Serialization Assembly" on the build tab of the project properties to ON.
It seems that using the sgen tool, the serialization assembly wasn't generated exactly the way that the CLR required it.

I found this website to be very useful in the end:
http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Regards,
O'Connor

|||

Hello Steven,

I am currently facing exactly the same strange problem as the initiator of this thread. I want to call a simple webservice from a stored procedure in SQL 2005. I've already tried the different ways of getting the serializer assemblies (using sgen and the build option in VS 2005). I also registered both assemblies one after another in in my SQL 2005 database. The db is trustworthy, assemblies are both marked with "external_access".

But I keep getting the Dynamic Load exception. In your previous post you said you could help if you have the full callstack; here it is...

Msg 6522, Level 16, State 1, Procedure CreateNotification, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'CreateNotification':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.Code
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, compilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at MAN.Applications.HFIF.Database.ClrExtensions.WebServiceSoapClient..ctor(String ...

How can I change the behaviour of my soap client class to use a particular XmlSerializer constructor that checks for pre-generated assemblies? Could you please give me a hint?

Thanks very much for your help. Frank

Consuming Web Services from the SQL CLR

Hi there,

I've been following Vineets and David's procedures to consume web

services using SQL CLR to the t. I created my web service in C#.NET

2005, and generated my proxy using this command:

wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx

I added both files to the project, set Generate Serialization Assembly to on and compiled it.
I then generated a strong name key for the assembly and signed my assembly with that key.
Inside my post-build event I added the following script:

"E:\Development\Microsoft

Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force

/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures

$(TargetDir)$(TargetName).dll

This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.

In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my

first assembly with permissions EXTERNAL ACCESS and then the

XmlSerializer assembly with permissions SAFE. I created my stored

procedure and ran it. When I did I got this error which I assumed the

XmlSerializer was supposed to solve for me:

System.InvalidOperationException:

Cannot load dynamically generated serialization assembly. In some

hosting environments assembly load functionality is restricted,

consider using pre-generated serializer. Please see inner exception for

more information. > System.IO.FileLoadException: LoadFrom(),

LoadFile(), Load(byte[]) and LoadModule() have been disabled by the

host.

I have seen alot of posts about this error, but none of them has been able to solve my problem.

Please can you help me?

O'Connor

Hi O'Connor,

As you have experienced, there are a few cases where sgen does not solve the problem of dynamically loading the XmlSerializer assembly.There is usually always a possible workaround, but it can vary depending on exactly what your code is doing.I was intending on writing some more blog posts about solutions to these dynamic assembly loading problems, but haven't had the time to finish them yet.

The problem you're most likely hitting is that the code doing the XmlSerialization uses an XmlSerializer constructor that does not check for the pre-generated (sgened) assemblies.This could be either directly in your code, or in other .NET Framework code that does this, such as in a strongly typed ADO.NET Dataset.This is covered in the msdn documentation for XmlSerializer if you look at the "Dynamically Generated Assemblies" section http://msdn2.microsoft.com/en-us/library/system.xml.serialization.xmlserializer.aspx

If this is indeed the problem, then the easiest way to fix this is to modify your code to instead use one of the constructors that does check for a pre-generated XmlSerializer assembly.This might be more difficult if it is not your code that is directly calling the XmlSerializer constructor.If you can include the full callstack of the error message you shared below and a snippet of the code where the XmlSerialization is happening, I should be able to help you fix it.

Steven

|||Hi Steven,

Thanks for getting back to me in such a short time.
I was playing with it last night and finally got it working.
My types aren't complex, I only use strings within my assembly (as it is a sms service)
What I did eventually do to fix it was to remove the post build script and set the "Generate Serialization Assembly" on the build tab of the project properties to ON.
It seems that using the sgen tool, the serialization assembly wasn't generated exactly the way that the CLR required it.

I found this website to be very useful in the end:
http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Regards,
O'Connor

|||

Hello Steven,

I am currently facing exactly the same strange problem as the initiator of this thread. I want to call a simple webservice from a stored procedure in SQL 2005. I've already tried the different ways of getting the serializer assemblies (using sgen and the build option in VS 2005). I also registered both assemblies one after another in in my SQL 2005 database. The db is trustworthy, assemblies are both marked with "external_access".

But I keep getting the Dynamic Load exception. In your previous post you said you could help if you have the full callstack; here it is...

Msg 6522, Level 16, State 1, Procedure CreateNotification, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'CreateNotification':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.Code
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, compilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at MAN.Applications.HFIF.Database.ClrExtensions.WebServiceSoapClient..ctor(String ...

How can I change the behaviour of my soap client class to use a particular XmlSerializer constructor that checks for pre-generated assemblies? Could you please give me a hint?

Thanks very much for your help. Frank

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

Consuming from Web Service and Load data into SQL

Hi:

Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.

MA,

Let me clarify. Do you want to consume data from a web service from within the data-flow?

-Jamie

|||

Well, the goal is to call a web service, and pump data into SQL server, although I thought it's less complex to hook up to a Web Services Task, and then utilize the output from the Web Services within a DFT somehow but not sure. Is there a better way to do this. Thanks.

|||

I think so, yes. It is possible to consume data from a web service from directly within the pipeline. What you are proposing would be an extra step.

To consume from a web service in the pipeline you will need a script component. Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/ref=pd_bbs_sr_1/104-7087211-5731917?ie=UTF8&s=books&qid=1181582618&sr=8-1) has a chapter explaining how to do it.

-Jamie

|||

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

|||

MA2005 wrote:

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

Fair enough. I think that's a worthy aim.

Out of interest, why do you not want to use the script component?

-Jamie

|||

No reason, just exploring an alternate solution. :-)