RelativeTime

Hi,

I have a traditional Time dimension, MatchDate (Year - Qtr - Month). I have created a dummy dimension RelativeTime in trying to copy the Cognos functionality. I have tried to created a calculated member:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

Hi

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

RelativeTime

Hi,

I have a traditional Time dimension, MatchDate (Year - Qtr - Month). I have created a dummy dimension RelativeTime in trying to copy the Cognos functionality. I have tried to created a calculated member:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

Hi

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

relationships in Project server2k3-> reporting services?

Ok,
We have setup SQL2005 reporting services with MOSS integration. I have
created a datasource that points at our Project server 2003 SQL
database so that we can start to create project server reports. The
data source works just fine BUT its *doesnt* pick up any relationships
between tables from the Project2k3 SQL database.
This means that when you create a report builder report the first
table you selct becomes the primary and no others are visable :( not
very useful for creating meaningful reports.
Questions:
a) How does SQL2005RS read the relationships between table entities in
a remote DB so that they can be used together within the report
builder?
b) Does anyone have recommendations, URLs, How-tos on setting up
reporting services against Project server 2003?
Tips welcome
Al BlakeI have written many reports against the Project Server 2003 database. I have
found there are not relationships in the Project Server database. You always
have to create your own joins of the data.
Most of the time I will create a query using query analyzer, Then use SRS
to group ,filter and sum the data.
Hope this helps
--
Ray McCoppin
http://www.randsmanagement.com
Project Server reports.
"scubaal" wrote:
> Ok,
> We have setup SQL2005 reporting services with MOSS integration. I have
> created a datasource that points at our Project server 2003 SQL
> database so that we can start to create project server reports. The
> data source works just fine BUT its *doesnt* pick up any relationships
> between tables from the Project2k3 SQL database.
> This means that when you create a report builder report the first
> table you selct becomes the primary and no others are visable :( not
> very useful for creating meaningful reports.
> Questions:
> a) How does SQL2005RS read the relationships between table entities in
> a remote DB so that they can be used together within the report
> builder?
> b) Does anyone have recommendations, URLs, How-tos on setting up
> reporting services against Project server 2003?
> Tips welcome
> Al Blake
>|||Thanks for the feedback. I thought that was the case but wanted be
sure I hadnt missed something.
I am quite comfortable in making the queries through SQL QA until I
get the right result but then I am unclear as to how I get this into
the SQL2005RS environment through MOSS.
Are you saying I should define views within the SQL database and then
use report builder to make a report against that?
OR
is there some way to 'parameterise' the Report Model object when you
create it to use a SQL queiry with join as the basis of its model?
As far as I can see when I create a RB model the ONLY option I have is
the datasource I want to use......there doesnt seem to be any way to
create an RBM from a datasourse using
SELECT * FROM A,B WHERE A.ID=B.ID
or am I missing something again?
Regards

Relationship diagram

Hi there,

I just set up my AppData and i'm trying to connect the Membership to a tbl_Profile that i created.
I notice there's an application_id and a user_id which are uniqueidentifiers. I've been trying create a relationship to tbl_Profile's user_id column but it won't let me saying it's not compatible. Am i suppose to set tbl_Profile's user_id column to int or uniqueidentifier? I've tried both and it won't let me...

THanks!

Hello lilboi,

Are there records in the table of which you try to change the column to int or uniqueidentifier?

When the original column type is char(n)/string, you can't change it to int or uniqueidentifier, because the conversion fails.

What relationship are you trying to create? Between what tables and what fields?

Jeroen Molenaar.

|||

hi jeroenm!

There are no records yet. So I was able to have it as int, but it wouldn't connect. THen i changed it to uniqueidentifier but it still wouldn't connect.

WHen i mean by connecting, i meant, when i try to draw a relationship between the column user_id for both the tables.

Thanks!

|||

Let me try to explain:

aspnet_Membership tbl_Profile
---------- -----
user_id 1----to----1 user_id

I try to draw a 1 to 1 relationship by going through Relationship Diagram and then dragging user_id(Profile) to user_id(Membership)

Relations without contrains

If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.sql

Relations without contrains

If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.

Relational Design

I have a database with 3 tables in a many to many relationship.

EmployeeGamingLicense
EmployeeDocuments
EmployeeRenewals

