Error 120

Hello.
I'm trying to run the following statement:
insert into [destination table]
([fields])
select [fields] from [source table]
When I try to run it, I get the following error messages:
Server: Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer
items than the insert list. The number of SELECT values
must match the number of INSERT columns.
Server: Msg 8180, Level 16, State 1, Line 8
Statement(s) could not be prepared.
The select list matches the insert list exactly.
The source and destination tables are located on two
different (linked) servers.
Both servers are running MSSQL 2000 SP3a.
What am I doing wrong?
Thank you.Without seeing the actual insert statement we can only guess. My guess
would be you have a space in one of the column names.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:12a5601c4432a$dcaeb0e0$a301280a@.phx
.gbl...
> Hello.
> I'm trying to run the following statement:
> insert into [destination table]
> ([fields])
> select [fields] from [source table]
> When I try to run it, I get the following error messages:
> Server: Msg 120, Level 15, State 1, Line 8
> The select list for the INSERT statement contains fewer
> items than the insert list. The number of SELECT values
> must match the number of INSERT columns.
> Server: Msg 8180, Level 16, State 1, Line 8
> Statement(s) could not be prepared.
> The select list matches the insert list exactly.
> The source and destination tables are located on two
> different (linked) servers.
> Both servers are running MSSQL 2000 SP3a.
> What am I doing wrong?
> Thank you.
>|||No, the statement is definitely OK, it works on other
servers, there's only one server it doesn't work on. And
it works if I run it locally, but it doesn't work when I
run it from another server.
Here are the statements:
declare @.IdSpatiu int,
@.IdTran int
set @.IdSpatiu = 15
set @.IdTran = 20
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select @.IdSpatiu, @.IdTran, Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9
If I replace "select @.IdSpatiu, @.IdTran, Numar"
with "select 15, 20, Numar", it works. It doesn't like
the variables in the select list.
If I'm connected to the server [ts-331], it works. But if
I'm connected to another server (to which ts-331 is
linked), it doesn't work.
It must be something about the ts-331 server. At first I
thought it must be the service pack, because it had no
service pack installed, but then I installed SP3a and the
problem persisted.
Thank you.

>--Original Message--
>Without seeing the actual insert statement we can only
guess. My guess
>would be you have a space in one of the column names.
>--|||It looks fine to me as well. Maybe there is something strange with the way
the Linked server is set up on that machine. Why do you want to do an
insert like that remotely anyway? It would be much cleaner if you had a
stored proc on the linked server that you call and pass in the 2 parameters.
That way the sp does not have to make any linked server calls at all when
actually doing the Insert.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:1318b01c443b8$d05fa8b0$a401280a@.phx
.gbl...
> No, the statement is definitely OK, it works on other
> servers, there's only one server it doesn't work on. And
> it works if I run it locally, but it doesn't work when I
> run it from another server.
> Here are the statements:
> declare @.IdSpatiu int,
> @.IdTran int
> set @.IdSpatiu = 15
> set @.IdTran = 20
> insert into [ts-331].Ploiesti.dbo.Factura
> (IdSpatiu, IdTran, Numar)
> select @.IdSpatiu, @.IdTran, Numar
> from [ts-331].Deva.dbo.Factura
> where IdTran = 11 and IdSpatiu = 9
> If I replace "select @.IdSpatiu, @.IdTran, Numar"
> with "select 15, 20, Numar", it works. It doesn't like
> the variables in the select list.
> If I'm connected to the server [ts-331], it works. But if
> I'm connected to another server (to which ts-331 is
> linked), it doesn't work.
> It must be something about the ts-331 server. At first I
> thought it must be the service pack, because it had no
> service pack installed, but then I installed SP3a and the
> problem persisted.
> Thank you.
>
> guess. My guess
>|||I had the same problem. Try this. I don't know why it works but it
does.
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select (select @.IdSpatiu), (select @.IdTran), Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9

Error 120

