General network error. Check your network documentation.

DTS Gurus !
We have a SQL 2000 package which uses a data driven query
which makes use of two stored procedures.
GETNeededRecords() and ProcessNeededRecords() with
transformations defined. Each record in the
result set returned by the GETNeededRecords is used by the
ProcessNeededRecords(). The Data Driven
step has a log file which logs the error messages.
Consistently the package fails reporting error
the below listed error. There is not enough we could find
online. Any help in this regard is
appreciated.
Command Error in Data Driven Query:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DBNETLIB][ConnectionRead (recv()).]
General network error. Check your network documentation.
Error Help File:
Error Help Context ID:0
If the package is running on the same server, I dont
understand why there should be network error.
Would this have to do with the details talked about in the
article
http://support.microsoft.com/default.aspx?scid=kb;en-
us;827452&Product=sql2k
.1. Please do a select * from this table, and preferably run a checktable on
this to make sure it's consistent.
2. If it is,hvae u tried reducing the number of rows/resultsets to be
processed? Same behaviour still?
3. Pls check the sql error logs and Event logs at the same time of DTS
failure.
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.sql

General Network Error

Hello.

I'm running into a rather intermittent problem when a search query is run on an ASP.Net web application.

The error is 'General Network Error' Check your Network Documentation...

As I said, it doesn't happen everytime which leads me to beleive that there is a problem with the database connection

There's probably no more than 50-60 max users hitting the app at any one time.

Anybody familiar with this?

Those can be a real nightmare to debug... See if this helps you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;875285

general network error

