Return Available appointments

I am upgrading an application that another developer wrote. Basically, it i
s
an application used to display appointments / request appointments. The new
request is to display available appointments. That is where I need help in
figuring out how to return available time slots.
Here is what the table looks like(Again, I did not write it or develop it so
far)
CREATE TABLE [dbo].[Meetings] (
[appt_ID] [int] IDENTITY (1, 1) NOT NULL ,
[appt_StartDT] [datetime] NOT NULL ,
[appt_RequesterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[appt_RequestEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[appt_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appt_Room] [int] NOT NULL ,
[appt_EndDT] [datetime] NOT NULL ,
[appt_State] [int] NOT NULL ,
[appt_attendees] [int] NULL
) ON [PRIMARY]
Some sample data with column names next to the values
apptID - 1177
appt_StartDt - 11/21/2003 8:00:00 AM
appt_requesterName - User Name
appt_requesterEmail- Email@.company.com
appt_Name - Acme Electric
appt_Room - 11
appt_EndDT - 11/21/2003 12:00:00 PM
appt_State - 1
appt_attendees - 8
Currently, I display all the appointments on the web page reading the
database. With the new change, I would like to be able to see the available
appointments
so users could enter in a date range or appt_room to see what is available
and then make appointments. My problem is the query to return the avaiable
appointments.
Please let me know if you have any questions.
TIA.Hi,
Welcome to use MSDN Managed Newsgroup!
Would you please give me a expected data row for "avaiable appointments"?
How to define this? For example, given a date range, list all the meetings
in this data range?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for the response. Yes, you are right, for a given range of days,
list all available appointments.
Here is more information.
The user would have the choice to enter day/ range of days to check for
available appointments. So if the user would want to make an appointment
2
ws from now, then the user would select that w and the result would be
all available appointments in that w. Appointments can be a full hour, 1
5
mins or a complete day.
For example:
Available appointments for 11/30/2005 are
1. Room1 9:30 AM to 10:30 PM
2. Room 1 1:45 PM to 2:15 PM
3. Room 2 9:00 AM to 10:00 AM
If the user selected a range(11/30/2005 - 12/1/2005) , then
Available appointments for 11/30/2005 are:
1. Room 1 time
2. Room 3 time
3. Room 4 time
Avaliable appointments for 12/1/2005 are:
1. Room 1 time
Hope this is helpful. Please let me know if you need more information or
have any questions.
TIA.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Welcome to use MSDN Managed Newsgroup!
> Would you please give me a expected data row for "avaiable appointments"?
> How to define this? For example, given a date range, list all the meetings
> in this data range?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
Thanks for your clarification.
What's your concern? Below is a sample about how to select all available
for two ws later (2005-11-28 ~ 2005-12-4)
SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
FROM Meetings
WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
--@.P1, @.P2 indicate the inputing data ranges
I guess the key point might be you will have to make a "sub table" for each
date. If I have misunderstood your concern, please feel free to point it
out.
With T-SQL statements only, I am afraid it is not possible to list by date
like below
Available appointments for 11/30/2005 are
..
Avaliable appointments for 12/1/2005 are:
..
To accomplish this, you should impliment this with .NET in your asp.net
pages or winform.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
The SQL below will return the appt_room, start and end dates from the
meetings table whether or not if the appointment is available.
I am expecting the result to be only of the available appointments and
then have a break down by day.
Could you please clarify on the sub table for each date thing that you
mentioned.
Hope this is been clear.
Thanks.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Thanks for your clarification.
> What's your concern? Below is a sample about how to select all available
> for two ws later (2005-11-28 ~ 2005-12-4)
> SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
> FROM Meetings
> WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
> --@.P1, @.P2 indicate the inputing data ranges
> I guess the key point might be you will have to make a "sub table" for eac
h
> date. If I have misunderstood your concern, please feel free to point it
> out.
> With T-SQL statements only, I am afraid it is not possible to list by date
> like below
> Available appointments for 11/30/2005 are
> ...
> Avaliable appointments for 12/1/2005 are:
> ...
> To accomplish this, you should impliment this with .NET in your asp.net
> pages or winform.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
I am sorry that I get .
Could you provide the the sample data and the expected results? I am not
sure for what situation the the appointment will be not available.
If you want to list the appointments day by day, you will have to implement
this with business logic instead of in T-SQL only.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Return a UNIQUEIDENTIFIER

Hi,

I am writing a C# application that uses a SQL server database to hold its data. I need to create a stored procedure that returns a particular row's primary key value. This is no problem if the primary key is an INT. But my primary key is a unique identifier, and the stored procedure doesn't want to let me return any values that aren't INTs. Can someone please tell me how to get around this?

Thanks in advance.

ScottYou'll have to declare it as an outparameter.
And if you want something easier to handle you can convert it to
a varchar using CONVERT(myguid,VARCHAR)

Regards
Fredr!k|||Fredrik2000,

Thank you so much. That is exactly what I needed. Also, for anyone else out there, it is actually in the format:

CONVERT(VARCHAR(36), myguid)

where of course 36 is the number of characters allocated for the datatype.

Sc0tt|||Ahh, I always get the order mixed up (didn't have a copy of books online at the computer
I'm posting from...)

Nice to hear you got it working.

Regards
Fredr!k

Retrieving the RDL from SQL Server

I just came across a query by the same name as this one, but I have a
slightly different purpose in mind. I have an application which builds
filterable and hideable columns on the fly, but it currently requires a
copy of the RDL to be kept outside of the Report Server, some place
accessible. Obviously, this makes things more difficult, since it means
that the external reports always have to match the uploaded reports. Is
there any way to extract the RDL from the report server so that these
columns can be built on the fly?
Thank you,
NoahIn your ReportServer database. Table Catalog and Content Field.
Thanks
Jerry
http://www.rdlcomponents.com
"Noah" wrote:
> I just came across a query by the same name as this one, but I have a
> slightly different purpose in mind. I have an application which builds
> filterable and hideable columns on the fly, but it currently requires a
> copy of the RDL to be kept outside of the Report Server, some place
> accessible. Obviously, this makes things more difficult, since it means
> that the external reports always have to match the uploaded reports. Is
> there any way to extract the RDL from the report server so that these
> columns can be built on the fly?
> Thank you,
> Noah
>|||Thanks.
Is there any easy way to read that data? It's in the database as an
image, so I cannot read the XML. Can I access it through the web service?
Thanks,
Noah
Jerry wrote:
> In your ReportServer database. Table Catalog and Content Field.
> Thanks
> Jerry
> http://www.rdlcomponents.com
> "Noah" wrote:
>
>>I just came across a query by the same name as this one, but I have a
>>slightly different purpose in mind. I have an application which builds
>>filterable and hideable columns on the fly, but it currently requires a
>>copy of the RDL to be kept outside of the Report Server, some place
>>accessible. Obviously, this makes things more difficult, since it means
>>that the external reports always have to match the uploaded reports. Is
>>there any way to extract the RDL from the report server so that these
>>columns can be built on the fly?
>>Thank you,
>>Noah|||You have to read that data as Byte[] and the convert to string, something like:
System.Text.Encoding enc = System.Text.Encoding.ASCII;
string myRDL = enc.GetString(myByteArray );
Thanks
Jerry
http://www.rdlcomponents.com
"Noah" wrote:
> Thanks.
> Is there any easy way to read that data? It's in the database as an
> image, so I cannot read the XML. Can I access it through the web service?
> Thanks,
> Noah
> Jerry wrote:
> > In your ReportServer database. Table Catalog and Content Field.
> >
> > Thanks
> > Jerry
> > http://www.rdlcomponents.com
> >
> > "Noah" wrote:
> >
> >
> >>I just came across a query by the same name as this one, but I have a
> >>slightly different purpose in mind. I have an application which builds
> >>filterable and hideable columns on the fly, but it currently requires a
> >>copy of the RDL to be kept outside of the Report Server, some place
> >>accessible. Obviously, this makes things more difficult, since it means
> >>that the external reports always have to match the uploaded reports. Is
> >>there any way to extract the RDL from the report server so that these
> >>columns can be built on the fly?
> >>
> >>Thank you,
> >>
> >>Noah
> >>
>

retrieving SQL Server roles and permissions

Hi,

I am developping an application using Windows forms(C#.net) and SQL Server 2005 Express edition. I would like to use SQL Server authentication. This is what I would like my application to do:

When a user logs in and is authenticated by SQL Server, the application to be able read the user's permissions/rights from SQL Server and use them to restrict to access what the user can do in the application. I have gone through lots of articles but all articles talk either of security in the .net environment or SQL Server security. None talks about integrating database security with application security.

Any leads will be appreciated.

Jakiiki

Hi Jakiiki,

Applications connect to SQL Server either via SQL Authentication or Windows Authentication. Once Autheticated the Applications identity in SQL Server is determined and access to resources is determined by the permissions the identity possess.

You can retrieve permission and role information about a user from sql server's catalog views.

sys.server_permissions and sys.database_permissions will list out all of the permissions granted to sql logins and sql user respectively.

sys.server_principals and sys.database_principals will list the sql logins for the instance and the sql users for the current database.

database and server roles will be listed in sys.database_principals and sys.server_principals

membership in these roles is tracked in the catalog view sys.database_role_members and sys.server_role_members

HTH,

-Steven Gott

SDE/T

SQL Server

sql

Retrieving Database Names via C#

Hello,
I am trying to develop a desktop application by using C#.Net. I am working with .NET Framewrok 2.0.
I need to list the database names which are taking place in Anlaysis Services.
Actually, my application will work on the OLAP Cube which is going to be chosen by the user.
In order to do this, I have to retrieve the Cube names so the user can choose what cube he/she wants to work with!
any help appritiated.

thanks in advance.

best regards

Tunc OVACIK

Check out Analysis Management Objects (AMO).

http://msdn2.microsoft.com/en-us/library/ms124924.aspx

|||Thanks for the link which is very usefull and has good informations about the whole programming stuff of OLAP technology but I guess those classes are for Analysis Manager 2005.
I am using Analysis Manager 2000 and those classes do not support AM 2000 as far as I understand. Because I have tried to implement the sample codes given in the link but
it did not work out.
Do you have any documents or any other side which is explaining how to get database names and such stuff from Analysis Manager 2000.

thanks for your time
best regards

Tunc OVACIK|||

In that case, check out Decision Support Objects (DSO)

http://msdn2.microsoft.com/en-us/library/aa902639(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms133828.aspx

Retrieving an image from SQL, test for null

I have an employee directory application that displays employees in a gridview. When a record is selected, a new page opens and displays all info about the employee, including their photo. I have the code working that displays the photos, however, when no photo is present an exception is thrown that "Unable to cast object of type System.DbNull to System.Byte[]". I'm not sure how to test for no photo before trying to write it out.

My code is as follows (with no error trapping):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load,Me.Load

Dim tempAsString

Dim connPhotoAs System.Data.SqlClient.SqlConnection

Dim connstringAsString

connstring = Web.Configuration.WebConfigurationManager.ConnectionStrings("connPhoto").ConnectionString

connPhoto =New System.Data.SqlClient.SqlConnection(connstring)

temp = Request.QueryString("id")

Dim SqlSelectCommand2As System.Data.SqlClient.SqlCommand

Dim sqlstringAsString

sqlstring ="Select * from dbo.PhotoDir WHERE (CMS_ID = " + temp +")"

SqlSelectCommand2 =New System.Data.SqlClient.SqlCommand(sqlstring, connPhoto)

Try

connPhoto.Open()

Dim myDataReaderAs System.Data.SqlClient.SqlDataReader

myDataReader = SqlSelectCommand2.ExecuteReader

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

connPhoto.Close()

Catch SQLexecAs System.Data.SqlClient.SqlException

Response.Write("Read Failed : " & SQLexec.ToString())

EndTry

EndSub

EndClass

If you could point me in the right direction I would appreciate it.

lwhalen618:

when no photo is present an exception isthrown that "Unable to cast object of type System.DbNull toSystem.Byte[]

lwhalen618:

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

did you try to check for nulls ??

DoWhile (myDataReader.Read())
if Not IsDBNull(myDataReader.Item("ImportedPhoto")) then
Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))
End if
Loop

hope it works... pls let me know

Good Luck./.

|||

I did try testing for null but was doing it incorrectly. Your code worked fine. Thanks!

retrieving agent id?

Hi all,

I have an application that creates a publication on the server, and
have multiple mobile devices creating annonymous subscriptions to that
publications. I need to write a report that checks if each device have
the replication synchronized successfully. I can run
distribution.dbo.sp_MSenum_merge or look into
distribution.dbo.MSmerge_history to get at the data for _all_
subscriptions to a given publication, but to look at a particular
subscription, I need to filter by either the subscriber_db or agent_id
column. The problem is, how do I get either one of these information
from the device? Or is there other way of retrieving the merge history
for a particular device/annonymous subscription?

Thanks in advance,
Harold<haroldsphsu@.gmail.com> wrote in message
news:1119475787.108503.99500@.g44g2000cwa.googlegro ups.com...
> Hi all,
> I have an application that creates a publication on the server, and
> have multiple mobile devices creating annonymous subscriptions to that
> publications. I need to write a report that checks if each device have
> the replication synchronized successfully. I can run
> distribution.dbo.sp_MSenum_merge or look into
> distribution.dbo.MSmerge_history to get at the data for _all_
> subscriptions to a given publication, but to look at a particular
> subscription, I need to filter by either the subscriber_db or agent_id
> column. The problem is, how do I get either one of these information
> from the device? Or is there other way of retrieving the merge history
> for a particular device/annonymous subscription?
> Thanks in advance,
> Harold

I don't know much about merge replication, but BOL suggests that you should
consider the SQL-DMO COM interface instead of using system stored
procedures - see "Introducing Replication Programming". In your specific
case, you might try the MergePublication object's EnumSubscriptions() and
EnumMergeAgentSessions() methods, although that's just a guess - your best
option might be to post in microsoft.public.sqlserver.replication

Simon

Retrieving a list ot tables

Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.

you can use SQL helper procedures like

sp_tables for tables

and

sp_databases for databases

this is recommended way, you can also try to use system views to get this data but it is not recommended by Microsoft.

Thanks

|||

To retrieve a list of databases...

Select [name]as DBName,crdatefrommaster.dbo.sysdatabaseswherehas_dbaccess([name]) = 1andNOT( (DATABASEPROPERTY([name], N'isdetached') <> 0)or (DATABASEPROPERTY([name], N'isshutdown') <> 0)or (DATABASEPROPERTY([name], N'issuspect') <> 0)or (DATABASEPROPERTY([name], N'isoffline') <> 0)or (DATABASEPROPERTY([name], N'isinload') <> 0)or (DATABASEPROPERTY([name], N'isinrecovery') <> 0)or (DATABASEPROPERTY([name], N'isnotrecovered') <> 0))

And to retrieve the list of tables in database [DatabaseName]...

select table_catalog, table_schema, table_name,sysobjects.crdatefrom information_schema.tablesinnerjoinsysobjectsonsysobjects.type ='U'andsysobjects.name = information_schema.tables.table_namewhere table_type ='BASE TABLE'and table_catalog ='[DatabaseName]'

Retrieve last inserted or updated record

Hi

I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.

On the event handler I am usin select ID,Name from my [table];

this will return all record from database.

I just want to get the record which is inserted or updated.

Can u help me in that.

Take care

Bye

You need no event to get the same. It can be easily done like:-

Dim sql As String = "INSERT INTO myTable (col2,col3) VALUES (bb,cc); SELECT * FROM jobs WHERE col1 = @.@.IDENTITY"

@.@.Identity carries the identity value of the last inserted record. Above statement can be executed with ExecuteReader method.

Hope this helps.

|||

Similarly code can be written for update.

|||

In the query you can use

SELECT@.ID=IDENT_CURRENT('TableName') for inserting

|||

Plz click "Mark as Answer" on the post that helped you.

Retrieve file stored in SQL database ...

Hi there

I'm using VS2005 (VB.net) and SQL 2005.

We've uploaded files from the web application to the SQL database.
The next thing I want to be able to do is to retrieve this uploaded file(s) from the database and attach it in the email when the user click on the Submit button on the web form.

How can this be done ?

Any help would be greatly appreciated.

TIA

Maybe you can use xp_sendmail, which allows sending SQL mail with query result as attachment. For example:

EXEC xp_sendmail @.recipients = 't-leijie',
@.query = 'SELECT pr_info FROM pubs..pub_info',
@.subject = 'SQL Server Report',
@.message = 'Test attachment',
@.attach_results = 'TRUE', @.width = 250

For more information, you can refer to:

Configuring Mail Profiles

xp_sendmail