Need help converting a CHAR into a Datetime

I have a Database which is having a Counterdate time Column stored in the form of Char(24)

But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error

"Conversion failed when converting datetime from character string."

I am done all permutatiions and combinations for this used

Set @.DE = convert ( datetime, @.ts,121)

Set @.de = cast( @.ts as datetime)

BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..

I DONT understand why the Server is behaving wiered..

Hoping to get an answer soon.

With regards

Sharad

Database Developer ,

UIC

Hi Sharad,

Can you post a sample of the (character) data that is causing the issue?

Thanks,

Rob

|||

Sir,

I am just using the performance Logs of the a server and in that the database get automatically generated and hence..

I get the data of datetime column in Char(24) form.. and when i use an function to convert that into a datetime form it gives me error..

take anytime .e.g of the form

"2006-11-15 17:33:22.015"

if this is the time stamp and stored in the char(24) format and I want it to be datetime ...

What any sensible person would do

1. cast it into datetime

2. Convert into datetime..

Both not working ..

With regards

Sharad

|||

PLZ reply if u could Help with this simple STUFF

This CODE I RUN

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print @.de

Output is

:::

2006-10-27 20:01:13.297

Oct 27 2006 8:01PM

But i want output to be exactly same

as 2006-10-27 20:01:13.297

Plz help me with this..

|||

change your statement as below,

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print convert(varchar, @.ds, 121)

Need help constructing stored procedure

Hi,

I need to construct a stored procedure that will accept a set of comma seperated numbers. What I would like to do is something like this

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where dvd_detail_id in (@.contents)

dvd_detail_id is defined as int in the table.
The problem is if I declare @.contents as varchar, the procedure only recognizes the first number and ignores the others. Does anyone know how to get around this?

Thanks

Dan@.array is your @.contents...you need to pass the separator too..it can be a ',' or '-' etc

Create procedure shopping_cart (@.Array varchar(1000), @.separator char(1) ) AS
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
GO

hth|||I did the following on some other project

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where CharIndex(',' + Cast(dvd_detail_id as varchar(3)) + ',', @.contents) > 0

In the above case you should be sure that the values passed in are going to have , next to it. For eample, it should be ,1,2,3,

Does the above make sense?

AP

Need help comparing output

Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0  =; = Update InvPrice set SellingPrice =3D 222 &nbs=p;  =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.

---

--=_NextPart_000_0028_01C3C8D7.32408980--

need help- case...when not working

I am getting the error
Incorrect syntax near 'fValue1_precision'.
from the stored procedure below
Can someone see what is wrong?
Thanks
...
@.MyField3 float(8)=NULL,
@.MyField3_precision int=NULL,
...
SELECT
MyTable.[ID],
MyTable.MyField1,
MyTable.MyField2,
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
END) AS MyField3,
MyTable.MyField4,
MyTable.MyField5,Mad Scientist Jr wrote:
> I am getting the error
> Incorrect syntax near 'fValue1_precision'.
> from the stored procedure below
> Can someone see what is wrong?
> Thanks
> ...
> @.MyField3 float(8)=NULL,
> @.MyField3_precision int=NULL,
> ...
> SELECT
> MyTable.[ID],
> MyTable.MyField1,
> MyTable.MyField2,
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
> END) AS MyField3,
> MyTable.MyField4,
> MyTable.MyField5,
Given that the code fragment you posted doesn't include the name
referred to in the error message it's kind of hard to be sure...
However, this bit isn't valid syntax:
CONVERT(decimal(10, MyField3_precision)
You can't use a column value for the precision. The precision must be a
literal value.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the actual error was
Incorrect syntax near 'MyField3_precision'.
but that sounds like it could be the problem
thanks|||so the correct way to do it would be something like this?
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
MyField3)
--WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
MyField3)
WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
MyField3)
WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
MyField3)
WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
MyField3)
WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
MyField3)
WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
MyField3)
WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
MyField3)
WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
MyField3)
WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
MyField3)
WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
MyField3)
ELSE MyField3
END) AS MyField3,|||Mad Scientist Jr wrote:
> so the correct way to do it would be something like this?
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
> MyField3)
> --WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
> WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
> MyField3)
> WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
> MyField3)
> WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
> MyField3)
> WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
> MyField3)
> WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
> MyField3)
> WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
> MyField3)
> WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
> MyField3)
> WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
> MyField3)
> WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
> MyField3)
> WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
> MyField3)
> ELSE MyField3
> END) AS MyField3,
No. A column can only have one type, scale and precision and that's
determined at compile-time, so the multiple versions of CONVERT aren't
doing much for you. If you want each row formatted differently in SQL
then you'll have to convert the values to strings. Probably makes more
sense to return numbers at full precision and then format them in the
client app or presentation tier.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Strange, the above code is working. Using case-when I am getting back
the precision I want in the select (the result is being displayed in a
datagrid). It appears to be working, so what is wrong with the code?
Thanks|||Mad Scientist Jr wrote:
> Strange, the above code is working. Using case-when I am getting back
> the precision I want in the select (the result is being displayed in a
> datagrid). It appears to be working, so what is wrong with the code?
> Thanks
Depends what you mean by "appears to be working". What is it supposed
to do? Formatting of numbers is controlled by your client application,
not by SQL Server. Possibly the implict rounding performed by CONVERT
is influencing the way your app displays the numbers - but you don't
need CONVERT to do rounding. You can use ROUND or FLOOR instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Back to basics. CASE is an expression. An expression returns a scalar
result of one and only one data type. Why? Because a column can have
one and only one data type!! You missed the very definition of 1NF.
You might also way to learn HUGE differences between columns and
fields. Fields can change types and structure because in a file
system, the host program gives meanng to the data as it reads it from
left to right. In a RDBMS, the meaning is in the schema, not the
applications.

