Error when I try to retrieve a HTML String from an table.

Error when I try to retrieve a HTML String from an table.I've got exactly the same problem - and can trigger it.
My stored procedure returns several rows of a single text field. This
works well by and large, until the total combined output length exceeds
1024 characters, then I get exactly this error
I'm completly stumped at the moment.
just in case it helps, using
http://dev/xml?sql=EXEC XML_Page 22420,1
I get..(this works - it's exaclty 1024 chars of valid xml - if I was to
increase a text item returned by stored procedure by just 1 more
character, its still valid - but I get the error)
..
[I cannot not - I still crash these forums - it's just 1024 characters
of valid xml]
..
Any help, links, possible explanations, hints - all very much
appreciated :)
davemcrae
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message337340.html|||(please bear with me as I post this editing and adding 1 line at a time
- and eNSERT pls read as INSERT - this causes forums to crash for me -
also {} are angle brackets)
I hate it when my boss walks in and suggests modifying a value then it
works - still doesnt to my satifaction though.
we have
CREATE PROCEDURE XML_Page
..
CREATE TABLE #XMLOut(XMLData TEXT)
.
eNSERT eNTO #XMLOutVALUES('{page}')
eNSERT eNTO #XMLOut xELECT aTextFieldWithValidXML FROM pageSections
WHERE ..
eNSERT eNTO #XMLOut VALUES('{page}')
.
xELECT XMLData FROM #XMLOut
fROP TABLE #XMLOut
this will fail if the total length exceeds 1024 characters
now change the CREATE TABLE #XMLOut(XMLData TEXT) to read CREATE TABLE
#XMLOut(XMLData VARCHAR(8000))
yes, you can see the problem with my pagesection data :)
but I do NOT get the error, as long as the pageSections are now under
8k, my total length can be unlimted
with a text field, I have no restrictions, but system dies when exceeds
1024 character length
not using a temporary table is worse
if, rather than INSERTS, I just SELECT for output, I get the 1024
character limitation
so for Karthikeyan K you may use a temporary table in your stored
procedure with a field that is Varchar rather than text and as long as
you can keep it under 8k (4k for nvarchar) this limitation wont bite.
Does anyone know of another way? - I really want to keep my sections as
text fields without an 8k limit :)
Whew - that was the most painful post ever - alter DROP to fROP try -
yes it works - paste in next line, fails, alter a character - passes,
next line, so on
davemcrae
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message337340.html|||What is the error message? What exactly does fail? The stored proc or IE?
Best regards
Michael
"davemcrae" <davemcrae.1nd3m3@.mail.mcse.ms> wrote in message
news:davemcrae.1nd3m3@.mail.mcse.ms...
> (please bear with me as I post this editing and adding 1 line at a time
> - and eNSERT pls read as INSERT - this causes forums to crash for me -
> also {} are angle brackets)
> I hate it when my boss walks in and suggests modifying a value then it
> works - still doesnt to my satifaction though.
> we have
> CREATE PROCEDURE XML_Page
> ..
> CREATE TABLE #XMLOut(XMLData TEXT)
> .
> eNSERT eNTO #XMLOutVALUES('{page}')
> eNSERT eNTO #XMLOut xELECT aTextFieldWithValidXML FROM pageSections
> WHERE ..
> eNSERT eNTO #XMLOut VALUES('{page}')
> .
> xELECT XMLData FROM #XMLOut
> fROP TABLE #XMLOut
>
> this will fail if the total length exceeds 1024 characters
> now change the CREATE TABLE #XMLOut(XMLData TEXT) to read CREATE TABLE
> #XMLOut(XMLData VARCHAR(8000))
> yes, you can see the problem with my pagesection data :)
> but I do NOT get the error, as long as the pageSections are now under
> 8k, my total length can be unlimted
> with a text field, I have no restrictions, but system dies when exceeds
> 1024 character length
> not using a temporary table is worse
> if, rather than INSERTS, I just SELECT for output, I get the 1024
> character limitation
> so for Karthikeyan K you may use a temporary table in your stored
> procedure with a field that is Varchar rather than text and as long as
> you can keep it under 8k (4k for nvarchar) this limitation wont bite.
> Does anyone know of another way? - I really want to keep my sections as
> text fields without an 8k limit :)
>
> Whew - that was the most painful post ever - alter DROP to fROP try -
> yes it works - paste in next line, fails, alter a character - passes,
> next line, so on
>
> --
> davemcrae
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message337340.html
>

