transalte access sentence to sql server

i have problems with this sentence  i convert to sql sertver but make me an error
 
THE ACCESS SENTENCE.....

PARAMETERS pFromDate DateTime, pToDate DateTime;
SELECT SCMCountings.*, Warehouses.Alias AS Warehouse, Programmed.Alias AS ProgrammedBy, Supervised.Alias AS Supervisor, Realized.Alias AS RealizedBy, qryCountingTypes.CountingType, qryCountingTypes.CountingKey, Switch([CountingStatus]="D","Diseño",[CountingStatus]="P","Programado",[CountingStatus]="S","Iniciado",[CountingStatus]="V","Validación",[CountingStatus]="F","Concluido",[CountingStatus]="X","Cancelado") AS CountingStatusName
FROM ((((SCMCountings INNER JOIN EMPParticipants AS Warehouses ON SCMCountings.WarehouseId = Warehouses.ParticipantId) INNER JOIN EMPParticipants AS Programmed ON SCMCountings.ProgrammedById = Programmed.ParticipantId) INNER JOIN EMPParticipants AS Supervised ON SCMCountings.SupervisedById = Supervised.ParticipantId) INNER JOIN EMPParticipants AS Realized ON SCMCountings.RealizedById = Realized.ParticipantId) INNER JOIN qryCountingTypes ON SCMCountings.CountingTypeId = qryCountingTypes.CountingTypeId
WHERE (((SCMCountings.ProgrammedDate)>=[pFromDate] And (SCMCountings.ProgrammedDate)<=[pToDate]));



THE SQL SERVER SENTENCE..............

CREATE PROCEDURE dbo.qryCountings
(
      @pFromDate datetime,
            @pToDate  datetime
)
AS

SELECT     dbo.SCMCountings.*, Warehouses.Alias AS Warehouse, Programmed.Alias AS ProgrammedBy, Supervised.Alias AS Supervisor,
                      Realized.Alias AS RealizedBy, dbo.qryCountingTypes.CountingType AS CountingType, dbo.qryCountingTypes.CountingKey AS CountingKey, case CountingStatus when 'D' then 'Diseño' when 'P' then 'Programado' when 'S'  then 'Iniciado' when 'V' then 'Validación' when 'F' then 'Concluido' when 'X' then 'Cancelado' END AS CountingStatusName
FROM         dbo.SCMCountings INNER JOIN
                      dbo.EMPParticipants Warehouses ON dbo.SCMCountings.WarehouseId = Warehouses.ParticipantId INNER JOIN
                      dbo.EMPParticipants Programmed ON dbo.SCMCountings.ProgrammedById = Programmed.ParticipantId INNER JOIN
                      dbo.EMPParticipants Supervised ON dbo.SCMCountings.SupervisedById = Supervised.ParticipantId INNER JOIN
                      dbo.EMPParticipants Realized ON dbo.SCMCountings.RealizedById = Realized.ParticipantId INNER JOIN
                      dbo.qryCountingTypes ON dbo.SCMCountings.CountingTypeId = dbo.qryCountingTypes.CountingTypeId

where SCMCountings.ProgrammedDate between @pFromDate and @pToDate

RETURN
GO




THE ERROR ........
[Microsoft][ODBC SQL Server Driver]Valor de carácter no válido para especificación cast
                                          " no  vaild character value  for the specification of CAST"

thanks a lot

darkpomixAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
create procedure GetDate @pFromDate datetime, @pToDate datetime
as
SELECT SCMCountings.*, Warehouses.Alias AS Warehouse, Programmed.Alias AS ProgrammedBy, Supervised.Alias AS Supervisor, Realized.Alias AS RealizedBy, qryCountingTypes.CountingType, qryCountingTypes.CountingKey
, CASE [CountingStatus] WHEN "D" then "Diseño" WHEN  "P" THEN "Programado" WHEN  "S" THEN "Iniciado" WHEN  "V" THEN "Validación" WHEN  "F" THEN "Concluido" WHEN "X" THEN "Cancelado" ELSE "OTHER" END AS CountingStatusName
FROM ((((SCMCountings INNER JOIN EMPParticipants AS Warehouses ON SCMCountings.WarehouseId = Warehouses.ParticipantId) INNER JOIN EMPParticipants AS Programmed ON SCMCountings.ProgrammedById = Programmed.ParticipantId) INNER JOIN EMPParticipants AS Supervised ON SCMCountings.SupervisedById = Supervised.ParticipantId) INNER JOIN EMPParticipants AS Realized ON SCMCountings.RealizedById = Realized.ParticipantId) INNER JOIN qryCountingTypes ON SCMCountings.CountingTypeId = qryCountingTypes.CountingTypeId
WHERE (((SCMCountings.ProgrammedDate)>=@pFromDate And (SCMCountings.ProgrammedDate)<=@pToDate));
0
darkpomixAuthor Commented:
make me the same error ,......



