"View Report" button.

Would someone be able to tell me if this is a bug or not. When I have two
parameters (both text boxes) and change the first one and click on the "View
Report" button to refresh the data with the new parameter values it blanks
the report out and I do not see "processing report" like I normally do. On
the second click it processes the report.
When I change a parameter in the second text box and click "View Report", it
processes the first click.
Seems like a bug of sorts. Anyone encounter this before? I'm more than
happy to provide the RDL.
Best Regards,
Benjamin PierceThis sometimes happens if parameter 2 has values based on a query and the
value of that query is dependent on the value of parameter 1 (hierarchical
parameters).
What happens is that when you choose a value for P1, we try to go back and
get values for P2. Now before we get those values the user clicks view
report. Since you've requested a to view a report with only one parameter
specified, we send back a page asking for the second parameter value.
However due to browser refresh, it doesn't look like anything happened. Now
you provide a value for parameter 2, you get the report rendered.
You can make this stop happening by providing a default value for parameter
2.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:ujptP0AcEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Would someone be able to tell me if this is a bug or not. When I have two
> parameters (both text boxes) and change the first one and click on the
> "View
> Report" button to refresh the data with the new parameter values it blanks
> the report out and I do not see "processing report" like I normally do.
> On
> the second click it processes the report.
> When I change a parameter in the second text box and click "View Report",
> it
> processes the first click.
> Seems like a bug of sorts. Anyone encounter this before? I'm more than
> happy to provide the RDL.
>
> Benjamin Pierce
>

"Tree View" with a SQL Server Database

We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?Simplist is an adjacency list. Store the parent ID along with each record,
also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the
tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you
want to order your nodes in an arbitrary way.

ALTER FUNCTION dbo.func_Get_Padded_Number8
(
@.number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @.Value VARCHAR(8)
DECLARE @.Length INTEGER

/*
Convert the number and get its string length
*/

SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
SET @.Length = LEN ( @.Value )

/*
If the length is less than 8, pad it
*/
IF LEN ( @.Value ) < 8
BEGIN
SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) + @.Value
END

RETURN @.Value
END

Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building
the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done
easily like this:

SELECT dbo.Adjacency.ID, (a unique ID)
dbo.Adjacency.ID_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.ID_Index, (the sibling number of this child)
LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.Path

Hope this helps some.

<imani_technology_spam@.yahoo.com> wrote in message
news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> We need to present hierarchical data on a web page, the same way the
> tree view shows files in Windows Explorer. Here's the catch: that
> tree view needs to be bound to a SQL Server database. How can this be
> done?|||This helps a lot. Thanks. I wonder if there is a practical graphical
solution in addition to the text-based solution?

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bqn6dt$o7l$1$830fa7b3@.news.demon.co.uk>...
> Simplist is an adjacency list. Store the parent ID along with each record,
> also store the "full path" in a text string, such as 0001/0004/0007/0002
> (the second node of the seventh node of the 4th node of the 1st node of the
> tree). I use a function to generate a number for the string (below)
> because each "path node" has to be the same length in order to perform a
> correct "select" of the tree order. You also need a "sibling number" if you
> want to order your nodes in an arbitrary way.
> ALTER FUNCTION dbo.func_Get_Padded_Number8
> (
> @.number INTEGER
> )
> RETURNS VARCHAR(8)
> AS
> BEGIN
> DECLARE @.Value VARCHAR(8)
> DECLARE @.Length INTEGER
> /*
> Convert the number and get its string length
> */
> SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
> SET @.Length = LEN ( @.Value )
> /*
> If the length is less than 8, pad it
> */
> IF LEN ( @.Value ) < 8
> BEGIN
> SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) + @.Value
> END
> RETURN @.Value
> END
> Adding a new node is simple given the parent, you just find the highest
> sibling number of the parent and increment it then insert a node, building
> the path string by taking the parent and adding "/00n" (where n is the
> sibling number). Listing all of the nodes in the tree structure can be done
> easily like this:
> SELECT dbo.Adjacency.ID, (a unique ID)
> dbo.Adjacency.ID_Parent, (the unique ID of the parent of
> this node)
> dbo.Adjacency.ID_Index, (the sibling number of this child)
> LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
> node, useful for building your tree structure afterwards)
> FROM dbo.Adjacency
> ORDER BY dbo.Adjacency.Path
> Hope this helps some.
> <imani_technology_spam@.yahoo.com> wrote in message
> news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> > We need to present hierarchical data on a web page, the same way the
> > tree view shows files in Windows Explorer. Here's the catch: that
> > tree view needs to be bound to a SQL Server database. How can this be
> > done?|||Hi