Hello.
I'm trying to run the following statement:
insert into [destination table]
([fields])
select [fields] from [source table]
When I try to run it, I get the following error messages:
Server: Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer
items than the insert list. The number of SELECT values
must match the number of INSERT columns.
Server: Msg 8180, Level 16, State 1, Line 8
Statement(s) could not be prepared.
The select list matches the insert list exactly.
The source and destination tables are located on two
different (linked) servers.
Both servers are running MSSQL 2000 SP3a.
What am I doing wrong?
Thank you.
Without seeing the actual insert statement we can only guess. My guess
would be you have a space in one of the column names.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:12a5601c4432a$dcaeb0e0$a301280a@.phx.gbl...
> Hello.
> I'm trying to run the following statement:
> insert into [destination table]
> ([fields])
> select [fields] from [source table]
> When I try to run it, I get the following error messages:
> Server: Msg 120, Level 15, State 1, Line 8
> The select list for the INSERT statement contains fewer
> items than the insert list. The number of SELECT values
> must match the number of INSERT columns.
> Server: Msg 8180, Level 16, State 1, Line 8
> Statement(s) could not be prepared.
> The select list matches the insert list exactly.
> The source and destination tables are located on two
> different (linked) servers.
> Both servers are running MSSQL 2000 SP3a.
> What am I doing wrong?
> Thank you.
>
|||No, the statement is definitely OK, it works on other
servers, there's only one server it doesn't work on. And
it works if I run it locally, but it doesn't work when I
run it from another server.
Here are the statements:
declare @.IdSpatiu int,
@.IdTran int
set @.IdSpatiu = 15
set @.IdTran = 20
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select @.IdSpatiu, @.IdTran, Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9
If I replace "select @.IdSpatiu, @.IdTran, Numar"
with "select 15, 20, Numar", it works. It doesn't like
the variables in the select list.
If I'm connected to the server [ts-331], it works. But if
I'm connected to another server (to which ts-331 is
linked), it doesn't work.
It must be something about the ts-331 server. At first I
thought it must be the service pack, because it had no
service pack installed, but then I installed SP3a and the
problem persisted.
Thank you.

>--Original Message--
>Without seeing the actual insert statement we can only
guess. My guess
>would be you have a space in one of the column names.
>--
|||It looks fine to me as well. Maybe there is something strange with the way
the Linked server is set up on that machine. Why do you want to do an
insert like that remotely anyway? It would be much cleaner if you had a
stored proc on the linked server that you call and pass in the 2 parameters.
That way the sp does not have to make any linked server calls at all when
actually doing the Insert.
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:1318b01c443b8$d05fa8b0$a401280a@.phx.gbl...
> No, the statement is definitely OK, it works on other
> servers, there's only one server it doesn't work on. And
> it works if I run it locally, but it doesn't work when I
> run it from another server.
> Here are the statements:
> declare @.IdSpatiu int,
> @.IdTran int
> set @.IdSpatiu = 15
> set @.IdTran = 20
> insert into [ts-331].Ploiesti.dbo.Factura
> (IdSpatiu, IdTran, Numar)
> select @.IdSpatiu, @.IdTran, Numar
> from [ts-331].Deva.dbo.Factura
> where IdTran = 11 and IdSpatiu = 9
> If I replace "select @.IdSpatiu, @.IdTran, Numar"
> with "select 15, 20, Numar", it works. It doesn't like
> the variables in the select list.
> If I'm connected to the server [ts-331], it works. But if
> I'm connected to another server (to which ts-331 is
> linked), it doesn't work.
> It must be something about the ts-331 server. At first I
> thought it must be the service pack, because it had no
> service pack installed, but then I installed SP3a and the
> problem persisted.
> Thank you.
> guess. My guess
>
|||I had the same problem. Try this. I don't know why it works but it
does.
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select (select @.IdSpatiu), (select @.IdTran), Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9

Error 120

