Environment discrepencies concerning stored procedures & data types

Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

The text, ntext, and image data types are invalid in this subquery or aggregate

the error is supposedly the top line here:

SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='',
'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId) ,
c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId
END

GO

wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.Somehow you got single quotes stuck around your column names.

Select 'cms_Document_Contents_ID'=''
...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

SELECT *,
cms_Document_Contents_ID='',
cms_Language_ID='',
Title='',
XML='',
search_text='',
File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId),
c.active,
c.sequence,
c.cms_Document_Section_ID,
c.cms_Section_ID,
c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId

blindman|||Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

sp_attatch_db @.dbname = 'MyDB',
@.filename1 = 'c:\path\to\datafilename.mdf',
@.filename2 = 'c:\path\to\datafilename.mdf'

if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

.^sUbMSg-\/.

see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp for info on this stored procedure.

ps: f*ck that DTS export b*llocks im sticking with the raw data files.

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

Environment and Application Domain between ASP.NET & SQL Server

Hi all,

I have posted the same topic under ASP-NET-Developers but had no reply;
I then posted it under ASP.Net Community and had also no reply.
Hence, i'm posting it here to prevent the impression of spam post ...

I have created a SqlContextTrigger using ADO.NET 2.0 to create a
trigger on
SQL Server 2005.

In the class, I create a file and specify its path to be in the
Application
Domain base directory such as:

String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()

However, the path is always the SQL Server Binn Directory. I'd like to
tell
the program that I want the path to be related not to SQL Server Binn
Directory but to Visual Studio Solution's Project Bin Directory.

How do I do that?

Best regards> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.

I doubt that you will find anything built-in that will do this. The trigger
thread is running in SQL Server and has no knowledge of your client folder
structure nor a means to access it.

One method to accomplish the desired result is to store the desired target
path in a table so that you can retrieve the value from within your trigger
code. If the path is remote, you'll need to use a UNC path and additional
security considerations apply.

I'm not sure what you are trying to do but you might take a look at Service
Broker.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1136546993.487438.187050@.o13g2000cwo.googlegr oups.com...
> Hi all,
> I have posted the same topic under ASP-NET-Developers but had no reply;
> I then posted it under ASP.Net Community and had also no reply.
> Hence, i'm posting it here to prevent the impression of spam post ...
> I have created a SqlContextTrigger using ADO.NET 2.0 to create a
> trigger on
> SQL Server 2005.
> In the class, I create a file and specify its path to be in the
> Application
> Domain base directory such as:
> String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()
> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.
> How do I do that?
> Best regardssql

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

HI,

Call to EnumObjectPermissions method on a SMO ApplicationRole or DatabseRole object returns an empty collection, however, a call to EnumDatabaseRoleMember on a DatabaseRole object using DMO returns the correct list of membership. Please clarify.

Regards,

Joginder

Hmm I had the same problem with SMO, but I did not try DMO.

I found a workaround. Use the similar method at the Database level, and supply the role name as a string.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Database dbHold = smoServer.Databases["YourDB"];

String holdString;

foreach( DatabaseRole dr in dbHold.Roles)

{

ObjectPermissionInfo[] ra = dr.EnumObjectPermissions(); // mysteriously returns null!!

foreach (ObjectPermissionInfo opi in dbHold.EnumObjectPermissions(dr.Name))

{

holdString = String.Format("-OPIRole:{0} has {1}{2}{3}", dr.Name, opi.Grantor, opi.Grantee, opi.ObjectName);

}

}

}

Regards,

Chip

EnumObjectPermissions on a ApplicationRole and DatabaseRole objects returns o entries

HI,

Call to EnumObjectPermissions method on a SMO ApplicationRole or DatabseRole object returns an empty collection, however, a call to EnumDatabaseRoleMember on a DatabaseRole object using DMO returns the correct list of membership. Please clarify.

Regards,

Joginder

Hmm I had the same problem with SMO, but I did not try DMO.

I found a workaround. Use the similar method at the Database level, and supply the role name as a string.

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

Database dbHold = smoServer.Databases["YourDB"];

String holdString;

foreach( DatabaseRole dr in dbHold.Roles)

{

ObjectPermissionInfo[] ra = dr.EnumObjectPermissions(); // mysteriously returns null!!

foreach (ObjectPermissionInfo opi in dbHold.EnumObjectPermissions(dr.Name))

{

holdString = String.Format("-OPIRole:{0} has {1}{2}{3}", dr.Name, opi.Grantor, opi.Grantee, opi.ObjectName);

}

}

}

Regards,

Chip

EnumErrorlogs using SQLDMO

