error executing query

I have a database running on MSDE it was working fine. Recently when I try to select from the database I am getting the following error after a few records have been returned. All of the tables are giving this problem. Not sure what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.
Connection Broken
These are network problems - update your drivers for network card, check
cables and so on.
You can try also to run from command prompt "ping -t yourserver" and see if
there are lost packets.
Just in case do a db check from query analyzer - "DBCC checkdb"
Bojidar Alexandrov

error executing query

I have a database running on MSDE it was working fine. Recently when I try t
o select from the database I am getting the following error after a few reco
rds have been returned. All of the tables are giving this problem. Not sure
what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network err
or. Check your network documentation.
Connection Broken"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:E77B88A4-C231-4C30-B2D2-C4282A43A32F@.microsoft.com...
> I have a database running on MSDE it was working fine. Recently when I try
to select from the database I am getting the following error after a few
records have been returned. All of the tables are giving this problem. Not
sure what to do.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData[/c
olor]
(CheckforData()).
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Ch
eck
your network documentation.
> Connection Broken
>
It could be several things... first a question: were any changes made or
different software installed on the computer running MSDE?
If not, databases like MSDE and SQL Server can expose network issues such as
packet drops or interruptions. Also, rule out name resolution problems like
DNS or WINS.
Another angle, consider installing the latest version of MDAC on the client
computer and enabling both SQL Server utilities TCP/IP and Named Pipes.
Steve

error executing query

I have a database running on MSDE it was working fine. Recently when I try to select from the database I am getting the following error after a few records have been returned. All of the tables are giving this problem. Not sure what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.
Connection Broken
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:E77B88A4-C231-4C30-B2D2-C4282A43A32F@.microsoft.com...
> I have a database running on MSDE it was working fine. Recently when I try
to select from the database I am getting the following error after a few
records have been returned. All of the tables are giving this problem. Not
sure what to do.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
> Connection Broken
>
It could be several things... first a question: were any changes made or
different software installed on the computer running MSDE?
If not, databases like MSDE and SQL Server can expose network issues such as
packet drops or interruptions. Also, rule out name resolution problems like
DNS or WINS.
Another angle, consider installing the latest version of MDAC on the client
computer and enabling both SQL Server utilities TCP/IP and Named Pipes.
Steve
sql

Error executing non query: Timeout expired

I was having some errors from the webpage accessing the OLlinks table in the database.

Error executing non query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at admin_admOLEditLinks.selectData(String strID) in e:\wwwroot\home\admin\admOLEditLinks.aspx.cs:line 101
DateTime:5/23/2007 1:14:10 PM
Source:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3
ErrorMessage:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error:

SQL Execution Error.

Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks

Error Source: .Net SqlClient Data Provider

Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.

Open any other table works fine except this table only. Any help is much appreciated.

Looks like you are returning too many rows to the application? If you run the query in Query Analyzer how many rows does your SELECT statement retrieve?

|||

Okay, here's what I did. In MS SQL Server Management Studio, I click on the New Query icon and pasted in the "SELECT lnkID, linkFromID, linkToID FROM OLlinks" query and the query run fine with only 5 records showing. But if I right click on the OLlinks table in the Tables folder and choose Open Table then I do not see any records showing...and then for a long time I will receive the above error.

This problem does not occur everytime. It happens randomly.

|||

From the description you provided it looks that there is a lock which is there on the table.Might be you are running something in transaction which uses this table. Check in the Activity monitor and see the Lock by process.

|||

I don't think that is the case. I was able to open other tables fine. In addition, this problem only occurs randomly or appears to occur randomly.

And by the way, where is the Activity monitor?

|||

In the sql management studio... under the Management Node... It is.. I was talking about just a possibility and it would be nice to check if it is the case.

|||

There is nothing in the Management Node.

|||

Hi,

If it only occurs randomly, I assume that some other operation might be locking the table you're querying.

You can try to increase the timeout value of the command execution. To check if someone else is locking the table, you can use SQL Profiler.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Error during query execution