I created the third table to hold the employeerenewal dates, I did this because I cant have repeating vaules in the primary table. I am just making sure that my course of action is the correct one. The purpose of the Employeerenewals is to give the users an indication that a certain renewal has been processed, because in the past there was several hundred that were not processed. The problem was that the users had no way of knowing this. What I was thinking was having the primary table calculate a renewal date based off the finalsuit and show the results in the Employeerenewals with a yes of no drop down. Now the yes or no drop down box will be give the users their red flag if the renewals have been processed or not.

Does that make sense??

Thanks guys :)3 tables in a many to many relationship all related to 'Employee'? Does the table EmployeeRenewal hold anything else other than someID, refID (license?) and a yes/no? Personally, I don't think there is such a thing as 'the correct course of action'; there's just a lot of courses that either work, don't work or somewhere in between (sometimes work). Anyway, how does the solution work so far?sql

Re-installing RS 2000 report designer

I am somewhat of an RS newbie -- many months ago I installed RS (using sql
v2K, VS 2005) and created a handful of projects - nothing crazy, but they
worked. Recently I needed to get in there and either edit an existing or
create a new one, but I am unable to do so. In VS, start page, if I hit my
project, it fails w/this error:
The application for projetc 'D:\Projects\....\Project Name.rptproj' is not
installed.
Make sure the application for the project type (.rptproj) is installed.
I have tried everything - I even removed and reinstalled VS. Still get the
same error, nothing works.
In VS, New Project, Business Intelligence Projects is no longer available.
Also, all reports are available/visible via Report Manager. I just can't
get into VS to access/edit them, or to create new ones.
I've done a little surfing, have seen indication that I may need to
re-install the report designer. Is that correct, and if so, how do I do that?
Any advice at all really is appreciated.
-- LynnCan anybody tell me how to uninstall/reinstall RS 2000 report designer?
-- Lynn
"Lynn" wrote:
> I am somewhat of an RS newbie -- many months ago I installed RS (using sql
> v2K, VS 2005) and created a handful of projects - nothing crazy, but they
> worked. Recently I needed to get in there and either edit an existing or
> create a new one, but I am unable to do so. In VS, start page, if I hit my
> project, it fails w/this error:
> The application for projetc 'D:\Projects\....\Project Name.rptproj' is not
> installed.
> Make sure the application for the project type (.rptproj) is installed.
> I have tried everything - I even removed and reinstalled VS. Still get the
> same error, nothing works.
> In VS, New Project, Business Intelligence Projects is no longer available.
> Also, all reports are available/visible via Report Manager. I just can't
> get into VS to access/edit them, or to create new ones.
> I've done a little surfing, have seen indication that I may need to
> re-install the report designer. Is that correct, and if so, how do I do that?
> Any advice at all really is appreciated.
> -- Lynn

ReInstalling Katmai.

I cannot reinstall Katmai.
Somehow, I created a situation where Management Studio could not connect to reporting services. Being unable to diagnose why, I decided to uninstall and reinstall. I had installed everything. After running about 10 uninstalls, I went to C:/Program Files/ and blasted the whole Microsoft SQL Server tree. I went to the registry and did the same thing in HKLM/Software/Microsoft/Microsoft SQL Server.
I rebooted. The error message: An instance with the same name is already installed on this computer. I'm guessing the setup program detected MSSQLSERVER in some registry key, and not another instance, but if I knew which one, I wouldn't be posting to this forum.

It may be that Control Panel|Administrative Tools|Services has an SQL Server(InstanceName) entry.
sql

Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:

> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paul
not exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR
|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul
|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
--
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

Reindex and Error 1105