[Microsoft][ODBC SQL Server Driver]Valor de carácter no válido para especificación cast


:S
0
fsilva79Commented:
Tu problema es que en Access usas comillas ("TEXTO") y en SQL Server debes usar apostrofes 'TEXTO'

Eso debe solucionar ese error.

Saludos
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

darkpomixAuthor Commented:
si ya las cambie, si note ese error pero sigue fallamdo y mostrandome el mismo error.....



 i changed that  but the sql server send me the same error....
0
Anthony PerkinsCommented:
Post your current query as it stands now and any errors.
0
Anthony PerkinsCommented:
The following is syntactically correct (no syntax errors) and is based on angelIII's suggestion (no points please):

SELECT      SCMCountings.*,
      Warehouses.Alias AS Warehouse,
      Programmed.Alias AS ProgrammedBy,
      Supervised.Alias AS Supervisor,
      Realized.Alias AS RealizedBy,
      qryCountingTypes.CountingType,
      qryCountingTypes.CountingKey,
      CASE [CountingStatus]
            WHEN 'D' THEN 'Diseño'
            WHEN  'P' THEN 'Programado'
            WHEN  'S' THEN 'Iniciado'
            WHEN  'V' THEN 'Validación'
            WHEN  'F' THEN 'Concluido'
            WHEN 'X' THEN 'Cancelado'
            ELSE 'OTHER'
      END AS CountingStatusName
FROM      SCMCountings
      INNER JOIN EMPParticipants AS Warehouses ON SCMCountings.WarehouseId = Warehouses.ParticipantId
      INNER JOIN EMPParticipants AS Programmed ON SCMCountings.ProgrammedById = Programmed.ParticipantId
      INNER JOIN EMPParticipants AS Supervised ON SCMCountings.SupervisedById = Supervised.ParticipantId
      INNER JOIN EMPParticipants AS Realized ON SCMCountings.RealizedById = Realized.ParticipantId
      INNER JOIN qryCountingTypes ON SCMCountings.CountingTypeId = qryCountingTypes.CountingTypeId
WHERE      SCMCountings.ProgrammedDate Between @pFromDate And @pToDate
0
darkpomixAuthor Commented:
this is another example  for this error ,  isnt the same thai i was post but  have the same error ,

CREATE  PROCEDURE dbo.qryNotSaledProducts
(
    @FromDate DateTime,
    @ToDate DateTime
)
AS
      /* SET NOCOUNT ON */

SELECT PLMProducts.ProductId AS NotSaledProductId, Sum(CASE WHEN [CRMProductRequisitions].[Quantity]is Null THEN 0 ELSE [CRMProductRequisitions].[Quantity] END) AS NotSaledQuantity
FROM CRMProductRequisitions RIGHT JOIN PLMProducts ON CRMProductRequisitions.ProductId = PLMProducts.ProductId
WHERE (((CRMProductRequisitions.RequestedDate)>=@FromDate And (CRMProductRequisitions.RequestedDate)<@ToDate) AND ((CRMProductRequisitions.IsDemand)=1 Or (CRMProductRequisitions.IsDemand) Is Null)) OR (((CRMProductRequisitions.RequestedDate) Is Null) AND ((CRMProductRequisitions.IsDemand)=1 Or (CRMProductRequisitions.IsDemand) Is Null))
GROUP BY PLMProducts.ProductId;

RETURN
GO

 but have the same error bad especification of CAST,  i have this problem when i put a range of dates like this
WHERE     SCMCountings.ProgrammedDate Between @pFromDate And @pToDate..... so i dont know that is the problem please help .....


Othoniel
0
Anthony PerkinsCommented:
Since there is no syntax error in the code posted, it has to come from the method that you are executing the stored procedure.  Are you testing this stored procedure from SQL Query Analyzer?  If so how?  Make sure you use valid dates.  I recommend the format yyyy-mm-dd
0
darkpomixAuthor Commented:
y check the store procedure from query analizer, and   the syntax  is ok ,  but when i debug the store procedure and asign the values for that store procedure,  runs and the  results  table  show the error :


bad especification of CAST ,  i put the values  like  the tables, but  in some parts  i remember that contains the date and the hour of the transaction,   so in access i dont have any problem when i put only the date, i dont know if in sql server doesn't match  the entire field make an error  beacuse some fields of the same column have only the date and others date an hour




