Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.

SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT

in above query misc1 column may contain some non printable ascii
characters.

But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"

DECLARE @.XMLMessage XML

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)

I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.

Let me know if something dose'nt make sense

any help is appreciated

Thanks

You should ask the question in the SQL Server XML Forums.

Can you base64 encode your varbinary data instead of using CDATA section?

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML AUTO, BINARY BASE64)

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanks
prashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
XML FAQ: http://xml.silmaril.ie/
|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant
|||If you need to transport these unprintable characters in XML, you need to
cast the column to varbinary(max) and transport it as base64 encoded binary
data.
Best regards
Michael
<prashant.k.jain@.gmail.com> wrote in message
news:1162602860.975903.50180@.m73g2000cwd.googlegro ups.com...
> Peter Flynn wrote:
> Just want to make sure is their no way other then filtering out or
> encoding in some other manner. I thought their musst be some way within
> SQL server to handel this.
> Thanks,
> Prashant
>
sql

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanksprashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant

problem with bit datatype conversion

Hi All,

I have create following table and inserted few records

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]

I am executing following procedure to update the table.

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END

ex:

Code Snippet

exec PROC1 1,2.50,0

but I am getting following error

Code Snippet

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".

I am struck up with this query .

Please Help me regarding this issue.

Regards
Gomaz

Code Snippet

CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )

AS

BEGIN

declare @.sSQL as nvarchar(max);

SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;

exec sp_executesql @.sSQL

END

sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT

Problem with Automatic Scrip Generator

Dear All.
As we all know withing SQL Server there is a facility to
automatically create the script to create databases, this
is something that we have been using successfully for the
past 3 years.
Lately however we have experienced a problem. On some
views it gets the name wrong, so instead of
vwPostCodeDetails, the automatic generater puts
vwPostCode.
Can anyone out there offer some guidence on what we need
to do ?
Thanks
PeterI think this is the problem if you rename the view / or stored procedure using EM or sp_rename.
When you do this underlying text in syscomments does not changes. What you have to do is "generate
the script only for these views" (which will consists of old name) update the script with the new
name that needs to be. drop these problematic view and recreate them with the new name and then
generate the new script.
--
- Vishal
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:043901c380ee$970dc660$a001280a@.phx.gbl...
> Dear All.
> As we all know withing SQL Server there is a facility to
> automatically create the script to create databases, this
> is something that we have been using successfully for the
> past 3 years.
> Lately however we have experienced a problem. On some
> views it gets the name wrong, so instead of
> vwPostCodeDetails, the automatic generater puts
> vwPostCode.
> Can anyone out there offer some guidence on what we need
> to do ?
> Thanks
> Peter

Problem with AuthenticationExtension

Hi,

I got SSRS2005 with FormsAuthentication working for a while now without any problems. I decided to create some kind of 'Account Enabled' functionality and therefore I modified my AuthenticationExtension.cs. But what happened, the new functionality does not work. After adding some logging stuff to this file nothing changed. So it looks as if AuthenticationExtension.css is not being used at all in my security extension. I verified this by removing the file from the project and still...no changes! Other files in my security extension module are being used however. When I make a change to AuthenticationUtilities I can see it in my results.

Does anyone know how this can happen? Why is my extended IAuthenticationExtension not been used?

Thanks,

Hans

I assume you've redeployed the dll to the report server / manager bin directory. Have you tried debugging and stepping into the code?|||...thanks. This brougth me to the solution. I replaced the security dll in my customized application's bin directory but not in the RS-server and RS-manager bin's. After doing this it worked as expected. Obviously only dll's in these bin directories are relevant.|||Hey i have a huge problem, right now my machine has installed SQL 2005 and .NET 2005, but my example for custom security needs SQL 2000.

Could you give the address where you found the FormsSecurity solution, please?
|||Hi, there's in fact no big difference with the extensions you're using with SQL2000. The security extensions should be there when you installed SQL2005. I know that a couple of extra methods came with the Security extension 2005, that you have to override in your own code, but you'll notice soon enough. What error message do you get?sql

Problem with AuthenticationExtension

Hi,

I got SSRS2005 with FormsAuthentication working for a while now without any problems. I decided to create some kind of 'Account Enabled' functionality and therefore I modified my AuthenticationExtension.cs. But what happened, the new functionality does not work. After adding some logging stuff to this file nothing changed. So it looks as if AuthenticationExtension.css is not being used at all in my security extension. I verified this by removing the file from the project and still...no changes! Other files in my security extension module are being used however. When I make a change to AuthenticationUtilities I can see it in my results.