I recently created a new Maintenance Plan to reindex my database. Under the
Optimizations tab I only have Reorganize pages witht the original amount of
free space checked but I still received the 1105 error(I previously had the
Remove unused space from database files checked and received the 1105 error).
I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
space) after the failed reindex attempt. Please advise how can I fix this
problem. Thanks.
Sometimes autogrow isn't fast enough. In that cases, you need to pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
>I recently created a new Maintenance Plan to reindex my database. Under the
> Optimizations tab I only have Reorganize pages witht the original amount of
> free space checked but I still received the 1105 error(I previously had the
> Remove unused space from database files checked and received the 1105 error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||When you saw 1105 error, did you check if your disk is full or the data file
reaches to its maximum specified size?
The data file growth is expected, when a index is rebuilding, SQL Server has
to keep the old index around. Hence, the space requirement is doubled (plus
additional space for sorting and logging). SQL Server won't truncate the
file automatically, therefore you have a bigger data file after reindex (it
does not matter if the reindex succeeds or fails).
There is no need to worry about the too much free space problem. Since you
have "Remove unused space from database files" checked, DBCC SHRINKDATABASE
will start and truncate the data file sometime later.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
> I recently created a new Maintenance Plan to reindex my database. Under
the
> Optimizations tab I only have Reorganize pages witht the original amount
of
> free space checked but I still received the 1105 error(I previously had
the
> Remove unused space from database files checked and received the 1105
error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as
free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||I advise you to read the whitepaper below that will explain a bunch about
fragmentation, what to do about it, and when you really need to do anything
about it. Also, be aware that rebuilding an index requires double the index
size (as a new index has to be built before the old one can be dropped).
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJGECQ0nEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Sometimes autogrow isn't fast enough. In that cases, you need to
pre-allocate storage.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ling" <Ling@.discussions.microsoft.com> wrote in message
> news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
the[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
error).[vbcol=seagreen]
free[vbcol=seagreen]
this
>

Registration form problem. String or binary data would be truncated

I have created a registration form. It works fine IF ALL fields are filled. However, all fields are not required. When I test the registration page and leave a field blank, I receive the following message:

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

Stack Trace:

[SqlException: String or binary data would be truncated.
The statement has been terminated.]

Even if I leave a validated field blank, I receive the same message instead of the required field validation control error message.

Here is the code for the registration page. I use web matrix to create the code as directed in the tutorial.

Can anyone help a newbie?

Function AddMember( _
 ByVal firstName As String, _
 ByVal lastName As String, _
 ByVal streetAddress1 As String, _
 ByVal streetAddress2 As String, _
 ByVal memCity As String, _
 ByVal state As String, _
 ByVal zipCode As String, _
 ByVal primAreaCode As String, _
 ByVal primPhone As String, _
 ByVal primExt As String, _
 ByVal secAreaCode As String, _
 ByVal secPhone As String, _
 ByVal secExt As String, _
 ByVal memEmail As String, _
 ByVal memUserID As String, _
 ByVal memPassword As String, _
 ByVal secretQuestion As String, _
 ByVal secretAnswer As String, _
 ByVal memBirthMonth As String, _
 ByVal memBirthDay As String, _
 ByVal memBirthYear As String) As Integer
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Members'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "INSERT INTO [Members] ([FirstName], [LastName], [StreetAddress1], [StreetAddress2"& _
"], [MemCity], [State], [ZipCode], [PrimAreaCode], [PrimPhone], [PrimExt], [SecAr"& _
"eaCode], [SecPhone], [SecExt], [MemEmail], [MemUserID], [MemPassword], [SecretQu"& _
"estion], [SecretAnswer], [MemBirthMonth], [MemBirthDay], [MemBirthYear]) VALUES "& _
"(@.FirstName, @.LastName, @.StreetAddress1, @.StreetAddress2, @.MemCity, @.State, @.Zip"& _
"Code, @.PrimAreaCode, @.PrimPhone, @.PrimExt, @.SecAreaCode, @.SecPhone, @.SecExt, @.Me"& _
"mEmail, @.MemUserID, @.MemPassword, @.SecretQuestion, @.SecretAnswer, @.MemBirthMonth"& _
", @.MemBirthDay, @.MemBirthYear)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_firstName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_firstName.ParameterName = "@.FirstName"
 dbParam_firstName.Value = firstName
 dbParam_firstName.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_firstName)
 Dim dbParam_lastName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_lastName.ParameterName = "@.LastName"
 dbParam_lastName.Value = lastName
 dbParam_lastName.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_lastName)
 Dim dbParam_streetAddress1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_streetAddress1.ParameterName = "@.StreetAddress1"
 dbParam_streetAddress1.Value = streetAddress1
 dbParam_streetAddress1.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_streetAddress1)
 Dim dbParam_streetAddress2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_streetAddress2.ParameterName = "@.StreetAddress2"
 dbParam_streetAddress2.Value = streetAddress2
 dbParam_streetAddress2.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_streetAddress2)
 Dim dbParam_memCity As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memCity.ParameterName = "@.MemCity"
 dbParam_memCity.Value = memCity
 dbParam_memCity.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memCity)
 Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_state.ParameterName = "@.State"
 dbParam_state.Value = state
 dbParam_state.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_state)
 Dim dbParam_zipCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_zipCode.ParameterName = "@.ZipCode"
 dbParam_zipCode.Value = zipCode
 dbParam_zipCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_zipCode)
 Dim dbParam_primAreaCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primAreaCode.ParameterName = "@.PrimAreaCode"
 dbParam_primAreaCode.Value = primAreaCode
 dbParam_primAreaCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primAreaCode)
 Dim dbParam_primPhone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primPhone.ParameterName = "@.PrimPhone"
 dbParam_primPhone.Value = primPhone
 dbParam_primPhone.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primPhone)
 Dim dbParam_primExt As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primExt.ParameterName = "@.PrimExt"
 dbParam_primExt.Value = primExt
 dbParam_primExt.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primExt)
 Dim dbParam_secAreaCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secAreaCode.ParameterName = "@.SecAreaCode"
 dbParam_secAreaCode.Value = secAreaCode
 dbParam_secAreaCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secAreaCode)
 Dim dbParam_secPhone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secPhone.ParameterName = "@.SecPhone"
 dbParam_secPhone.Value = secPhone
 dbParam_secPhone.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secPhone)
 Dim dbParam_secExt As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secExt.ParameterName = "@.SecExt"
 dbParam_secExt.Value = secExt
 dbParam_secExt.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secExt)
 Dim dbParam_memEmail As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memEmail.ParameterName = "@.MemEmail"
 dbParam_memEmail.Value = memEmail
 dbParam_memEmail.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memEmail)
 Dim dbParam_memUserID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memUserID.ParameterName = "@.MemUserID"
 dbParam_memUserID.Value = memUserID
 dbParam_memUserID.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memUserID)
 Dim dbParam_memPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memPassword.ParameterName = "@.MemPassword"
 dbParam_memPassword.Value = memPassword
 dbParam_memPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memPassword)
 Dim dbParam_secretQuestion As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secretQuestion.ParameterName = "@.SecretQuestion"
 dbParam_secretQuestion.Value = secretQuestion
 dbParam_secretQuestion.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secretQuestion)
 Dim dbParam_secretAnswer As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secretAnswer.ParameterName = "@.SecretAnswer"
 dbParam_secretAnswer.Value = secretAnswer
 dbParam_secretAnswer.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secretAnswer)
 Dim dbParam_memBirthMonth As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthMonth.ParameterName = "@.MemBirthMonth"
 dbParam_memBirthMonth.Value = memBirthMonth
 dbParam_memBirthMonth.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthMonth)
 Dim dbParam_memBirthDay As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthDay.ParameterName = "@.MemBirthDay"
 dbParam_memBirthDay.Value = memBirthDay
 dbParam_memBirthDay.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthDay)
 Dim dbParam_memBirthYear As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthYear.ParameterName = "@.MemBirthYear"
 dbParam_memBirthYear.Value = memBirthYear
 dbParam_memBirthYear.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthYear)
 Dim rowsAffected As Integer = 0
 dbConnection.Open
 Try
 rowsAffected = dbCommand.ExecuteNonQuery
 Finally
 dbConnection.Close
 End Try
 Return rowsAffected
 End Function

