Problem with referencing System.Data.SqlServerCE.dll

Hello

I have referrenced System.Data.SqlServerCE.dll to my web project in order to create a .sdf file. I haven't wrote any code,just referenced it. When I build the project I get this error:

Error 2 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' cannot be loaded.

It sounds like you dont have the client dlls installed.

If you already installed the SDK you need to install it from C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop

if you dont have the SDK, I suggest you to install it, another option would be to download directly another redist.

|||

I have had problems too, related to incorrect referencing. There is a version in the public assemblies folder which causes this error.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\System.Data.SqlServerCe.dll

This specific file/version is needed by the data designer in visual studio so dont delete it, just change any references in your projects, for System.Data.SqlServerCe.dll, to point to the correct file.

Usually

C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\System.Data.SqlServerCe.dll

OR

C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\System.Data.SqlServerCe.dll

Cheers

Anton

problem with recursive cte query

I am running into 2 problems with this cte query. First off, it's not returning the results in the right order. What I mean is that, as you can see from the graph, the first row (tabid=4) should be followed by rows 3,4 and 5 but it's throwing in a row between. No matter how I try and order the results, it's not working as it should. The tabindex field is a user defined field as to what order the tabs should show up for the user so I have to order by that field at some point.

ParentTabId TabId Title Link TabIndex14Personal Info/Employee/employeeAdmin.aspx115Employment Info/Employee/positionInfo.aspx248Dependents/Employee/dependents.aspx34169Emergency Contacts/Employee/Contacts.aspx24170Demographics/Employee/EmployeeAdmin.aspx11172Employment Files/Employee/empFiles.aspx3172210New Hire Paperwork/Employee/empFiles.aspx1172211Form I-9/Employee/I9.aspx2172212General/Employee/employment.aspx3172213Notes/Employee/notes.aspx4172214Pre-Employment/Employee/preemployment.aspx5172217Protected Health Information/Employee/protectedhealth.aspx61220View All/Employee/viewAll.aspx4

