Cannot drop type

Cannot drop a type event though it is not in use.

The type was created via create type and is an non-nullable nvarchar. It should have been nullable.

I have removed all dependencies on the type, by dropping tabes, stored procs etc, but still cannot drop it. SSMSE says the type cannot be dropped because it's in use. SSMSE shows no dependencies on the type.

Any help?

Can you run these queries

select object_name(object_id), * from sys.columns where user_type_id=type_id('Your_type_name')

select object_name(object_id), * from sys.parameters where user_type_id=type_id('Your_type_name')

and see if they return any rows back. If they do then there are still some dependencies left in the database on the type. The object_id is the object which has those dependencies.

Thanks

Asvin

|||

Many thanks Asvin, that got it.

Turned out an old view was still referencing the type. Strange that SSMSE did not see this dependency.

|||

Also, check out this very good blog by Umachandar titled "Direct Dependencies on column"

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

Cannot drop type

Cannot drop a type event though it is not in use.

The type was created via create type and is an non-nullable nvarchar. It should have been nullable.

I have removed all dependencies on the type, by dropping tabes, stored procs etc, but still cannot drop it. SSMSE says the type cannot be dropped because it's in use. SSMSE shows no dependencies on the type.

Any help?

Can you run these queries

select object_name(object_id), * from sys.columns where user_type_id=type_id('Your_type_name')

select object_name(object_id), * from sys.parameters where user_type_id=type_id('Your_type_name')

and see if they return any rows back. If they do then there are still some dependencies left in the database on the type. The object_id is the object which has those dependencies.

Thanks

Asvin

|||

Many thanks Asvin, that got it.

Turned out an old view was still referencing the type. Strange that SSMSE did not see this dependency.

|||

Also, check out this very good blog by Umachandar titled "Direct Dependencies on column"

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

Cannot drop triggers using dynamic SQL

Hi,
I'm using:
SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
I've used this on a number of occaisions but it doesn't seem to be dropping
the triggers this time.
Can anybody think of what I may be doing wrong here?
Many thanks for any assistance in advance
AntHi Ant
This SELECT will generate DROP TRIGGER statements, but it will not execute
them to actually drop the triggers. You need to take the output and execute
it.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> Hi,
> I'm using:
> SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> I've used this on a number of occaisions but it doesn't seem to be
> dropping
> the triggers this time.
> Can anybody think of what I may be doing wrong here?
> Many thanks for any assistance in advance
> Ant|||Hi Kalen,
Apoligies but you might have to walk me through that.
I tried:
exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
)
but naturally I got an error.
How is this done?
Many thanks for your answer.
Ant
"Kalen Delaney" wrote:
> Hi Ant
> This SELECT will generate DROP TRIGGER statements, but it will not execute
> them to actually drop the triggers. You need to take the output and execute
> it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> > Hi,
> > I'm using:
> >
> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >
> > I've used this on a number of occaisions but it doesn't seem to be
> > dropping
> > the triggers this time.
> >
> > Can anybody think of what I may be doing wrong here?
> >
> > Many thanks for any assistance in advance
> >
> > Ant
>
>|||Ant
DECLARE @.DeleteTrigger nvarchar(4000)
DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
FOR
SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
OPEN DeleteTrigger
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
EXEC(@.DeleteTrigger)
END
CLOSE DeleteTrigger
DEALLOCATE DeleteTrigger
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> Hi Kalen,
> Apoligies but you might have to walk me through that.
> I tried:
> exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> )
> but naturally I got an error.
> How is this done?
> Many thanks for your answer.
> Ant
>
> "Kalen Delaney" wrote:
>> Hi Ant
>> This SELECT will generate DROP TRIGGER statements, but it will not
>> execute
>> them to actually drop the triggers. You need to take the output and
>> execute
>> it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> > Hi,
>> > I'm using:
>> >
>> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >
>> > I've used this on a number of occaisions but it doesn't seem to be
>> > dropping
>> > the triggers this time.
>> >
>> > Can anybody think of what I may be doing wrong here?
>> >
>> > Many thanks for any assistance in advance
>> >
>> > Ant
>>|||Thank you.
Can this be done with Dynamic SQL? I'm sure I've done this before without
having to resort to using a Cursor(?)
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>|||Uri's solution IS dynamic SQL. Dynamic SQL means that we are building the
SQL command and then using EXEC to execute it, and he has this:
EXEC(@.DeleteTrigger)
I am assuming you are looking for a way to do it all with one statement.
You have not said what version you are using.
SQL 2000 has a procedures sp_execresultset that lets you execute the output
of another statement. This was not included in SQL 2005; I don't know why.
However, you should be able to get the definition of sp_execresultset from a
SQL 2000 server and create it in the master database of a SQL 2005 server
and get the same behavior.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:E46E8721-4815-4025-8C0E-D2092AF975DF@.microsoft.com...
> Thank you.
> Can this be done with Dynamic SQL? I'm sure I've done this before without
> having to resort to using a Cursor(?)
>
> "Uri Dimant" wrote:
>> Ant
>> DECLARE @.DeleteTrigger nvarchar(4000)
>> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
>> FOR
>> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> OPEN DeleteTrigger
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
>> IF @.@.FETCH_STATUS <> 0 BREAK
>> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
>> EXEC(@.DeleteTrigger)
>> END
>> CLOSE DeleteTrigger
>> DEALLOCATE DeleteTrigger
>>
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
>> > Hi Kalen,
>> >
>> > Apoligies but you might have to walk me through that.
>> >
>> > I tried:
>> >
>> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> > )
>> >
>> > but naturally I got an error.
>> >
>> > How is this done?
>> >
>> > Many thanks for your answer.
>> > Ant
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Ant
>> >>
>> >> This SELECT will generate DROP TRIGGER statements, but it will not
>> >> execute
>> >> them to actually drop the triggers. You need to take the output and
>> >> execute
>> >> it.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> >> > Hi,
>> >> > I'm using:
>> >> >
>> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >> >
>> >> > I've used this on a number of occaisions but it doesn't seem to be
>> >> > dropping
>> >> > the triggers this time.
>> >> >
>> >> > Can anybody think of what I may be doing wrong here?
>> >> >
>> >> > Many thanks for any assistance in advance
>> >> >
>> >> > Ant
>> >>
>> >>
>> >>
>>|||Hello Uri,
Thank you for this solution. I thought I'd exhaust all possiblities before
commiting to this. It does seem to ber the best one.
Much appreciated & sorry for the late reply
Ant
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>

