conversion

hai how to convert sql server table into xml format?ussainbasha2@.gmail.com wrote:
> hai how to convert sql server table into xml format?
Read BOL on the FOR XML clause, see
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

conversion

hai how to convert sql server table into xml format?
ussainbasha2@.gmail.com wrote:
> hai how to convert sql server table into xml format?
Read BOL on the FOR XML clause, see
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
sqlsql

Content Management System where everything is read from a database

Most content management systems I've seen have data from a database, and fixed template and background information held in ASP and XML files. Surely it would be easier and more centralised to have everything stored in a database?

I've created a simple content management system that does this using SQL Server 2003, and it does work, albeit a little slowly.

My intention is to develop this into a "proper" content management system that I could sell.

My question is is this a sensible idea to persue, from a technical perspective? Does SQL Server have the capability of supplying large amounts of data to web browser clients? Are there any other issues?

I've also just noticed that Microsoft appear to have added something similar to this type of functionality in the latest version of SQL: SQL 2005. Maybe I'm too late?

More details on my fledgling system are on:

http://dohat.com/dohatcms

Which itself is hosted on it.

Cheers.

Hi Neil
There are several content management systems commercially available. I believe some of them serve everything off a database. SQL Server 2005 has capability to provide results to requests via SOAP and also contains Reporting Services. I'm not sure if these are the capabilities you refer to.

- Christian Kleinerman
Program Manager
SQL Engine

Content Management System where everything is read from a database

Most content management systems I've seen have data from a database, and fixed template and background information held in ASP and XML files. Surely it would be easier and more centralised to have everything stored in a database?

I've created a simple content management system that does this using SQL Server 2003, and it does work, albeit a little slowly.

My intention is to develop this into a "proper" content management system that I could sell.

My question is is this a sensible idea to persue, from a technical perspective? Does SQL Server have the capability of supplying large amounts of data to web browser clients? Are there any other issues?

I've also just noticed that Microsoft appear to have added something similar to this type of functionality in the latest version of SQL: SQL 2005. Maybe I'm too late?

More details on my fledgling system are on:

http://dohat.com/dohatcms

Which itself is hosted on it.

Cheers.

Hi Neil
There are several content management systems commercially available. I believe some of them serve everything off a database. SQL Server 2005 has capability to provide results to requests via SOAP and also contains Reporting Services. I'm not sure if these are the capabilities you refer to.

- Christian Kleinerman
Program Manager
SQL Engine

CONTAINS full-text XML problem

I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:

> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:

> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:

> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>

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!