I posted this a short time ago!
http://tinyurl.com/xw5s

John

<imani_technology_spam@.yahoo.com> wrote in message
news:8be6e8.0312050907.644b5abe@.posting.google.com ...
> This helps a lot. Thanks. I wonder if there is a practical graphical
> solution in addition to the text-based solution?
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bqn6dt$o7l$1$830fa7b3@.news.demon.co.uk>...
> > Simplist is an adjacency list. Store the parent ID along with each
record,
> > also store the "full path" in a text string, such as 0001/0004/0007/0002
> > (the second node of the seventh node of the 4th node of the 1st node of
the
> > tree). I use a function to generate a number for the string (below)
> > because each "path node" has to be the same length in order to perform a
> > correct "select" of the tree order. You also need a "sibling number" if
you
> > want to order your nodes in an arbitrary way.
> > ALTER FUNCTION dbo.func_Get_Padded_Number8
> > (
> > @.number INTEGER
> > )
> > RETURNS VARCHAR(8)
> > AS
> > BEGIN
> > DECLARE @.Value VARCHAR(8)
> > DECLARE @.Length INTEGER
> > /*
> > Convert the number and get its string length
> > */
> > SET @.Value = CONVERT ( VARCHAR ( 8 ), @.number )
> > SET @.Length = LEN ( @.Value )
> > /*
> > If the length is less than 8, pad it
> > */
> > IF LEN ( @.Value ) < 8
> > BEGIN
> > SET @.Value = REPLACE ( SPACE ( 8 - @.Length ), ' ', '0' ) +
@.Value
> > END
> > RETURN @.Value
> > END
> > Adding a new node is simple given the parent, you just find the highest
> > sibling number of the parent and increment it then insert a node,
building
> > the path string by taking the parent and adding "/00n" (where n is the
> > sibling number). Listing all of the nodes in the tree structure can be
done
> > easily like this:
> > SELECT dbo.Adjacency.ID, (a unique ID)
> > dbo.Adjacency.ID_Parent, (the unique ID of the parent
of
> > this node)
> > dbo.Adjacency.ID_Index, (the sibling number of this
child)
> > LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of
the
> > node, useful for building your tree structure afterwards)
> > FROM dbo.Adjacency
> > ORDER BY dbo.Adjacency.Path
> > Hope this helps some.
> > <imani_technology_spam@.yahoo.com> wrote in message
> > news:8be6e8.0312030710.3b40bc89@.posting.google.com ...
> > > We need to present hierarchical data on a web page, the same way the
> > > tree view shows files in Windows Explorer. Here's the catch: that
> > > tree view needs to be bound to a SQL Server database. How can this be
> > > done?

"sql data source" updating

hello all,

I ran into a problem using the "sql data source updating " method. i'm using a form view with about 20 parameters that are bind to controls in the form view and 5 other parameters that i'm setting the value to in the "sql data source updating" method. Every thing updates find execept for the last 5 parameters that i'm setting the values to in the "sql data source updating" method. My store procedure(sp) updates and insert mulitple tables. For some UNKOWN reason the tables that uses the parameteres in the "sql data source updating " are inserting multple records when only 1 record should be inserted. Have anyone have this problem?