need help adding onto my stored procedure

calculate age:
CREATE PROCEDURE selectage
AS
SELECTAge = datediff(yy, birthday, getdate()) +
case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else -1
end
FROM nopay
my ? is how do fix this problem:
I add in
WHERE (((Age)>= @.age1 And (Age)<= @.age2))
and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?

Unfortunately SQL won't handle it that way. One approach is to use your calculation in the WHERE column:

WHERE

    datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

             -1

        end BETWEEN @.age1 And @.age2)

Alternately, you could do this:

SELECT

    age

FROM

(SELECT

   Age = datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

            -1

        end

FROM

    nopay) AS subquery

WHERE Age BETWEEN @.age1 And @.age2

Also, I would suggest writing a user-defined function (UDF) tocalculate the age. This will keep your code cleaner and is thesort of thing you will likely need to reuse. For example:

SELECT

   dbo.fnAge(birthday) AS age

FROM

    nopay

WHERE

     dbo.fnAge(birthday)BETWEEN @.age1 And @.age2)

CREATE FUNCTION age (@.indate datetime) -- note: untested!!

RETURNS int

AS

BEGIN

    DECLARE @.age int

    SELECT @.age = datediff(yy, @.indate, getdate()) +

        CASE

            WHEN datepart(dy, @.indate) <= datepart(dy, getdate()) then 0

        ELSE

            -1

        END

    RETURN @.age

END

|||

Can I kiss you?

One more thing I tried to add onto the where and it gave me an error about the comma

WHERE Age BETWEEN @.age1 And @.age2, breed = @.breed

i also did

WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

than it complained about input was not in correct form

Dim paramBreed As SqlClient.SqlParameter
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, breed.SelectedValue)
paramBreed.Value = breed.SelectedValue
objCM.Parameters.Add(paramBreed)

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

InvalidCastException: Cast from string "Dalmation" to type 'Integer' is not valid.]
if i change that statement to

paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar)

the program will execute but than it wont display as if the breed = @.breed was incorrect

|||Don't get excited, it's only SQL after all!
If that's an exact copy-and-paste of your WHERE clause, you need a space between the @.breed and the AND:
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2
(You should use the UDF. This is the sort of thing they were made for.)
|||

my mistake, no the syntax checks fine, just the asp gives that error

i tried writing

SELECT

   dbo.fnAge(birthday) AS age

FROM

and i dont understand dbo.fnAge

do i delete create stored procedure?

|||In your stored procedure, give a length to the @.breed nvarchar parameter
@.breed nvarchar(100)
And in your code, try this line instead for creating the parameter:
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, 100)
(Be sure to use the same length that corresponds to your database column.)
|||

Big thanks,

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay WHEREfirst.username = second.username) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

i figured out how to whatever you call it when u have same feilds and eliminate unwanted data

sql

need help - SP to move data

Hello
I am trying to create a stored procedure to move a ROW from Table1 to Table
based on a coditon.
Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
old_stock table.
I want to be able to pass the value for ITEMNO from my code which calls the
SP.
Please help me
TIA
Mackok, I have done myself.
Thanks
"MackS" <mackS@.yahooa.com> wrote in message
news:OWiNi8H$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am trying to create a stored procedure to move a ROW from Table1 to
> Table based on a coditon.
> Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
> old_stock table.
> I want to be able to pass the value for ITEMNO from my code which calls
> the SP.
> Please help me
> TIA
> Mack
>

Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need Experts on Vb.net CLR intergration problem