Hello.
I'm trying to run the following statement:
insert into [destination table]
([fields])
select [fields] from [source table]
When I try to run it, I get the following error messages:
Server: Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer
items than the insert list. The number of SELECT values
must match the number of INSERT columns.
Server: Msg 8180, Level 16, State 1, Line 8
Statement(s) could not be prepared.
The select list matches the insert list exactly.
The source and destination tables are located on two
different (linked) servers.
Both servers are running MSSQL 2000 SP3a.
What am I doing wrong?
Thank you.Without seeing the actual insert statement we can only guess. My guess
would be you have a space in one of the column names.
--
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:12a5601c4432a$dcaeb0e0$a301280a@.phx.gbl...
> Hello.
> I'm trying to run the following statement:
> insert into [destination table]
> ([fields])
> select [fields] from [source table]
> When I try to run it, I get the following error messages:
> Server: Msg 120, Level 15, State 1, Line 8
> The select list for the INSERT statement contains fewer
> items than the insert list. The number of SELECT values
> must match the number of INSERT columns.
> Server: Msg 8180, Level 16, State 1, Line 8
> Statement(s) could not be prepared.
> The select list matches the insert list exactly.
> The source and destination tables are located on two
> different (linked) servers.
> Both servers are running MSSQL 2000 SP3a.
> What am I doing wrong?
> Thank you.
>|||No, the statement is definitely OK, it works on other
servers, there's only one server it doesn't work on. And
it works if I run it locally, but it doesn't work when I
run it from another server.
Here are the statements:
declare @.IdSpatiu int,
@.IdTran int
set @.IdSpatiu = 15
set @.IdTran = 20
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select @.IdSpatiu, @.IdTran, Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9
If I replace "select @.IdSpatiu, @.IdTran, Numar"
with "select 15, 20, Numar", it works. It doesn't like
the variables in the select list.
If I'm connected to the server [ts-331], it works. But if
I'm connected to another server (to which ts-331 is
linked), it doesn't work.
It must be something about the ts-331 server. At first I
thought it must be the service pack, because it had no
service pack installed, but then I installed SP3a and the
problem persisted.
Thank you.
>--Original Message--
>Without seeing the actual insert statement we can only
guess. My guess
>would be you have a space in one of the column names.
>--|||It looks fine to me as well. Maybe there is something strange with the way
the Linked server is set up on that machine. Why do you want to do an
insert like that remotely anyway? It would be much cleaner if you had a
stored proc on the linked server that you call and pass in the 2 parameters.
That way the sp does not have to make any linked server calls at all when
actually doing the Insert.
--
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:1318b01c443b8$d05fa8b0$a401280a@.phx.gbl...
> No, the statement is definitely OK, it works on other
> servers, there's only one server it doesn't work on. And
> it works if I run it locally, but it doesn't work when I
> run it from another server.
> Here are the statements:
> declare @.IdSpatiu int,
> @.IdTran int
> set @.IdSpatiu = 15
> set @.IdTran = 20
> insert into [ts-331].Ploiesti.dbo.Factura
> (IdSpatiu, IdTran, Numar)
> select @.IdSpatiu, @.IdTran, Numar
> from [ts-331].Deva.dbo.Factura
> where IdTran = 11 and IdSpatiu = 9
> If I replace "select @.IdSpatiu, @.IdTran, Numar"
> with "select 15, 20, Numar", it works. It doesn't like
> the variables in the select list.
> If I'm connected to the server [ts-331], it works. But if
> I'm connected to another server (to which ts-331 is
> linked), it doesn't work.
> It must be something about the ts-331 server. At first I
> thought it must be the service pack, because it had no
> service pack installed, but then I installed SP3a and the
> problem persisted.
> Thank you.
> >--Original Message--
> >Without seeing the actual insert statement we can only
> guess. My guess
> >would be you have a space in one of the column names.
> >
> >--
>|||I had the same problem. Try this. I don't know why it works but it
does.
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select (select @.IdSpatiu), (select @.IdTran), Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9