Hi,

Please check if the insert command in the stored procedure has been called several times. Also, is there any triggers fired after the insertion?

If that still doesn't work, please let us see the stored procedure and code for Updating event handler.

Also, if you're inserting a record into the database, I suppose you need to handle the SqlDataSource.Inserting event.

"Length" in table design view

I haven't been able to find an answer to this simple question from any of the
online documentation. In table design view, each one of my columns has a
datatype and length. Specifically, I have a column of dataype "ntext" with a
length of 16, but I don't know what that "16" means. 16 Bytes? How can I
determine the appropriate length to set it to, based on how many characters I
expect the field to hold?
Is there anywhere that explains in simple terms what each datatype is and
what the "length" means for each individual dataype? Thanks!
When text and image datatypes are stored in SQL Server, data page stores 16
byte pointer is stored that points to pages that stores the text\image data.

> How can I
> determine the appropriate length to set it to, based on how many
characters I
> expect the field to hold?
ntext datatype can store upto 1073741823 characters.
See more help on this in books online.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

"IF"s in a view?

I Access SQL one can imbed "IF" statements in a select query (or view).
In SQL Server, Microsoft seems to only allow the use of control statements such as IF and CASE in Stored Procedures but not in a view.

Question: Is there any way to use "IF" or "CASE" in a view? If a stored procdure is the best route, how do i access the results set afterwards?Case works fine - please post the view you tried to create.|||Here is the SQL fragment converted from ACCESS IIF's to SQL Server CASE. I don't have access to the SQL Server at the moment but I will try out your suggestion first chance I get.

CASE
WHEN [IncidentHeaderDynamicsCustNmbr] Like '99900%' THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
WHEN IsNull(IncidentHeaderSiteID])=True And IsNull(IncidentHeaderClientAddressID])=True) THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
ELSE
Trim([IncidentHeaderSiteCustName]))) AS InvoiceHeaderSiteCustName,
Trim([IncidentHeaderSiteCntcPrsn]))) AS InvoiceHeaderSiteCntcPrsn,
Trim([IncidentHeaderSiteStmtName]))) AS InvoiceHeaderSiteStmtName,
Trim([IncidentHeaderSiteAddress1]))) AS InvoiceHeaderSiteAddress1,
Trim([IncidentHeaderSiteAddress2]))) AS InvoiceHeaderSiteAddress2,
Trim([IncidentHeaderSiteCity]))) AS InvoiceHeaderSiteCity,
Trim([IncidentHeaderSiteState]))) AS InvoiceHeaderSiteState,
Trim([IncidentHeaderSiteCountry]))) AS InvoiceHeaderSiteCountry,
Trim([IncidentHeaderSiteZip]))) AS InvoiceHeaderSiteZip,
Trim([IncidentHeaderSitePhone1]))) AS InvoiceHeaderSitePhone1,
Trim([IncidentHeaderSitePhone2]))) AS InvoiceHeaderSitePhone2,
Trim([IncidentHeaderSiteFax]))) AS InvoiceHeaderSiteFax,
END|||The problem looks like with your syntax (check out BOL) - the case statement goes as follows:

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

or

CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Anyway, you have case when - but you are missing the end statement - Maybe you are thinking you can return multiple results - You will have to test for each case.

"cannot resolve collation conflict for equal to operation" when running view

