ODBC Isolation level: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

OLAP based reports not rendering

I hope someone with a bit of knowledge about Analysis Services and Reporting
Services can give me some help here, please.
A client has the following setup:
One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services sp
2 and IIS 5.
One Windows 2003 server running Analysis Services sp 3.
--
1 role has been created in AS, with full access to everything. This is an AD
group, and we've tried adding all sorts of AD users to this group, to give
them access to the cube. We haven't tried adding "Everyone" and given it
full access.
--
When developing reports, we can access the OLAP cube with the usual NT login
of the developer. The report report renders perfectly on the dev box.
In Report Manager, reports based on SQL Server queries works perfectly.
Reports based on OLAP will work when run on the server running Reporing
Services, but NO OTHER computer. So when we access the Reporing Services
server with Remote Desktop, the OLAP report will render. From any other
computer, all in the same domain, it won't work.
The error message we get is basically "Cannot create a connection to data
source '<our datasource>'". But the "explanation" varies depending on what
user we try to use in our connection settings. The most common was "Database
'<our database>' could not be found." The other one was "Unknown error
E_(bunch of characters)".
So... any suggestions? I've found posts from other people who seem to have
had the same problem as ours, but no solution. We haven't figured out where
the problem is. Is it the credentials sent from Internet Explorer? Is it the
identity used in the connection string? Is it a new bug in SP 2? Reporting
Services has just been installed, and SP2 was installed immedeately. We
don't know if it would have worked without SP2, and I guess we won't find
out.
All help is really appreciated on this one!
Yours,
Kaisa M. Lindahloh yes please - we are having the same problems and noone seems to be able
to help !!!!
Please can someone help ?
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
>I hope someone with a bit of knowledge about Analysis Services and
>Reporting Services can give me some help here, please.
> A client has the following setup:
> One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> sp 2 and IIS 5.
> One Windows 2003 server running Analysis Services sp 3.
> --
> 1 role has been created in AS, with full access to everything. This is an
> AD group, and we've tried adding all sorts of AD users to this group, to
> give them access to the cube. We haven't tried adding "Everyone" and given
> it full access.
> --
> When developing reports, we can access the OLAP cube with the usual NT
> login of the developer. The report report renders perfectly on the dev
> box.
> In Report Manager, reports based on SQL Server queries works perfectly.
> Reports based on OLAP will work when run on the server running Reporing
> Services, but NO OTHER computer. So when we access the Reporing Services
> server with Remote Desktop, the OLAP report will render. From any other
> computer, all in the same domain, it won't work.
> The error message we get is basically "Cannot create a connection to data
> source '<our datasource>'". But the "explanation" varies depending on what
> user we try to use in our connection settings. The most common was
> "Database '<our database>' could not be found." The other one was "Unknown
> error E_(bunch of characters)".
> So... any suggestions? I've found posts from other people who seem to have
> had the same problem as ours, but no solution. We haven't figured out
> where the problem is. Is it the credentials sent from Internet Explorer?
> Is it the identity used in the connection string? Is it a new bug in SP 2?
> Reporting Services has just been installed, and SP2 was installed
> immedeately. We don't know if it would have worked without SP2, and I
> guess we won't find out.
> All help is really appreciated on this one!
> Yours,
> Kaisa M. Lindahl
>|||Provider=MSOLAP.2;Client Cache Size=25;Data
Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
Period=20000
Look into setting up Kerberos/trust betweent he servers and check the
connection string above "Kerberos"
--
Gary Foster
"Kathy" wrote:
> oh yes please - we are having the same problems and noone seems to be able
> to help !!!!
> Please can someone help ?
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> >I hope someone with a bit of knowledge about Analysis Services and
> >Reporting Services can give me some help here, please.
> >
> > A client has the following setup:
> >
> > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > sp 2 and IIS 5.
> >
> > One Windows 2003 server running Analysis Services sp 3.
> > --
> > 1 role has been created in AS, with full access to everything. This is an
> > AD group, and we've tried adding all sorts of AD users to this group, to
> > give them access to the cube. We haven't tried adding "Everyone" and given
> > it full access.
> > --
> >
> > When developing reports, we can access the OLAP cube with the usual NT
> > login of the developer. The report report renders perfectly on the dev
> > box.
> > In Report Manager, reports based on SQL Server queries works perfectly.
> > Reports based on OLAP will work when run on the server running Reporing
> > Services, but NO OTHER computer. So when we access the Reporing Services
> > server with Remote Desktop, the OLAP report will render. From any other
> > computer, all in the same domain, it won't work.
> >
> > The error message we get is basically "Cannot create a connection to data
> > source '<our datasource>'". But the "explanation" varies depending on what
> > user we try to use in our connection settings. The most common was
> > "Database '<our database>' could not be found." The other one was "Unknown
> > error E_(bunch of characters)".
> >
> > So... any suggestions? I've found posts from other people who seem to have
> > had the same problem as ours, but no solution. We haven't figured out
> > where the problem is. Is it the credentials sent from Internet Explorer?
> > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > Reporting Services has just been installed, and SP2 was installed
> > immedeately. We don't know if it would have worked without SP2, and I
> > guess we won't find out.
> >
> > All help is really appreciated on this one!
> >
> > Yours,
> >
> > Kaisa M. Lindahl
> >
>
>|||For our reports that use Analysis Services we had to do the following.
First, we ended up was using a single domain account hard-coded in the
data-link properties for the datasource. This account must have at least a
read-only role on the AS box in Analysis Manager. We were not allowed to use
Kerberos delegation as out SA team thought it was a security reisk on AD2k.
We may go this route once they have upgraded to AD2k3 as that release is
supposed to have the ability to provide scope limited Kerberos delegation.
Anyway, from here, it seemed like it would work. Our users are given access
or not based on their domain accounts being in a group ReportingServices on
the Report Server machine. What we found though was this still did not work.
Finally we found that after every deploy, we always have to go to the
web-based report manager, find the datasource that points to AS and check the
box "Use as Windows credentials when connecting to the data source" and then
retype the password and apply. After this our reports worked fine. Be aware
that this check box gets unchecked each time you deploy. I have not found
any other workaround. If you do, please let me know.
Hope this helps.
"garyf@.someone.com" wrote:
> Provider=MSOLAP.2;Client Cache Size=25;Data
> Source=myOLAPServer;SSPI=Kerberos;Initial Catalog=myOLAPDB;Auto Synch
> Period=20000
> Look into setting up Kerberos/trust betweent he servers and check the
> connection string above "Kerberos"
> --
> Gary Foster
>
> "Kathy" wrote:
> > oh yes please - we are having the same problems and noone seems to be able
> > to help !!!!
> > Please can someone help ?
> >
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:OqAZtwYbFHA.3240@.TK2MSFTNGP12.phx.gbl...
> > >I hope someone with a bit of knowledge about Analysis Services and
> > >Reporting Services can give me some help here, please.
> > >
> > > A client has the following setup:
> > >
> > > One Windows 2000 server running SQL Server 2000 sp 3, Reporting Services
> > > sp 2 and IIS 5.
> > >
> > > One Windows 2003 server running Analysis Services sp 3.
> > > --
> > > 1 role has been created in AS, with full access to everything. This is an
> > > AD group, and we've tried adding all sorts of AD users to this group, to
> > > give them access to the cube. We haven't tried adding "Everyone" and given
> > > it full access.
> > > --
> > >
> > > When developing reports, we can access the OLAP cube with the usual NT
> > > login of the developer. The report report renders perfectly on the dev
> > > box.
> > > In Report Manager, reports based on SQL Server queries works perfectly.
> > > Reports based on OLAP will work when run on the server running Reporing
> > > Services, but NO OTHER computer. So when we access the Reporing Services
> > > server with Remote Desktop, the OLAP report will render. From any other
> > > computer, all in the same domain, it won't work.
> > >
> > > The error message we get is basically "Cannot create a connection to data
> > > source '<our datasource>'". But the "explanation" varies depending on what
> > > user we try to use in our connection settings. The most common was
> > > "Database '<our database>' could not be found." The other one was "Unknown
> > > error E_(bunch of characters)".
> > >
> > > So... any suggestions? I've found posts from other people who seem to have
> > > had the same problem as ours, but no solution. We haven't figured out
> > > where the problem is. Is it the credentials sent from Internet Explorer?
> > > Is it the identity used in the connection string? Is it a new bug in SP 2?
> > > Reporting Services has just been installed, and SP2 was installed
> > > immedeately. We don't know if it would have worked without SP2, and I
> > > guess we won't find out.
> > >
> > > All help is really appreciated on this one!
> > >
> > > Yours,
> > >
> > > Kaisa M. Lindahl
> > >
> >
> >
> >sql

