Create an XML Schema for a SQL Database structure

Hey,

I was wondering does anyone know a way to get the structure, with relationships, of a Sql Database and generate a XML Schema. I want to use the Schema to build a CrystalReport.

Thanks!
-KevinCheck this artilce;

Retrieving Objects from SQL Server Using SQLXML and Serialization
http://www.15seconds.com/issue/040713.htm

create a list if user

Hi there,

Can someone please help me how to generate the list of all "user" in a database and it's access role? really need it . .

Thanks

I am guessing you are looking for the information that is accessible from the following catalog views:

· Sys.database_principals http://msdn2.microsoft.com/en-us/library/ms187328.aspx

· Sys.database_role_members http://msdn2.microsoft.com/en-us/library/ms189780.aspx

· Sys.database_permissions http://msdn2.microsoft.com/en-us/library/ms188367.aspx

I would also like to recommend the following links from BOL:

· Principals http://msdn2.microsoft.com/en-us/library/ms181127.aspx

· Permissions hierarchy http://msdn2.microsoft.com/en-us/library/ms191465.aspx

· Database-level roles http://msdn2.microsoft.com/en-us/library/ms189121.aspx

Hopefully these links will help you find the answer you are looking for.

Please let us know if you have further questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for the quick reply, is there any possbile way I can make a select stament with an out like this (ex only)

User ServerRole DBRole Permission ?

Actully this is my first task in work . . we need this fof auditing purposes . . . Pls help

Raul Garcia - MS wrote:

I am guessing you are looking for the information that is accessible from the following catalog views:

· Sys.database_principals http://msdn2.microsoft.com/en-us/library/ms187328.aspx

· Sys.database_role_members http://msdn2.microsoft.com/en-us/library/ms189780.aspx

· Sys.database_permissions http://msdn2.microsoft.com/en-us/library/ms188367.aspx

I would also like to recommend the following links from BOL:

· Principals http://msdn2.microsoft.com/en-us/library/ms181127.aspx

· Permissions hierarchy http://msdn2.microsoft.com/en-us/library/ms191465.aspx

· Database-level roles http://msdn2.microsoft.com/en-us/library/ms189121.aspx

Hopefully these links will help you find the answer you are looking for.

Please let us know if you have further questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.
sql

cr90devwin_en.exe error

Hello,

In our organization we are using a mat work a client registration software.
To generate reports there we are using the Crystal reports.
De client registration application running on 4 Citrix servers.
Last month after an abdate of the application there were problems with the reporting system.
The advice of the application liverancier was the installation of the (cr90devwin_en.exe). I have installed the exe on 3 servers without problems and the problem is resolved.
When I run the exe on the fourth server he begins with the stopping of the www, smtp and IIS manager services, and then I am getting the error message (can not create backup folder). check the attachment

Maybe you know how can i solve this problem.

Thanx in advanceSee if you find answer here

http://support.businessobjects.com/

counting problem...

Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:

SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9

This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.

now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )|||I hope I don't show up in that result set :)|||select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )
thank you! that works perfectly!

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber. What I'd like is a select query which will generate a new field which counts back the consecutive number of days that ErrorNumber \= 0. I'd like the record set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days in a row.
I've been pulling my hair out about this.
Thanks!
Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist of what its doin. But I'm no database dynamo, so I'm gonna have to work with this for a bit to see what it'll actually return.
Thanks again!
|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!
|||Thats exactly it! I gotta be able to pass a parameter for each servicedate, but I'll be able to figure that out.
Thanks - I appreciate it!

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber
. What I'd like is a select query which will generate a new field which coun
ts back the consecutive number of days that ErrorNumber \= 0. I'd like the r
ecord set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days
in a row.
I've been pulling my hair out about this.
Thanks!Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist
of what its doin. But I'm no database dynamo, so I'm gonna have to work with
this for a bit to see what it'll actually return.
Thanks again!|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!|||Thats exactly it! I gotta be able to pass a parameter for each servicedate,
but I'll be able to figure that out.
Thanks - I appreciate it!