Get the message "cannot resolve collation conflict for equal to
operation" in a view when a have restored a database on an other
computer. In this view I compare fields from myDB and master. Have
changed so I have the same collation for all my databases om SQL
Server, but still get this problem. Anyone has a suggestion?
//di6pejo
Have you checked that the columns you are comparing actually have the same
collation. Changing the collation of the database does _not_ change the
collation of the columns that exist already in the database, it only
provides a default collation for newly created columns.
Jacco Schalkwijk
SQL Server MVP
"Peter" <ingeskrap@.tjohoo.se> wrote in message
news:3aa71bd7.0407140151.4b78d2d0@.posting.google.c om...
> Get the message "cannot resolve collation conflict for equal to
> operation" in a view when a have restored a database on an other
> computer. In this view I compare fields from myDB and master. Have
> changed so I have the same collation for all my databases om SQL
> Server, but still get this problem. Anyone has a suggestion?
> //di6pejo
|||Big thanks, seem like it worked to change one of the fields is a table.
One questions though, in the setting "SQL_LAtin1_General_CP1_CI_AS" What
does "CP1" stands for?
//Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||CP1 stands for codepage 1, which isn't actually codepage 1, but codepage
1252. It is the ANSI standard codepage (loosely translated as character
set), and is used by English and most other Western European languages. For
more information look up "code page" in BOL.
Jacco Schalkwijk
SQL Server MVP
"Pejo" <ingeskrap@.tjohoo.se> wrote in message
news:OuFu3TZaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Big thanks, seem like it worked to change one of the fields is a table.
> One questions though, in the setting "SQL_LAtin1_General_CP1_CI_AS" What
> does "CP1" stands for?
> //Peter
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

"cannot resolve collation conflict for equal to operation" when running view

Get the message "cannot resolve collation conflict for equal to
operation" in a view when a have restored a database on an other
computer. In this view I compare fields from myDB and master. Have
changed so I have the same collation for all my databases om SQL
Server, but still get this problem. Anyone has a suggestion?
//di6pejoHave you checked that the columns you are comparing actually have the same
collation. Changing the collation of the database does _not_ change the
collation of the columns that exist already in the database, it only
provides a default collation for newly created columns.
Jacco Schalkwijk
SQL Server MVP
"Peter" <ingeskrap@.tjohoo.se> wrote in message
news:3aa71bd7.0407140151.4b78d2d0@.posting.google.com...
> Get the message "cannot resolve collation conflict for equal to
> operation" in a view when a have restored a database on an other
> computer. In this view I compare fields from myDB and master. Have
> changed so I have the same collation for all my databases om SQL
> Server, but still get this problem. Anyone has a suggestion?
> //di6pejo|||Big thanks, seem like it worked to change one of the fields is a table.
One questions though, in the setting "SQL_LAtin1_General_CP1_CI_AS" What
does "CP1" stands for'
//Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||CP1 stands for codepage 1, which isn't actually codepage 1, but codepage
1252. It is the ANSI standard codepage (loosely translated as character
set), and is used by English and most other Western European languages. For
more information look up "code page" in BOL.
Jacco Schalkwijk
SQL Server MVP
"Pejo" <ingeskrap@.tjohoo.se> wrote in message
news:OuFu3TZaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Big thanks, seem like it worked to change one of the fields is a table.
> One questions though, in the setting "SQL_LAtin1_General_CP1_CI_AS" What
> does "CP1" stands for'
> //Peter
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

"cannot resolve collation conflict for equal to operation" when running view

Get the message "cannot resolve collation conflict for equal to
operation" in a view when a have restored a database on an other
computer. In this view I compare fields from myDB and master. Have
changed so I have the same collation for all my databases om SQL
Server, but still get this problem. Anyone has a suggestion?
//di6pejoHave you checked that the columns you are comparing actually have the same
collation. Changing the collation of the database does _not_ change the
collation of the columns that exist already in the database, it only
provides a default collation for newly created columns.
--
Jacco Schalkwijk
SQL Server MVP
"Peter" <ingeskrap@.tjohoo.se> wrote in message
news:3aa71bd7.0407140151.4b78d2d0@.posting.google.com...
> Get the message "cannot resolve collation conflict for equal to
> operation" in a view when a have restored a database on an other
> computer. In this view I compare fields from myDB and master. Have
> changed so I have the same collation for all my databases om SQL
> Server, but still get this problem. Anyone has a suggestion?
> //di6pejo