Monday, March 26, 2012

oh so slow query...

The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?

Thanks in advance. Clive

SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')ummmm...nothing?

Seriously though...I think I'd break it up...

Looks like the driver is the aliased table T1...

Make a dervide table out of that and get it as small as possible, then do your joins...

You need to help us with DDL though...esp. the indexes...

Ya gotta love surrogate keys...|||I think I've realised what's going on. The query in question is generated by the application (app sever probably). However, it's not a stored procedure. The query is exactly the same every time it is executed with one exception - a single id value changes. That will mean compilation every time the query is made I guess. I was noticing this as I was trying out a derived table approach and I was using Profiler to see if any improvements were made. Running any version of the query for the first time in QA took around 2 minutes but subequent exectutions were a few seconds. I am assuming that if the application vendor rewrote that part of their app to call a stored procedure with the id value as a parameter, it would run a lot more quickly without all those compilations.

Thanks,

Clive|||Either sp or prepared statement would do. In either case the plan should be reused, though I'd prefer sp.

Wednesday, March 21, 2012

Odd SQL Performance problem

I can't post DDL for this, but perhaps some of you may have experience
with this and will know what's happening.
I have the following problem:
INSERT TableName
SELECT * FROM ViewName
ViewName is a view that returns about 500,000 rows. TableName is a
materialized, cached representation of that data because the view is
relatively complex. I'm hesitant to use an Indexed View because I want
control over how and when my materialized copy gets updated.
I stopped the above statement after it executed for nearly 24 hours.
The following set of statements only took a few minutes to complete
successfully:
SELECT * INTO #X FROM ViewName
INSERT TableName
SELECT * FROM #X
Any ideas as to why the second set of statements would execute so much
more quickly than the first? I assume that the first statement is
trying to sort the data as it selects it to do the clustered index
inserts, thus executing multiple times on smaller sets of data, whereas
the second set of statements executes the view, stores the data and
then has a much simpler result set to sort. Are there any types of
hints that I could provide the processor to speed execution of the
first set of statements?
-AlanDid you look to see what the bottleneck was? Was there blocking, were the
disks or cpu pegged or was it just sitting there? Have you checked the
estimated plans for each to see what the differences were?
Andrew J. Kelly SQL MVP
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1145127196.691104.246480@.z34g2000cwc.googlegroups.com...
>I can't post DDL for this, but perhaps some of you may have experience
> with this and will know what's happening.
> I have the following problem:
> INSERT TableName
> SELECT * FROM ViewName
> ViewName is a view that returns about 500,000 rows. TableName is a
> materialized, cached representation of that data because the view is
> relatively complex. I'm hesitant to use an Indexed View because I want
> control over how and when my materialized copy gets updated.
> I stopped the above statement after it executed for nearly 24 hours.
> The following set of statements only took a few minutes to complete
> successfully:
> SELECT * INTO #X FROM ViewName
> INSERT TableName
> SELECT * FROM #X
> Any ideas as to why the second set of statements would execute so much
> more quickly than the first? I assume that the first statement is
> trying to sort the data as it selects it to do the clustered index
> inserts, thus executing multiple times on smaller sets of data, whereas
> the second set of statements executes the view, stores the data and
> then has a much simpler result set to sort. Are there any types of
> hints that I could provide the processor to speed execution of the
> first set of statements?
> -Alan
>|||all of the following options are untested :)
insert into tablename select * from (select * from viewname) as a
or
insert into tablename exec('select * from viewname')
P.S: Regarding the second option... I don't know if this kind of an execute
statement works.. never came across a situation where I had to use this :)|||Check the indexes on the target table.
If the data your inserting has primary key values (or other clustered
index values) that are all over the place (not in relative sequence) or
require insertion somewhere other than the end of the table, you might
run into a lot of page splits.
If that target table is large and the inserted data can't fit into the
indexes free space (fillfactor), it can take forever.
If this is the case, you might be better of dropping the clustered
index...insert the data...recreate the clustered index.
Hope this helps...John|||I think it's more related to the processor trying to batch and insert
blocks of results from the source view. One thing I've noticed is that
when running sp_who2 I seem to catch the spid shifting between SELECT,
INSERT and EXECUTE. I was kindof surprised to see EXECUTE in there.
Yes, it's exactly as you saw it INSERT x SELECT * FROM y -- No triggers
or anything.
My main reason for reporting this is here on USENET that I think it's
something that should be optimized in the query processor/optimizer
within SQL Server.
-Alan
retlaw wrote:
> Check the indexes on the target table.
> If the data your inserting has primary key values (or other clustered
> index values) that are all over the place (not in relative sequence) or
> require insertion somewhere other than the end of the table, you might
> run into a lot of page splits.
> If that target table is large and the inserted data can't fit into the
> indexes free space (fillfactor), it can take forever.
> If this is the case, you might be better of dropping the clustered
> index...insert the data...recreate the clustered index.
> Hope this helps...John|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> insert into tablename exec('select * from viewname')
> P.S: Regarding the second option... I don't know if this kind of an
> execute statement works.. never came across a situation where I had to
> use this :)
Yes, it works, but I'd be surprised if it makes any changes to performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Odd Primary Key Error