Hi all I have the following CLR stored procedure :

Partial Public Class StoredProcedures

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub sssGetActiveRepositoryByTitle( _

ByVal title As String)

' Add your code here

Using conn As New SqlConnection("context connection=true")

Dim objCommand As New SqlCommand()

Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)

TitleParam.Value = title

objCommand.Connection = conn

conn.Open()

'build the delete command

objCommand.CommandText = _

"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString

SqlContext.Pipe.ExecuteAndSend(objCommand)

conn.Close()

End Using

End Sub

Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :

Imports System.Data.SqlClient

Imports NBS.SURVEYSDATABASEservice.DBMS

Public Class clsClient

' it inherits the stored procedures from the DBMS class which is the name

'of the CLR dll

Inherits StoredProcedures

Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet

Try

Dim i As SqlDataReader

'parameters are stored in an array (zero based) for use in the base class

Dim parmArrSqlParms(0) As SqlClient.SqlParameter

' Dim fff As Int32

i = sdsGetClientByVirtualPath(pstrVirtualPath)

' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)

Catch ex As Exception

'log the error

'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)

'raise the error to the caller for handling

Throw ex

End Try

End Function

I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"

any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .

Help please !

Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--RichardRichard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>|||I agree with Mark, use SQL Agent for this.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
> > Hi,
> >
> > I need to schedule a stored procedure to run on a recurring basis. It
would
> > be acceptable if the procedure ran 1x per minute though it would be
better if
> > I could schedule & re-schedule procedure start time from within a
trigger or
> > other stored procedure. The recurring stored procedure is NOT an
> > administrative maintenance task. The recurring procedure calls other
stored
> > procedures across servers to gather data for the application.
> >
> > Question:
> >
> > 1) What's the preferred way to schedule & reschedule recurring execution
of
> > an application's stored procedures? From reading Books Online I gather
that
> > SQL Server Agent and DTS can both schedule tasks? Is one of those two
> > approaches appropriate for my needs?
> >
> > --Richard
> >

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--Richard
Richard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>
|||I agree with Mark, use SQL Agent for this.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
would[vbcol=seagreen]
better if[vbcol=seagreen]
trigger or[vbcol=seagreen]
stored[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]

Need debugging help--stored procedure to move database objects

Hi all--I'm adapting a stored procedure to work on transferring SQL Server 2005 tables and other objects owned under the dbo schema on a database to another non-dbo schema. Given that, I'm trying to use an "if...else" to prevent dbo-owned stored procedures and tables like dtproperties from moving with the rest of the objects to the non-dbo schema. Here's the code:

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

drop procedure [dbo].[chObjOwner]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

CREATE proc chObjOwner( @.usrName varchar(20), @.newUsrName varchar(50))

as

-- @.usrName is the current user

-- @.newUsrName is the new user

set nocount on

declare @.uid int -- UID of the user

declare @.objName varchar(50) -- Object name owned by user

declare @.currObjName varchar(50) -- Checks for existing object owned by new user

declare @.outStr varchar(256) -- SQL command with 'alter schema', 'alter authorization'

set @.uid = user_id(@.usrName)

declare chObjOwnerCur cursor static

for

select name from sys.objects where schema_id = @.uid

open chObjOwnerCur

if @.@.cursor_rows = 0

begin

print 'Error: No objects owned by ' + @.usrName

close chObjOwnerCur

deallocate chObjOwnerCur

return 1

end

fetch next from chObjOwnerCur into @.objName

while @.@.fetch_status = 0

begin

set @.currObjName = @.newUsrName + "." + @.objName

if (object_id(@.currObjName) > 0)

print 'WARNING *** ' + @.currObjName + ' already exists ***'

if @.currObjName in ('dt_addtosourcecontrol','dt_addtosourcecontrol_u','dt_adduserobject','dt_adduserobject_vcs','dt_checkinobject','dt_checkinobject_u','dt_checkoutobject','dt_checkoutobject_u','dt_displayoaerror','dt_displayoaerror_u','dt_droppropertiesbyid','dt_dropuserobjectbyid','dt_generateansiname','dt_getobjwithprop','dt_getobjwithprop_u','dt_getpropertiesbyid','dt_getpropertiesbyid_u','dt_getpropertiesbyid_vcs','dt_getpropertiesbyid_vcs_u','dt_isundersourcecontrol','dt_isundersourcecontrol_u','dt_removefromsourcecontrol','dt_setpropertybyid','dt_setpropertybyid_u','dt_validateloginparams','dt_validateloginparams_u','dt_vcsenabled','dt_verstamp006','dt_verstamp007','dt_whocheckedout','dt_whocheckedout_u','dtproperties')