Error - help

Hi,
Something is wrong with this:
CREATE PROCEDURE Admin_LogError
AS
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
INSERT INTO Admin_Errors
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
END
Error:
Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
The name "ErrorNumber" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.This is not how TSQL work. You first do a SELECT of what the function returns, returning that as a
result set to the client, then you refer to just the word ERRORNUMBER in the insert statement, where
that word has no correlation to the prior SELECT. Skip the SELECT, hand do the INSERT like:
INSERT INTO tblname(col1, col2, col3...) --Always specify column names
VALUES(ERRORNUMBER(), ERRORSEVERITY(), ...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Your error is here:
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
Hint: that should be ERROR_NUMBER() not ErrorNumber
The select statement does not assign values, it selects them.
If you need to store the error information so that you can use it later you
will need to do things a bit differently within your stored procedure
DECLARE @.errNum int
SELECT @.errNum = ERROR_NUMBER()
Now you can use @.errNum at other parts of the stored procedure.
--
Keith Kratochvil
<tootsuite@.gmail.com> wrote in message
news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Thanks all - this was an oversight on my part - I borrowed this code
from somewhere else - wrong context
Keith Kratochvil wrote:
> Your error is here:
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> Hint: that should be ERROR_NUMBER() not ErrorNumber
> The select statement does not assign values, it selects them.
> If you need to store the error information so that you can use it later you
> will need to do things a bit differently within your stored procedure
> DECLARE @.errNum int
> SELECT @.errNum = ERROR_NUMBER()
> Now you can use @.errNum at other parts of the stored procedure.
> --
> Keith Kratochvil
>
> <tootsuite@.gmail.com> wrote in message
> news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> > Hi,
> >
> > Something is wrong with this:
> >
> > CREATE PROCEDURE Admin_LogError
> > AS
> > BEGIN
> >
> > SELECT
> > ERROR_NUMBER() AS ErrorNumber,
> > ERROR_SEVERITY() AS ErrorSeverity,
> > ERROR_STATE() as ErrorState,
> > ERROR_PROCEDURE() as ErrorProcedure,
> > ERROR_LINE() as ErrorLine,
> > ERROR_MESSAGE() as ErrorMessage;
> >
> > INSERT INTO Admin_Errors
> > VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> > ErrorLine, ErrorMessage, GETDATE())
> >
> > END
> >
> > Error:
> >
> > Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> > The name "ErrorNumber" is not permitted in this context. Valid
> > expressions are constants, constant expressions, and (in some contexts)
> > variables. Column names are not permitted.
> >

Error - Application uses a value of the wrong type for the current operation