quote:

> Dear All,
> I have been getting Primary key violation errors, however
> I am a bit confused why. Here is the reason
> We have a table with the following structure with the ID
> field as the primary key.
> CREATE TABLE [dbo].[tblTest] (
> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> and
> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> We then use a store procedure to insert items into the
> table. The store procedure does not access the ID field,
> but it does return it after insert.
> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> commands I have found the next key to be generated is
> correct.
> However we are starting to get intermediate primary key
> violation errors.
> Any pointers please.
> J

|||Almost at the end of every insert store procedure we have
a 'RETURN scope_identity()' command.
This is filtered back to the VB app though ado.
J

quote:

>--Original Message--
>Julie
> Did you try in the bottom of the sp adding return

@.@.identity and the to

quote:

>carry out on the client side?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
however[QUOTE]
>
>.
>

|||Julie
Look , IDENTITY property does not guarantee sequence of insertion. There may
be gaps.
Can you provide sample data + code of your sp to reproduce your problem?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...[QUOTE]
> Almost at the end of every insert store procedure we have
> a 'RETURN scope_identity()' command.
> This is filtered back to the VB app though ado.
> J
>
> @.@.identity and the to
> message
> however|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J

quote:

>--Original Message--
>Julie
>Look , IDENTITY property does not guarantee sequence of

insertion. There may

quote:

>be gaps.
>Can you provide sample data + code of your sp to

reproduce your problem?

quote:

>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
have[QUOTE]
the ID[QUOTE]
the[QUOTE]
field,[QUOTE]
key[QUOTE]
>
>.
>

Odd Primary Key Error

Dear All,
I have been getting Primary key violation errors, however
I am a bit confused why. Here is the reason
We have a table with the following structure with the ID
field as the primary key.
CREATE TABLE [dbo].[tblTest] (
[ID] [int] IDENTITY (8, 20) NOT NULL , ...
and
ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
We then use a store procedure to insert items into the
table. The store procedure does not access the ID field,
but it does return it after insert.
Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
commands I have found the next key to be generated is
correct.
However we are starting to get intermediate primary key
violation errors.
Any pointers please.
JJulie
Did you try in the bottom of the sp adding return @.@.identity and the to
carry out on the client side?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
> Dear All,
> I have been getting Primary key violation errors, however
> I am a bit confused why. Here is the reason
> We have a table with the following structure with the ID
> field as the primary key.
> CREATE TABLE [dbo].[tblTest] (
> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> and
> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> We then use a store procedure to insert items into the
> table. The store procedure does not access the ID field,
> but it does return it after insert.
> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> commands I have found the next key to be generated is
> correct.
> However we are starting to get intermediate primary key
> violation errors.
> Any pointers please.
> J|||Almost at the end of every insert store procedure we have
a 'RETURN scope_identity()' command.
This is filtered back to the VB app though ado.
J
>--Original Message--
>Julie
> Did you try in the bottom of the sp adding return
@.@.identity and the to
>carry out on the client side?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
>> Dear All,
>> I have been getting Primary key violation errors,
however
>> I am a bit confused why. Here is the reason
>> We have a table with the following structure with the ID
>> field as the primary key.
>> CREATE TABLE [dbo].[tblTest] (
>> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
>> and
>> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
>> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
>> (
>> [ID]
>> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> We then use a store procedure to insert items into the
>> table. The store procedure does not access the ID field,
>> but it does return it after insert.
>> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
>> commands I have found the next key to be generated is
>> correct.
>> However we are starting to get intermediate primary key
>> violation errors.
>> Any pointers please.
>> J
>
>.
>|||Julie
Look , IDENTITY property does not guarantee sequence of insertion. There may
be gaps.
Can you provide sample data + code of your sp to reproduce your problem?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
> Almost at the end of every insert store procedure we have
> a 'RETURN scope_identity()' command.
> This is filtered back to the VB app though ado.
> J
>
> >--Original Message--
> >Julie
> > Did you try in the bottom of the sp adding return
> @.@.identity and the to
> >carry out on the client side?
> >
> >
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
> >> Dear All,
> >> I have been getting Primary key violation errors,
> however
> >> I am a bit confused why. Here is the reason
> >>
> >> We have a table with the following structure with the ID
> >> field as the primary key.
> >>
> >> CREATE TABLE [dbo].[tblTest] (
> >> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> >>
> >> and
> >>
> >> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> >> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> >> (
> >> [ID]
> >> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> >>
> >> We then use a store procedure to insert items into the
> >> table. The store procedure does not access the ID field,
> >> but it does return it after insert.
> >>
> >> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> >> commands I have found the next key to be generated is
> >> correct.
> >>
> >> However we are starting to get intermediate primary key
> >> violation errors.
> >>
> >> Any pointers please.
> >>
> >> J
> >
> >
> >.
> >|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J
>--Original Message--
>Julie
>Look , IDENTITY property does not guarantee sequence of
insertion. There may
>be gaps.
>Can you provide sample data + code of your sp to
reproduce your problem?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
>> Almost at the end of every insert store procedure we
have
>> a 'RETURN scope_identity()' command.
>> This is filtered back to the VB app though ado.
>> J
>>
>> >--Original Message--
>> >Julie
>> > Did you try in the bottom of the sp adding return
>> @.@.identity and the to
>> >carry out on the client side?
>> >
>> >
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
>> >> Dear All,
>> >> I have been getting Primary key violation errors,
>> however
>> >> I am a bit confused why. Here is the reason
>> >>
>> >> We have a table with the following structure with
the ID
>> >> field as the primary key.
>> >>
>> >> CREATE TABLE [dbo].[tblTest] (
>> >> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
>> >>
>> >> and
>> >>
>> >> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ID]
>> >> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> >>
>> >> We then use a store procedure to insert items into
the
>> >> table. The store procedure does not access the ID
field,
>> >> but it does return it after insert.
>> >>
>> >> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
>> >> commands I have found the next key to be generated is
>> >> correct.
>> >>
>> >> However we are starting to get intermediate primary
key
>> >> violation errors.
>> >>
>> >> Any pointers please.
>> >>
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>sql

Tuesday, March 20, 2012

odd deadlocking behaviour

I am running SQL Server 2000 SP3a on a single processor computer and:
I have a table with the following columns
CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
[JOB_NO] [int] NOT NULL ,
[OVERRIDDEN_PERIOD_START] [datetime] NULL ,
[OVERRIDDEN_PERIOD_END] [datetime] NULL
) ON [PRIMARY]
GO
That has the following data:
1,06/01/2003 17:00:00,NULL
2,NULL,13/01/2003 08:00:00
2,13/01/2003 17:00:00,NULL
3,NULL,20/01/2003 08:00:00
3,20/01/2003 17:00:00,NULL
4,NULL, 27/01/2003 08:00:00
4,27/01/2003 17:00:00,NULL
If I now try deleting these rows from two database sessions in the order as
seen below I end up with a deadlock:
Firstly on SPID 56:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 2
Secondly on SPID 57:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 1
... This then blocks ? Which I am not sure about.
Thirdly back on SPID 56:
delete from SP148_JOB_AHO_PERIODS where job_no = 6
Creates a deadlock victimising SPID 57.
I accept that this table does not have a primary key but when I add one it
still deadlocks.
Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
Why does a deadlock occur when SPID 56 subsequently deletes the row where
job_no is 6 i.e. no rows?
When I looked at sp_lock output it appeared that both sessions were waiting
on the same RID, does this mean that a RID is not necessarily 1 database
table row?
Thanks- What kind of lock triggered the deadlock? row, index, page, extent, table,
database
- Do you have an index by "job_no"?
AMB
"Tony Jones" wrote:
> I am running SQL Server 2000 SP3a on a single processor computer and:
> I have a table with the following columns
> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> [JOB_NO] [int] NOT NULL ,
> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> [OVERRIDDEN_PERIOD_END] [datetime] NULL
> ) ON [PRIMARY]
> GO
> That has the following data:
> 1,06/01/2003 17:00:00,NULL
> 2,NULL,13/01/2003 08:00:00
> 2,13/01/2003 17:00:00,NULL
> 3,NULL,20/01/2003 08:00:00
> 3,20/01/2003 17:00:00,NULL
> 4,NULL, 27/01/2003 08:00:00
> 4,27/01/2003 17:00:00,NULL
> If I now try deleting these rows from two database sessions in the order as
> seen below I end up with a deadlock:
> Firstly on SPID 56:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 2
> Secondly on SPID 57:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 1
> ... This then blocks ? Which I am not sure about.
> Thirdly back on SPID 56:
> delete from SP148_JOB_AHO_PERIODS where job_no = 6
> Creates a deadlock victimising SPID 57.
> I accept that this table does not have a primary key but when I add one it
> still deadlocks.
> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> Why does a deadlock occur when SPID 56 subsequently deletes the row where
> job_no is 6 i.e. no rows?
> When I looked at sp_lock output it appeared that both sessions were waiting
> on the same RID, does this mean that a RID is not necessarily 1 database
> table row?
> Thanks|||Turn on trace flag 1204 and -1. Then your answers will be in the errorlog.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5E921D65-22E3-40D0-AE4C-BEBF76DF8FC7@.microsoft.com...
>- What kind of lock triggered the deadlock? row, index, page, extent,
>table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
>> I am running SQL Server 2000 SP3a on a single processor computer and:
>> I have a table with the following columns
>> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
>> [JOB_NO] [int] NOT NULL ,
>> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
>> [OVERRIDDEN_PERIOD_END] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> That has the following data:
>> 1,06/01/2003 17:00:00,NULL
>> 2,NULL,13/01/2003 08:00:00
>> 2,13/01/2003 17:00:00,NULL
>> 3,NULL,20/01/2003 08:00:00
>> 3,20/01/2003 17:00:00,NULL
>> 4,NULL, 27/01/2003 08:00:00
>> 4,27/01/2003 17:00:00,NULL
>> If I now try deleting these rows from two database sessions in the order
>> as
>> seen below I end up with a deadlock:
>> Firstly on SPID 56:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 2
>> Secondly on SPID 57:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 1
>> ... This then blocks ? Which I am not sure about.
>> Thirdly back on SPID 56:
>> delete from SP148_JOB_AHO_PERIODS where job_no = 6
>> Creates a deadlock victimising SPID 57.
>> I accept that this table does not have a primary key but when I add one
>> it
>> still deadlocks.
>> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
>> Why does a deadlock occur when SPID 56 subsequently deletes the row where
>> job_no is 6 i.e. no rows?
>> When I looked at sp_lock output it appeared that both sessions were
>> waiting
>> on the same RID, does this mean that a RID is not necessarily 1 database
>> table row?
>> Thanks|||The blocked SPID 57 is waiting on a RID (so a row lock) both SPIDs are
waiting on the same rid i.e. the same fileid:pageid:slot, hence the question
is the RID locking more than one row?
No I have not had a index on job_no I have tried a primary kry on a new
column defined to be the Identity column. This produced the same deadlock
behaviour.
"Alejandro Mesa" wrote:
> - What kind of lock triggered the deadlock? row, index, page, extent, table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
> > I am running SQL Server 2000 SP3a on a single processor computer and:
> >
> > I have a table with the following columns
> > CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> > [JOB_NO] [int] NOT NULL ,
> > [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> > [OVERRIDDEN_PERIOD_END] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > That has the following data:
> >
> > 1,06/01/2003 17:00:00,NULL
> > 2,NULL,13/01/2003 08:00:00
> > 2,13/01/2003 17:00:00,NULL
> > 3,NULL,20/01/2003 08:00:00
> > 3,20/01/2003 17:00:00,NULL
> > 4,NULL, 27/01/2003 08:00:00
> > 4,27/01/2003 17:00:00,NULL
> >
> > If I now try deleting these rows from two database sessions in the order as
> > seen below I end up with a deadlock:
> >
> > Firstly on SPID 56:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 2
> >
> > Secondly on SPID 57:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 1
> > ... This then blocks ? Which I am not sure about.
> >
> > Thirdly back on SPID 56:
> > delete from SP148_JOB_AHO_PERIODS where job_no = 6
> >
> > Creates a deadlock victimising SPID 57.
> >
> > I accept that this table does not have a primary key but when I add one it
> > still deadlocks.
> >
> > Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> > Why does a deadlock occur when SPID 56 subsequently deletes the row where
> > job_no is 6 i.e. no rows?
> >
> > When I looked at sp_lock output it appeared that both sessions were waiting
> > on the same RID, does this mean that a RID is not necessarily 1 database
> > table row?
> >
> > Thanks

Monday, March 12, 2012

ODBCBCP Driver Mismatch

Hello,
I am receiving the following error message whenever I try to replicate using Snapshot repl. (or any type of replication) from the snapshot agent:
Error Message: The process could not bulk copy out of table '[dbo].[syncobj_xxxxxxxx]'.
Error Details: ODBCBCP/Driver version mismatch
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
I have checked the versions of the odbcbcp.dll on both of my SQL Servers (both of which are win2k3 w/ SQL Server 2000 sp3) and they are both 2000.85.1022.0. The version number of sqlsrv32.dll and sqlsrv32.rll are 2000.85.1025.0. Do all three have to mat
ch, is that my problem? Please HELP!! I can't replicate at all!
there are some reports that this problem can be solved by upgrading to a
consistent MDAC versions on both machines.
"Paul Pelletier" <anonymous@.discussions.microsoft.com> wrote in message
news:3E372B82-39B2-4A93-B7DE-5B7740527F13@.microsoft.com...
> Hello,
> I am receiving the following error message whenever I try to replicate
using Snapshot repl. (or any type of replication) from the snapshot agent:
> Error Message: The process could not bulk copy out of table
'[dbo].[syncobj_xxxxxxxx]'.
> Error Details: ODBCBCP/Driver version mismatch
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> I have checked the versions of the odbcbcp.dll on both of my SQL Servers
(both of which are win2k3 w/ SQL Server 2000 sp3) and they are both
2000.85.1022.0. The version number of sqlsrv32.dll and sqlsrv32.rll are
2000.85.1025.0. Do all three have to match, is that my problem? Please
HELP!! I can't replicate at all!
|||Hillary,
I have, I re-applied MDAC 2.7 on both SQL Servers and still the same problem. Any other possible solutions?
Do all there files have to have the same version numbers?
Thanks,
Paul
|||Check the version of the ODBC32.dll. It could be the one that is
mismatched. It should be version 3.525.1022.0.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||It is mismatched in a sense. The odbcbcp.dll is version 2000.85.1022.0. The sqlsrv32.dll is 2000.85.1025.0. The sqlsrv32.rll is 2000.85.1025.0. So really the one that is mismatched is the sqlsrv32.dll, but which version should be the correct version f
or all three, the 1025 or 1022?
Thanks again,
Paul
|||Sorry about the previous post, I did not completely read your post and I missed the fact that you were talking about a completely different dll, I'm a tard! Anyway both of the odbc32.dll do match on both machines and they are in fact 3.525.1022.0. Where
now?

ODBC: SQL_ATTR_CONCURRENCY changing after simple select

The following is the log from an ODBC Test session against a SQL Server 2000 database.
MDAC Ver. 2.7 [SP1 I think].

The point of interest is that the SQL_ATTR_CONCURRENCY setting is changing from SQL_CONCUR_LOCK (READ/WRITE), to SQL_CONCUR_READ_ONLY after the SQLExecDirect() call [I've also tried SQLPrepare/SQLExecute with the same result].

Can anyone please throw some light onto why this happens, and how to avoid it? [It kills my SQLBulkOperations() call to insert values into the table]

Many thanks.
PeterC.

dbc: szSqlState = "01000", *pfNativeError = 5701, *pcbErrorMsg = 84, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'pctest'."
dbc: szSqlState = "01000", *pfNativeError = 5703, *pcbErrorMsg = 86, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english."

Full Connect:

Full Connect(Use Driver)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

Successfully connected to DSN 'Testpc'.

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = SQL_CONCUR_LOCK=2,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CURSOR_TYPE=6,
ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLSetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_ROW_ARRAY_SIZE=27,
ValuePtr = 1,
StringLength = SQL_NTS=-3,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0

SQLExecDirect:
In: Statementhandle = 0x00841978,
StatementText = "select * from pc02",
Statementlength = 20
Return: SQL_SUCCESS_WITH_INFO=1

SQLGetStmtAttr:
In: StatementHandle = 0x00841978,
Attribute = SQL_ATTR_CONCURRENCY=7,
ValuePtr = 0x0014C3F8,
BufferLength = 300,
StringLengthPtr = 0x0014E968,
fAttribute Type = SQL_C_SLONG=-16
Return: SQL_SUCCESS=0
Out:
*ValuePtr = SQL_CONCUR_READ_ONLY = 1,
*StringLengthPtr = 4I should also add that the cursor is changing from SQL_CURSOR_KEYSET_DRIVEN to SQL_CURSOR_STATIC.

PeterC.

ODBC, SQL Server Driver alias error

I am running SQL Server 2000, trying to create a new database and
received the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server Login]
is aliased or mapped to a user in one or more databases. Drop the
user or alias before dropping the login.
Can anyone assist me with this error.
Thanks."Dave" <dave@.groupfive.net> wrote in message
news:993ef28c.0403120844.663b8391@.posting.google.com...
> I am running SQL Server 2000, trying to create a new database and
> received the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server Login]
> is aliased or mapped to a user in one or more databases. Drop the
> user or alias before dropping the login.
If you do not have system administrator rights, please see the following
article:
http://support.microsoft.com/defaul...3&Product=sql2k
Steve

ODBC Virtual Driver

Hi,
I have the following requirement :

I need to have a way to intercept the SQL queries from an application written in VB and using a ODBC driver and modify the SQL queries before it goes through the ODBC driver and then to the database. This I need to do without modifying the original application.

The solution I have in mind is to write a ODBC virtual driver and configure my application to use my virtual ODBC driver. The ODBC virtual driver in turn will use the actual ODBC driver to the database. The virtual driver will basically intercept the SQL queries, modify it and then give it to the real ODBC driver.

My question is

1. Is this a feasible solution?
2. What should I do in order to implement the vitrtual ODBC driver.
3. Any pointer will be appreciated.

Thanks
Jake.NEver heard of virtual odbc driver, but you may find some information from http://www.microsoft.com site.|||Hi,
What I meant by Virtual driver is basically a ODBC proxy which can sit between my application and the ODBC driver and intercept the SQL queries.

Thanks
Jake

Originally posted by Satya
NEver heard of virtual odbc driver, but you may find some information from http://www.microsoft.com site.|||I'm working from memory here, but I think an ODBC Proxy something like you are describing is included as a VC project in the ODBC Driver SDK.

-PatP

Wednesday, March 7, 2012

ODBC SQL Server Driver-Network Error

Hi,
Can anyone Please help me with the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection Broken
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (recv())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection Broken
Kobe,
This indicates network problems. Ask your LAN administrators to check all
network equipment between the machines involved.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Kobe" <anonymous@.discussions.microsoft.com> wrote in message
news:28c201c42811$6caeb300$a301280a@.phx.gbl...
> Hi,
> Can anyone Please help me with the following error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionRead (recv())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken

ODBC SQL Server Driver-Network Error

Hi,
Can anyone Please help me with the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection Broken
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (recv())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection BrokenKobe,
This indicates network problems. Ask your LAN administrators to check all
network equipment between the machines involved.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Kobe" <anonymous@.discussions.microsoft.com> wrote in message
news:28c201c42811$6caeb300$a301280a@.phx.gbl...
> Hi,
> Can anyone Please help me with the following error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionRead (recv())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken

ODBC SQL Server Driver-Network Error

Hi,
Can anyone Please help me with the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection Broken
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (recv())
Server:Msg 11,Level 16,State 1,Line 0
General network error. Check your network documentation.
Connection BrokenKobe,
This indicates network problems. Ask your LAN administrators to check all
network equipment between the machines involved.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Kobe" <anonymous@.discussions.microsoft.com> wrote in message
news:28c201c42811$6caeb300$a301280a@.phx.gbl...
> Hi,
> Can anyone Please help me with the following error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionRead (recv())
> Server:Msg 11,Level 16,State 1,Line 0
> General network error. Check your network documentation.
> Connection Broken

Saturday, February 25, 2012

ODBC Redux

I asked the following a few days ago. Am I asking the wrong group?
I am attempting to put a couple of Oracle 8i views in a report project I am
building. When I attempt to build the connection I have two drivers to
choose from one is the Microsoft OLE DB provider for Oracle and the other is
"Oracle Provider for OLE DB". When I use the first I do not get all of the
views and when I get the second I get an error saying that the service name
cannot be resolved. I have created a regular DSN and looked at the data in
Access so I know the views I want are there but I cannot seem to make it
happen in Reporting services. Any Ideas?
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor CompanySometimes when you ask a question there isn't anyone who knows the solution.
I don't know the solution but I do have some ideas on what you can try.
First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
will be trying to use OLEDB from the query designer and managed dotnet
provider when you run it. The dotnet provider requires the 9i client (not
sure if a 9i client can go against 8i). If you pick the Microsoft one it
might work from the designer but it would most likely not run when you
preview the report.
A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as the
Oracle OLEDB provider you are missing a step in configuring, i.e. you have
not setup the service name (which is what the error says). I haven't worked
for awhile with Oracle so I can't give you the exact steps. Google OLEDB and
Oracle.
My suggestion is to go with the oledb provider for odbc because then you can
pick the DSN that you setup previously.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Oracle behaves differently when you use the QBE layout and the text layout.
More precisely, two different mechanisms are used to get to the database. I
think you're supposed to use the text layout for entering your query into
the datasource.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
>I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
> am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
> is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
> name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Sorry if I sounded testy. I plead innocent due to the limitations of text.
There are a lot of messages that get posed here and I assumed that mine
either got lost in the mass or it was considered off topic hence the first
sentence of the message.
Now, that being said I am running the Oracle 9i client and when I try to use
the Microsoft OLE DB driver for ODBC I get the same error that the service
name cannot be resolved. I can connect any other tool using the ODBC DSN
except for the MS tool.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Sometimes when you ask a question there isn't anyone who knows the
solution.
> I don't know the solution but I do have some ideas on what you can try.
> First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
> will be trying to use OLEDB from the query designer and managed dotnet
> provider when you run it. The dotnet provider requires the 9i client (not
> sure if a 9i client can go against 8i). If you pick the Microsoft one it
> might work from the designer but it would most likely not run when you
> preview the report.
> A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
the
> Oracle OLEDB provider you are missing a step in configuring, i.e. you have
> not setup the service name (which is what the error says). I haven't
worked
> for awhile with Oracle so I can't give you the exact steps. Google OLEDB
and
> Oracle.
> My suggestion is to go with the oledb provider for odbc because then you
can
> pick the DSN that you setup previously.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||Hmm, there should not be any difference between Access (which you says works
with the DSN you have created) and using MS OLEDB Driver for ODBC. Is this a
system DSN or a user DSN. I used ODBC against Sybase and I see no
difference between using the DSN with Access (which I have done) and using
it in RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23rFT7wX%23EHA.2580@.TK2MSFTNGP15.phx.gbl...
> Sorry if I sounded testy. I plead innocent due to the limitations of text.
> There are a lot of messages that get posed here and I assumed that mine
> either got lost in the mass or it was considered off topic hence the first
> sentence of the message.
> Now, that being said I am running the Oracle 9i client and when I try to
use
> the Microsoft OLE DB driver for ODBC I get the same error that the service
> name cannot be resolved. I can connect any other tool using the ODBC DSN
> except for the MS tool.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Sometimes when you ask a question there isn't anyone who knows the
> solution.
> > I don't know the solution but I do have some ideas on what you can try.
> > First, do not pick the Microsoft OLE DB Provider for Oracle. If you do
it
> > will be trying to use OLEDB from the query designer and managed dotnet
> > provider when you run it. The dotnet provider requires the 9i client
(not
> > sure if a 9i client can go against 8i). If you pick the Microsoft one it
> > might work from the designer but it would most likely not run when you
> > preview the report.
> >
> > A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
> the
> > Oracle OLEDB provider you are missing a step in configuring, i.e. you
have
> > not setup the service name (which is what the error says). I haven't
> worked
> > for awhile with Oracle so I can't give you the exact steps. Google OLEDB
> and
> > Oracle.
> >
> > My suggestion is to go with the oledb provider for odbc because then you
> can
> > pick the DSN that you setup previously.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||Ok this suggestion worked (in a kluge kind of way) even though I could not
see the view in the picker list if I entered the name manually it then
appeared. Thanks for the help.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Oracle behaves differently when you use the QBE layout and the text
layout.
> More precisely, two different mechanisms are used to get to the database.
I
> think you're supposed to use the text layout for entering your query into
> the datasource.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> >I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> > am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> > is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> > name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||So did you end up using Oracle OLEDB provider?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Ok this suggestion worked (in a kluge kind of way) even though I could not
> see the view in the picker list if I entered the name manually it then
> appeared. Thanks for the help.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Oracle behaves differently when you use the QBE layout and the text
> layout.
> > More precisely, two different mechanisms are used to get to the
database.
> I
> > think you're supposed to use the text layout for entering your query
into
> > the datasource.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > >I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||What I did:
I had created a DSN Called EMC. For the Data Source I used Microsoft OLE DB
connector for ODBC and selected the DSN. This gave me a list of tables and
views in the Table Picker but the views I wanted were not in the list so I
had to not use the picker and enter the SQL manually by using the syntax:
userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered that
the table picker then showed that table.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> So did you end up using Oracle OLEDB provider?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > Ok this suggestion worked (in a kluge kind of way) even though I could
not
> > see the view in the picker list if I entered the name manually it then
> > appeared. Thanks for the help.
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > Oracle behaves differently when you use the QBE layout and the text
> > layout.
> > > More precisely, two different mechanisms are used to get to the
> database.
> > I
> > > think you're supposed to use the text layout for entering your query
> into
> > > the datasource.
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > >I asked the following a few days ago. Am I asking the wrong group?
> > > >
> > > > I am attempting to put a couple of Oracle 8i views in a report
project
> I
> > > > am
> > > > building. When I attempt to build the connection I have two drivers
to
> > > > choose from one is the Microsoft OLE DB provider for Oracle and the
> > other
> > > > is
> > > > "Oracle Provider for OLE DB". When I use the first I do not get all
of
> > the
> > > > views and when I get the second I get an error saying that the
service
> > > > name
> > > > cannot be resolved. I have created a regular DSN and looked at the
> data
> > in
> > > > Access so I know the views I want are there but I cannot seem to
make
> it
> > > > happen in Reporting services. Any Ideas?
> > > >
> > > >
> > > > --
> > > > Andrew C. Madsen
> > > > Network Specialist
> > > > Harley-Davidson Motor Company
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I wondered what ended up working for you in case I see someone else
with the problem. I wonder if the views have some special naming to them
that causes them to not show in the list.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:e2wfiXZ%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> What I did:
> I had created a DSN Called EMC. For the Data Source I used Microsoft OLE
DB
> connector for ODBC and selected the DSN. This gave me a list of tables and
> views in the Table Picker but the views I wanted were not in the list so I
> had to not use the picker and enter the SQL manually by using the syntax:
> userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered
that
> the table picker then showed that table.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> > So did you end up using Oracle OLEDB provider?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > > Ok this suggestion worked (in a kluge kind of way) even though I could
> not
> > > see the view in the picker list if I entered the name manually it then
> > > appeared. Thanks for the help.
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > > Oracle behaves differently when you use the QBE layout and the text
> > > layout.
> > > > More precisely, two different mechanisms are used to get to the
> > database.
> > > I
> > > > think you're supposed to use the text layout for entering your query
> > into
> > > > the datasource.
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > > >I asked the following a few days ago. Am I asking the wrong group?
> > > > >
> > > > > I am attempting to put a couple of Oracle 8i views in a report
> project
> > I
> > > > > am
> > > > > building. When I attempt to build the connection I have two
drivers
> to
> > > > > choose from one is the Microsoft OLE DB provider for Oracle and
the
> > > other
> > > > > is
> > > > > "Oracle Provider for OLE DB". When I use the first I do not get
all
> of
> > > the
> > > > > views and when I get the second I get an error saying that the
> service
> > > > > name
> > > > > cannot be resolved. I have created a regular DSN and looked at the
> > data
> > > in
> > > > > Access so I know the views I want are there but I cannot seem to
> make
> > it
> > > > > happen in Reporting services. Any Ideas?
> > > > >
> > > > >
> > > > > --
> > > > > Andrew C. Madsen
> > > > > Network Specialist
> > > > > Harley-Davidson Motor Company
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Monday, February 20, 2012

ODBC LIKE clause

I'm trying to execute the following query within a Visual
C++ program...
SELECT LastName from Employees WHERE LastName LIKE ?
In the C++ program, I use the SQLBindParameter() to
associate to the ?. The SQLBindParameter() variable used
contains 'A%' (without the single quote).
This is a very simple statement, but constantly returns
no data.
I run this exact statement within a MS Access database
connection, and it returns 4 records. The MS Access wild
card is also 'A%'. I think there is a bug in the SQL
Server ODBC, but I cannot confirm this.
Using the SQL Trace doesn't tell me the translations
being performed within the ODBC driver to confirm a bug.
Help...please...
Regards,
AngeloHi Angelo,
The SQL Trace (you are using SQL 7 ?) will tell you the command the SQL
Servers sees. What does that look like?
Also have you tried 'A*' or have you tired
SELECT LastName from Employees WHERE LastName LIKE '?'
Just some ideas, I hope you fix the issue.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Angelo" <anonymous@.discussions.microsoft.com> wrote in message
news:04ee01c3b861$09b06700$a301280a@.phx.gbl...

quote:

> I'm trying to execute the following query within a Visual
> C++ program...
> SELECT LastName from Employees WHERE LastName LIKE ?
> In the C++ program, I use the SQLBindParameter() to
> associate to the ?. The SQLBindParameter() variable used
> contains 'A%' (without the single quote).
> This is a very simple statement, but constantly returns
> no data.
> I run this exact statement within a MS Access database
> connection, and it returns 4 records. The MS Access wild
> card is also 'A%'. I think there is a bug in the SQL
> Server ODBC, but I cannot confirm this.
> Using the SQL Trace doesn't tell me the translations
> being performed within the ODBC driver to confirm a bug.
> Help...please...
> Regards,
> Angelo

|||Thanks for the reply...
I'm using SQL 8. I will try the TRACE again. I've tried
your other suggestions but still get the same results.
Later,

quote:

>--Original Message--
>Hi Angelo,
>The SQL Trace (you are using SQL 7 ?) will tell you the

command the SQL

quote:

>Servers sees. What does that look like?
>Also have you tried 'A*' or have you tired
>SELECT LastName from Employees WHERE LastName LIKE '?'
>Just some ideas, I hope you fix the issue.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL

Scribe Documentation

quote:

>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL

Server Extended

quote:

>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.

Free icon extraction

quote:

>program
>http://www.ag-software.com. Free programming tools
>
>"Angelo" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:04ee01c3b861$09b06700$a301280a@.phx.gbl...
Visual[QUOTE]
used[QUOTE]
wild[QUOTE]
bug.[QUOTE]
>
>.
>

|||Could you post all the parameters for SQLBindParameter() method. I am
pretty confident that 'A%' is the correct syntax
Pete
Angelo wrote:

quote:

>I'm trying to execute the following query within a Visual
>C++ program...
>SELECT LastName from Employees WHERE LastName LIKE ?
>In the C++ program, I use the SQLBindParameter() to
>associate to the ?. The SQLBindParameter() variable used
>contains 'A%' (without the single quote).
>This is a very simple statement, but constantly returns
>no data.
>I run this exact statement within a MS Access database
>connection, and it returns 4 records. The MS Access wild
>card is also 'A%'. I think there is a bug in the SQL
>Server ODBC, but I cannot confirm this.
>Using the SQL Trace doesn't tell me the translations
>being performed within the ODBC driver to confirm a bug.
>Help...please...
>Regards,
>Angelo
>

You don't pay to get spam, why pay to clean it?
Visit http://www.spammarshall.com to create an account for free
<http://www.spammarshall.com>|||I'm probably saying something pretty dumb right now.. but how are you using
ODBC?
Why can you not simply (assuming you're using MS Visual C++ with MFC) do:
CString sql;
sql.Format(_T("SELECT LastName from Employees WHERE LastName LIKE %s",
_T("A%%");
rs.Open(sql...)
Or something like that.. ?
"Angelo" <anonymous@.discussions.microsoft.com> wrote in message
news:04ee01c3b861$09b06700$a301280a@.phx.gbl...

quote:

> I'm trying to execute the following query within a Visual
> C++ program...
> SELECT LastName from Employees WHERE LastName LIKE ?
> In the C++ program, I use the SQLBindParameter() to
> associate to the ?. The SQLBindParameter() variable used
> contains 'A%' (without the single quote).
> This is a very simple statement, but constantly returns
> no data.
> I run this exact statement within a MS Access database
> connection, and it returns 4 records. The MS Access wild
> card is also 'A%'. I think there is a bug in the SQL
> Server ODBC, but I cannot confirm this.
> Using the SQL Trace doesn't tell me the translations
> being performed within the ODBC driver to confirm a bug.
> Help...please...
> Regards,
> Angelo

|||Here is a sample application that results in a zero
records found eventhrough there are a number of records
within the table.
================================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
================================
I am using the latest SQL Server 8 with Visual C/C++ 6.00.
Everything has the latest software patches including the
DB and compilers.
Regards,
Angelo

quote:

>--Original Message--
>Could you post all the parameters for SQLBindParameter()

method. I am

quote:

>pretty confident that 'A%' is the correct syntax
>Pete
>Angelo wrote:
>
Visual[QUOTE]
used[QUOTE]
wild[QUOTE]
>--
>You don't pay to get spam, why pay to clean it?
>Visit http://www.spammarshall.com to create an account

for free

quote:

><http://www.spammarshall.com>
>
>

Friday, March 30, 2012

OLAP based reports not rendering

Monday, March 26, 2012

oh so slow query...

Wednesday, March 21, 2012

Odd SQL Performance problem

Odd Primary Key Error

Odd Primary Key Error

Tuesday, March 20, 2012

odd deadlocking behaviour

odd deadlocking behaviour

odd deadlocking behaviour

Monday, March 12, 2012

ODBCBCP Driver Mismatch

ODBC: SQL_ATTR_CONCURRENCY changing after simple select

ODBC, SQL Server Driver alias error

ODBC Virtual Driver

Wednesday, March 7, 2012

ODBC SQL Server Driver-Network Error

ODBC SQL Server Driver-Network Error

ODBC SQL Server Driver-Network Error

Saturday, February 25, 2012

ODBC Redux

Monday, February 20, 2012

ODBC LIKE clause

ODBC Isolation level

Blog Archive

About Me