I have a publisher that has worked for months. Now, two of the three
subscriptions are getting errors:
The process could not query row metadata at the subscriber.
(source merge replication provider (agent): error number -217200996
General network error: check your network documentation
I turned on logging and everything is fine until:
[1/31/2007 4:26:58 AM]subscribeDB: {call sp_MSgetversion }
The process could not query row metadata at the 'Subscriber'.
The process could not enumerate changes at the 'Publisher'.
The merge process encountered an unexpected network error. The
connection to Subscriber 'subscribeDB' is no longer available.
Percent Complete: 0
The process could not query row metadata at the 'Subscriber'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200996
Message: The process could not query row metadata at the 'Subscriber'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSgetmetadatabatch(?,?,?)}
Percent Complete: 0
Category:SQLSERVER
Source: subscribeDB
Number: 11
Message: General network error. Check your network documentation.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process was successfully stopped.
I know the general network error is a catch-all. I have done Hilary's
keepalive and it never seems to fail. I have done a checkdb and checkdb
w/ rebuild_repair and that has made no difference. One subscriber has
had no problems, two started failing on Friday.
Any help would be greatly appreciated.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
FWIW, GNEs are almost always indicative of a hardware (switch, NIC, cable)
or driver error. Not 100%, but pretty close in my experience.
Are your SQL Servers and MDACs current? Can you see anything in a Netmon
trace run from both machines?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0TlpZTRHHA.1228@.TK2MSFTNGP06.phx.gbl...
>I have a publisher that has worked for months. Now, two of the three
> subscriptions are getting errors:
> The process could not query row metadata at the subscriber.
> (source merge replication provider (agent): error number -217200996
> General network error: check your network documentation
> I turned on logging and everything is fine until:
> [1/31/2007 4:26:58 AM]subscribeDB: {call sp_MSgetversion }
> The process could not query row metadata at the 'Subscriber'.
> The process could not enumerate changes at the 'Publisher'.
> The merge process encountered an unexpected network error. The
> connection to Subscriber 'subscribeDB' is no longer available.
> Percent Complete: 0
> The process could not query row metadata at the 'Subscriber'.
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147200996
> Message: The process could not query row metadata at the 'Subscriber'.
> Percent Complete: 0
> Category:COMMAND
> Source: Failed Command
> Number: 0
> Message: {call sp_MSgetmetadatabatch(?,?,?)}
> Percent Complete: 0
> Category:SQLSERVER
> Source: subscribeDB
> Number: 11
> Message: General network error. Check your network documentation.
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147200999
> Message: The process was successfully stopped.
>
> I know the general network error is a catch-all. I have done Hilary's
> keepalive and it never seems to fail. I have done a checkdb and checkdb
> w/ rebuild_repair and that has made no difference. One subscriber has
> had no problems, two started failing on Friday.
> Any help would be greatly appreciated.
> Thanks.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||Yes everything is current on all servers regarding the OS and SQL
programs.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi,
Dont take this the wrong way but I had this error and found that my
broadband had a fault on the line. I blamed the server for weeks and spent
hours trying this and that but it was the link between my servers blipping
out that was causing the problem.
Just a thought!.
"Darin" wrote:

> Yes everything is current on all servers regarding the OS and SQL
> programs.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||If my keepalive script (which doesn't really keep anything alive unless you
have a router which will shut down automatically when it senses no traffic)
reveals no network problems, it probably is a timeout issue. Change
querytimeout to something larger.
You can also run a ping -t and watch for dropped packets which just
indicates the line going down. replication is normally resilient to lossy
lines.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:u0TlpZTRHHA.1228@.TK2MSFTNGP06.phx.gbl...
>I have a publisher that has worked for months. Now, two of the three
> subscriptions are getting errors:
> The process could not query row metadata at the subscriber.
> (source merge replication provider (agent): error number -217200996
> General network error: check your network documentation
> I turned on logging and everything is fine until:
> [1/31/2007 4:26:58 AM]subscribeDB: {call sp_MSgetversion }
> The process could not query row metadata at the 'Subscriber'.
> The process could not enumerate changes at the 'Publisher'.
> The merge process encountered an unexpected network error. The
> connection to Subscriber 'subscribeDB' is no longer available.
> Percent Complete: 0
> The process could not query row metadata at the 'Subscriber'.
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147200996
> Message: The process could not query row metadata at the 'Subscriber'.
> Percent Complete: 0
> Category:COMMAND
> Source: Failed Command
> Number: 0
> Message: {call sp_MSgetmetadatabatch(?,?,?)}
> Percent Complete: 0
> Category:SQLSERVER
> Source: subscribeDB
> Number: 11
> Message: General network error. Check your network documentation.
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147200999
> Message: The process was successfully stopped.
>
> I know the general network error is a catch-all. I have done Hilary's
> keepalive and it never seems to fail. I have done a checkdb and checkdb
> w/ rebuild_repair and that has made no difference. One subscriber has
> had no problems, two started failing on Friday.
> Any help would be greatly appreciated.
> Thanks.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I am still getting the general network failure, and I agree that it is
the network. Unfortunitly, the ISP says the lines are fine, so we are
all pointing fingers at eachother.
I can open QA and connect to the remote server from the publisher fine,
and acctually issue SELECT statements. So, what I am thinking is if I
can get the replication to go in very small bursts it might have better
success. So, I am playing w/ the profile. I have am using one I created
that has:
bcpbatchsize=100000
changesperhistory 20
destthreads 4
the next 3 download all 20
fastrowcount 1
historyverboselevel 1
keepalive 300
logintimeout 15
maxdownload & maxupload 20
metadataretention 1
numdeadlockretries 25
pollinginternal 60
querytimout 600
srcthreads 3
startqueuetimout 0
3 uploads 20
validate 0
validateinterval 60
These settings haven't made any difference. I am still getting the
errors:
the process could not deliver inserts at the subscriber
general network error
the merge process encountered an unexpected network error. The
connection to the subscriber is no longer available.
2 of the 3 subscribers have errors, the third subscriber works
perfectly. Can anyone think of any settings in the profile that might
help "slow-down" the replication so it will try more and wait longer and
send small bursts only?
Thanks - this is getting very important as it has been messed up for a
week.
THanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***

general DB query

Hi! How do you query an entire db for a value?
I know how to do table queries from the SQL Analyzer, but not a full DB search.
Like, if I wanted to find a value '137.51 in a DB, what would I use?
Many thanks!
JJDynamic SQL
A Cursor
A temp table
And a lot of hours

General Database/Query and Form Design question

Currently working on an existing system written using an Access 2002 project
(.adp) and SQL Server 2000 and need to add some ehancements.
The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.
For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.
The proposed table design is as follows:
tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
...
tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
...
tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
...
tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
...
Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:
Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...
A Boat $50 $35 $25
The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.
Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?
Your assistance apreciated
Guy HortonWell, for each "asset", you got
AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds
etc.
Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.
If your cursor is in Boat, then you can enter:
Period Amount
1 - 12 $50
13 - 18 $35
etc.
I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.
Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...
http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:
1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.
Good luck,
Rob
"Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Currently working on an existing system written using an Access 2002
> project (.adp) and SQL Server 2000 and need to add some ehancements.
> The system is a leasing system where a customer leases one or more assets
> for a defined term (eg. 24, 36, 48 months...). Each lease may also be
> associated with a fixed, or variable monthly repayment regime.
> For example a customer wants to lease a Boat for 24 months for the first
> 12 months (period 1 - 12) they pay $50 per month, for period 13 - 18 they
> pay $35 per month, and for the last 6 months (period 19 - 24) they pay $25
> per month.
> The proposed table design is as follows:
> tblLease
> LeaseId int (identity) PK
> CustomerId FK
> TermId FK
> ...
> tblLeaseAsset (1:M relationship to tblLease)
> AssetId int PK
> LeaseId int PK/FK to tblLease
> AssetDescription
> ...
> tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
> 1:M relationship to tblLeaseTerm)
> AssetId int PK/FK
> TermPeriodId int PK/FK
> Payment decimal (19,4)
> ...
> tblLeasePeriodTerm (1:M relationship to tblLease)
> TermPeriodId int (identity) PK
> LeaseId int FK to tblLease
> FromTermPeriod small int
> ToTermPeriod small int
> ...
> Whilst the users are happy to enter the initial lease and period/term
> information as a Parent/Main form and Child/Subform combination. They
> would like to be able to enter the Asset and payment information together
> as a single Child/Subform:
> Period Period Period
> Asset Description 1 - 12 13 - 18 19 - ...
> A Boat $50 $35 $25
> The problem is that this requires a pivot table/cross tabulation type view
> of the data and these types of queries are not generally updatable.
> Does anyone have any ideas how I might achieve the objective either in
> terms of database, query or form design so that users can insert, update,
> delete and view records?
> Your assistance apreciated
> Guy Horton
>|||Albert,
Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.
I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.
Your thoughts appreciated.
Best Regards,
Guy
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Well, for each "asset", you got
>
> AssetName AssetCost WherePurchased
> Boat $15,000 WalMart
> Car $5,000 MacDonalds
> etc.
> Now, just put another sub-form to the "right" of a above where you can
> enter "many" values for each of the above.
> If your cursor is in Boat, then you can enter:
> Period Amount
> 1 - 12 $50
> 13 - 18 $35
> etc.
> I can think of "many" cases where you got a detail line, and need "many"
> for that details. consider the QuickBooks when you write a single check,
> and a split amount, you need to "split" out the funds to "many" values.
> So, the solution is to make two side by side sub-forms.
> Take a look at the following screen shots, and especially the last one
> where I have a "classic" cheque "distribution" screen (for each
> check/person on the left, I can enter "many" split values for that
> particlar amount (donation in this example) on the right side...
> http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||Robert,
Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.
Your thoughts appreciated
Guy
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> I'd strongly recommend Albert's way, but if you're absolutely forced to
> doing it on the same line, you can try a few different things that I can
> think of:
> 1. Play with Access' PivotTable features. I've never found them all that
> useful, and very klunky to use, but it may get you where you're trying to
> go. Don't ask me for more detailed info on how to do that, though, I
> generally avoid PivotTables like the plague.
> 2. Implement a temporary table that goes across as you'd like it to, then
> as each record is read/updated, transfer the values to/from the "real"
> table in the OnCurrent and Before/AfterUpdate events.
> 3. Use an embedded control of some kind (Hierarchical FlexGrid?) to
> display the data instead.
>
> Good luck,
> Rob
> "Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

gatting at my data!

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim BuntonHi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose ow
ner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies
- I an assuming that this means there is a table called companies whose owne
r is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and c
onnect then I can access the table!!! - Select user_name informs me that I'm
dbo [I can still access the table whether or not Select . . permission
s are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition'
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Truste
d Connection (windows authentification] to work for Northwind and resorted t
o using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.
2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton

gatting at my data!

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton

future of English Query??

I think English Query is a great utility, but I didn't hear anything about
the improvement and enhancement in SQL 2005.
Does anyone know the future of English Query? We should continue using
English Query in the development or we should decommission it?SQL Server 2005 Books Online says:
English Query has been discontinued. There are no upgrade options or setup
programs for English Query in this release.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:E03C9DE5-201E-40E9-9522-DAF277331DA2@.microsoft.com...
I think English Query is a great utility, but I didn't hear anything about
the improvement and enhancement in SQL 2005.
Does anyone know the future of English Query? We should continue using
English Query in the development or we should decommission it?|||Do you know is there any product from Microsoft can replace it?
"Narayana Vyas Kondreddi" wrote:

> SQL Server 2005 Books Online says:
> English Query has been discontinued. There are no upgrade options or setup
> programs for English Query in this release.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:E03C9DE5-201E-40E9-9522-DAF277331DA2@.microsoft.com...
> I think English Query is a great utility, but I didn't hear anything about
> the improvement and enhancement in SQL 2005.
> Does anyone know the future of English Query? We should continue using
> English Query in the development or we should decommission it?
>
>

further to problem with obtaining first/last occurance of contiguous blocks of data

Hi again, I was hoping someone could help me create a sql query to
minimizing the content in my table based on a few rules.
Further to my previous post...
f2b9da63" target="_blank">http://groups.google.com.au/group/m...br />
f2b9da63
... I have decided to use triggers to process my table (see trigger solutio
n
in my previous post). However since triggers are slow at processing, I aim
to search for a solution to minimize my initial table structure so the
triggers have less data to work with (hence process it much quicker). What I
am looking for is a method to perform the following:
This is my initial table (as an example):
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
I want my final table to look like this:
[system] [date] [isOn]
A 01 0
A 04 1
-
A 06 0
A 20 1
A 21 0
-
A 27 1
-
-
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
-
-
-
B 67 0
... where i have placed a '-' sign to indicate the rows i need deleted
I am deleting rows under the following conditions:
1. for a contiguous block of 1's WITHIN the same client range, delete all
but the first one ( i.e.the one at the earliest date)
2. for a contiguous block of 0's WITHIN the same client range, delete all
but the first one (i.e. the one at the earliest date)
i.e. notice above that even though there is a contiguous block of 1's from
date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
B.
After this table is thus processed, I can use my cursor on it.
Any help would be really appreciated!
many thanks
wileyI have scrapped my cursor implementation for a much faster set-based
approach put forward by Itzik Ben-Gan (as im my previous post). Thanks
everyone!
cheers
wiley
"wiley" <wiley@.nospam.com> wrote in message
news:uK5SI4SFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi again, I was hoping someone could help me create a sql query to
> minimizing the content in my table based on a few rules.
> Further to my previous post...
> 29f2b9da63" target="_blank">http://groups.google.com.au/group/m... />
29f2b9da63
> ... I have decided to use triggers to process my table (see trigger
> solution in my previous post). However since triggers are slow at
> processing, I aim to search for a solution to minimize my initial table
> structure so the triggers have less data to work with (hence process it
> much quicker). What I am looking for is a method to perform the following:
> This is my initial table (as an example):
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
> I want my final table to look like this:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> -
> A 06 0
> A 20 1
> A 21 0
> -
> A 27 1
> -
> -
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> -
> -
> -
> B 67 0
> ... where i have placed a '-' sign to indicate the rows i need deleted
> I am deleting rows under the following conditions:
> 1. for a contiguous block of 1's WITHIN the same client range, delete all
> but the first one ( i.e.the one at the earliest date)
> 2. for a contiguous block of 0's WITHIN the same client range, delete all
> but the first one (i.e. the one at the earliest date)
> i.e. notice above that even though there is a contiguous block of 1's from
> date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
> B.
> After this table is thus processed, I can use my cursor on it.
> Any help would be really appreciated!
> many thanks
> wiley
>

Functions in SQL Server7

Is it possible to create Functions in SqlServer 7?
I have a huge query > 500,000 rows that I want to select a subset of using a
function
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
**************************************
--Function
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
****************************************
******Just in SQL Server 2000 for now.
AMB
"Sanjay Pais" wrote:

> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
a
> function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>
>|||No, but you can do this in the where clause:
Select IdentityInd, ColA, ColB
From TableA
Where EXISTS( Select inExists.IdentityInd
From TableA as inExists
Where IdentityInd = tableA.IdentityInd
AND CONTAINS(inExists.ColA, tableA.ColB))
Can't you? It should be preferrable performancewise anyhow, I would expect.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
> a function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>|||You can't use two columns in a contains clause which caused my dilema in the
first place :)
Sanjay
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
> No, but you can do this in the where clause:
> Select IdentityInd, ColA, ColB
> From TableA
> Where EXISTS( Select inExists.IdentityInd
> From TableA as inExists
> Where IdentityInd = tableA.IdentityInd
> AND CONTAINS(inExists.ColA, tableA.ColB))
> Can't you? It should be preferrable performancewise anyhow, I would
> expect.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>|||Ah, sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:uI%23xQvWBFHA.3700@.tk2msftngp13.phx.gbl...
> You can't use two columns in a contains clause which caused my dilema in
> the first place :)
> Sanjay
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
>

Functions and Execution Plan

The cost of query with usage of functions is as same as that of without
functions

In the below code, the query cost of insert is 0.02% and two select
statements costs same 0.04%

Declare @.t table(mydate datetime)
Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into @.t values(getdate())
set @.i=@.i+1EndSelect mydate from @.t
Select convert(varchar,mydate,112) from @.t

But I thought usage of convert function will take more query cost
What do you think of this?

MadhivananAdding a CONVERT() to the output is very little extra work, but I
suspect that you're referring to the fact that using a function on a
column in the WHERE clause can prevent MSSQL from using an index. That
can have a significant impact on the query plan, eg:

create table dbo.m (mydate datetime primary key)

Declare @.i int
set @.i=1
while @.i<=5000
Begin
insert into m values(getdate() + @.i)
set @.i=@.i+1
End

-- now run these two queries in the same batch

select *
from m
where mydate between '20100815' and '20100917'

select *
from m
where convert(char(8), mydate, 112) between '20100815' and '20100916'

On my test server, the first query takes 11% of the batch, the second
is 89% - although they are functionally equivalent, the first one can
do a seek in the clustered index, but the second must scan it. That's
not to say that functions in the SELECT will never affect the query
plan or cost, but when reviewing code it's probably more important to
look at the WHERE clause first.

Simon|||Thanks Simon

So only in Where condition it affects the performance and not in select
isnt it?

Madhivanan|||I'm sure that functions in the SELECT clause can affect the query cost
- nested string functions, nested CASE expressions, a scalar UDF which
looks up other tables etc. And all other things being equal, "SELECT
col1" will be more efficient than "SELECT somefunc(col1)", simply
because MSSQL has less work to do.

But the difference may be extremely small (as in your example), and I
guess that in most cases, any really big differences in performance
would come from functions in the WHERE clause, not the SELECT clause.
Of course there are many other reasons why a query might run slowly -
missing indexes, out-of-date statistics and so on - which have nothing
to do with functions at all, so if you have a performance problem with
a specific query, then it's best to start by looking at the query plan
before you think about how to re-write the code.

Simon|||Madhivanan (madhivanan2001@.gmail.com) writes:
> So only in Where condition it affects the performance and not in select
> isnt it?

What matters is that if you put an indexed column into an expression,
the index can no longer be used for searches.

If you have

where mydate between '20100815' and '20100917'

and there is an index on mydate, SQL Server can use that index to
find the matching rows. But if you say:

convert(char(8), mydate, 112) between '20100815' and '20100916'

that index can no longer be used, because that index holds datetime
values, and this is a string expression.

Note that if mydate is not indexed, the only cost for the function
call is the function call itself. In this case, it's not more expensive
that having it in the SELECT list. (Except that if it's in a WHERE
clause, it may be applied to more values.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Well
Thanks for the suggesstions

Madhivanan

Functions

I have a subquery that is running really slow when i use a date from the parent query

* the function udfMinContact returns a table of the earliest contact for every child that occured after the date passed to the function

* the function udfMaxReferral returns a table of the latest Referral for every child that occured before the date passed to the function

* the referral happens first, then the child is contacted. i'm looking for contacts that happed over 45 days after the referral
*************************************************************************************
DECLARE
@.EndDate DateTime,
@.StartDate DateTime

SET @.StartDate = '4/1/2007'
SET @.EndDate = '6/30/2007'

SELECT c.ChildId, c.FN, c.LN, c.DOB

FROM Child c INNER JOIN udfMinContact(@.StartDate) ct ON c.ChildID = ct.ChildId

WHERE ct.ContactDate BETWEEN @.StartDate AND @.EndDate

AND EXISTS
(
SELECT ChildId
FROM udfMaxReferral(ct.ContactDate) r
WHERE r.ChildId = c.ChildId
AND r.ReferralDate < DATEADD(dd, -45, ct.ContactDate)

)
*********************************************************************************************

If i run as is, it takes over 40 min. If i replace 'ct.ContactDate' which i highlighted with a static date like '1/1/2007' it runs in just a few seconds.

any idea why the drastic time difference and any suggestions on how to speed it up?

thanks

Check the Execution Plan.

Your FUNCTION has to fully execute for each and every row in the table, perhaps two times per row if it needs to re-calculate for the sub-query..

You may be able to substanially improve execution speed if you JOIN with the data that has the earliest contact INSTEAD of using the function. (I'm assuming that the function is a query.)

Please post the entire FUNCTION code and we can better determine the optimal way to deal with your issue.

|||

Is udfMaxReferral a multi-statement or inline TVF? Look at the query plan to see how the join is being done. If it is a nested loop join then it is possible that the TVF is invoked for every row that is being joined in the outer SELECT statement. Also, if the TVF is multi-statement then there are no statistics on the rows being returned so the plan will be sub-optimal. You should consider using inline TVF so that the query can be optimized as a whole.

Now, as for the question why if you use a variable or column in the TVF it is slower than a value or constant is due to plan caching and query optimization. When you specify a constant in a predicate or parameter to SP or function etc then the query optimizer can use that value and determine the best plan based on the available statistics. On the other hand, if you specify a variable or column then the value is not known and it can be any value within the domain of a data type so the query optimizer will pick a plan that works optimally for any search value. See the white paper on compilation, recompilation for more details on how this works.

|||Function: udfMinContact
Description: Returns info on the earliest 'IF' Contact (that occured on or after the StartDate) for every child

ALTER FUNCTION [dbo].[udfMinContact]
(
@.StartDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ContactId uniqueidentifier,
ContactDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ContactId, ContactDate
FROM Contact ct
WHERE ContactId =
(
SELECT TOP(1) ContactId
FROM Contact
WHERE ChildId = ct.ChildId
AND ContactDate >= @.StartDate
ORDER BY ContactDate ASC, CREATE_TIME ASC
)

RETURN
END;

*********************************************************************************

Function: udfMaxReferral
Description: Returns info on the most current Referral (that occured on or before the EndDate) for every child

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral r
WHERE ReferralId =
(
SELECT TOP(1) ReferralId
FROM Referral
WHERE ChildId = r.ChildId
AND ReferralDate <= @.EndDate
ORDER BY ReferralDate DESC, CREATE_TIME DESC
)

RETURN
END;

*********************************************************************************************************

I know the functions are not ideal, but the way the database is set up, it's the best i could do. there are many cases where a child will have several contacts or referrals on the same day so this is how i forced only 1 to be returned
|||

you can try this, and check if it can change your query speed,

notice, the get_datetime is a function needed you defined it.

i think ORDER BY clause always waste resource!!!

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral a inner join

(

SELECT ChildId,ReferralId,MAX(get_datetime(ReferralDate,ReferralTime) datetime
FROM Referral

WHERE ReferralDate <= @.EndDate

GROUP BY ChildId,ReferralId

) b on a.ChildId=b.ChildId and a.ReferralId=ReferralId.ReferralId and get_datetime(a.ReferralDate,a.ReferralTime)=b.datetime

WHERE ReferralDate <= @.EndDate

RETURN
END;

|||

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

Use the following functions,

Code Snippet

CREATE FUNCTION [dbo].[udfMinContact]

(

@.StartDate datetime

)

RETURNS TABLE

AS

RETURN (

SELECT ChildId, ContactId, ContactDate

FROM Contact ct

WHERE ContactId =

(

SELECT TOP(1) ContactId

FROM Contact

WHERE ChildId = ct.ChildId

AND ContactDate >= @.StartDate

ORDER BY ContactDate ASC, CREATE_TIME ASC

)

)

GO

CREATE FUNCTION [dbo].[udfMaxReferral]

(

@.EndDate datetime

)

RETURNS TABLE

AS

RETURN

(

SELECT ChildId, ReferralId, ReferralDate

FROM Referral r

WHERE ReferralId =

(

SELECT TOP(1) ReferralId

FROM Referral

WHERE ChildId = r.ChildId

AND ReferralDate <= @.EndDate

ORDER BY ReferralDate DESC, CREATE_TIME DESC

)

)

|||

Manivannan.D.Sekaran wrote:

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

What is the difference? Sorry I'm still pretty new to anything more the simple SQL

functions

I need to do a LEAST() and Greatest() function in SQL query.

Is this possible in anyway shape or form?

Here is an example...

tblTable.oneID = 4
tblTable.twoID = 5

SELECT ... LEAST(tblTable.oneID,tblTable.twoID) FROM tblTable

and it returns 4. Or in the GREATEST function, it would return 5.

Please help.

Thanks in advanceI think i figured it out...

LEAST function = SELECT (CASE WHEN oneID < twoID THEN oneID ELSE twoID) as LeastOFtheTWO FROM ...

GREATEST function = SELECT (CASE WHEN oneID > twoID THEN oneID ELSE twoID) as LeastOFtheTWO FROM ...|||You are on the right track. Try the following for the least case:

select case when (a < b) then a else b end
from (select min(field1) as a, min(field2) as b from table) as abc

Function that replaces ntext and compares ntext with nvarchar

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)(verb13@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"
>
The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.
>
So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"
>
How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.

However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:

SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?

(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)

Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.

--
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|||It is just what I needed. Thanks a lot.

On Nov 29, 12:42 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(ver...@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

>

Quote:

Originally Posted by

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

>

Quote:

Originally Posted by

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

>

Quote:

Originally Posted by

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

>
I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.
>
However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:
>
SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?
>
(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)
>
Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Function Suppressing Index

I have the following query that needs to be rewritten (if possible) so that
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thank
s
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
Message posted via http://www.droptable.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:

> I have the following query that needs to be rewritten (if possible) so tha
t
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column
is
> the goal, but I am not seeing how that might be done in this instance. Tha
nks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.droptable.com
>

Function Suppressing Index

I have the following query that needs to be rewritten (if possible) so that
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thanks
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
--
Message posted via http://www.sqlmonster.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:
> I have the following query that needs to be rewritten (if possible) so that
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column is
> the goal, but I am not seeing how that might be done in this instance. Thanks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.sqlmonster.com
>

Function Keys in Query Manager

This question is surely a general PC question, but someone
here surely knows how to program a hotkey, or funciton key
F6, F7, etc. to put in a string of text to avoid some
typing. I'd like to hit F12 and automatically get:
SELECT * FROM objects WHERE objectid =
I'm having to type this in ALL OF THE TIME.
Any help appreciated.
thanks,
--Bob
Open the Object Browser and click on the Objects tab at the bottom. Drill down to your favourite table, right click and select one of the scripting options.
You're also able to add your own templates as well as using the defaults.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BobS" <anonymous@.discussions.microsoft.com> wrote in message news:1aeaa01c41e8d$1bf687e0$a101280a@.phx.gbl...
This question is surely a general PC question, but someone
here surely knows how to program a hotkey, or funciton key
F6, F7, etc. to put in a string of text to avoid some
typing. I'd like to hit F12 and automatically get:
SELECT * FROM objects WHERE objectid =
I'm having to type this in ALL OF THE TIME.
Any help appreciated.
thanks,
--Bob

Function for return element list from a query

Hello,

I have do a sql function for return a list of element from a query send in variable.

When I test the function on self I have no problem.

But when I use the function in a sql query I have problem.

example :

Code Snippet

SELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,
dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

This is the error :

Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.

When I do an exec of the function I have this problem :

Code Snippet

exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'

This is the function

Code Snippet

CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@.QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @.v_List_ITEM as varchar(8000)
set @.v_List_ITEM=''

if @.QUERY is not null
Begin
declare @.cur_Lect_ITEM CURSOR;
declare @.FUNCTION AS NVARCHAR(4000);
Declare @.ITEM as VARCHAR(255);

SET @.FUNCTION = 'set @.mainCursor=cursor for ' + @.QUERY + ' for read only open @.mainCursor'

EXEC sp_executesql @.FUNCTION,N'@.mainCursor cursor output', @.cur_Lect_ITEM output

fetch next from @.cur_Lect_ITEM into @.ITEM
while @.@.fetch_status=0
begin
set @.v_List_ITEM=@.ITEM + ' ; ' + @.v_List_ITEM
fetch next from @.cur_Lect_ITEM into @.ITEM
end
deallocate @.cur_Lect_ITEM
SET @.v_List_ITEM=REPLACE(REPLACE(@.v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @.v_List_ITEM=left(@.v_List_ITEM,len(@.v_List_ITEM)-3)
End
RETURN @.v_List_ITEM
END

Can you help me please?

Thank you

You DO NOT execute a FUNCTION.

You use a FUNCTION inline (like an expression), or

you use a FUNCTION like a table.

Perhaps your 'function' should be a Stored Procedure...

|||

1. You can't use dynamic SQL in Functions

2. You needn't this much complex query to concatinate the items

If you use sql server 2005 the following query will work for you..

Code Snippet

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

(SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) + ';' as [text()]

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_CONSTRUCTION.N_SUPPLIER_ID )

for XML path('')) AS V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER APPLI_SUPPLIER_MAIN

INNER JOIN APPLI_SUPPLIER_SKILL APPLI_SUPPLIER_SKILL_MAIN

ON APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL_MAIN.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL_MAIN.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

If you use sql server 2000,

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

DBO.GET_PROCESS_ITEMS(APPLI_CONSTRUCTION.N_SUPPLIER_ID) V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER

INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

Hello Manivannan.D.Sekaran

thank you for your answer.

I use SQL SERVER 2000 SP4

The objective of the function it's to use any query for return a list of element

It's not possible to do a generic function?

|||

In SQL Server 2000, we can't able to achieve this. You have to create a separate function. Since you have to embedded this function in your query you have to use the UDF (for each requirement).

In SQL Server 2005, you can achieve this using .NET CLR integration..

|||

thank you for answer ............. I have not chance|||

Hello,
I would like send a list of ID in the function.
How I can do that?
Do you have a idea?
thank you

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN @.SKILL_ID

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

|||

You can use the SPLIT UDF,

Code Snippet

CREATE FUNCTION SPLITINTOROWS

(

@.LIST AS VARCHAR(8000),

@.DELIMITER AS VARCHAR(10)

)

RETURNS @.LISTOFIDS TABLE (ITEM VARCHAR(8000))

AS

BEGIN

WHILE CHARINDEX(@.DELIMITER, @.LIST) <> 0

BEGIN

INSERT INTO @.LISTOFIDS

VALUES(SUBSTRING(@.LIST,1,CHARINDEX(@.DELIMITER,@.LIST)-1))

SET @.LIST = SUBSTRING(@.LIST, CHARINDEX(@.DELIMITER,@.LIST)+1, LEN(@.LIST))

END

INSERT INTO @.LISTOFIDS VALUES(@.LIST)

RETURN;

END

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (select item from SplitIntoRows(@.SKILL_ID, ','))

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

|||

Thank you very much

You are my god

Function for getting a extension from a filename

I need a function that returns the extension of a filename, im not so the T-SQL expert so i wanted to ask if this query is ok?

would it be faster to do this as a CLR function?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_GetFileExtension]
(
@.Name nvarchar(256)
)

RETURNS nvarchar(256)
AS
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - 3, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - 2, 3 ));

DECLARE @.i int;
SELECT @.i = 1;

WHILE ( @.i < LEN(@.Name) )
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - @.i, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - @.i + 1, @.i ));
ELSE
SELECT @.i = @.i + 1;
END

RETURN '';
END

In .NET, you can use the FileInfo class:

return new FileInfo(filename).Extension;

You need to run some tests to see if that's faster than SQL, though...|||I would recommend that you use some other method personally. If part of the consumer is using .NET or if you are using SQL 2k5 then you have Path.GetExtension (a static method that when provided with a filename returns the extension).
|||i tried the clr way (with Path.GetExtension) and it's the faster solution. it's over 10 times faster than the sp, that's much more than i expected...|||Yep. Glad the problem was solved.

Function call in Dataset Query

Hello Guys,

I have a question that seems easy but I can not figure out...

Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.

I have a Dataset that has a calculation for a column within a select statement

Query Pseudocode:

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")

Custom Code:

Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function

How To use:

If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.

Problem:

How do I add this code reference in the following dataset select statement

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1

---

I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...

---

But it did not work...gave me this error:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

BUTTONS:

OK

---

Help!

P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.

anyone...?|||

Hello,

Unfortunately, you can't use custom code in your SQL query (as you've found). What you can do is supply both fields in the calculation (cost and income) to the report and have it do the percentage, or create a 'SafeDiv' function in SQL and do it there.

Hope this helps.

Jarret

|||

On the DataSet

Use Generic Query Designer

Then you can use

="Select tableName.ProductID, "& code.Safediv(Parameters) & " as ColumnName

From tableName"

Try to adapts it to your report.

I hope it help you.

|||Thanks I will try it...|||

I think this will work...I will reply with result...

Thank You!