Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

query

Hi,

select * from test a where test.col1 = a;

The above query will not work and it will throw the error 'The column prefix 'test' does not match with a table name or alias name used in the query'. i.e if alias defined for a table, then the columns can be qualified only using using the alias. Is it true ? Is it true in all the places like "Group By', 'Order' , 'SELECT list' etc. Is that true in all databases, is it ANSI standard ?

Please advice,

Thanks,
MiraJhi

from BOL
"If an alias is assigned to a table, all explicit references to the table in the Transact-SQL statement must use the alias, not the table name."

Query

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/verslag/MIncSum4.asp, line 9
This is the error i get when running a query.shown below:
line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID = V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit = V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
(V_Transaksie.Maand =" & request.form("Maand") & ") AND
(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
line8:set rstMain = CreateObject("ADODB.Recordset")
9: rstMain.Open sql, _
10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,4
Any idea what might be cuasing the error?One thing you could try is : extract the sql statement , hard code in some
some values and try to execute in SQL Query Analyzer
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"amatuer" <njoosub@.gmail.com> wrote in message
news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Line 1: Incorrect syntax near '.'.
> /verslag/MIncSum4.asp, line 9
> This is the error i get when running a query.shown below:
> line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> line8:set rstMain = CreateObject("ADODB.Recordset")
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,4
> Any idea what might be cuasing the error?
>|||I tried that,it gives the same error in sql server as well.
any other ideas?
Jack Vamvas wrote:
> One thing you could try is : extract the sql statement , hard code in some
> some values and try to execute in SQL Query Analyzer
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> > Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> > Line 1: Incorrect syntax near '.'.
> >
> > /verslag/MIncSum4.asp, line 9
> >
> > This is the error i get when running a query.shown below:
> >
> > line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> > V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> > V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => > V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => > V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> > (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> > (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> > (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> > (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> > line8:set rstMain = CreateObject("ADODB.Recordset")
> > 9: rstMain.Open sql, _
> > 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> > ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> > Source=172.16.4.180",1,4
> >
> > Any idea what might be cuasing the error?
> >|||On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
>Microsoft OLE DB Provider for SQL Server error '80040e14'
>Line 1: Incorrect syntax near '.'.
>/verslag/MIncSum4.asp, line 9
>This is the error i get when running a query.shown below:
>line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
>V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
>V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =>V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =>V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
>(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
>(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
>(V_Transaksie.Maand =" & request.form("Maand") & ") AND
>(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
>line8:set rstMain = CreateObject("ADODB.Recordset")
>9: rstMain.Open sql, _
>10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
>ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
>Source=172.16.4.180",1,4
>Any idea what might be cuasing the error?
Hi amatuer,
The SUM function needs parentheses. So for instance, change
> SELECT Sum V_Transaksie.Aantal As Total,
to
SELECT Sum(V_Transaksie.Aantal) As Total,
And so on for the other totals in the select list.
--
Hugo Kornelis, SQL Server MVP|||that helped. thanx Hugo.
Although my query still nt working the way i want it to...lol
Hugo Kornelis wrote:
> On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
> >Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> >Line 1: Incorrect syntax near '.'.
> >
> >/verslag/MIncSum4.asp, line 9
> >
> >This is the error i get when running a query.shown below:
> >
> >line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> >V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> >V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => >V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => >V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> >(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> >(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> >(V_Transaksie.Maand =" & request.form("Maand") & ") AND
> >(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> >line8:set rstMain = CreateObject("ADODB.Recordset")
> >9: rstMain.Open sql, _
> >10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> >ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> >Source=172.16.4.180",1,4
> >
> >Any idea what might be cuasing the error?
> Hi amatuer,
> The SUM function needs parentheses. So for instance, change
> > SELECT Sum V_Transaksie.Aantal As Total,
> to
> SELECT Sum(V_Transaksie.Aantal) As Total,
> And so on for the other totals in the select list.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
>that helped. thanx Hugo.
>Although my query still nt working the way i want it to...lol
Hi amatuer,
I'll be happy to help you further (as I'm sure several others are as
well). But to do that, we do need some more information:
- how do your tables look (please post CREATE TABLE statements,
including all constraints, properties, and indexes. You may omit
irrelevant columns);
- what does your data look like (please post INSERT statements with a
few well-chosen rows of sample data the nicely illustrate your problem);
- how should the results look like (please post expected results, for
example in tabular format - for readability, I suggest using a
fixed-size font and using spaces rather than tabs to layout the table).
Also see www.aspfaq.com/5006 for more tips on information to provide
when asking for help.
--
Hugo Kornelis, SQL Server MVP|||Hugo, i appreciate your willingness to help,but unfortunately i am nt
permitted to give out valuable info.so it seems i will hav to figure it
out by myself.
If i hav any othr questions i will let u know.
Thank you for ur time,it is highly appreciated
Hugo Kornelis wrote:
> On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
> >that helped. thanx Hugo.
> >Although my query still nt working the way i want it to...lol
> Hi amatuer,
> I'll be happy to help you further (as I'm sure several others are as
> well). But to do that, we do need some more information:
> - how do your tables look (please post CREATE TABLE statements,
> including all constraints, properties, and indexes. You may omit
> irrelevant columns);
> - what does your data look like (please post INSERT statements with a
> few well-chosen rows of sample data the nicely illustrate your problem);
> - how should the results look like (please post expected results, for
> example in tabular format - for readability, I suggest using a
> fixed-size font and using spaces rather than tabs to layout the table).
> Also see www.aspfaq.com/5006 for more tips on information to provide
> when asking for help.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
>Hugo, i appreciate your willingness to help,but unfortunately i am nt
>permitted to give out valuable info.so it seems i will hav to figure it
>out by myself.
>If i hav any othr questions i will let u know.
>Thank you for ur time,it is highly appreciated
Hi amatuer,
That's a common requirement. One way to work aroound that is to change
the data (and possibly even the table and column names), but keep the
structure intact. That is a bit more work, of course, but it can be
worth it if you're really stuck.
On the other hand, you learn more by figuring it out for yourself :-))
--
Hugo Kornelis, SQL Server MVP|||thanx Hugo, but my prob sorted.took me a while bt i gt it to do wat i
wanted it to do.
Hugo Kornelis wrote:
> On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
> >Hugo, i appreciate your willingness to help,but unfortunately i am nt
> >permitted to give out valuable info.so it seems i will hav to figure it
> >out by myself.
> >If i hav any othr questions i will let u know.
> >Thank you for ur time,it is highly appreciated
> Hi amatuer,
> That's a common requirement. One way to work aroound that is to change
> the data (and possibly even the table and column names), but keep the
> structure intact. That is a bit more work, of course, but it can be
> worth it if you're really stuck.
> On the other hand, you learn more by figuring it out for yourself :-))
> --
> Hugo Kornelis, SQL Server MVP

QUERY

QUERY
select enddate EDT
NEW_TIME(ENDDATE, 'EDT','PDT'
FROM PROJEC
ERROR
Server: Msg 195, Level 15, State 10, Line
'NEW_TIME' is not a recognized function nameRMSANDS (whoever you are)
You are posting many functions that are not defined in SQL-Server. You
are probably converting code from a different RDBMS.
You should really explain what each function is supposed to do. In
addition to that, it wouldn't hurt to take a look at the SQL-Server
Books Online (a download link has been posted by Ray Higdon) to see what
functions SQL-Server does support.
Gert-Jan
RMSANDS wrote:
> 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.
--
(Please reply only to the newsgroup)

QUERY

Using sql 2000 here is my table, query, and error
Table
create table projec
(task varchar(14) not null
startdate datetime
enddate datetime)
Query
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH
FROM PROJEC
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE
Error
Server: Msg 195, Level 15, State 10, Line
'ADD_MONTHS' is not a recognized function nameI think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

Query

Using sql 2000
Query
SELECT enddate,last_day(enddate
FROM Project;
Error
Server: Msg 195, Level 15, State 10, Line
'last_day' is not a recognized function nameWhat is last_day? Is enddate the column and you want the max value? If so,
SELECT max(enddate)
FROM Project
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"rmsands" <anonymous@.discussions.microsoft.com> wrote in message
news:43EAC36D-FC2A-4616-B2EE-2F9EE97E915A@.microsoft.com...
> Using sql 2000:
> Query:
> SELECT enddate,last_day(enddate)
> FROM Project;
> Error:
> Server: Msg 195, Level 15, State 10, Line 1
> 'last_day' is not a recognized function name.
>
>|||If your last_day is a user defined function you need to put the owner in
front of it
SELECT enddate,dbo.last_day(enddate)
FROM Project;
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:uaj1z9X#DHA.3816@.tk2msftngp13.phx.gbl...
> What is last_day? Is enddate the column and you want the max value? If so,
> SELECT max(enddate)
> FROM Project
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "rmsands" <anonymous@.discussions.microsoft.com> wrote in message
> news:43EAC36D-FC2A-4616-B2EE-2F9EE97E915A@.microsoft.com...
> > Using sql 2000:
> >
> > Query:
> >
> > SELECT enddate,last_day(enddate)
> > FROM Project;
> >
> > Error:
> >
> > Server: Msg 195, Level 15, State 10, Line 1
> > 'last_day' is not a recognized function name.
> >
> >
> >
>|||I WANT TO KNOW WHAT THE LAST DAY OF THE MONTH IS IN THE COLUMN ENDDATE.|||Date of the last day of the month of Enddate:
SELECT DATEADD(MONTH,1,DATEADD(DAY,-DAY(enddate),enddate))
FROM Project
Last day of the month of Enddate:
SELECT DAY(DATEADD(MONTH,1,DATEADD(DAY,-DAY(enddate),enddate)))
FROM Project
--
David Portas
SQL Server MVP
--

QUERY

Using sql 2000 here is my table, query, and error.
Table:
create table project
(task varchar(14) not null,
startdate datetime,
enddate datetime);
Query:
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
FROM PROJECT
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
Error:
Server: Msg 195, Level 15, State 10, Line 3
'ADD_MONTHS' is not a recognized function name.I think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

Query

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/verslag/MIncSum4.asp, line 9
This is the error i get when running a query.shown below:
line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
(V_Transaksie.Maand =" & request.form("Maand") & ") AND
(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
line8:set rstMain = CreateObject("ADODB.Recordset")
9: rstMain.Open sql, _
10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,4
Any idea what might be cuasing the error?One thing you could try is : extract the sql statement , hard code in some
some values and try to execute in SQL Query Analyzer
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"amatuer" <njoosub@.gmail.com> wrote in message
news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Line 1: Incorrect syntax near '.'.
> /verslag/MIncSum4.asp, line 9
> This is the error i get when running a query.shown below:
> line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
> V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
> V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> line8:set rstMain = CreateObject("ADODB.Recordset")
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,4
> Any idea what might be cuasing the error?
>|||I tried that,it gives the same error in sql server as well.
any other ideas?
Jack Vamvas wrote:[vbcol=seagreen]
> One thing you could try is : extract the sql statement , hard code in some
> some values and try to execute in SQL Query Analyzer
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:436868
On 7 Jun 2006 01:28:19 -0700, amatuer wrote:

>Microsoft OLE DB Provider for SQL Server error '80040e14'
>Line 1: Incorrect syntax near '.'.
>/verslag/MIncSum4.asp, line 9
>This is the error i get when running a query.shown below:
>line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
>V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
>V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
>V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
>V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
>(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
>(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
>(V_Transaksie.Maand =" & request.form("Maand") & ") AND
>(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
>line8:set rstMain = CreateObject("ADODB.Recordset")
>9: rstMain.Open sql, _
>10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
>ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
>Source=172.16.4.180",1,4
>Any idea what might be cuasing the error?
Hi amatuer,
The SUM function needs parentheses. So for instance, change

> SELECT Sum V_Transaksie.Aantal As Total,
to
SELECT Sum(V_Transaksie.Aantal) As Total,
And so on for the other totals in the select list.
Hugo Kornelis, SQL Server MVP|||that helped. thanx Hugo.
Although my query still nt working the way i want it to...lol
Hugo Kornelis wrote:
> On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
>
> Hi amatuer,
> The SUM function needs parentheses. So for instance, change
>
> to
> SELECT Sum(V_Transaksie.Aantal) As Total,
> And so on for the other totals in the select list.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 02:36:57 -0700, amatuer wrote:

>that helped. thanx Hugo.
>Although my query still nt working the way i want it to...lol
Hi amatuer,
I'll be happy to help you further (as I'm sure several others are as
well). But to do that, we do need some more information:
- how do your tables look (please post CREATE TABLE statements,
including all constraints, properties, and indexes. You may omit
irrelevant columns);
- what does your data look like (please post INSERT statements with a
few well-chosen rows of sample data the nicely illustrate your problem);
- how should the results look like (please post expected results, for
example in tabular format - for readability, I suggest using a
fixed-size font and using spaces rather than tabs to layout the table).
Also see www.aspfaq.com/5006 for more tips on information to provide
when asking for help.
Hugo Kornelis, SQL Server MVP|||Hugo, i appreciate your willingness to help,but unfortunately i am nt
permitted to give out valuable info.so it seems i will hav to figure it
out by myself.
If i hav any othr questions i will let u know.
Thank you for ur time,it is highly appreciated
Hugo Kornelis wrote:
> On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
>
> Hi amatuer,
> I'll be happy to help you further (as I'm sure several others are as
> well). But to do that, we do need some more information:
> - how do your tables look (please post CREATE TABLE statements,
> including all constraints, properties, and indexes. You may omit
> irrelevant columns);
> - what does your data look like (please post INSERT statements with a
> few well-chosen rows of sample data the nicely illustrate your problem);
> - how should the results look like (please post expected results, for
> example in tabular format - for readability, I suggest using a
> fixed-size font and using spaces rather than tabs to layout the table).
> Also see www.aspfaq.com/5006 for more tips on information to provide
> when asking for help.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 04:42:01 -0700, amatuer wrote:

>Hugo, i appreciate your willingness to help,but unfortunately i am nt
>permitted to give out valuable info.so it seems i will hav to figure it
>out by myself.
>If i hav any othr questions i will let u know.
>Thank you for ur time,it is highly appreciated
Hi amatuer,
That's a common requirement. One way to work aroound that is to change
the data (and possibly even the table and column names), but keep the
structure intact. That is a bit more work, of course, but it can be
worth it if you're really stuck.
On the other hand, you learn more by figuring it out for yourself :-))
Hugo Kornelis, SQL Server MVP|||thanx Hugo, but my prob sorted.took me a while bt i gt it to do wat i
wanted it to do.
Hugo Kornelis wrote:
> On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
>
> Hi amatuer,
> That's a common requirement. One way to work aroound that is to change
> the data (and possibly even the table and column names), but keep the
> structure intact. That is a bit more work, of course, but it can be
> worth it if you're really stuck.
> On the other hand, you learn more by figuring it out for yourself :-))
> --
> Hugo Kornelis, SQL Server MVP

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?

The error text is:

This wizard will close because it encountered the following error: (Microsoft SQL Server)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

===================================

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

Program Location:

at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)

Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.

HTH

|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?

The error text is:

This wizard will close because it encountered the following error: (Microsoft SQL Server)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

===================================

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

Program Location:

at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)

Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.

HTH

|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

QA simple question

Dariusz Tomon,
Go to the messages window and double click over the error message. QA will
take you directly to the line where the error is.
AMB
"Dariusz Tomon" wrote:

> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So debuggin
g
> where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>Hi Darek
You can double-click on the error message, and QA will take you to the line
in the Query Window where the error is.
HTH
Kalen Delaney, SQL Server MVP
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So
> debugging where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:95A3E7C1-AAAA-46B6-BEAC-74AE111023E4@.microsoft.com...
> Dariusz Tomon,
> Go to the messages window and double click over the error message. QA will
> take you directly to the line where the error is.
>
> AMB
Thank you - I did not know that it's so simple [vbcol=seagreen]
>
> "Dariusz Tomon" wrote:
>|||Hi
I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
"Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
Incorrect syntax near the keyword 'select'."
I wonder how to force it to show correct line. In the example above I
checked line 45 and there is no "select" statement.
I suspect that it ignores lines with comments and blank lines. So debugging
where the error occurs is very difficult.
Best Regards
(and sorry for the simplicity of the question)
Darek T.|||Dariusz Tomon,
Go to the messages window and double click over the error message. QA will
take you directly to the line where the error is.
AMB
"Dariusz Tomon" wrote:

> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So debuggin
g
> where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>|||Hi Darek
You can double-click on the error message, and QA will take you to the line
in the Query Window where the error is.
HTH
Kalen Delaney, SQL Server MVP
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So
> debugging where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:95A3E7C1-AAAA-46B6-BEAC-74AE111023E4@.microsoft.com...
> Dariusz Tomon,
> Go to the messages window and double click over the error message. QA will
> take you directly to the line where the error is.
>
> AMB
Thank you - I did not know that it's so simple [vbcol=seagreen]
>
> "Dariusz Tomon" wrote:
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umPIQL8jGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi Darek
> You can double-click on the error message, and QA will take you to the
line
> in the Query Window where the error is.
Dang, in all these years, I never realized that.
Thanks.

> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umPIQL8jGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi Darek
> You can double-click on the error message, and QA will take you to the
line
> in the Query Window where the error is.
Dang, in all these years, I never realized that.
Thanks.

> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
>

QA Debugger

I need help resolving a problem our developers are having with Query Analyzer
debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
disabled for connection 63." when they attempt to debug stored procedures.
Does anyone have any suggestions?You want to start with the steps in the article in books
online for: Troubleshooting the Transact-SQL Debugger
In addition, if you are using SQL Server 2000 SP 3 you will
want to refer to the following article:
The T-SQL Debugger is turned off by default for earlier
clients after you install SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=328151
-Sue
On Fri, 14 Jan 2005 06:19:10 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>I need help resolving a problem our developers are having with Query Analyzer
>debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
>sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
>on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
>as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
>disabled for connection 63." when they attempt to debug stored procedures.
>Does anyone have any suggestions?
>

QA Debugger

I need help resolving a problem our developers are having with Query Analyze
r
debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect
to debugger
on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
disabled for connection 63." when they attempt to debug stored procedures.
Does anyone have any suggestions?You want to start with the steps in the article in books
online for: Troubleshooting the Transact-SQL Debugger
In addition, if you are using SQL Server 2000 SP 3 you will
want to refer to the following article:
The T-SQL Debugger is turned off by default for earlier
clients after you install SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=328151
-Sue
On Fri, 14 Jan 2005 06:19:10 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:

>I need help resolving a problem our developers are having with Query Analyz
er
>debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
>sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connec
t to debugger
>on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
>as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
>disabled for connection 63." when they attempt to debug stored procedures.
>Does anyone have any suggestions?
>

QA Debugger

I need help resolving a problem our developers are having with Query Analyzer
debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
disabled for connection 63." when they attempt to debug stored procedures.
Does anyone have any suggestions?
You want to start with the steps in the article in books
online for: Troubleshooting the Transact-SQL Debugger
In addition, if you are using SQL Server 2000 SP 3 you will
want to refer to the following article:
The T-SQL Debugger is turned off by default for earlier
clients after you install SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=328151
-Sue
On Fri, 14 Jan 2005 06:19:10 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:

>I need help resolving a problem our developers are having with Query Analyzer
>debugger. They get an error "Server: Msg 504, Level 16, State 1, Procedure
>sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
>on SALESTEMP (Error = 0x800706ba). Ensure that client-side components, such
>as SQLDBREG.EXE, are installed and registered on <local pc name>. Debugging
>disabled for connection 63." when they attempt to debug stored procedures.
>Does anyone have any suggestions?
>

Q3: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q2: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?
Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
> website running on a windows 2000sp3 tries to write data to sql 2000 server
> running on windows 2003 server. Could this be the cause? Is there anything
> else that could be causing this problem?

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed whe
n a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed w
hen a
> website running on a windows 2000sp3 tries to write data to sql 2000 serve
r
> running on windows 2003 server. Could this be the cause? Is there anythin
g
> else that could be causing this problem?

q; using RAISERROR

In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
stored procedure, In case of an error in the stored procedure I need to
return an exception to application, will RAISERROR in stored procedure
accomplish that?JIM.H.,
Yes.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"JIM.H." wrote:

> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?|||For a full discussion of RAISERROR, refer to Books on Line.
You can use RAISERROR to 'thow' an error condition to the calling process.
Normally, you would have a condition to check, then use a BEGIN-END block to
both RAISERROR and then RETURN to the calling process. Without the RETURN,
any code after the RAISERROR will also execute.
i.e.,
IF {condition to check}
BEGIN
RAISERROR ("Error Occurred", 16, 1)
RETURN
END
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:116D7CFD-FE3D-432B-B5DA-9A0CD93F744A@.microsoft.com...
> In my Windows application I use sqlCmd.ExecuteNonQuery() to execute the
> stored procedure, In case of an error in the stored procedure I need to
> return an exception to application, will RAISERROR in stored procedure
> accomplish that?