Do u have all the fields in the table defined as "not null"?|||no. some fields allow nulls.

Registering assembly in SQL Server 2005

Hi,

I have created a C# stored procedure that has a typed dataset (XSD). When I try register that assembly in SQL Server 2005, I am getting the following error.

CREATE ASSEMBLY failed because method "add_InvoiceDTRowChanging" on type "InvoiceDTDataTable" in external_access assembly "SS.StoredProc" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. (Microsoft SQL Server, Error: 6213)

I am using SEP CTP for both VS and SQL Server. I do not have any app.config file in my project. Can anyone help me resolve this?

Thanks,
Prakash

Exactly as the error messsage says, the assembly you try to catalogue contains something that is not allowed under the permission set you try to do it under. In this case it looks to be the method add_InvoiceDTRowChanging in the class for one of the tables. If you look at that method you should see it has (probably) a HostProtectionAttribute with a property taking about synchronization. And, as the error mesage says, this is not allowed in a assembly set for EXTERNAL ACCESS. Change the permission set to be UNSAFE, and you shoudl be OK.

Notice that when you set UNSAFE you may have to mark your database as TRUSTWORTHY and assign the owner of the database UNSAFE PERMISSION rights.

Niels
|||Hi Neil,

Thanks for your insight. The problem I am having is, in the class I dont have any method that reads as add_InvoiceDTRowChanging. Also I did checked for the word 'Host' through out my solution and I dont find this word.