I am wanting to use the EnumErrorlogs method of the SQLServer object to
identify which of the error logs have been written to in a specified period.
I also need to be able to deploy this to any of our SQL Servers which could
all be running with different locales.
The Date column in the QueryResults output shows this but not in the date
format that I expected. My server is set to English(Australian), my default
language for my login in SQL Server is British English ... i.e. everything is
configured to return dates in the D/M/Y format. However, the date string
returned from EnumErrorlogs is in a M/D/Y format which then messes up the
remainder of my processing.
Is the result ALWAYS returned in M/D/Y ? If so then I could at least code
for that, but if it is possible for it to be in the D/M/Y format then I need
to know where this is set so that I can test for it before continuing to
process the logs.
Can anyone direct me to information about this?
I was hoping it was something to do with the current holder of the ashes (ie
you could have waited about a year for it to fix itself),
but I'm seeing the same here in the UK on totally UK Server. The description
is a bit vague in BOL , going to try a few things.
cheers,
Andy.
"MartinC" <MartinC@.discussions.microsoft.com> wrote in message
news:79DF19A6-89D7-4464-92BF-EC7F6E6B78BB@.microsoft.com...
>I am wanting to use the EnumErrorlogs method of the SQLServer object to
> identify which of the error logs have been written to in a specified
> period.
> I also need to be able to deploy this to any of our SQL Servers which
> could
> all be running with different locales.
> The Date column in the QueryResults output shows this but not in the date
> format that I expected. My server is set to English(Australian), my
> default
> language for my login in SQL Server is British English ... i.e. everything
> is
> configured to return dates in the D/M/Y format. However, the date
> string
> returned from EnumErrorlogs is in a M/D/Y format which then messes up the
> remainder of my processing.
> Is the result ALWAYS returned in M/D/Y ? If so then I could at least
> code
> for that, but if it is possible for it to be in the D/M/Y format then I
> need
> to know where this is set so that I can test for it before continuing to
> process the logs.
> Can anyone direct me to information about this?
|||tried
- Service Account as interactive user with Brit English / UK locale
- changing / reapplying default to UK DD/MM/YY and reboot
giving up, attached is a noddy func to fix
bloody septics :-)
cheers,
Andy.
"Andy Ball" <ng@.spamno77greenfell.com> wrote in message
news:ua3F10v%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was hoping it was something to do with the current holder of the ashes
>(ie
> you could have waited about a year for it to fix itself),
> but I'm seeing the same here in the UK on totally UK Server. The
> description
> is a bit vague in BOL , going to try a few things.
> cheers,
> Andy.
>
> "MartinC" <MartinC@.discussions.microsoft.com> wrote in message
> news:79DF19A6-89D7-4464-92BF-EC7F6E6B78BB@.microsoft.com...
>
begin 666 ConvertErrorlogDate.txt
M( T*)R!A<R!E<G)O<FQO9R!I;B!M+V0O>2!F;W)M870@.:6X@.16YU ;45R<F]R
M3&]G<PT*1G5N8W1I;VX@.0V]N=F5R=$5R<F]R3&]G1&%T92A%<G)O<DQO9T1A
M=&4I#0H)0V]N=F5R=$5R<F]R3&]G1&%T92 ](&-3='(H36ED*$5R<F]R3&]G
M1&%T92PT+#(I("L@.(B\B("L@.;6ED*$5R<F]R3&]G1&%T92P@.,2PR*2 K("(O
J(B K(&UI9"A%<G)O<DQO9T1A=&4L(#<L,C I*0T*16YD($9U;F-T:6]N
`
end

Enumeration variables??

Is it possible to create variables of type enum in PL/SQL?
In VB.NET it would be something like e.g.: enum seasons {Spring, Summer, Autumn, Winter}
Thx.Originally posted by OracleDev
Is it possible to create variables of type enum in PL/SQL?
In VB.NET it would be something like e.g.: enum seasons {Spring, Summer, Autumn, Winter}

Thx.
No, PL/SQL does not have such a type.|||I feared this, because I couldn't find info about it.

Thx anyway.

Enumerating Xml elements and inserting

I have an untyped XML variable that for example holds the following data:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Using SQL DML and\or XQuery I would like to enumerate the XML elements and add an <ExternalId> element to any <Address> element if one doesn't exist with a value of NewId().

So the end result would be:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12334343A</ExternalId>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-333333</ExternalId>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12312312</ExternalId>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Any help would be greatly appreciated.

Regards

Why are you using a unique identifier. Are you familiar with the performance hit that comes with using a unique identifier? I admit there are reasons to want to use a unique identifier and yours may be one of them, but frequently I see unique identifiers used without an understanding of the consequences to performance. Here are some previous threads related to this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430995&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544519&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304764&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1493312&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525445&SiteID=1

|||

Thanks for the reply.

Perfomance is not an issue in this instance as it will be run once in a blue moon. But thanks for the heads-up on the performance issues. It doesn't have to be NewID() it can be any globally unique number.

Regards

sql