Cannot create variable of type table

Hi all,
Just heard of table-type variables and wanted to try one out in a SP
(MS-SQL Server 7.0).
However, every time I try to declare such a variable as, (for instance)
:
declare @.mytbl table(pos_num smallint, seq_num smallint)
I get an error :
Error 156: Incorrect syntax near the word 'table' .
Any ideas as to what the problem here might be (I have full admin
rights on this particular database)?
TIA, RobertSQL Server 7.0 doesnt know about table variables:
http://developer.com/db/article.php/3414331
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"robertino" <rbanfield@.telair.com> schrieb im Newsbeitrag
news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
> Hi all,
> Just heard of table-type variables and wanted to try one out in a SP
> (MS-SQL Server 7.0).
> However, every time I try to declare such a variable as, (for instance)
> :
> declare @.mytbl table(pos_num smallint, seq_num smallint)
> I get an error :
> Error 156: Incorrect syntax near the word 'table' .
> Any ideas as to what the problem here might be (I have full admin
> rights on this particular database)?
> TIA, Robert
>|||Table variables are not supported in7.0. They are available in SQL Server
2000.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"robertino" <rbanfield@.telair.com> wrote in message
news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
Hi all,
Just heard of table-type variables and wanted to try one out in a SP
(MS-SQL Server 7.0).
However, every time I try to declare such a variable as, (for instance)
:
declare @.mytbl table(pos_num smallint, seq_num smallint)
I get an error :
Error 156: Incorrect syntax near the word 'table' .
Any ideas as to what the problem here might be (I have full admin
rights on this particular database)?
TIA, Robert|||Hmmm, just as I suspected - we have here SQL Server 2000 clients
running as frontends to a SQL Server 7 DB. Phooey! (Been on holiday, so
haven't been able to reply until now.)
Robert
Tom Moreau wrote:
> Table variables are not supported in7.0. They are available in SQL Server
> 2000.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robertino" <rbanfield@.telair.com> wrote in message
> news:1117443939.893947.77470@.g44g2000cwa.googlegroups.com...
> Hi all,
> Just heard of table-type variables and wanted to try one out in a SP
> (MS-SQL Server 7.0).
> However, every time I try to declare such a variable as, (for instance)
> :
> declare @.mytbl table(pos_num smallint, seq_num smallint)
> I get an error :
> Error 156: Incorrect syntax near the word 'table' .
> Any ideas as to what the problem here might be (I have full admin
> rights on this particular database)?
> TIA, Robert

Cannot create SSIS projects

When i create a new SSIS project, the system alert will popup a window to show 'Constructor on type 'Microsoft.DataTransformationService.wizards.ETLProjectInitializer' not found'

I met this kind of questions more than 3 time.

Reinstall VS2005?

How should i do?

It sounds like something in the system dll's did not get included or was removed.

Probably the best thing would be to reinstall business intelligence which is a part of the SQL Server install

Re Installing Visual Studio would do nothing due to the fact that SSIS is not associated with Visual Studio 2005 other then through Business Intelligence.

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create
a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the nam
e
cannot be an empty string" but I have no place to write the name of the
dimension.I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.

Cannot Create private Time Dimension

I have a fact table with 32.000.000 rows. In cube editor , I want to create a
private time dimension based on a datetime type column of my fact table.
after I press next button on "select advanced options" window, several
minutes passes and then a "finish" button appears in the same window
instead of next button. when I pressed finish, I got pop up message "the name
cannot be an empty string" but I have no place to write the name of the
dimension.
I typically discourage creating a time dimension like this. I strongly
recommend that you create an independent dimension table which incorporates
"Time". Then use the timestamps from your fact table as FKs to this
dimension. This is normally the best approach because it:
1) allows you to model data which does not exist, e.g. holidays, weekends,
etc, which are not represented in your fact table.
2) dimension processing goes much faster (i.e. you don't have to scan
multiple rows); this is particularly true here because the system will issue
a SELECT DISTINCT to process the private time dimension.
3) private dimensions must always be reprocessed when you process the fact
table -- which can greatly increase your overhead (see #2 above)
4) you will always be limited to a single partition because you can't build
across multiple fact tables (which can limit your scalability, query
response time, rolling "n" months -- all of which use multiple partitions).
5) if you create an independent time dimension table, you can have
interesting extensions to just a timestamp, e.g. model holidays, seasons,
work-days compared to weekends, etc.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_Tr" <Banu_Tr@.discussions.microsoft.com> wrote in message
news:ABBE65C4-C6C8-4DF5-AEB8-2DFBE6489608@.microsoft.com...
> I have a fact table with 32.000.000 rows. In cube editor , I want to
create a
> private time dimension based on a datetime type column of my fact table.
> after I press next button on "select advanced options" window, several
> minutes passes and then a "finish" button appears in the same window
> instead of next button. when I pressed finish, I got pop up message "the
name
> cannot be an empty string" but I have no place to write the name of the
> dimension.
sql