Problem 2 is that I need to format all of this as heirarchal xml but when I output it as xml in the query itself, every element is coming through as MainTabs (the cte name) instead of nesting rows 3,4 & 5 under tab id 4. So, how do I (if it's even possible) fix these two things? If I can't do this in a query, how would you suggest getting an xml formatted result set from a table structure like this? Structure meaning parenttabid = tabid of another row ordered by the tabid and then tabindex? Thanks.

with MainTabs(ParentTabId, TabId, Title, Link, tabindex) as
(
select ParentTabId, TabId, Title, Link, tabindex from Tabs where parenttabid = 1

UNION ALL

select t.ParentTabId, t.TabId, t.Title, t.Link, t.tabindex from Tabs t inner join
MainTabs on MainTabs.TabId = t.ParentTabId
)
select ParentTabId, TabId, Title, Link, tabindex from MainTabs order by tabid, tabindex

You can generate hierarchical XML using this

CREATE FUNCTION dbo.SubTree(@.TabId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE ParentTabId=@.TabId
ORDER BY TabId
FOR XML PATH('Tabs'),TYPE)
END

GO

SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE parenttabid = 1
ORDER BY TabId
FOR XML PATH('Tabs') , ROOT('MyRoot'), TYPE

|||Wonderful, thank you so much!!! Now, if I can just figure out what it's all doing:)|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate option?

problem with recursive cte query

I am running into 2 problems with this cte query. First off, it's not returning the results in the right order. What I mean is that, as you can see from the graph, the first row (tabid=4) should be followed by rows 3,4 and 5 but it's throwing in a row between. No matter how I try and order the results, it's not working as it should. The tabindex field is a user defined field as to what order the tabs should show up for the user so I have to order by that field at some point.

ParentTabId TabId Title Link TabIndex14Personal Info/Employee/employeeAdmin.aspx115Employment Info/Employee/positionInfo.aspx248Dependents/Employee/dependents.aspx34169Emergency Contacts/Employee/Contacts.aspx24170Demographics/Employee/EmployeeAdmin.aspx11172Employment Files/Employee/empFiles.aspx3172210New Hire Paperwork/Employee/empFiles.aspx1172211Form I-9/Employee/I9.aspx2172212General/Employee/employment.aspx3172213Notes/Employee/notes.aspx4172214Pre-Employment/Employee/preemployment.aspx5172217Protected Health Information/Employee/protectedhealth.aspx61220View All/Employee/viewAll.aspx4

Problem 2 is that I need to format all of this as heirarchal xml but when I output it as xml in the query itself, every element is coming through as MainTabs (the cte name) instead of nesting rows 3,4 & 5 under tab id 4. So, how do I (if it's even possible) fix these two things? If I can't do this in a query, how would you suggest getting an xml formatted result set from a table structure like this? Structure meaning parenttabid = tabid of another row ordered by the tabid and then tabindex? Thanks.

with MainTabs(ParentTabId, TabId, Title, Link, tabindex) as
(
select ParentTabId, TabId, Title, Link, tabindex from Tabs where parenttabid = 1

UNION ALL

select t.ParentTabId, t.TabId, t.Title, t.Link, t.tabindex from Tabs t inner join
MainTabs on MainTabs.TabId = t.ParentTabId
)
select ParentTabId, TabId, Title, Link, tabindex from MainTabs order by tabid, tabindex

You can generate hierarchical XML using this

CREATE FUNCTION dbo.SubTree(@.TabId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE ParentTabId=@.TabId
ORDER BY TabId
FOR XML PATH('Tabs'),TYPE)
END

GO

SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE parenttabid = 1
ORDER BY TabId
FOR XML PATH('Tabs') , ROOT('MyRoot'), TYPE

|||Wonderful, thank you so much!!! Now, if I can just figure out what it's all doing:)
|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate option?

Problem with Printing of Crystal Report

Hello

I have an application which uses crystal reports.
Only the runtime dll's are shipped with the application in order to design and also print.

I'm unable to print the report .I guess some mandatory runtime dll's are missed
to ship with the application.

Can anyone help me out in listing all the runtime dll's used for crystal report printing? I use Crystal report 9.0 version dll's.

If I install crystal reportDesigner, then the print option works.

Regards
PallCrystal Reports 8.5 comes with a file called runtime.hlp that lists all the runtime files needed and (I believe) their dependencies. Maybe version 9 has something similar?

I use InstallShield to create my Setups. There's a download for InstallShield that will add all the MergeModules for Crystal Reports. (A MergeModule is a collection of system files and their dependecies.) You just check off the ones you need and it takes care of installing all the dependency files.|||Open your crystal reports in EDIT mode and open the printer / printing options. Uncheck the NoPrint option.

This problem is with CR 8.0.

problem with orphaned "dbo" user of an attached database

Hi everybody,
I'm in a situation where I allow my users of SQL Server the creation of her
own databases. This is been done in order to allow the "creator" (the owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the login
that
performed the operation, which is fine. But, the "dbo" user in the attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER" permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE" permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
regarding signature?
Thanks,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> I know that this can be fixed with ALTER AUTHORIZATION ON
> DATABASE::universe to [login] So, after attaching a database, the user
> can simply all ALTER > AUTHORIZATION. My problem is that ALTER
> AUTORIZATION requires "CONTROL SERVER" permission, which is simply a
> synonym for "sysadmin" role membership and therefore not what I want.
> All I want my users grant is "CREATE ANY DATABASE" permission. Does
> anybody know a solution besides doing the CREATE DATABASE ... FOR ATTACH
> with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
> regarding signature?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Holger
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> Hi everybody,
> I'm in a situation where I allow my users of SQL Server the creation of
> her
> own databases. This is been done in order to allow the "creator" (the
> owner)
> full acces to the created DB. Everything works fine, as long as the DB is
> created from scratch, like:
> CREATE DATABASE universe
> But additionally, my users are also exchanging databases, so they will
> also
> use
> CREATE DATABASE universe ... FOR ATTACH
> When attaching a database, the owner of the so created database is the
> login
> that
> performed the operation, which is fine. But, the "dbo" user in the
> attached
> database is still associated with the original login (before detaching the
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
> this can be fixed with
> ALTER AUTHORIZATION ON DATABASE::universe to [login]
> So, after attaching a database, the user can simply all ALTER
> AUTHORIZATION.
> My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
> permission,
> which is simply a synonym for "sysadmin" role membership and therefore not
> what I want. All I want my users grant is "CREATE ANY DATABASE"
> permission.
> Does anybody know a solution besides doing the CREATE DATABASE ... FOR
> ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
> a
> regarding signature?
> Thanks,
> Holger|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

problem with ordering by rank

I have a query that is supposed to order the results by rank. When I actually get the results though, it doesn't seem like the rank makes sense. In the result set that I just got, I got 6 records where I did a keyword search on 'sales'. Here's what I got. The word sales was mentioned ...

1. 7 times
2. 2 times
3. 3 times
4. 4 times
5. 3 times
6. 1 time

As a casual user of the system, this ranking wouldn't seem logical to me because I would expect it to have the listings with the most occurances of 'sales' higher in the list. Can someone explain why it works this way and is there a flag or something that I need to set to get it to work more along the lines of what we're expecting? Thanks.

? I assume you're referring to FTS? Unfortunately, there's not a lot you can do to control it. If you want more info about the algorithms it uses for ranking, see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005ftsearch.asp -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <gswartz@.discussions.microsoft.com> wrote in message news:bf865951-9156-4718-8184-64968a57e0f8@.discussions.microsoft.com... I have a query that is supposed to order the results by rank. When I actually get the results though, it doesn't seem like the rank makes sense. In the result set that I just got, I got 6 records where I did a keyword search on 'sales'. Here's what I got. The word sales was mentioned ... 1. 7 times2. 2 times3. 3 times4. 4 times5. 3 times6. 1 time As a casual user of the system, this ranking wouldn't seem logical to me because I would expect it to have the listings with the most occurances of 'sales' higher in the list. Can someone explain why it works this way and is there a flag or something that I need to set to get it to work more along the lines of what we're expecting? Thanks.