Does anyone know how this can happen? Why is my extended IAuthenticationExtension not been used?

Thanks,

Hans

I assume you've redeployed the dll to the report server / manager bin directory. Have you tried debugging and stepping into the code?|||...thanks. This brougth me to the solution. I replaced the security dll in my customized application's bin directory but not in the RS-server and RS-manager bin's. After doing this it worked as expected. Obviously only dll's in these bin directories are relevant.|||Hey i have a huge problem, right now my machine has installed SQL 2005 and .NET 2005, but my example for custom security needs SQL 2000.

Could you give the address where you found the FormsSecurity solution, please?|||Hi, there's in fact no big difference with the extensions you're using with SQL2000. The security extensions should be there when you installed SQL2005. I know that a couple of extra methods came with the Security extension 2005, that you have to override in your own code, but you'll notice soon enough. What error message do you get?

Problem with AuthenticationExtension

Hi,

I got SSRS2005 with FormsAuthentication working for a while now without any problems. I decided to create some kind of 'Account Enabled' functionality and therefore I modified my AuthenticationExtension.cs. But what happened, the new functionality does not work. After adding some logging stuff to this file nothing changed. So it looks as if AuthenticationExtension.css is not being used at all in my security extension. I verified this by removing the file from the project and still...no changes! Other files in my security extension module are being used however. When I make a change to AuthenticationUtilities I can see it in my results.

Does anyone know how this can happen? Why is my extended IAuthenticationExtension not been used?

Thanks,

Hans

I assume you've redeployed the dll to the report server / manager bin directory. Have you tried debugging and stepping into the code?|||...thanks. This brougth me to the solution. I replaced the security dll in my customized application's bin directory but not in the RS-server and RS-manager bin's. After doing this it worked as expected. Obviously only dll's in these bin directories are relevant.|||Hey i have a huge problem, right now my machine has installed SQL 2005 and .NET 2005, but my example for custom security needs SQL 2000.

Could you give the address where you found the FormsSecurity solution, please?|||Hi, there's in fact no big difference with the extensions you're using with SQL2000. The security extensions should be there when you installed SQL2005. I know that a couple of extra methods came with the Security extension 2005, that you have to override in your own code, but you'll notice soon enough. What error message do you get?

problem with an UPDATE...

trying to create an UPDATE but am getting and error.
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

update XAPCHECKS
set xapck_amt =
(select sum(apph_paymnts), * from APPHISTF
LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = '01') and (apph_vend = '1010') and
xapck_check = apph_payck and xapck_chk_type = (CASE chm_type WHEN null THEN ' ' ELSE chm_type END) and xapck_check_status = (CASE chm_stat when null then ' ' ELSE chm_stat END)
and xapck_bank = apt_bank
GROUP by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id, apph_paymnts)the problem is here --

