Char and Varchar: available

Showing posts with label available. Show all posts

Thursday, March 22, 2012

Check if External Image Url valid/exist

Hi,
I would like to check if the external image url is valid, if it is not valid
i would like to show another external "not available" image from an alternate
url. Any idea how do do it or any function that i can use?
i have added a vb function is the custom code to be called to check if the
url is valid, it works great in report designer preview, however after i
deployed, in the report manager all the image return an "not available" image
from an alternate url, regardless if the url is valid.
Below is the VB function that i added into the custom code:
Function IsValid(ByVal Url As String) As Boolean
Dim sStream As IO.Stream
Dim URLReq As Net.HttpWebRequest
Dim URLRes As Net.HttpWebResponse
Try
URLReq = Net.WebRequest.Create(Url)
URLRes = URLReq.GetResponse()
sStream = URLRes.GetResponseStream()
Dim reader As String = New IO.StreamReader(sStream).ReadToEnd()
Return True
Catch ex As Exception
Return False
End Try
End FunctionPlease can someone answer my question'? Thanks
"SL" wrote:
> Hi,
> I would like to check if the external image url is valid, if it is not valid
> i would like to show another external "not available" image from an alternate
> url. Any idea how do do it or any function that i can use?
> i have added a vb function is the custom code to be called to check if the
> url is valid, it works great in report designer preview, however after i
> deployed, in the report manager all the image return an "not available" image
> from an alternate url, regardless if the url is valid.
> Below is the VB function that i added into the custom code:
> Function IsValid(ByVal Url As String) As Boolean
> Dim sStream As IO.Stream
> Dim URLReq As Net.HttpWebRequest
> Dim URLRes As Net.HttpWebResponse
> Try
> URLReq = Net.WebRequest.Create(Url)
> URLRes = URLReq.GetResponse()
> sStream = URLRes.GetResponseStream()
> Dim reader As String = New IO.StreamReader(sStream).ReadToEnd()
> Return True
> Catch ex As Exception
> Return False
> End Try
> End Functionsql

Tuesday, March 20, 2012

Check free space on database

You can use xp_fixeddrives to check the available space on disk drives, but
is there a stored procedure to check and see how much space is available in a
database?
What I would like to do before running our jobs is first check and see if
there is space in the database. If not, then check the disk drives to see if
there is room for the database to grow.
Thanks,sp_spaceused
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andy" wrote:
> You can use xp_fixeddrives to check the available space on disk drives, but
> is there a stored procedure to check and see how much space is available in a
> database?
> What I would like to do before running our jobs is first check and see if
> there is space in the database. If not, then check the disk drives to see if
> there is room for the database to grow.
> Thanks,|||Is it possible to insert the results of this into a temp table and then query
it? I would like to automate the process and if the amount of free space
isn't enough, raise an error.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> sp_spaceused
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Andy" wrote:
> > You can use xp_fixeddrives to check the available space on disk drives, but
> > is there a stored procedure to check and see how much space is available in a
> > database?
> >
> > What I would like to do before running our jobs is first check and see if
> > there is space in the database. If not, then check the disk drives to see if
> > there is room for the database to grow.
> >
> > Thanks,|||Hi,
See the below script:-
create table #space(spaceused varchar(1000))
execute master.dbo.xp_cmdshell 'osql -SSQLServername -E -Q"exec
sp_spaceused" -o"c:\\sp_space.txt" -s"" '
insert into #spaceexecute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt"
'
select spaceused from #space
Thanks
Hari
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:7B459A15-783C-45FC-BFD4-E6678015B44A@.microsoft.com...
> Is it possible to insert the results of this into a temp table and then
> query
> it? I would like to automate the process and if the amount of free space
> isn't enough, raise an error.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
>> sp_spaceused
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Andy" wrote:
>> > You can use xp_fixeddrives to check the available space on disk drives,
>> > but
>> > is there a stored procedure to check and see how much space is
>> > available in a
>> > database?
>> >
>> > What I would like to do before running our jobs is first check and see
>> > if
>> > there is space in the database. If not, then check the disk drives to
>> > see if
>> > there is room for the database to grow.
>> >
>> > Thanks,

Check free space on database

Thursday, March 8, 2012

Check available Time procedure

Hey Guys,
I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
Example User enters today's date and 5:30 AM and the search for all available techs to perform that task