cannot create multiple triggers

PLS HELP !!!!
I cannot create multiple triggers (AFTER) of command type INSERT. When
I create a new one the previous one gets deleted.
Prodcut: SQL Server Enterprise Edition.
Product Version:8.00.194 (TRM).
The funny thing is that this happens on clients production environment
while I am able to create multiple triggers on my development
environment. The properties for this sql servers is as follows
Product: SQL Server Enterprise Edition.
Production Version: 8.00.760 (SP3).
Is this because of different product version or am i missing something?Hi
Tell you client to apply at least SQL Server 20000 SP3a. They are totally
unpatched and susceptible to Slammer.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<agandhi@.usc.edu> wrote in message
news:1121983311.188033.51980@.g47g2000cwa.googlegroups.com...
> PLS HELP !!!!
> I cannot create multiple triggers (AFTER) of command type INSERT. When
> I create a new one the previous one gets deleted.
> Prodcut: SQL Server Enterprise Edition.
> Product Version:8.00.194 (TRM).
> The funny thing is that this happens on clients production environment
> while I am able to create multiple triggers on my development
> environment. The properties for this sql servers is as follows
> Product: SQL Server Enterprise Edition.
> Production Version: 8.00.760 (SP3).
> Is this because of different product version or am i missing something?
>|||Thanks for the recommendation and I will do that immediately but any
idea about not being able to create multiple triggers.
Regards,|||Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
Machine.|||The service pack may fix the problem you're having with creating multiple
triggers. If it works on the development server and not on the production
server, then it is probably the service pack. Unless the triggers are
instead of triggers, you can have more than one trigger of the same kind on
a table provided the name of the trigger is different for each one.
<agandhi@.usc.edu> wrote in message
news:1121985554.564111.259640@.g47g2000cwa.googlegroups.com...
> Thanks for the recommendation and I will do that immediately but any
> idea about not being able to create multiple triggers.
> Regards,
>|||agandhi@.usc.edu wrote:
> Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
> Machine.
He said SP3a as a *minimum*. Whether they're ready to move up to SP4 or
not is more of a business decision, and less of a "we have a massive
security hole here" kind of thing.
Damien