set xapck_amt = (select sum(apph_paymnts), *

the error says the subquery has more than one column|||"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."|||ah, that's because the subquery used in a SET can return only one column, one row

it's called a scalar subquery because it's supposed to return only a single scalar value|||not sure why i had that in there but it seems to working ok.
thanks

problem with ALTER TABLE syntax

I finally found a way to "deploy" my local SqlServerExpress (SSE) database to the remote Sql2K server... In VisualWebDeveloper (VWD) I can create the table definition and then save the creation sql script and use that in SSE Express Manager while connected to my remote DB. I am describing this because I'm so surprised no one has had problems with this as thousands of developers, some very unexperienced (as me maybe!), are trying the same situation now that some are offering 2.0 hosting... Well I thought this was a great idea until the Sql2K server is returning error messages on the script VWD created. So please could you help since I'm not that good at complex sql scripting. It seems the error comes from the ALTER TABLE syntax:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table2
(
prID int NOT NULL IDENTITY (1, 1),
DateInserted datetime NOT NULL,
Title nvarchar(100) NOT NULL,
Description nvarchar(MAX) NULL,
CategoryID smallint NOT NULL,
DateLastUpdated datetime NULL,
Price int NOT NULL,
SpecialPrice int NULL,
ImgSuffix nvarchar(5) NULL,
ImgCustomWidth smallint NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
prID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Exactly what error message are you getting?
Tryst
|||

Msg 170... Incorrect syntax near '('.
I've found out if I remove "WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)"
it works...

Problem with a sql statement

I have a table with the monthly internet usage in minutes for each account.

I wish to create a new table based of the previously mentioned that limits the number of records per account to 9, so only include records based on each account for their past 9 months worth of internet usage.

Can anyone help me do this?Take a look at the Transact-SQL forum for help in this regard.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||I think TSQl forum is a better place for this question...moved from SSIS forum

Problem with a script to create a DB

hi, I am writing from argentina, and the question is beacuse I have problems to create a new DB from a script, in SQL Express Edition
when I execute the query the following erros appears:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\dce05\dce05_ejemplos_estrella1_Data.MDF" failed with the operating system error 2(El sistema no puede hallar el archivo especificado.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'dce05_ejemplos_estrella1'. No entry found with that name. Make sure that the name is entered correctly.
Msg 2714, Level 16, State 6, Line 3
There is already an object named 'Productos' in the database.

I am thankful to who helps to solve the problem me
See this file....

http://www.moleculardevices.com/technotes_d1/MDC_D1_D50007_TroubleshootDB.pdf

If the method doesnt work ,then

see this troubleshooting method given on MSDN for the error msg 911
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_2d4h.asp

and then finally do this
http://msdn2.microsoft.com/en-us/library/aa933271(SQL.80).aspx

have a nice day...tell me wether it worked or not
|||

post the script... and also is this DEC05 directory exists ... if not create it... if exists then check the permission

pse post the script

Madhu

Problem with a publication. Please help !

Dear friend:
I got this error message inmediatly after to create a suscription for a
publication.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
insert duplicate key in object '#6A3D66CE'.
Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
What should I do ? I drop the publication, but when I configure it again, I
got the same result.
Thanks in advance.
Hi Salvador,
This is a known issue which has been fixed in SQL2000 sp4, and here is the
download link from the microsoft web site:
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
HTH
-Raymond
"Salvador De los Reyes" wrote:

> Dear friend:
> I got this error message inmediatly after to create a suscription for a
> publication.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
> insert duplicate key in object '#6A3D66CE'.
> Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
> What should I do ? I drop the publication, but when I configure it again, I
> got the same result.
> Thanks in advance.
>
>
|||Perfect ! Thks
"Raymond Mak [MSFT]" <RaymondMakMSFT@.discussions.microsoft.com> escribi en
el mensaje news:B17ACDF1-BBD0-45BD-ADDA-5D2C79A8C432@.microsoft.com...[vbcol=seagreen]
> Hi Salvador,
> This is a known issue which has been fixed in SQL2000 sp4, and here is the
> download link from the microsoft web site:
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> HTH
> -Raymond
> "Salvador De los Reyes" wrote:
|||Hi,
I have seen your email in www.codecomments.com, you said "This
problem is a bug", with sp4 sql server it is solucionated.
Last command{call sp_MSget_repl_commands(46, ?, 0, 7500000)}
Error Message Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
Error details Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
(Source: SQLA01 (Data source); Error number: 2627)
but, How the replication worked before sp4?
In my case, only appears the error when I select related tables, why?
Thanks,
Jose Luis
*** Sent via Developersdex http://www.codecomments.com ***

problem while using sp_ExecuteSql

Hi all

I have a stored procedure as I placed below

create procedure ps_Select_Student
@.RollNo nvarchar(50),
@.Class int
AS
Begin
declare @.MainQuery nvarchar(4000)
set @.MainQuery = 'select StudentName where RollNo=@.RollNo and Class=@.Class'

exec sp_ExecuteSql @.MainQuery
End

Go

While executing this procedure in Query Analyser i am agetting an error that

must declare @.RollNo

What is the problem. Please help me out.

You have to declare those variables on param declaration parameter of sp_executesql

Code Snippet

create procedure ps_Select_Student

@.RollNo nvarchar(50),

@.Class int

AS

Begin

Declare @.MainQuery nvarchar(4000)

Declare @.ParamDecl nvarchar(4000)

set @.MainQuery = N'select StudentName where RollNo=@.RollNo and Class=@.Class'

set @.ParamDecl = N'@.RollNo nvarchar(50),@.Class int'

Exec sp_ExecuteSql @.MainQuery , @.ParamDecl, @.RollNo, @.Class

End

Go

problem while creating an ENDPOINT

Hi,

I am trying to create an ENDPOINT in SQL Server 2005 database. My script is as below. I am unable to create this endpoint. It shows error as 'Incorrect syntext near HTTP'

Script:

/****** Object: Endpoint [GetEmployees] Script Date: 03/02/2007 22:21:26 ******/
CREATE ENDPOINT [GetEmployeesBINA]
AUTHORIZATION [TIS-WORLD\Administrator]
STATE=STARTED
AS HTTP (PATH = http://tisserver:80/sql/shapeserv, PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED), SITE=N'localhost', CLEAR_PORT = 80, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'EmployeeList'( NAME=N'[AdventureWorks].[dbo].[GetEmployees]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'AdventureWorks', NAMESPACE=N'http://AdventureWorks/Employee', SCHEMA=STANDARD, CHARACTER_SET=XML)

2) Once I create this endpoint. If I want to check the endpoint in browser , Which url I haeve to use.

Nilkanth Desai

You'd not included single quotes around the PATH, also the PATH was not in absolute format.

Try the code below instead.

Chris

CREATE ENDPOINT [GetEmployeesBINA]
AUTHORIZATION [TIS-WORLD\Administrator]
STATE = STARTED
AS HTTP (PATH = '/sql/shapeserv',

PORTS = (CLEAR),

AUTHENTICATION = (INTEGRATED),

SITE=N'localhost',

LEAR_PORT = 80,

COMPRESSION=DISABLED)

FOR SOAP (WEBMETHOD 'EmployeeList'( NAME=N'[AdventureWorks].[dbo].[GetEmployees]'

, SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS),

BATCHES=DISABLED,

WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',

SESSIONS=DISABLED,

SESSION_TIMEOUT=60,

DATABASE=N'AdventureWorks',

NAMESPACE=N'http://AdventureWorks/Employee',

SCHEMA=STANDARD,

CHARACTER_SET=XML)

|||

Hi Chris,

Thanks it works. But I am still facing problem while accessing it from client.As we use ASMX file in standard web services I can use url path of the ASMX file to get refference of the web service. But I do not know how can I get refference of WSDL & where it was generated?

Thank you,

Nilkangth

|||

Does this work?

http://Server/sql/shapeserve?wsdl

Chris

|||

Hi Chris,

This does not work. It is giving me error Page cannot be founf. TTP Error 404- File or Directory not found.

Server Name is TISSERVER.

SQL Server Name instance is DEVELOPMENT

Nilkanth Desai

|||

Hi Chris,

I have resoved this issue by taking web refference at http://localhost:8080/ShapeServ?wsdl . As I am publishing this webservice on 8080 port I am able to get refference in client.

Thanks for your Response,

Nilkanth Desai

Nilkanth Desai

problem while creating an ENDPOINT

Hi,

I am trying to create an ENDPOINT in SQL Server 2005 database. My script is as below. I am unable to create this endpoint. It shows error as 'Incorrect syntext near HTTP'

Script:

/****** Object: Endpoint [GetEmployees] Script Date: 03/02/2007 22:21:26 ******/
CREATE ENDPOINT [GetEmployeesBINA]
AUTHORIZATION [TIS-WORLD\Administrator]
STATE=STARTED
AS HTTP (PATH = http://tisserver:80/sql/shapeserv, PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED), SITE=N'localhost', CLEAR_PORT = 80, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'EmployeeList'( NAME=N'[AdventureWorks].[dbo].[GetEmployees]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'AdventureWorks', NAMESPACE=N'http://AdventureWorks/Employee', SCHEMA=STANDARD, CHARACTER_SET=XML)

2) Once I create this endpoint. If I want to check the endpoint in browser , Which url I haeve to use.

Nilkanth Desai

You'd not included single quotes around the PATH, also the PATH was not in absolute format.

Try the code below instead.

Chris

CREATE ENDPOINT [GetEmployeesBINA]
AUTHORIZATION [TIS-WORLD\Administrator]
STATE = STARTED
AS HTTP (PATH = '/sql/shapeserv',

PORTS = (CLEAR),

AUTHENTICATION = (INTEGRATED),

SITE=N'localhost',

LEAR_PORT = 80,

COMPRESSION=DISABLED)

FOR SOAP (WEBMETHOD 'EmployeeList'( NAME=N'[AdventureWorks].[dbo].[GetEmployees]'

, SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS),

BATCHES=DISABLED,

WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',

SESSIONS=DISABLED,

SESSION_TIMEOUT=60,

DATABASE=N'AdventureWorks',

NAMESPACE=N'http://AdventureWorks/Employee',

SCHEMA=STANDARD,

CHARACTER_SET=XML)

|||

Hi Chris,

Thanks it works. But I am still facing problem while accessing it from client.As we use ASMX file in standard web services I can use url path of the ASMX file to get refference of the web service. But I do not know how can I get refference of WSDL & where it was generated?

Thank you,

Nilkangth

|||

Does this work?

http://Server/sql/shapeserve?wsdl

Chris

|||

Hi Chris,

This does not work. It is giving me error Page cannot be founf. TTP Error 404- File or Directory not found.

Server Name is TISSERVER.

SQL Server Name instance is DEVELOPMENT

Nilkanth Desai

|||

Hi Chris,

I have resoved this issue by taking web refference at http://localhost:8080/ShapeServ?wsdl . As I am publishing this webservice on 8080 port I am able to get refference in client.

Thanks for your Response,

Nilkanth Desai

Nilkanth Desai

problem while creating a linked server from SQL-Server2000 to an Access97 database

I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts

EXEC sp_addlinkedserver

@.server='REMOTE_OFFICE',

@.srvproduct='Jet 4.0',

@.provider='Microsoft.Jet.OLEDB.4.0',

@.datasrc='F:\RealEstate_Office1.mdb'

and

EXEC sp_addlinkedsrvlogin

@.rmtsrvname='REMOTE_OFFICE',

@.useself='false',

@.locallogin='sa',

@.rmtuser='Admin',

@.rmtpassword=NULL

And while querying the linked server from the query analyzer using the following select command

SELECT *

FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK

I got the following error

Server: Msg 7312, Level 16, State 1, Line 1

Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

OLE DB error trace [Non-interface error].

could you explain why this happen

The error indicates that Access does not support database or schema names. Try leaving those parts empty. SELECT * FROM REMOTE_OFFICE...E_GOV_RE_OK.

This post belongs in another forum.
|||Moving to the SQL Server Data Access Forum.|||

Thanks JayH,

after a little search I found that it was an article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database and its link is as follows

http://support.microsoft.com/?kbid=246255

Article ID : 246255
Last Review : February 12, 2007
Revision : 4.3

Thanks all.

Problem when passing parameter to Execute SQL Task

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?\ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?\ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?\ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?

Thank you!

Did you set the "BypassPrepare" to True on the execute task? That gets me every time I use variables.

If you don't set that, it treats the ? as a literal and fails.|||

Hi!

Yes, BypassPrepare is set to true...

Thank you!

|||Setup another variable to hold your full string so that in your SQL you only have the ? placeholder instead of ?/ASPNET.

So, in my test, I created a new variable, set its expression to concatenate the User::ServerName variable and the /ASPNET string. Set the new variable to EvaluateAsExpression and then use it in the parameter mapping of the ExecuteSQL task.

Does that do what you're looking for?|||

Hi!

I tried what you suggest and I still have the error... Then I tried something simpler, CREATE LOGIN ? WITH PASSWORD = 'lalala', and I got the following error:

Executing the query "CREATE LOGIN ? WITH PASSWORD = 'lalala'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And if I change it to CREATE LOGIN test WITH PASSWORD = 'lalala' it works fine, so I guess it's not a connection problem. I guess I'm missing something silly!

Thank you!

|||What type of connection are you using? OLE DB, ADO, ADO.NET, ODBC, etc..

The connection type that you use will dictate how you name the parameter and then use a parameter in your SQL.|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx|||

I'm using an OLE DB connection, and it seems the parameter name is ok... Posting the code would help?

Thank you!

|||I have a variable (User::Test) set as a string with a value of "P0160".

In the Execute SQL Task editor, my sql statement is:
update client set clientdesc = 'testing' where client = ?

Then, in the parameter mapping section, I added a variable and selected User::Test as the variable name. Its data type is VARCHAR. The Parameter Name is simply, 0.

That's it. I run the task and it works correctly.|||It works now. Thank you very much for you help!

Problem when moving index with included columns to another filegroup

Hi,

This may be a GUI bug. When I manually create and then execute a script to move an index with included columns to another filegroup, it works.

When I try to edit the storage property of the index through the GUI, index properties, then when I ask to generate the script for the change to the clipboard, I get an error

Could not add [FirstIncludedColumn] to the collection because it already exist. Microsoft.sqlserver.Smo

it works if the index does not includes addtl columns.

Is that a bug? It really looks it works when done manually.

Thanks, Philippe

Moving this thread to the Tools forum since this is not a TSQL/DDL issue.|||

This sounds like a bug to me. Please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

If possible, please include the T-SQL to create the index that causes problems. It also helps if you can include really detailed instructions for what you clicked/typed in the index dialog to make the storage change.

Thanks,
Steve