Something seriously wrong

We have one database that is actually quite small, about 1.8GB. Upon moving
this db to SQL2K5 we get an error everytime we attempt to view the "Views"
folder. The error is:
Value cannot be null.
Parameter name: context (ObjectExplorer)
No more detail is available. I've gone back to the source database, removed
all views and repeated the process. Even with no views in the database we
get the same error when clicking on the "Views" folder in SQL Managment
Studio. All other db's have migrated fine.
NOW I figured I'd use the compare utility we have (We use Red Gates SQL
Compare) to compare the schema from that database to another server with an
old copy. This worked a few weeks ago, now it gives the error "Specified
cast is invalid" which sounds very likely to be related. This happens on
all servers running a copy of this database regardless of SQL Server
version. So at some point something bad was introduced. I'm just not sure
where to look, it sounds metadata related possibly but I'd hope there's some
other way of determining this other than going to a 6 week old backup and
hoping the issue isn't there.
Any help is greeted with humble thanks...we're really stuck.Tim,
Did you run the Upgrade Advisor on the DB before the move to 2005? This
might tell you something that does not migrate.
Chris Wood
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uLgna8WaGHA.504@.TK2MSFTNGP03.phx.gbl...
> We have one database that is actually quite small, about 1.8GB. Upon
> moving this db to SQL2K5 we get an error everytime we attempt to view the
> "Views" folder. The error is:
> Value cannot be null.
> Parameter name: context (ObjectExplorer)
> No more detail is available. I've gone back to the source database,
> removed all views and repeated the process. Even with no views in the
> database we get the same error when clicking on the "Views" folder in SQL
> Managment Studio. All other db's have migrated fine.
> NOW I figured I'd use the compare utility we have (We use Red Gates SQL
> Compare) to compare the schema from that database to another server with
> an old copy. This worked a few weeks ago, now it gives the error
> "Specified cast is invalid" which sounds very likely to be related. This
> happens on all servers running a copy of this database regardless of SQL
> Server version. So at some point something bad was introduced. I'm just
> not sure where to look, it sounds metadata related possibly but I'd hope
> there's some other way of determining this other than going to a 6 week
> old backup and hoping the issue isn't there.
> Any help is greeted with humble thanks...we're really stuck.
>|||Yes I did. It only flagged a couple of sprocs with a syntax change (using
aliases in the order by clause)....
I might not have been clear. We encountered the issue during migration, BUT
then going back to our SQL 2000 servers currently in production this error
was encountered when using a specific tool. So, the issue is present BEFORE
migration.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eZTYegXaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> Tim,
> Did you run the Upgrade Advisor on the DB before the move to 2005? This
> might tell you something that does not migrate.
> Chris Wood
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uLgna8WaGHA.504@.TK2MSFTNGP03.phx.gbl...
>> We have one database that is actually quite small, about 1.8GB. Upon
>> moving this db to SQL2K5 we get an error everytime we attempt to view the
>> "Views" folder. The error is:
>> Value cannot be null.
>> Parameter name: context (ObjectExplorer)
>> No more detail is available. I've gone back to the source database,
>> removed all views and repeated the process. Even with no views in the
>> database we get the same error when clicking on the "Views" folder in SQL
>> Managment Studio. All other db's have migrated fine.
>> NOW I figured I'd use the compare utility we have (We use Red Gates SQL
>> Compare) to compare the schema from that database to another server with
>> an old copy. This worked a few weeks ago, now it gives the error
>> "Specified cast is invalid" which sounds very likely to be related. This
>> happens on all servers running a copy of this database regardless of SQL
>> Server version. So at some point something bad was introduced. I'm just
>> not sure where to look, it sounds metadata related possibly but I'd hope
>> there's some other way of determining this other than going to a 6 week
>> old backup and hoping the issue isn't there.
>> Any help is greeted with humble thanks...we're really stuck.
>