I have a sql statement using MS SQL Server as follows
SELECT TowerNumber, TowerNumber AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth) AND
(LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
When I use the same query and add group by, I get the error - Application
uses a value of the wrong type for the current operation
SELECT TowerNumber, COUNT(TowerNumber) AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth)
GROUP BY TowerNumber, LocationID, SystemID
HAVING (LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
How do I get past this error?
ThanksHi,
Have u used any variable with datatype uniqueidentifier.If yes then
change the datatype.
from
Doller|||Not using uniqueidentifier.
I do have a field that is an identity field but it is not in the query and I
have never run into this before.
Plus the first query does work the second does not.
"doller" <sufianarif@.gmail.com> wrote in message
news:1128420745.167906.89260@.o13g2000cwo.googlegroups.com...
> Hi,
> Have u used any variable with datatype uniqueidentifier.If yes then
> change the datatype.
> from
> Doller
>|||Hi Craig,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood adding GROUP BY will lead to the error
message "Application uses a value of the wrong type for the current
operation". If I have misunderstood your concern, please feel free to point
it out.
Based on my knowledge, this could be caused by various reasons and let
perform the troubleshooting step by step.
1. Check the length of parameters. The error could if the length of the
data passed to the parameter exceeds the
maximum size of the parameter or field.
2. Use the Profiler to trace what was inserted into SQL Server
3. If we replace all the parameters with real value and execute the
replaced statements in Query Analyzer, will it report the error message?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

error

hi i have one varchar field i m convarting that field to datetime in format of dd/mm/yyyy

but i m getting eror

select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,convert(varchar(10),cast(AdditionalField5 as datetime),103) 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

error is

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

change your bold area as follow as,

Convert(datetime, AdditionalField5,103) as 'ValDate'

|||

hi thanx for reply

now i m getting this error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||

Ok.. Expected one.. Use the following query..

SET DATEFORMAT dmy

Select ....
Case When IsDate(AdditionalField5)=1 Then

Convert(datetime, AdditionalField5,103)

Else NULL END as 'ValDate'

... From ....|||

hi i tried this

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime, AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

now getting error

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

|||Are you using any UNION on your query..?|||

no actuallu it is like this

if @.fileFormatId='83'
Begin

set @.s='select top '+cast( @.j as varchar(10)) +' CMS_Upload_Details_ID,Scheme_Code,DrCr,AdditionalField5 ''ValDate'',AdditionalField13 ''dept_slip'',AdditionalField14 ''dept_dt'',Cheque_No ''Instrm_No'',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 ''Drawer Name'' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status=''Pending'' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top '+ cast(@.i as varchar(10))+' ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = ''Pending '' and Format_ID ='+ convert(varchar(5),@.fileFormatId) +')and Compare_Status=''Pending'' and Format_ID ='+ convert(varchar(5),@.fileFormatId)
end

|||

Try the following query..

Code Snippet

SET DATEFORMAT dmy

if @.fileFormatId='83'
Begin

set @.s='select top '+cast( @.j as varchar(10)) +' CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime, AdditionalField5,103) Else NULL END as ''ValDate'',AdditionalField13 ''dept_slip'',AdditionalField14 ''dept_dt'',Cheque_No ''Instrm_No'',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 ''Drawer Name'' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status=''Pending'' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top '+ cast(@.i as varchar(10))+' ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = ''Pending '' and Format_ID ='+ convert(varchar(5),@.fileFormatId) +')and Compare_Status=''Pending'' and Format_ID ='+ convert(varchar(5),@.fileFormatId)
end

Exec(@.s)

|||

Hi

Can you just confirm the SQL you are now using for the bold section of your query, and also can we see a sample of the format of the data in AdditionalField5. Also can this field be null (or have non-date text in it) and if so what do you expect to happen in this case.

|||mani actually i tried this earlier and getting error ..see my 2nd previous post|||

mani after printing my query i m getting this one ..so i m running this query.

SET DATEFORMAT dmy
select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr,Case when IsDate(AdditionalField5)=1 Then Convert(datetime,AdditionalField5,103) Else NULL END as 'ValDate',AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83

|||

AdditionalField5 is varchar field ...and i m getting my date in this format

12/21/2006--this is is mm/dd/yyyy format i want to change this dd/mm/yyyy format

|||

I am really wondering....

IsDate() function first parse & validate your input before it parse so there is no issue on your bolded area..

Can you remove other date to character conversions to validate the query..

|||i didn't get u what i have to do now?|||hi yes this field can be null (or have non-date text in it) and so in this case how to d this?

Error

select showed_up =
case client_regist when (isnull(s.client_regist,'')='')
and (s.clin_appt_status like '%cancel%')
then 'cancel'
ELSE 'Unknown'END,count(p.lognum) as 'MonthlyNoshow'
from t_client p inner join t_schedule s on
p.lognum = s.log_no where s.Create_dt between '11/01/04'
and '11/05/04'
and p.ic_status like '4un%'
group by client_regist
I get this error when I run this code:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Can you help me identify my mistake'
Thanks.Try,
select
case client_regist
when (isnull(s.client_regist,'')='') and (s.clin_appt_status like
'%cancel%') then 'cancel'
ELSE 'Unknown'
END as showed_up,
count(p.lognum) as 'MonthlyNoshow'
from
t_client p
inner join
t_schedule s
on p.lognum = s.log_no
where
s.Create_dt between '11/01/04' and '11/05/04'
and p.ic_status like '4un%'
group by
client_regist
go
AMB
"Lyn" wrote:

> select showed_up =
> case client_regist when (isnull(s.client_regist,'')='')
> and (s.clin_appt_status like '%cancel%')
> then 'cancel'
> ELSE 'Unknown'END,count(p.lognum) as 'MonthlyNoshow'
> from t_client p inner join t_schedule s on
> p.lognum = s.log_no where s.Create_dt between '11/01/04'
> and '11/05/04'
> and p.ic_status like '4un%'
> group by client_regist
> I get this error when I run this code:
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '='.
> Can you help me identify my mistake'
> Thanks.
>|||Correction,
select
case
when (isnull(s.client_regist,'')='') and (s.clin_appt_status like
'%cancel%') then 'cancel'
ELSE 'Unknown'
END as showed_up,
count(p.lognum) as 'MonthlyNoshow'
from
t_client p
inner join
t_schedule s
on p.lognum = s.log_no
where
s.Create_dt between '11/01/04' and '11/05/04'
and p.ic_status like '4un%'
group by
client_regist
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case client_regist
> when (isnull(s.client_regist,'')='') and (s.clin_appt_status like
> '%cancel%') then 'cancel'
> ELSE 'Unknown'
> END as showed_up,
> count(p.lognum) as 'MonthlyNoshow'
> from
> t_client p
> inner join
> t_schedule s
> on p.lognum = s.log_no
> where
> s.Create_dt between '11/01/04' and '11/05/04'
> and p.ic_status like '4un%'
> group by
> client_regist
> go
>
> AMB
>
> "Lyn" wrote:
>

Error

I need to adjust the time according to the time zone I am in. Here is my query
select enddate EDT
new_time(enddate, 'EDT','PDT'
from project
Error
Server: Msg 195, Level 15, State 10, Line
'new_time' is not a recognized function nameIs new_time a user-defined function? If so, you need to qualify it with the
owner as in:
dbo.new_time(...)
There's no built-in function called "new_time()". Perhaps GETUTCDATE can be
helpful...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"trgain" <anonymous@.discussions.microsoft.com> wrote in message
news:9B2E3165-7B1E-4F47-98D2-64E33AFB16C7@.microsoft.com...
> I need to adjust the time according to the time zone I am in. Here is my
query:
> select enddate EDT,
> new_time(enddate, 'EDT','PDT')
> from project;
>
> Error:
> Server: Msg 195, Level 15, State 10, Line 2
> 'new_time' is not a recognized function name.
>
>

error

create #table
insert into #table
select A,b,c,d
from x,y,z
group by a,b,c,d
select *,(select max(d) from #table t
where t.a = d.a and t.b = d.b and
t.c like 'm%') as mx1
drop #table
go
when there is such data as t.c like 'm%',
it works OK but otherwise, i get error:
not supported,
details:
cannot obtain error message from server.
--
Sent by 3 from yahoo within field com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comHi alex.
Your script really looks off the map in a few ways:
(a) create #table.. (what about columns?)
(b) insert into #table (what about values?)
(c) select ... from x,y,z (no join clauses - likely a major cartesian
product)
(d) select *... where t.a = d.a ... (there's no table / derived table
aliased as "d", although you do have a column aliased as "d")
Please post a better attempt at communicating your query so we've got at
least some chance of establishing what you're trying to achieve / what's
going wrong.
Regards,
Greg Linwood
SQL Server MVP
"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066069486.1257385253@.[63.127.215.130]...
> create #table
> insert into #table
> select A,b,c,d
> from x,y,z
> group by a,b,c,d
> select *,(select max(d) from #table t
> where t.a = d.a and t.b = d.b and
> t.c like 'm%') as mx1
> drop #table
> go
>
> when there is such data as t.c like 'm%',
> it works OK but otherwise, i get error:
> not supported,
> details:
> cannot obtain error message from server.
>
>
> --
> Sent by 3 from yahoo within field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

Error

I have full text catalog on my table.
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, '"a.b.c.d*"')
query execute error;
"Msg 9937, Level 16, State 5, Line 1
Too many full-text columns or the full-text query is too complex to be
executed."
otherwise another query which doesn't have --"--
SELECT TOP 10 * FROM MyTable My(NOLOCK)
WHERE CONTAINS My.*, 'a.b.c.d*')
returns data normally.
what is the differences between them?
Is anyone can help me?
The wildcard is applied to each term in your query, so its a* and b* and c*
and d*. That is why you are getting the error. I can't see a good solution
to this.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>I have full text catalog on my table.
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, '"a.b.c.d*"')
> query execute error;
> "Msg 9937, Level 16, State 5, Line 1
> Too many full-text columns or the full-text query is too complex to be
> executed."
>
> otherwise another query which doesn't have --"--
> SELECT TOP 10 * FROM MyTable My(NOLOCK)
> WHERE CONTAINS My.*, 'a.b.c.d*')
> returns data normally.
> what is the differences between them?
> Is anyone can help me?
|||a.b.c.d is one term like u.s.a.(united states of america)
so i dont want to divide my term.
"Hilary Cotter" wrote:

