?
Solved

transalte access sentence to sql server

Posted on 2006-04-18
21
Medium Priority
?
612 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:darkpomix
21 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16481742
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
 

Author Comment

by:darkpomix
ID: 16482702
make me the same error ,......



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


:S
0
 
LVL 2

Expert Comment

by:fsilva79
ID: 16483157
Tu problema es que en Access usas comillas ("TEXTO") y en SQL Server debes usar apostrofes 'TEXTO'

Eso debe solucionar ese error.

Saludos
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:darkpomix
ID: 16483492
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16483729
Post your current query as it stands now and any errors.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16483751
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
 

Author Comment

by:darkpomix
ID: 16503336
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16503735
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
 

Author Comment

by:darkpomix
ID: 16503961
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16504017
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
 

Author Comment

by:darkpomix
ID: 16504136
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16504423
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16504427
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16504433
Also, is CRMProductRequisitions.RequestedDate a datetime or smalldatetime column?
0
 

Author Comment

by:darkpomix
ID: 16504475
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16504492
>>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
 

Author Comment

by:darkpomix
ID: 16509035
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16509670
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
 

Author Comment

by:darkpomix
ID: 16509848
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16512698
>>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
 

Author Comment

by:darkpomix
ID: 16512719
ok thanks   :D
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question