begin

print 'WARNING *** ' + @.currObjName + ' is a system procedure'

end

else

begin

set @.outStr = "alter authorization on " + @.usrName + "." + @.objName + " to " + @.newUsrName

print @.outStr

set @.outStr = "alter schema " + @.newUsrName + " transfer " + @.usrName + "." + @.objName

print @.outStr

print 'go'

fetch next from chObjOwnerCur into @.objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

GO

Here's the error I get:

Msg 102, Level 15, State 1, Procedure chObjOwner, Line 51

Incorrect syntax near '0'.

I know that the cursor is having a problem closing and deallocating, but am not sure why the error is happening at this point. Any ideas?

Thanks!

One of your 'begin' s has no 'end'.

Need Coding for inserting data into SQL Server Using stored proecdure.

Hi to all..

I am new to ASP.Net, I want coding for inserting data in to SQL server 2005 that code contain the following criteria.

1.ASP.Net using C#.Net.

2.Using Stored Procdure.

3.the connetion string ,command , Data reader is in the same class. this class will be reusable for other page also without modifying any thing in it.

Please Help me.

Advance thanks.

Balaji,

I have an article in VB.NET which explains how to insert images to SQL server. Apart from images, i am using other columns to insert into the table.

Here is the article link:http://aspalliance.com/138_Inserting_Images_to_SqlServer_in_ASP_NET

And here is a link which will convert any VB.net code to c#http://labs.developerfusion.co.uk/convert/vb-to-csharp.aspx

Also, here is another link from ASP.NET quick starts which explains your scenario.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/databases.aspx#insert

Hope this helps.

|||

See if below thread can be useful.

http://forums.asp.net/t/1165758.aspx

need brief answers

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

what are pseudo tables - Never heard of them, you may mean Derived Tables (A table created in a query that lives only during that query), or Views (a sql statement describing a virtual table)

how queries run by MSDE - You have to use a tool, like Query Analyzer or a client application to send queies to the MSDE engine

how stored procedures run in backend by MSDE - Same as above

how triggers run by MSDE - Triggers are run automatically by the database engine when the proper event happens to a table with a trigger on it.

where triggers and stored proc stored in MSDE and in which form - They are stored in system tables, you access them to create and edit them through Enterprise Manager or writing a DDL query in Query Analyzer

where logs are being maintained of transactions/DML statement by default - The log file(s) are where ever you tell the database to put them when you create a database. By default I think they are in the <SQl Server install location>\Data folder.

|||I sometimes use the term "pseudo table" to describe the INSERTED and DELETED logical tables that are available in triggers. I am not sure if that is what applies here or not.|||ah, good point. I have never heard that term though, always heard them referred to as virtual tables.

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >

Need an opinion

Over the past two years (and with the help of many of you), I have created a CMMS that is used by my company. I have all the machine data stored in SQL Server. I have used MS Access and ASP's as the front end for data entry. I use SQL Server Agent to run exception reporting (email).

When I started I had no Experience at all with programming other than classes taken 98-99 (html, C++, vb).

Having said that, I am sure some of you can recall your first attempts at creating an app. With the constant upgrades to do more stuff, I am at the point that I need to go back and clean up (remove obsolete code).

Now for the question. I want to revamp my front-end particularly the Access App. If you were going to create a new app for data entry/reporting and your main strength was vbscript, what would you use?

Thanks,
LeeVB.Net of course.|||Originally posted by joejcheng
VB.Net of course.

I have created asp's using vbscript, but how does one get started with VB.net?|||My recommendation would be c#. There are enough differences between vbscript and vb.net that transitioning to c# is marginally steeper - c# also encourages better techniques for object oriented development. Also, you can translate easily to java once you have learned c#.