THanks

Othoniel
0
Anthony PerkinsCommented:
In Spanish:
Te lo voy a repetir en español porque al parecer no me estoy comunicando:

La pregunta es sencilla:  ¿Cómo estás probando el "stored procedure" qryNotSaledProducts en SQL Query Analyzer?

_________________________________________________________________________________________________________________
In English:
I am going to repeat my question in Spanish as I appear not to be communcating:

The question is quite simple:  How are you testing the stored procedure qryNotSaledProducts in SQL Query Analyzer?
0
darkpomixAuthor Commented:
estoy probando el store procedure, en el query analizer,   checo sintaxis ,  y todo ok, pero cuando lo corro con la funcion de debug pues me marca el error :

[Microsoft][ODBC SQL Server Driver]Valor de carácter no válido para especificación cast


 entonces, pues  me sale el  error ,  coloque las posibles soluciones,  y nada, entonces pues  nose que pueda ser, la verdad.


 y todos los queries que tengo que usan el metodo FROMDATE--TODATE, la sintaxis esta correcta pero al momento de correrlo  me marca ese error .


Gracias
0
Anthony PerkinsCommented:
Translation:
I am checking the syntax for the stored procedure in query analyzer and everything is OK, but when I try and run it in debug mode, well I get the error:
[Microsoft][ODBC SQL Server Driver]Valor de carácter no válido para especificación cast

so, I get this error, I tried some possible solutions and nothing, so the truth be known, I have no idea.

and all the ones I have use the method FROMDATE--TODATE, the syntax is correct, but I get the error when I run it.

Thanks.
0
Anthony PerkinsCommented:
I guess my Spanish was not much better, so I will try once again in English, perhaps I will have better luck.

Please execute (do not use the debug mode) your stored procedure in SQL Query Analyzer and post how you are doing it here with the resulting error.
0
Anthony PerkinsCommented:
Also, is CRMProductRequisitions.RequestedDate a datetime or smalldatetime column?
0
darkpomixAuthor Commented:
its a datetime  column, i will try tomorrow beacuse im in  my house and i dont have the database here , so tomorrow i will post the error ,  thanks,  


where do you learn to write in spanish?
0
Anthony PerkinsCommented:
>>so tomorrow i will post the error <<
More than anything else, we need to see how you execute the stored procedure from SQL Query Analyzer as in:
exec dbo.qryNotSaledProducts '?????????', '?????????'


>>where do you learn to write in spanish?<<
With "Dora the Explorer".  No, just kidding, I lived in the D.F.
0
darkpomixAuthor Commented:
i execute the store procedure in query analizer, i check the syntax, and lather   there is a button  in the top that says, execute store procedure, and  all is ok,  but  when i debug  and put the parametersa @from date --@to date,  is when the program show me  the error..


0
Anthony PerkinsCommented:
I am just at the point of giving up.  So I will just try one final time:

Please post the line where you execute the stored procedure in SQL Query Analyzer.  The line should look something like this:
exec dbo.qryNotSaledProducts '?????????', '?????????'

Specifically, I need to know the values of the date parameters and any error messages.

I don't need anything else.  

I don't know what else to say to make my point clearer.


In Spanish (to make myself clear):

Estoy a punto de darme por vencido.  Voy a intentar una vez más:

Por favor, necesito ver el código donde executas el stored procedure en SQL Query Analyzer.  La línea debe ser algo parecido a esto:
exec dbo.qryNotSaledProducts '?????????', '?????????'

Específicamente, necesito saber los valore de los parametros tipo fecha y cualquier mensaje de error.

No necesito nada más.  

No sé que más decir para ser más claro.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
darkpomixAuthor Commented:
okk thanks for your help,   when i was execute the storeprocedure from query analizer, i use the debug mode  and make me that error , but   i execute  with
exec dbo.qryNotSaledProducts '01/01/2005', '01/01/2006'


the result was ok, thanks a lot for all you patience  




 muchas gracias,  y  no   te enojes tanto que  te va a hacer daño jajaj , ntc es broma,  un saludo desde sonora, mexico


0
Anthony PerkinsCommented:
>>exec dbo.qryNotSaledProducts '01/01/2005', '01/01/2006'<<
Instead of doing it this way, make sure to do it using an unambiguous date format such as yyyy-mm-dd, as in:

exec dbo.qryNotSaledProducts '2005-01-01', '2006-01-01'
0
darkpomixAuthor Commented:
ok thanks   :D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.