> The wildcard is applied to each term in your query, so its a* and b* and c*
> and d*. That is why you are getting the error. I can't see a good solution
> to this.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Deniz Emeci" <Deniz Emeci@.discussions.microsoft.com> wrote in message
> news:176E6C5D-D092-42B7-A7AD-F501304CA6D7@.microsoft.com...
>
>

error

create #table
insert into #table
select A,b,c,d
from x,y,z
group by a,b,c,d

select *,(select max(d) from #table t
where t.a = d.a and t.b = d.b and
t.c like 'm%') as mx1
drop #table
go

when there is such data as t.c like 'm%',
it works OK but otherwise, i get error:
not supported,
details:
cannot obtain error message from server.

--
Sent by 3 from yahoo within field com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comHi alex.

Your script really looks off the map in a few ways:

(a) create #table.. (what about columns?)
(b) insert into #table (what about values?)
(c) select ... from x,y,z (no join clauses - likely a major cartesian
product)
(d) select *... where t.a = d.a ... (there's no table / derived table
aliased as "d", although you do have a column aliased as "d")

Please post a better attempt at communicating your query so we've got at
least some chance of establishing what you're trying to achieve / what's
going wrong.

Regards,
Greg Linwood
SQL Server MVP

"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066069486.1257385253@.[63.127.215.130]...
> create #table
> insert into #table
> select A,b,c,d
> from x,y,z
> group by a,b,c,d
> select *,(select max(d) from #table t
> where t.a = d.a and t.b = d.b and
> t.c like 'm%') as mx1
> drop #table
> go
>
> when there is such data as t.c like 'm%',
> it works OK but otherwise, i get error:
> not supported,
> details:
> cannot obtain error message from server.
>
>
> --
> Sent by 3 from yahoo within field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

error

create #table(A nvarchar(30), B nvarchar(20), c nvarchar(15), D int)
insert into #table
select A,b,c,d
from x,y,z
group by a,b,c,d

select *,(select max(d) from #table t
where t.a = d.a and t.b = d.b and
t.c like 'm%') as mx1
drop #table
go

when there is such data as t.c like 'm%',
it works OK but otherwise, i get error:
not supported,
details:
cannot obtain error message from server.

--
Sent by 3 from yahoo part of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comReplied in
microsoft.public.sqlserver.programming

--
David Portas
----
Please reply only to the newsgroup
--