Hi,
Does anyone know how to solve the following error during
query execution?
Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to e:\sql_data\Microsoft SQL
Server\MSSQL$DEVA_AP04_SQL\log\SQL00048.dmp
*
****************************************
*******************
********************
*
* BEGIN STACK DUMP:
* 03/11/04 16:40:34 spid 57
*
* Exception Address = 00418209 (FastDBCSToUnicode +
000000B6 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000015
* Input Buffer 2824 bytes -
* SELECT event_time as [Alert Time],
combo_orders.firm_name AS [Firm], dbo
* .GetTradingSymbol(underlying_id) AS [Underlying],
combo_orders.order_pri
* ce AS [Price], combo_orders.original_quantity AS
[Original Quantity], tr
* aded_quantity AS [Traded Quantity],
combo_orders.current_quantity AS [Cu
* rrrent Quantity], order_begin_time as [Time of Entry],
dbo.GetOrderStatu
* sText(combo_orders.current_order_status) AS [Current
Status], dbo.GetCom
* bexAlertText(reason_code) as [Alert Type],
dbo.GetCombexAlertAcknowledge
* dText(acknowledged) as [Acknowledged], login_id as [ISE
Analyst], combex
* _alerts.logb_order_number as [LogB Order Number],
item_id as [Item ID],
* combex_alert_id as [Combex Alert ID] FROM combex_alerts
WITH (NOLOCK), c
* ombo_orders WITH (NOLOCK) WHERE
combex_alerts.logb_order_number = combo_
* orders.logb_order_number AND reason_code IN
(13,7,2,3,9,11,12,10,8,4,6,1
* ,14,5,15,22,20,18,21,19,16,17) AND display_on_off = 1
AND combex_alerts.
* market_date ='3/11/2004 12:00:00 AM' AND
combo_orders.logb_order_number
* <> 999 AND (combo_orders.sequence_number = (SELECT MAX
(sequence_number)
* FROM combo_orders co2 WITH (NOLOCK) WHERE
co2.logb_order_number = combo
* _orders.logb_order_number AND co2.logb_order_number <>
999 AND co2.mar
* ket_date = combo_orders.market_date AND
co2.market_date = '3/11/2004 12
* :00:00 AM' AND co2.underlying_id =
combo_orders.underlying_id)) ORDER B
* Y [Acknowledged], [Combex Alert ID] DESC
.
.
.
Error: 0, Severity: 19, State: 0
language_exec: Process 57 generated an access violation.
SQL Server is terminating this process..
Regards,
JennyJenny,
Access violations are more often than not SQL Server bugs. The first
thing to do is make sure you are up-to-date on service packs. What is
the result you get when you run select @.@.version? The one solution I
can find in newsgroups to an access violation referring to
FastDBCSToUnicode was solved by the installation of SQL Server 2000
service pack 3.
SK
Jenny wrote:

>Hi,
>Does anyone know how to solve the following error during
>query execution?
>Using 'dbghelp.dll' version '4.0.5'
>*Stack Dump being sent to e:\sql_data\Microsoft SQL
>Server\MSSQL$DEVA_AP04_SQL\log\SQL00048.dmp
>*
> ****************************************
*******************
>********************
>*
>* BEGIN STACK DUMP:
>* 03/11/04 16:40:34 spid 57
>*
>* Exception Address = 00418209 (FastDBCSToUnicode +
>000000B6 Line 0+00000000)
>* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
>* Access Violation occurred reading address 00000015
>* Input Buffer 2824 bytes -
>* SELECT event_time as [Alert Time],
>combo_orders.firm_name AS [Firm], dbo
>* .GetTradingSymbol(underlying_id) AS [Underlying],
>combo_orders.order_pri
>* ce AS [Price], combo_orders.original_quantity AS
>[Original Quantity], tr
>* aded_quantity AS [Traded Quantity],
>combo_orders.current_quantity AS [Cu
>* rrrent Quantity], order_begin_time as [Time of Entry],
>dbo.GetOrderStatu
>* sText(combo_orders.current_order_status) AS [Current
>Status], dbo.GetCom
>* bexAlertText(reason_code) as [Alert Type],
>dbo.GetCombexAlertAcknowledge
>* dText(acknowledged) as [Acknowledged], login_id as [ISE
>Analyst], combex
>* _alerts.logb_order_number as [LogB Order Number],
>item_id as [Item ID],
>* combex_alert_id as [Combex Alert ID] FROM combex_alerts
>WITH (NOLOCK), c
>* ombo_orders WITH (NOLOCK) WHERE
>combex_alerts.logb_order_number = combo_
>* orders.logb_order_number AND reason_code IN
>(13,7,2,3,9,11,12,10,8,4,6,1
>* ,14,5,15,22,20,18,21,19,16,17) AND display_on_off = 1
>AND combex_alerts.
>* market_date ='3/11/2004 12:00:00 AM' AND
>combo_orders.logb_order_number
>* <> 999 AND (combo_orders.sequence_number = (SELECT MAX
>(sequence_number)
>* FROM combo_orders co2 WITH (NOLOCK) WHERE
>co2.logb_order_number = combo
>* _orders.logb_order_number AND co2.logb_order_number <>
>999 AND co2.mar
>* ket_date = combo_orders.market_date AND
>co2.market_date = '3/11/2004 12
>* :00:00 AM' AND co2.underlying_id =
>combo_orders.underlying_id)) ORDER B
>* Y [Acknowledged], [Combex Alert ID] DESC
>.
>.
>.
>Error: 0, Severity: 19, State: 0
>language_exec: Process 57 generated an access violation.
>SQL Server is terminating this process..
>
>Regards,
>Jenny
>
>

error converting datatypes

Hello,

Firstly, i need to work out why I cannot change my datatypes(please see query)

Code Snippet

SELECT * FROM (

SELECT top 10

ref,

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM (

SELECT 1 seq,

'ref' ref,

'RecordDate' RecordDate,

'TransactionID' TransactionID,

'TransactionStatus' TransactionStatus,

'StatusChangedTimeStamp' StatusChangedTimeStamp,

'PartyTransactionStatus' PartyTransactionStatus,

'BadDeliveryReason' BadDeliveryReason,

'TradingDaysRef' TradingDaysRef

UNION ALL

SELECT 2 seq,

cast(ref as bigint),

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM dbo.ParticipantTradeStatusChange

) x

order by seq, RecordDate

) y

The error returned is:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

The reason for me doing this, is exporting both column names & data to a xls file.

Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do

SELECT top 10 * FROM (

SELECT *

It falls over and dies

Help much appreciated

thanks,

i

The data -- literals -- that you have above your unions are not implicitly compatible with what is below the union. Look at this example:

Code Snippet

select 'Header'
union all
select 2

/*
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Header' to a column of data type int.
*/

I am basically getting the same error. You may need to explicitly declare the datatype of the lower part of the union to have them go as varchar -- maybe like:

Code Snippet

select 'Header' as Data
union all
select cast (2 as varchar)

/*
Data
Header
2
*/

|||

i've done a dirty workaround..

simply used cast(columnname as varchar(4000))

did the trick and i have my bcp file with headings

all todo now is make it run faster...

Error BC30654

I get the following error ... Any advice?
The value expression for the query parameter â'@.JIDsâ' contains an error:
[BC30654] 'Return' statement in a Function or a Get must return a value.
--
Frederick Volking
System Architect
SC Dept of Juvenile JusticeIt looks like the parameter gets its value from a function...
Please post the function code...
--
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
"FlashMerlot" <FlashMerlot@.discussions.microsoft.com> wrote in message
news:5F5D73EC-6F29-4809-A1A2-6506E0B641EA@.microsoft.com...
>I get the following error ... Any advice?
> The value expression for the query parameter '@.JIDs' contains an error:
> [BC30654] 'Return' statement in a Function or a Get must return a value.
> --
> Frederick Volking
> System Architect
> SC Dept of Juvenile Justice

Error at parse query

Hello

I have the following:

One data flow task in the control flow
In this data flow task I have an OLE DB Source with the following sql command

'.... where cc = ?'

Then I click on the Parameters button and I map the ? to the right Parameter

Parameter0 = User::var_cc

When I click on parse query I get the following message :

"Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

Anyone has an idea?
ThxThis is a known issue. Parse Query does not know how to use the parameters you mapped.

If you do not get any error after clicking OK, your query should be fine and you can safely ignore the error from Parse Query.

Error after migrating to SQL Server 2005

I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.

ERROR: Incorrect syntax near the keyword 'LEFT'

MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;

SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)

Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp

I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.

|||SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL

MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;

Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*
|||

Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:

http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx

You will get more help on the SSMA related issues there.

|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.

I need help with another query:

SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));

Error on lines : Line 11 ans 20
|||I tried aliasingqryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]

But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.

ACCESS QUERY:

UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));

STORED PROCEDURE:

CREATE PROCEDURE UpdChkNum

AS
BEGIN
SET NOCOUNT ON;

/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float

/*SELECT stmts for procedure*/
WHILE
BEGIN

SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum

UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto

and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))

BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END

fetch next
END
END
GO

Error after migrating to SQL Server 2005

I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.

ERROR: Incorrect syntax near the keyword 'LEFT'

MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;

SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)

Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp

I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.

|||SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL

MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;

Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*|||

Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:

http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx

You will get more help on the SSMA related issues there.

|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.

I need help with another query:

SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));

Error on lines : Line 11 ans 20|||I tried aliasing qryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]

But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.

ACCESS QUERY:

UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));

STORED PROCEDURE:

CREATE PROCEDURE UpdChkNum

AS
BEGIN
SET NOCOUNT ON;

/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float

/*SELECT stmts for procedure*/
WHILE
BEGIN

SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum

UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto

and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))

BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END

fetch next
END
END
GO