Cannot create a row of size 8075 which is greater than... (-214721

Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/d...serr_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCulloughHi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/d...serr_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough

Cannot create a row of size 8075 which is greater than... (-214721

Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough

Cannot convert between unicode and non-unicode string data type

HI.

I'm having this problem.

I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.

Destinated collumns in SQL are defined as varchar and char.

Thanks for help

I had the same problem and I put a Data Conversation object to convert the fields from XML to string [ST_STR] and everything works fine.

Hope this helps
Paulo Aboim Pinto
Odivelas - Portugal

|||Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.
|||

pkv wrote:

Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.

Please mark your post as answered if you've solved your own issue. It helps us to filter out which posts still require attention.|||Post from another topic that might help...
-

Katrina, you are a goddess among men. For anyone out there who still has this problem, Katrina has led me to the solution. I was still a little confused when I read it, so here are the exact steps I took to change it. I was loading from a flat text file. In the edit page of the file connection manager (not flat file source), go to the advanced tabs to see the columns. Here you can set the load type for the columns (I wanted non-unicode, so I chose String). Then you connect to (let's say) an OLE DB Destination. If you double click to open the connection, you can choose "metadata" and see the types are correct. Right click on the OLE DB Destination, and choose "Show Advanced Editor". Then click on the "Input and Output Properties" tab. Expand "OLE DB Destination Input" and you will see two folders ("External Columns" and "Input Columns"). If you open "Input Columns" and choose one of your columns, you will see the correct data type (ie. DT_STR). Now, if you expand the "External Columns" folder and choose one of your columns, you will see that it thinks an incorrect data type is coming in (ie. "Unicode string [DT_WSTR]"). This is what is causing your error. This makes the package think that it needs to implicitly convert what it THINKS is coming in (DT_WSTR) to what it wants (DT_STR), which it refuses to do. In the "External Columns" folder, change all of your data types to the correct incoming types, and all your worries will float away.
To aid in the discussion on many forums about why this is a problem, this is most definitely a Microsoft error. When you connect the source to the destination, the connection should force the destination to update its input types. Again, thanks to Katrina for leading the way!
|||

Tim/Katrina, that all sounds great. But when I change the External Columns to DT_STR, it doesn't save my changes!!!! I'll go right back into my OLE DB Destination, and whamo, it changes it back to DT_WSTR.

Any ideas?

I'm running:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

|||

Hi..

Set ValidateExternalMetata option of the Destination False from True, and change types of external columns in Advnaced Editor.

Related Article and sample packages (written by Korean..Sorry!!)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=807

test user_id and password : test / test

HTH

|||

OH MY GOD THANKS FOR POINTING THIS VALIDATEEXTERNALMETADATA OUT I HAVE SPENT HOURS FIDDLING WITH SETTINGS AND FINALLY IT WORKS!!!!!

But it totally sucks beyond belief that SSIS is so full of gotchas like this.

Cannot convert between unicode and non-unicode string data type

HI.

I'm having this problem.

I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.

Destinated collumns in SQL are defined as varchar and char.

Thanks for help

I had the same problem and I put a Data Conversation object to convert the fields from XML to string [ST_STR] and everything works fine.

Hope this helps
Paulo Aboim Pinto
Odivelas - Portugal

|||Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.|||

pkv wrote:

Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.

Please mark your post as answered if you've solved your own issue. It helps us to filter out which posts still require attention.|||Post from another topic that might help...
-

Katrina, you are a goddess among men. For anyone out there who still

has this problem, Katrina has led me to the solution. I was still a

little confused when I read it, so here are the exact steps I took to

change it. I was loading from a flat text file. In the edit page of the

file connection manager (not flat file source), go to the advanced tabs

to see the columns. Here you can set the load type for the columns (I

wanted non-unicode, so I chose String). Then you connect to (let's say)

an OLE DB Destination. If you double click to open the connection, you

can choose "metadata" and see the types are correct. Right click on the

OLE DB Destination, and choose "Show Advanced Editor". Then click on

the "Input and Output Properties" tab. Expand "OLE DB Destination

Input" and you will see two folders ("External Columns" and "Input

Columns"). If you open "Input Columns" and choose one of your columns,

you will see the correct data type (ie. DT_STR). Now, if you expand the

"External Columns" folder and choose one of your columns, you will see

that it thinks an incorrect data type is coming in (ie. "Unicode string

[DT_WSTR]"). This is what is causing your error. This makes the package

think that it needs to implicitly convert what it THINKS is coming in

(DT_WSTR) to what it wants (DT_STR), which it refuses to do. In the

"External Columns" folder, change all of your data types to the correct

incoming types, and all your worries will float away.

To aid in the discussion on many forums about why this is a problem,

this is most definitely a Microsoft error. When you connect the source

to the destination, the connection should force the destination to

update its input types. Again, thanks to Katrina for leading the way!|||

Tim/Katrina, that all sounds great. But when I change the External Columns to DT_STR, it doesn't save my changes!!!! I'll go right back into my OLE DB Destination, and whamo, it changes it back to DT_WSTR.

Any ideas?

I'm running:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

|||

Hi..

Set ValidateExternalMetata option of the Destination False from True, and change types of external columns in Advnaced Editor.

Related Article and sample packages (written by Korean..Sorry!!)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=807

test user_id and password : test / test

HTH

|||

OH MY GOD THANKS FOR POINTING THIS VALIDATEEXTERNALMETADATA OUT I HAVE SPENT HOURS FIDDLING WITH SETTINGS AND FINALLY IT WORKS!!!!!

But it totally sucks beyond belief that SSIS is so full of gotchas like this.

Cannot convert between unicode and non-unicode string data type

HI.

I'm having this problem.

I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.

Destinated collumns in SQL are defined as varchar and char.

Thanks for help

I had the same problem and I put a Data Conversation object to convert the fields from XML to string [ST_STR] and everything works fine.

Hope this helps
Paulo Aboim Pinto
Odivelas - Portugal

|||Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.|||

pkv wrote:

Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.

Please mark your post as answered if you've solved your own issue. It helps us to filter out which posts still require attention.|||Post from another topic that might help...
-

Katrina, you are a goddess among men. For anyone out there who still

has this problem, Katrina has led me to the solution. I was still a

little confused when I read it, so here are the exact steps I took to

change it. I was loading from a flat text file. In the edit page of the

file connection manager (not flat file source), go to the advanced tabs

to see the columns. Here you can set the load type for the columns (I

wanted non-unicode, so I chose String). Then you connect to (let's say)

an OLE DB Destination. If you double click to open the connection, you

can choose "metadata" and see the types are correct. Right click on the

OLE DB Destination, and choose "Show Advanced Editor". Then click on

the "Input and Output Properties" tab. Expand "OLE DB Destination

Input" and you will see two folders ("External Columns" and "Input

Columns"). If you open "Input Columns" and choose one of your columns,

you will see the correct data type (ie. DT_STR). Now, if you expand the

"External Columns" folder and choose one of your columns, you will see

that it thinks an incorrect data type is coming in (ie. "Unicode string

[DT_WSTR]"). This is what is causing your error. This makes the package

think that it needs to implicitly convert what it THINKS is coming in

(DT_WSTR) to what it wants (DT_STR), which it refuses to do. In the

"External Columns" folder, change all of your data types to the correct

incoming types, and all your worries will float away.

To aid in the discussion on many forums about why this is a problem,

this is most definitely a Microsoft error. When you connect the source

to the destination, the connection should force the destination to

update its input types. Again, thanks to Katrina for leading the way!|||

Tim/Katrina, that all sounds great. But when I change the External Columns to DT_STR, it doesn't save my changes!!!! I'll go right back into my OLE DB Destination, and whamo, it changes it back to DT_WSTR.

Any ideas?

I'm running:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

|||

Hi..

Set ValidateExternalMetata option of the Destination False from True, and change types of external columns in Advnaced Editor.

Related Article and sample packages (written by Korean..Sorry!!)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=807

test user_id and password : test / test

HTH

|||

OH MY GOD THANKS FOR POINTING THIS VALIDATEEXTERNALMETADATA OUT I HAVE SPENT HOURS FIDDLING WITH SETTINGS AND FINALLY IT WORKS!!!!!

But it totally sucks beyond belief that SSIS is so full of gotchas like this.