Error updating a DateTime field

Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do...I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:
<code>
Dim CompDate As DateTime
Dim aComm As SQLCommand
Dim aReader As SQLDataReader
Dim bSQL,bConn As String
bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39)
bConn = serverStuff aConn = New SQLConnection(bConn)
aComm = New SQLCommand(bSQL,aConn)
aConn.Open()
result = aComm.ExecuteReader()
'fills controls with data
While result.Read()
CompDate = result("CompleteDate")
PreviousMarkoutStatus.Text = result("StatusOfMarkout")
End While
result.Close()
aConn.Close()
sSqlCmd ="Update OneCallTickets CompleteDate=@.CompleteDate, StatusOfMarkout=@.StatusOfMarkout WHERE TicketFileName=@.TicketFileName"
dim SqlCon as New SqlConnection(serverStuff)
dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon)
If Flag1List.SelectedItem.Value = "No Change" Then
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = PreviousMarkoutStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CompDate
Else
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = CurrentStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = Today()
End If
SqlCon.Open()
SqlCmd.ExecuteNonQuery()
SqlCon.Close()
</code>
Can anybody help me with this? Thanks a bunchIs your CompDate a DateTime or is it a string? #1 make sure it is a DateTime.
You might also try using the Convert function in your procedure to convert the argument to a SQL DateTime and see what happens.|||

Yes, the value I'm reading from the database is 100% a datetime. Could you give me an example of how to convert? Thanks

|||

I think you need the DateDiff function of both VB .NET and SQL Server and also change your data type to SmallDateTime it has less resolution if the seconds are not important. Try the links below for sample code using TimeSpan with DateDiff. Hope this helps.
http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

|||Hmm, I guess I don't understand. I read through those links and all I could find was how to calculate the difference between two dates. I couldn't really find an answer in there?|||CONVERT(DATETIME, Your_Date_Field)|||

When I try that here:
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CONVERT(DATETIME, CompDate)
I get a Compiler Error Message: BC30684: 'CONVERT' is a type and cannot be used as an expression.
Should I be using it somewhere else?

|||I was assuming you are using a stored procedure to do the work. The CONVERT function is a T_SQL function and should be done inside of a stored procedure or SQL statement.

error trying to import packages to the MSDB

When I try import a SSIS package into the MSDB I get the following error message:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

The .Net Framework OleDb Data Provider requires Microsoft Data Access Components(MDAC) version 2.6 or later. Version 2.12.4202.3 was found currently installed. (MsDtsSrvr)

BUTTONS:

OK

I do have default instance installed and the SSIS servie is running. When I look at the SSIS service configuration it shows a period for the server which I thought meant local host. Also I have MDAC 2.8 installed. What else do I need to look at?

Try the MDAC version checker just to be sure you have the correct version. http://support.microsoft.com/default.aspx?kbid=301202

As you can see from the error message this is more likely to be an MDAC issue than an SSIS issue per se, but let's see what the version says first.

Donald

|||The version checker shows that 2.8 is installed but the Registry setting shows 2.1. We had this issue before and just had to install 2003 OS Sp 1 again.|||

So you think you may be able to fix this again? If so, great. If not, I think the SQL Server Data Access forum would be the right place to get help.

thanks

Donald

|||

Phil,

Do you have multiple instances installed on your server? If not you might try hard coding your server name into your SSIS configuration file

<ServerName>my-server-01</ServerName>

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my config file

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

This did the trick for me, hopefully it will work for you.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?
|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?
|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.