Anyway, research books on amazon.com to see which books would best suit your needs (whether it be vb.net or c#). Also, which will you focus more on - windows or web development. If you will develop in asp.net, then search for the appropriate books. Do you have access to visual studio .net or 2003 ?|||I have visual studio V6 but it is not .net|||At one point ms had a $50 upgrade from vs 6 to .net - but that does not exist anymore. Well if you can get .net then do so. If not, then you can develop with vs6 using visual interdev developing asp web pages using vbscript/javascript. But I would push your company to purchase the .net ide because ms is starting to drop support/distribution for several applications including those that deal with asp.|||"Front end" applications ought to be simple .. as simple as possible .. and there's usually a lot of rationale for "stick with the devil you know." That would be Visual Basic in some variation or another. And if possible, it ought to be the existing application if you can possibly do it.

First, you should carefully and systematically identify exactly what it is about the present application that compels you to replace it. That makes it absolutely essential to commit many thousands of dollars to doing so (even if you'll be the one who receives most of those dollars!). You need to be able to write down exactly what the deficiencies are, exactly how you'd be able to recognize an appropriate solution to those deficiencies if one hit you in the face (a solution, that is, not a deficiency...), and you ought to be able to defend those conclusions and decisions against your favorite "devil's advocate." Think from the business's point of view, not your own, and defend your company's supply of liquid cash as hard as you can. It's a good exercise.

You ought to thoroughly explore the "build vs. buy" decision angle: why is it impossible for your business not to simply buy a solution? Is your company really the first one to have been faced with this problem? Has no one solved it before? If such a solution does exist, how much would that alternative cost? (And don't blink at "$50,000" 'cuz your alternative might cost more. Again, be honest. Even cynical.)

If the true answer is, "well, I wanna learn another language and this sounds like a good excuse to do it," :rolleyes: be honest, from a business point of view that don't wash. :)

Maybe the existing application can be tweaked a little and fixed. You've put a lot of time into what you have now and it's tough to justify scrapping it.

These are the kinds of arguments you should impose on yourself. If your project flies through all of this self-imposed rigor, then :D ... you've found a project really worth doing, and you'll have developed a crystalline idea of exactly how to succeed with it.|||First you must know that in the beginning my job was to "administrator" a canned MMS and not in the development of a program. My background is in day to day maintenance of machinery. I wear several hats and the developer is not even in the job description. It is something that has happened out of necessity.

It is kinda complicated, but the short of it is that my program is very custom to our plant, and in the past 6 months, I have had several people from other plants and divisions come in to review our maintenance program. All have liked our program as it keeps the end user in mind, but because it is so custom it is not easily transferable. It combines machine history with plant performance down to the operator.

What I would like to do is take the custom code (machine specific to our plant hard coded in) out and make it dynamic, so that it would work for other plants. I want to do in a way that is most efficient and most appealing to the user. And in that I mean low resource usage (hardware and peoples time).

I understand your last 4 paragraphs and that is the reason that the majority of the redo will be done by me on my own as an opportunity to continue to grow as a developer. Having said that, that is why I am wanting to find a platform that is easy to learn and not expensive to purchase as an individual.

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works. 

Need Advise on Stored Procedure

Little rusty on my SQL but here is what I need. Currently have a stored procedure that returns numerical data for 8 different element readings. ( Carbon, Silicon, Sulfur, etc ). This data is then put into a datagrid on an asp.net page and shows the readings for each test.
On my asp .net page... they only want data that is out of the specified ranges to be shown ( EX: > 1.6 AND < 2.0 ). Currently it is setup on the page to change the font color to red of data of its range but now they don't want data to be shown at all if for that one test everything is in its range.
So how could I write my Where clause to check the 8 different element ranges and only need one of them to be true in order to show.
Could I use an If / Then statement?
Any help is appreciated... Sorry this is quickly done but got to have it done soon.

Show your table layout. Also, are the ranges for all elements the same, or element specific?

Need Advice/Help on querying a different db

I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?My first guess would be that you forgot how to spell the object name. The next guess would be that you are doing something that doesn't make sense, like SELECT * FROM myStoredProcedure or the equivalent.

-PatP|||stored procedure lies in ABC4_Dev1_Reporting
Data resides in ABC_Dev1_Call1

From Reporting db in query analyzer I cannot get the following query to run

select * from dbo.ABC_Dev1_Call1.Call (Call is the table)

What am I missing?
Thanks in advance!|||Ah, a snippet of code is worth a thousand words of description! I'd use:SELECT *
FROM ABC_Dev1_Call1.dbo.Call-PatP|||Ah... very interesting... I'm claiming a bad case of the friday's on that one. I owe you one. Thanks a million. Have a good weekend.|||Listen...if Pat collected them all...he'd be hammered all year...

You threw me with the 4 part naming convention....

See what happens when I take a break and get a sandwich...