Thanks,
Prakash|||Hi Prakash,
I believe that in your stored procedure you have a CLR Event of some kind. The add_InvoiceDTRowChanging method is created automatically for you as part of the Event Handler. Unfortunately, the way that the CLR currently handles Event synchronization is not allowed under SAFE or EXTERNAL_ACCESS assemblies. Hopefully this will be fixed in a future release. Your assembly should work under the UNSAFE permission set.
Steven
|||

Hi Steven,

When registering with UNSAFE, I successfully added the Assembly. When I try to create a instance of the typed dataset in my c# stored procedure, I am getting the following error:

Msg 6522, Level 16, State 1, Procedure InsertInvoice, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertInvoice':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boo
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlAttributeOverrides overrides, Type[] extraTypes, XmlRootAttribute root, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlRootAttribute root)
at System.Data.Common.ObjectStorage.ConvertXmlToObject(XmlReader xmlReader, XmlRootAttribute xmlAttrib)
at System.Data.XmlDataLoader.LoadColumn(DataColumn column, Object[] foundColumns)
at System.Data.XmlDataLoader.LoadTable(DataTable table, Boolean isNested)
at System.Data.XmlDataLoader.LoadData(XmlReader reader)
at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)
...

When I tried to create a new class and just copy pasted the same code that was generated by the typed data set, I was able to create a new instance of that class. Why I cannot create instance of a typed dataset?

Thanks,
Prakash

|||This just works like a charm with the Visual Studio RTM bits

Thanks,
Prakash|||I'm glad you got it to work. As you found, Visual Studio RTM deployment takes care of this automatically for you.
However, if you're doing this outside of Visual Studio or with beta bits, then you need to generate the Xml Serialization assembly yourself using sgen. How to do this is described in the following blog posting:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
Thanks,
Steven|||Hi Steven,

I got into another trap now. When I try to create a new instance of StringReader class in C# stored proc I am getting the same error. I am passing a XML string and I am trying to use the StringReader Class to populate the typed dataset. When I use the following code, I am running into the error.

StringReader sr = new StringReader(data);

Can you please help?

Thanks,
Prakash|||Actually this turns to be a BIG issue for me now. After careful examining, I found out that its all because of Generic type in my typed dataset. I have created a AddressCollection generics of type Address. The typed dataset has a column of datatype AddressCollection.

When I load the XML file that I get as parameter from UI, it cannot understand that type and its throwing exception. But I did created the AddressCollection assembly into the SQLServer with UNSAFE permission.

Anyone has any idea of dealing TypedDataset with Generics inside SQL Server stored procedure?

Thanks,
Prakash|||

Hi Prakash,

Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or parameter for your stored proc) then it shouldn't be throwing an error. If you attach your code or send it to me ( stevehem at microsoft dot com) I'll take a look.

Steven

|||<Steven Hemingray - MSFT@.discussions.microsoft..com> wrote in message news:381fd38d-a93c-49fd-8f28-c4680a22993c@.discussions.microsoft.com... Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or Hi Steven, Can you clarify this statement? Do you mean that absolutely no use of generics is supported within SQLCLR routines? Including use of generic collections, etc? Or am I taking this too far out of context? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||

What I meant was that using generics inside SQLCLR is fine as long as they're not visible to the T-SQL side. This is the same restriction as on many .NET objects (arrays, regex, etc.) where you can use them all you want on the CLR side, but in order to interface with the T-SQL side you need to wrap them in constructs that T-SQL can understand.

In response to Prakash, I was trying to determine why the use of generics would throw a .NET serialization exception and not one of the 'generics are not supported' error messages .