the tables ddl is
USE [Schedule]
GO
/****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllDays](
[ID] [int] NOT NULL,
[DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [Schedule]
GO
/***Appointments Table where trouble is *****
/****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Tech_ID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[App_Date] [datetime] NOT NULL,
[Created_By] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Sku_ID] [int] NOT NULL,
[Comment_ID] [int] NOT NULL,
CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
REFERENCES [dbo].[Comments] ([ID])
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
REFERENCES [dbo].[Techs] ([ID])

USE [Schedule]
GO
/****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Tech_ID] [int] NOT NULL,
[AllDayID] [int] NOT NULL,
[ShiftStartTime] [datetime] NOT NULL,
[ShiftEndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
REFERENCES [dbo].[AllDays] ([ID])

Plus the Techs Table which holds their ID's names etc
I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help

Thanks for any inputDefine "first available tech".
Does an appointment have to fall completely within a single tech's shift?
Can an appointment span a day?
And why are you using varchar to store date values?

Look, a basic query would look like this:

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Commited.TechID = Schedule.Tech_ID
and ((Committed.ShiftStartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.ShiftEndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.ShiftStartTime and Committed.ShiftEndTime)))|||Thanks for the reply blindman,
What I meant by first tech can be best explained with this scenario.
End user selects date as 06/02/2006 at 10:00 AM then search. the query should return all available techs for that date and period of time and the closest time available if 10:00 AM is not available on that day.
Example*******************
Tech available from date available
Tech1 10:00 AM 06/02/2006
Tech2 11:00 AM 06/02/2006
tech2 9:00 AM 06/02/2006
************************
now as for your question
>Does an appointment have to fall completely within a single tech's shift?
Yes, all techs will be required to reschedule their appointment if they cant finish them on day assigned.
Can an appointment span a day?
No they shouldnt, its just not within the nature of the business to do this.
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

I hope that answers your questions.

Thanks for your time|||...
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

"[DayString] [varchar](20) "??

Will the logic I gave you in my sample code work for your situation?|||"[DayString] [varchar](20) "??
Oh this table hold an integer representing the day and DayString is the day itself.
**************************
ID DayString
1 Sunday
2 Monday
**************************
I tried to run the SQL the following

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Committed.Tech_ID = Schedule.Tech_ID
and ((Committed.StartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.EndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.StartTime and Committed.EndTime)))

the result was no records. Am I missing something here?
Thanks for your help|||Logic error. Should have been:
select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime >= Appointments.EndTime
.........to find shifts that bracket the appointments.|||Thanks Blindman :-)
After the I changed the script to what you recommended, The statements returned one row.
Now the stupid question, Is it possible to return the top 1 then the closest available techs as well?
IE, If user enters 12/02/2007 10:30 AM and we have tech1 who starts at 9:30 AM on that day and have no appointment but tech2 starts 12:00 PM. and have an appointment starting 12/02/2007 1:30 PM and ending 12/02/2007 4:00 PM. is it possible to list tech2 with his/her availablity. this way the user doesnt have to go back and query the database again by changing date and time.

second stupid question and that is related to my lack of experience in SQL is how can send this SP the date and time to look for.

Thank you very much for your help.|||In answer to your first question, just about anything can be done in SQL. But you are getting into some fairly complex business logic that is particular to your application. It will require a substantial amount of effort in requirements gathering and programming to get this right. You either need to get educated on SQL fast, or hire a contractor to do the work for you.

To answer your second question, the datetime value can be sent to the stored procedure as a parameter. Please read about stored procedures in Books Online.|||Thank you for all your help blindman
You have been great

I'll be hitting the book store today!|||Hit Books Online first. It's free.

Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
--
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||Jame
By default SQL Server should use memory dynamically. In most cases this works well. Your comments about the memory available, suggests to me that you are running other applications on the server. You really should try to run SQL Server on a dedicated server, you can get unpredictable results when it shares a server with other applications
Regard
John|||As I wrote, the other application is Analysis Services
We sell a solution including SQL Server and AS, and we cannot ask the customers to run the solution on 2 machines instead of one.|||Thanks, I had missed that one
You guys don't know a way of throttling Analysis Services' memory, do you?|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||James
By default SQL Server should use memory dynamically. In most cases this work
s well. Your comments about the memory available, suggests to me that you ar
e running other applications on the server. You really should try to run SQL
Server on a dedicated serv
er, you can get unpredictable results when it shares a server with other app
lications.
Regards
John|||Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||As I wrote, the other application is Analysis Services.
We sell a solution including SQL Server and AS, and we cannot ask the custom
ers to run the solution on 2 machines instead of one.|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/.../reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Thursday, March 22, 2012

Check if External Image Url valid/exist

Tuesday, March 20, 2012

Check free space on database

Check free space on database

Check free space on database

Thursday, March 8, 2012

Check available Time procedure

Check available memory for SQL 2000

Check available memory for SQL 2000

Char and Varchar

Blog Archive

About Me