I am trying to connect Microsoft Access Database from PowerBuilder(ODBC),
when execute a select clause, get an error SQLSTATE = 01S01(Row errors), bu
t
trying open the database from Microsoft Access 2000 tools , the row is very
good. I am puzzled this error. Someone help me to this problem, pleaseHi
This is a SQL Server newsgroup a better place to post this would be a
powerbuilder or Access newsgroup. Searching google for 01S01 returned quite
a
few hits
http://tinyurl.com/79ru9
this one looked promising
http://tinyurl.com/cr6u7
and indicates that is pssibly masking the real error which in this case the
truncation of a string.
John
"guo-feng lui via webservertalk.com" wrote:
> I am trying to connect Microsoft Access Database from PowerBuilder(ODBC
),
> when execute a select clause, get an error SQLSTATE = 01S01(Row errors),
but
> trying open the database from Microsoft Access 2000 tools , the row is ver
y
> good. I am puzzled this error. Someone help me to this problem, please
>sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 23, 2012
power and ^
Hi,
I am try to replicate an access query that contains the following syntax
173.3^2 which in access = 17.33
the access help describes the ^ operator as Used to raise a number to the power of an exponent.
I am try to replicate an access query that contains the following syntax
173.3^2 which in access = 17.33
the access help describes the ^ operator as Used to raise a number to the power of an exponent.
I tried to replicate this with the power function
power(173.3,2) = 33043.29
so i guess power is not the right way to replicate this.
can any tell me how i can replicate this correctly ?Access also return the same result as power() function. What is the logic behind here.|||It turns out that it was the way that access priorities it's mathematic operations,
it did a ^ before a division.
so 173.3/10^2 results in 173.3 /100.
i had written the t-sql code as power(173.3/10,2) when it should have been 173.3/ power(10,2)
so my own fault did not read the code right.sql
Tuesday, March 20, 2012
Post SP1 troubles
Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!
I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!
I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
Post SP1 troubles
Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
Post SP1 troubles
Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
A perfectly working form with subform from two inner joined tables now is
giving me annoying messages when inserting records in the master table.
"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default of NULL for the timestamp column."
There's nothing done programmatically, all data bound to form's controls
(obvoiusly not the timestamp field).
Help!I would use Profiler to determine the exact SQL being passed to the server.
The message is probably correct and the app is probably trying to do what
the message says it is preventing.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:10guprqnccn2g2b@.news.supernews.com...
> Access 2003 + Sp1 + adp + ADO 2.8 + SQL Server 2000.
> A perfectly working form with subform from two inner joined tables now is
> giving me annoying messages when inserting records in the master table.
> "Cannot insert a non-null value into a timestamp column. Use INSERT with a
> column list or with a default of NULL for the timestamp column."
> There's nothing done programmatically, all data bound to form's controls
> (obvoiusly not the timestamp field).
> Help!
>
Friday, March 9, 2012
possible to link 2 stored procedures to produce only 1 recordset?
Hello everybody!
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all
Would it be possible to migrate the MS Access 2000 to MS SQL Server
2000?
My application is using MS Access 2000 as database and as user
interface such as forms. Now, I want to migrate the backend database
from MS Access 2000 to MS SQL Server 2000. However, I want to keep the
MS Access 2000 interface. Would it be possible?
If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?
Which tool I can use to do the migration? Upsizing wizard or exporting
the Access database and then importing it to the SQL server?
Thanks in advance
Cheers
BonThis is somewhat easy.
Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.|||db55 wrote:
> This is somewhat easy.
> Basically you need to port or transfer your Access tables to a SQL DB
> and then link the Access DB/App. to the SQL tables on the SQL Server.
> If you keep the table names the same, the code etc shouldn't need
> altering, bu I would test it first.
If only it were that easy :-)|||On Tue, 01 Nov 2005 08:09:08 +0000, Trevor Best <nospam@.localhost.invalid>
wrote:
>db55 wrote:
>> This is somewhat easy.
>>
>> Basically you need to port or transfer your Access tables to a SQL DB
>> and then link the Access DB/App. to the SQL tables on the SQL Server.
>> If you keep the table names the same, the code etc shouldn't need
>> altering, bu I would test it first.
>>
>If only it were that easy :-)
What Trevor is getting at is that it's easy to take an average Access app,
change the tables into links to tables on the server, and have it technically
function. Unfortunately, many of the design decisions commonly built with a
JET back end are not appropriate for a C/S app, and perform dismally in that
context.|||Bon wrote:
> Hello all
> Would it be possible to migrate the MS Access 2000 to MS SQL Server
> 2000?
> My application is using MS Access 2000 as database and as user
> interface such as forms. Now, I want to migrate the backend database
> from MS Access 2000 to MS SQL Server 2000. However, I want to keep the
> MS Access 2000 interface. Would it be possible?
> If I migrate the MS Access to SQL Server, would the queries, back-end
> VBA, macro, tables and forms be affected? Do I need to change the MS
> Access data type to SQL server supported data type?
Queries are one thing you'll need to look at. In the first instance
Access may do a direct translation to T-SQL (if you're lucky) otherwise
it can prepare a load of SPs and execute them, sometimes this works and
sometime it results in it effectively bringing across the entrire tables
from SQL Server and performing joins locally, which is bad. YMMV.
If you're moving queries to views, take care if the query uses any built
in or VBA functions as these won't exist in T-SQL. You can either write
a UDF or write the query differently.
In Access the use of "where exists (select...)" performs very badly
compared to "where column in (Select column...)", in SQL Server the
exists method is more efficient.
DAO code may behave unexpectedly, e.g. I had this problem
(http://www.besty.org.uk/memory.htm) but that's since been fixed and I
can't reproduce it now. Using ISAM methods in DAO (.Index, .Seek) will
not work.
Data-types shouldn't be a problem, SQL Server has more than Access,
Access will assume its own types when it sees them on the server. A few
caveats apply:
Access Yes/No is equivalent to SQL Bit but make sure you make it
required and default to 0 else unpredictable results can occur. Also
realise the values are different, in SQL Server its 0 and 1, in Access
its 0 and -1 and most of the time translates OK but if using an Access
query its better to use <>0 as criteria than =1 or =-1 or =True just to
be on the safe side.
Datetime data.
SQL Server only allows back to 1753 or some such, Access allows
(incorrectly as it doesn't handle missing days) further back than that.
You may think this might not concern you but the number of people who
put in 1/12/202 instead of 2002 will cause you immediate problems. If
you have that situation you have a problem already but just not realise it.
Also datetimes are stored differently in each and floating point errors
can occur resulting in the dreaded "#deleted" appearing in rows where
this occurs or "data has changed" errors. A timestamp column in the
table cures this.
> Which tool I can use to do the migration? Upsizing wizard or exporting
> the Access database and then importing it to the SQL server?
I've not come across a perfect one but in the past I've used the
upsizing wizard as it upsized more in the way of indexes, relationships,
etc.
Wednesday, March 7, 2012
possible to access postgresql from sql server ce
I am wondering if it is possible to access a remote postgresql database using the sql server ce access classes. I do not want to run a postgresql server on windows ce, just access one from a mobile device.
No, you can not. As name implies you can only access SQL Server CE with SQL Server CE access classes.
|||Thanks for your reply. I guess the real question I have is: Is thereany way to access a remote database that supports ODBC using Windows CE and Visual Studio .Net?|||
Windows CE has no support for ODBC. It has support for OLEDB, but there's no OLEDB ADO.Net provider not to mention you'd need Postgre OLEDB driver for Windows CE and I doubt Postgre has one.
|||Spasiba|||After doing a little research based on what you said I would need, Idid find a beta OLEDB provider for postgresql
http://pgfoundry.org/projects/oledb/ (which may or may not work on CE)
But if there's not Ado.net provider for CE, then it's a moot point.|||
If you get it to work you can use COM interop to talk to database instead of ADO.Net. May be not as convenient, but should work.
Now, using LGPL beta code which you need to port to CE first is another story…
Possible to access no. rows in groups outside the table?
Hi there,
I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).
Any help is much appreciated,
Thanks.
Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.
Thanks again.
|||Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.
Shyam
|||Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:
Group1 Total: 4
Group2 Total: 7
Group3 Total: 22
Then a single textbox at the top of the page would say
Group1 + Group2 = 11
Group1 + Group2 = 50% of Group3.
How would I accomplish this using the invisible list?
Thanks again.
|||Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.
In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:
=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))
=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))
=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))
And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName
Shyam
Possible Timeout error??
Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. No
w
i am getting the following error, which i believe is related to a timeout, a
s
i can transfer in the region of 3-4000 records before the error occurs. Is i
t
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL serv
er does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. No
w
i am getting the following error, which i believe is related to a timeout, a
s
i can transfer in the region of 3-4000 records before the error occurs. Is i
t
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL serv
er does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Possible Timeout error??
Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time?
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.
You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time?
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.
You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Possible Timeout error??
Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
possible SQL query for this function?
i am trying to write an SQL query for an Access database. in this database i have a table with inventory data. what i want is to select distinct item numbers and sum up all of the inventory quantity for those item numbers. i know how to select distinct item numbers OK from this table, but how can i go through the table and sum the quantities for those item numbers and dump all the results into a new table? that would mean for each distinct item number, there would be one resulting sum for that item. so the table would be 2 columns, one with distinct item numbers and one with their sums. thanks for any help!GROUP BY
-PatP|||Since it is F-F-F-Friday and I'm seeing out the last 30 mins...
SELECT MyDisinctCol, Sum(QuantityCol) AS TheTotal
FROM MyTable
GROUP BY MyDisinctCol
Another 20 secs gone!|||that did it! thanks for the help...
-PatP|||Since it is F-F-F-Friday and I'm seeing out the last 30 mins...
SELECT MyDisinctCol, Sum(QuantityCol) AS TheTotal
FROM MyTable
GROUP BY MyDisinctCol
Another 20 secs gone!|||that did it! thanks for the help...
Monday, February 20, 2012
Positioning of Stored Procedures
Hi all,
I have been facing this dilemma since when I started coding in asp.net 2.0. I can have Data Access Layer wherein I can write stored procedures to access the data from database. I can create data access object, data table and all other stuff. Also I can create stored procedure in SQL 2000 server, and then access them from the Data Access layer.
Which of the two method is preferable, and why. i have been searching net for answers to this question since long, but could not find anything.
All answers can contribute may be little but invaluable knowledge.
Thanks.
If security is critical, it's best to use stored procedures always because it lowers the attackable area of your database. I think this is what you are asking.
|||I wanted to know, which is better:
1) Creating stored procedures in SQL Server 2000 and calling them in the Data access Layer, or may be in the code behind straight away.
2) Creating Table Adapters in Data Access Layer, and creating Table Adapter queries and accessing database or may be stored procedures within the Data Access Layer.
I have been informed that if you create the Table Adapter queries, they are equally secure as stored procedures; though i am not pretty confident about it.
Thanks again.
|||Tell the truth, the issue is depending on your situation.
If you can connect database and your database permittion contol well, you need to do that on db.
if not, don't do that.
|||Read this an argument against using SP. This will clarify your question as well
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
Hope that helps
|||hello.
well, to be honest, sps aren't really something i'd advocate for crud behavior. in my opinion, using parametrized sql is the way to go. the performance/security bla bla that's has been used for several years is a myth and there are some posts out there that just show it. for instance, there's an old discussion between frans bouma and rob howard that started with a post from rob and a very well answer by frans. i'm putting only frans' post here since it is linked to rob's post.
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
having said that, i'm not saying that there really isn't a place for sps; just saying that kmost of the time the argument for using them are pure myths!
|||Hi,
I feel creating stored procedures in SQL Server 2000 and calling them in the Data access Layer is better choice. The book Titled :
"Database programming using C#, VB 2005 and SQL Server 2005", Chapter 10:Developing Components for three-tier applications explains this concept.
Let us say we have developed an three-tier application using SQL server 2000. In future, the same application should able to access/insert to Oracle database. In this situation, writing stored procedures at the server level is better.
I will find out further info on this matter.
|||
With the technologies ASP.NET 2 provide, you r always free to choose the way you like (depending upon your handy side). With the nearly the same amount of effort or even less you can still handle the shift to Oracle database.
But that said, your approach of choosing store procedure suppose to be a little faster in most cases.
|||
hello.
ask4jm:
But that said, your approach of choosing store procedure suppose to be a little faster in most cases
again, this is a known myth. read frans' post to see what i'm speaking about.
|||You r absolutely rite Luis. Unless the database developers wants to give the data through specific routines hiding rest of the infra, store procedures can be completely avoided.
|||db2Command cmd= db2Commant();
cmd.Connection=con;
param = new DB2Parameter("@.ClientId", DB2Type.Decimal, 8);
((DbCommand)base.dbSelectCommand[0]).Parameters.Add(param);
//Insert Command and parameters
string sqlInserCommand = "ProcCon";
base.dbInsertCommand = new DbCommand[1];
base.dbInsertCommand[0] = new DB2Command(sqlInserCommand);
param = new DB2Parameter("@.Name", DB2Type.VarChar, 100, "ClientName");
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);
param = new DB2Parameter("@.Cid", DB2Type.Int);
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);
Ports used by ReportViewer web controls
When a ReportViewer control is used to access a report on a remote SQL Server database, on what ports does the communcation occur? Does the control communicate on on the SQL Server ports of 1433 and 1434 or does it communcate as a web service on port 80 (or 443 for HTPPS)?It will depend on the url that you set for the report server. For a standard url it will use 80 (and 443), however you can always pass in a url for a non-standard port.
Subscribe to:
Posts (Atom)