Access Denied Error

I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3 o
n
SQL. I am getting an error when I query against my views. The views use a
linked server that grabs data from FoxPro dbf files located on the same
machine.
In resolving a memory issue, we disabled the 'Allow InProcess' setting on
the OLE DB for ODBC Drivers provider. I don't think this has anything to do
w/ the access denied error because I enabled 'Allow InProcess' to test it an
d
then disabled again when I got the error.
The error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
The account is a domain account that is part of the administrators group on
the server. It is also a System Administrator role in SQL.
Any help is appreciated. Thanks, PhilPhil,
The obvious things to check are that the account running SQL Server
(which is likely not your Windows account) has the proper permissions
on the files in question, and that you are referring to them in a way that
is correct for that account (not using a drive letter defined at logon for
your own Windows account, for example)
Next, you could try a different way of accessing the linked server data.
Try an alternative among the 4-part name lnksrv.database.owner.table,
OpenQuery, OpenRowSet, or OpenDataSource. If there is another
provider besides MSDASQL, try it.
Beyond that, unfortunately, most newsgroup threads with this question
end with
no resolution. :( My best guess is to try adding this registry key,
though I'm not
certain this works for an arbitrary provider.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disallow
AdhocAccess
Make this a REG_DWORD with value 0.
Here is a link to a few dozen newsgroup threads on this issue - maybe
you'll find something.
http://groups-beta.google.com/group...rface+sqlserver
If you resolve this problem, please let us know!
Steve Kass
Drew University
phil wrote:

>I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3
on
>SQL. I am getting an error when I query against my views. The views use a
>linked server that grabs data from FoxPro dbf files located on the same
>machine.
>In resolving a memory issue, we disabled the 'Allow InProcess' setting on
>the OLE DB for ODBC Drivers provider. I don't think this has anything to d
o
>w/ the access denied error because I enabled 'Allow InProcess' to test it a
nd
>then disabled again when I got the error.
>The error message is:
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'MSDASQL' reported an error. Access denied.
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
>returned 0x80070005: Access denied.].
>The account is a domain account that is part of the administrators group on
>the server. It is also a System Administrator role in SQL.
>Any help is appreciated. Thanks, Phil
>
>|||Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
Provider for ODBC Drivers. However, I had deselected this setting to correc
t
a memory issue I am having. Now, I'm looking to resolve that a different
way. (argh.)
- Phil
"Steve Kass" wrote:

> Phil,
> The obvious things to check are that the account running SQL Server
> (which is likely not your Windows account) has the proper permissions
> on the files in question, and that you are referring to them in a way that
> is correct for that account (not using a drive letter defined at logon for
> your own Windows account, for example)
> Next, you could try a different way of accessing the linked server data.
> Try an alternative among the 4-part name lnksrv.database.owner.table,
> OpenQuery, OpenRowSet, or OpenDataSource. If there is another
> provider besides MSDASQL, try it.
> Beyond that, unfortunately, most newsgroup threads with this question
> end with
> no resolution. :( My best guess is to try adding this registry key,
> though I'm not
> certain this works for an arbitrary provider.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disall
owAdhocAccess
> Make this a REG_DWORD with value 0.
> Here is a link to a few dozen newsgroup threads on this issue - maybe
> you'll find something.
> http://groups-beta.google.com/group...rface+sqlserver
> If you resolve this problem, please let us know!
> Steve Kass
> Drew University
> phil wrote:
>
>|||An alternative solution is to use the -g switch, which we may pursue. This
is explained in BOL. - Phil
"phil" wrote:

> Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
> Provider for ODBC Drivers. However, I had deselected this setting to corr
ect
> a memory issue I am having. Now, I'm looking to resolve that a different
> way. (argh.)
> - Phil
>

Access Deleting from MSDE - Help!

Hi
I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.
Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.
Can anyone help with this please?
Regards
Tom
hi Tom,
Tom wrote:
> Hi
> I am a newbie to MSDE and am having a problem deleting records from
> an MSDE backend using an Access query front end.
> Our main data is stored on a UNIX server (informix type database) and
> we link to it using ODBC. We load records to be updated into an
> Access database and then use the Access table as a reference for
> updating the MSDE database. This means I have a reference table in
> Access that is joined to the MSDE table. The records that exist in
> the Access table are the ones to be deleted from MSDE. When I use the
> Access UI to create a 'delete' query, I get an error message (cannot
> delete records from this table) when I try to run the query. I can
> delete the records individually.
> Can anyone help with this please?
when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cadgbF6mrrenU1@.individual.net...
> hi Tom,
> Tom wrote:
> when defining the linked table in Access, did you identify the column(s)
> uniquelly idenitying each row (primary key)?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tom,
Tom wrote:
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is
> called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
> [WORKS_ORDER], which is a works order number. It joins to the MSDE
> table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
> name, [WORKS_ORDER]. The join is set to select all from
> 'WORKS_ORDER_TO_DELETE', and only those that match on the join in
> 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
> set the query as a 'delete', so the only field in the UI grid is
> labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
> is set to 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing
> to do with MSDE: it seems to be when you are identifying the records
> to delete through a join. I have also tried specifying all the
> columns individually in the records to be deleted, with the same
> result. I have also added the single field in the table
> 'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
> 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it
> best to give all the details rather than to try and guess at what you
> meant.
unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )
INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )
SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID
DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2
is a valid T-SQL syntax...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Tom,
I don't know if this will help....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):
'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)
'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK
'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null
Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.
OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).
If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)
gary
"Tom" wrote

> Hi Andrea
> In the Access UI I have the two tables. The local Access table is called
> 'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
> a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
> where it joins to a field of the same name, [WORKS_ORDER]. The join is set
> to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
> the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
> query as a 'delete', so the only field in the UI grid is labelled
> 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
> 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing to do
> with MSDE: it seems to be when you are identifying the records to delete
> through a join. I have also tried specifying all the columns individually
> in the records to be deleted, with the same result. I have also added the
> single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
> it's delete option as 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it best
> to give all the details rather than to try and guess at what you meant.
> Regards
> Tom
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3cadgbF6mrrenU1@.individual.net...
>
|||For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default...b;en-us;207761
"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.
To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "
|||You are a star, Gary.
All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!
Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.
Regards
Tom
"Gary Walter" <garylwplease@.nospamwamego.net> wrote in message
news:eLGCHSoQFHA.1476@.TK2MSFTNGP09.phx.gbl...
> For "Access part,"
> ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
> http://support.microsoft.com/default...b;en-us;207761
> "Delete Queries
> *****************************************
> When a delete query contains more than one table,
> such as a query that deletes duplicate records from one of the tables,
> the UniqueRecords property must be set to Yes for all versions of
> Microsoft Access.
> ******************************************
> However, because the default value for UniqueRecords is No in Access 2000,
> you must set the value of this property manually when you create a new
> delete query in Access 2000.
> To do so, follow these steps:
> Open the delete query in Design view.
> If the property sheet is not already open, on the View menu, click
> Properties.
> Click an empty area in the upper half of the query window so that the
> property sheet displays "Query Properties" in the title bar.
> Set the UniqueRecords property to Yes.
> Save the query, close it, and then run the query. "
>
|||Hi Andrea
Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.
However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).
Many thanks for that.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cc9f6F6dtrtmU1@.individual.net...
> hi Tom,
> Tom wrote:
> unfortunately I'm not an Access guy and I do not feel very confortable
> with Access UI... perhaps you'll have better luck posting in Office NG
> hierarchy...
> apologise
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||:D
found another way...
modifying the SQL statement to
DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID
did the trick, even with no primary key
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Access Data From Multiple SqlSever Instances

Hi
I am having two SQL Server instances installed at two different systems and want to fetch data from both of them in a sigle query with joins. Somebody suggested that I need to link up the sql server instances at the admin level. If you have an idea about the process of linkng multiple sql server instances kindly convey it to me.

For Details of the discussion kindly follow the undergiven link

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

Thanks

How to add linked servers is described in the Books Online topic for sp_addlinkedserver and the related topics. See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fed3adb0-4c15-4a1a-8acd-1b184aff558f.htm.

|||Thank you Steven, I'll check it up.

Access Crosstab to an SQL Express PIVOT

I need some help in converting this crosstab SQL from an Access query to a View in SQL Server Express:

TRANSFORM First(tblPhones.PhoneNumber)AS FirstOfPhoneNumberSELECT tblPhones.ClientIDFROM tblPhonesGROUP BY tblPhones.ClientIDPIVOT tblPhones.PhoneType;

Hello:

You may need to change hardcoded phonetype in your case:

If this is not exact what you want, you can post some sample data and expected result here.

SELECT

ClientID,

MIN

(CASEWHEN PhoneType='office'THEN PhoneNumberEND)as OfficePhoneNumber,

MIN

(CASEWHEN PhoneType='home'THEN PhoneNumberEND)as HomePhoneNumber

FROM

(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) p

WHERE

PhoneTypeIN('home','office')

GROUP

BY ClientID

--Or PIVOT solution:(SQL Server 2005)

SELECT

ClientID, office, home

FROM

(SELECT ClientID, PhoneType, PhoneNumberFROM tblPhones) p

PIVOT

(MIN(PhoneNumber)FOR PhoneTypeIN([office], [home]))AS pvt|||

I will give this try, but is it possible to do this without hard coding the phone type names? In the access query it just shows a gorup by list of used phonetypes.

|||

FYI. This is a version with dynamic pivot for your table.

SETNOCOUNTON

DECLARE @.TASTABLE(ynvarchar(10)NOTNULLPRIMARYKEY)

INSERTINTO @.TSELECTDISTINCT PhoneTypeFROM tblPhones

DECLARE @.T1ASTABLE(numintNOTNULLPRIMARYKEY)

DECLARE @.iASint

SET @.i=1

WHILE @.i<10

BEGIN

INSERTINTO @.T1SELECT @.i

SET @.i=@.i+1

END

DECLARE @.colsASnvarchar(MAX), @.yASnvarchar(10)

SET @.y=(SELECTMIN(y)FROM @.T)

SET @.cols= N''

WHILE @.yISNOTNULL

BEGIN

SET @.cols= @.cols+ N',['+CAST(@.yASnvarchar(10))+N']'

SET @.y=(SELECTMIN(y)FROM @.TWHERE y> @.y)

END

SET @.cols=SUBSTRING(@.cols, 2,LEN(@.cols))

DECLARE @.sqlASnvarchar(MAX)

SET @.sql= N'SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN('+ @.cols+ N')) AS pvt'

EXECsp_executesql @.sql

|||

You are obviously very good at this, I tried to test this and get a syntax error neer SET.

I tried to run the other PIVOT and received this type or message 'You must set your Compatibiltiy level higher (sp_dbcmptlevel)'. Does this mean that SQL Server 2005 Express does not support the PIVOT?

|||

Hello:

--You need to change the Compatibility level to SQL Server 2005 which is 90.

EXEC sp_dbcmptlevel yourDatabasename, 90;

--Or you can use SQL Server Management Studio (Express), right cilck on your database name to get the property window; under Options tab>> Compatibility level: ; you can choose from SQL Server 7.0(70), 2000(80), or 2005(90) from the dropdownbox.

|||

Ok, I set the compatibiltiy to 90, the first Pivot now works, but the dynamic PIVOT does not. I receive the following error:

The Set SQL construct or statement is not supported.

Syntax error near SET

Any thoughts.

|||

Here is one more piece that might help: I added the AS keyword to the start of the sproc and it saved (I really need it to be a view, but 1 thing at a time) it ran with the following error:

String or binary data would be truncated.

The statement has been terminated.

Incorrect syntax near ')'.

No rows affected.

|||I dont intend to steal limno's credit but to answer your question, you prbly have a variable that is beingset a value higher than what it can take. Increase the size of your string variable for which you are getting the error.|||

Ok, that helped I can save and run it as a sproce. I increased all of the nvarchars to (20).

I have tried to save it as a view and I get: 'The Set SQL construct or statement is not supported.' Then I get a incorrect syntax near keyword SET.

Any thoughts on this?

|||

Hello,

Could be this line?

@.TASTABLE(ynvarchar(10)NOTNULLPRIMARYKEY)

Please change the nvarchar(10) to nvarchar(50) or something bigger and try again.

If you have some sample data, I can test them from my end too.

|||

I set them to 20 and it works. Do you know how I could change this to allow me to save it as a view?

I very much appreciate your help,

|||Try to create the view from the query in the dynamical sql command, for example:

.
DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'CREATE VIEW v_test AS SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN(' + @.cols + N')) AS pvt'

SELECT * FROM v_Test|||

I tried your suggestion, but I get a SET not supported message and then a vw_DYPhoneList is an invalid name.

Any thoughts?

SETNOCOUNT ON DECLARE@.TAS TABLE(ynvarchar(20)NOT NULLPRIMARY KEY)INSERTINTO @.TSELECT DISTINCT Phone_TypeFROM tblPhonesDECLARE@.T1AS TABLE(numintNOT NULLPRIMARY KEY)DECLARE@.iAS int SET@.i=1WHILE@.i <20BEGININSERTINTO @.T1SELECT @.iSET@.i=@.i+1END--select * from @.T1DECLARE@.colsAS nvarchar(MAX), @.yAS nvarchar(20)SET@.y = (SELECT MIN(y)FROM @.T)SET@.cols = N''WHILE@.yISNOT NULLBEGINSET@.cols = @.cols + N',['+CAST(@.yAS nvarchar(20))+N']'SET@.y = (SELECT MIN(y)FROM @.TWHERE y > @.y)ENDSET@.cols =SUBSTRING(@.cols, 2,LEN(@.cols))DECLARE@.cols1AS nvarchar(MAX), @.numAS nvarchar(20)SET@.num = (SELECT MIN(num)FROM @.T1)SET@.cols1 = N''WHILE@.numISNOT NULLBEGINSET@.cols1 = @.cols1 + N',['+CAST(@.numAS nvarchar(20))+N']'SET@.num = (SELECT MIN(num)FROM @.T1WHERE num > @.num)ENDSET@.cols1 =SUBSTRING(@.cols1, 2,LEN(@.cols1))DECLARE@.cols2AS nvarchar(MAX), @.num2AS nvarchar(20)SET@.num2 = (SELECT MIN(num)FROM @.T1)SET@.cols2 = N'[1]+'WHILE@.num2ISNOT NULLBEGINIF@.num2>1SET@.cols2 = @.cols2 + N'coalesce('','''+ N'+['+CAST(@.num2AS nvarchar(20))+N'],'''')+ 'SET@.num2 = (SELECT MIN(num)FROM @.T1WHERE num > @.num2)ENDSET@.cols2 =SUBSTRING(@.cols2, 0,LEN(@.cols2))DECLARE @.sqlAS nvarchar(MAX)SET @.sql = N'CREATE VIEW vwDYPhoneList AS SELECT * FROM (SELECT ClientID, Phone_Type, Phone_Number FROM tblPhones) as tPIVOT (min(Phone_Number) FOR Phone_Type IN(' + @.cols + N')) AS pvt'SELECT *FROM vwDYPhoneList

|||

You need to execute the @.sql.

Do this at the end.

.....

DECLARE

@.sqlASnvarchar(MAX)

SET

@.sql= N'CREATE VIEW vwDYPhoneList AS SELECT * FROM (SELECT ClientID, PhoneType, PhoneNumber FROM tblPhones) as t

PIVOT (min(PhoneNumber) FOR PhoneType IN('

+ @.cols+ N')) AS pvt'EXECsp_executesql @.sql

SELECT

*FROM vwDYPhoneList

Access crosstab -> SQL crosstab

Hi all
I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.

Thanks.

TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
SELECT tProjContacts.ProjectID
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
PIVOT tCompanyType.CoType;SQL Server 2000 does not have built-in crosstab functionality. Crosstab queries can be constructed using the CASE statement, if you follow the example you will find under CROSSTAB in Books Online. This will now produce dynamic crosstabs as in Access, however, as you must predefine your column names/groups.
In general, crosstabs should not be performed on the back-end server anyway. Nine times out of ten crosstabbing is a presentation issue, and is best handled by your interface or reporting application. The main reason Access has this built in is that Access incorporates an interface and reporting engine utility with the database. Interfaces and reports like to have consistent recordset formats for input. Try creating an Access report off of a dynamic Access crosstab and you will see the problems that crop up when you let the database do the cross-tabbing.

Access 2000, SQL Server 2000, Bulk Insert

I am pulling information from a DB2 table through a pass-through query
in Access 2000. I am going to create a comma delimited file that I want
to use Bulk Insert to put into SQL Server 2000.
I have the query to pull the data, all is well. How do I program in VBA
(Acess 2000) to do a bulk insert into a SQL Server table. I have 100000
records that I do this to every day. Bulk Insert seems to be the fastest
way. I will bulk insert the comma delimited file.
I am on a PC, not the SQL Server for the bulk insert. I cannot access
the mainframe DB2 tables from the SQL Server. My PC is the middle man
right now. How do I effectively do the bulk insert.
Does anybody have any experience in doing this, if so, can you post some
code to assist.
Thanks, Lyners
--
Posted via http://dbforums.comOne more thing, I have the code written so that it pulls the data and
loads the records 1 by 1. It takes to long and I keep blowing up the
transaction file. That is why I want to do the bulk insert. 1 record for
the transaction file. (AT least that is what I have read).
Posted via http://dbforums.com

Access / SQL Query issue

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] -- Unique Key
[SECURITY_TYPE]

As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0309081059.1817f089@.posting.google.c om...
> I will explain (or at least try to) first and then give an example
> after.
> I need to append a number of rows from several tables into one master
> table. Unfortunately there are certain columns (which are UNIQUE
> columns in the master table) in the source tables that are repeated in
> the same table or across the source tables.
> Example:
> Source 1 Source 2
> [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> MSFT STOCK AAPL STOCK
> AA STOCK MER OPTION
> MER OPTION
> MSFT OPTION
> Master
> [SECURITY] -- Unique Key
> [SECURITY_TYPE]
>
> As you can see in the example, MSFT is listed twice in Source 1 -- as
> a stock and an option. MER is listed as an option in both Source 1 and
> Source 2.
> My first solution (when I wrote it in Access) was to read in a source
> row, check if it existed in the master table, and then add it if it
> did not. This of course ran very slow. To fix that I added all the
> source rows to a temp table and then appended that temp table to the
> master. Since I was able to turn off warnings with the SetWarnings
> action in access any row that caused a duplicate key error got ignored
> BUT the query continued to the end.
> (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> row while MSFT-Option is another.)
> I rewrote the query to run in SQL Query Analyzer but cannot seem to
> figure out how to turn off errors so the query runs to completion. It
> may not even be possible to do so in which case I am hoping for a
> solution other than checking each row to see if it exists before I add
> it.

Your example isn't really clear without DDL (CREATE TABLE statements) and
sample data. You seem to indicate that Master.Security is the primary key,
but if so, you could have only one row in Master for MSFT, not two, which is
what I think you want. If my understanding is correct, you probably want
something like this, but without extra details, it's only a guess:

insert into
dbo.Master (Security, Security_Type)
select
Symbol,
Symbol_Type
from
dbo.Source1 s1
where
not exists (select *
from dbo.Master m
where s1.Symbol = m.Security and
s1.Symbol_Type = m.Security_Type)

You can modify the same query to use Source2.

Simon|||Jason,

It's not clear to me what you want, particularly in
the SECURITY_TYPE column of the master table. If the
primary key of that table is SECURITY, then you can't put
MSFT in twice - so what is SECURITY_TYPE? Is it something
different than STOCK or OPTION?

If you want the master table to represent what the source
tables represent, you will need to have (SECURITY,SECURITY_TYPE)
as the primary key (and you won't have the trouble you're having.

If SECURITY_TYPE is something completely different, and you
want MSFT in the table only once, then you can just insert
select SYMBOL, NULL from [Source 1]
union
select STOCK_SYMBOL, NULL from [Source 2]
-- union will eliminate duplicates

and then you can update the SECURITY_TYPE column as needed.

If you want to list MSFT-STOCK and MSFT-OPTION as the values
in the first column of the master table, then insert
select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
from [Source 1]
union
...

-- Steve Kass
-- Drew University
-- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0

Jason wrote:
> I will explain (or at least try to) first and then give an example
> after.
> I need to append a number of rows from several tables into one master
> table. Unfortunately there are certain columns (which are UNIQUE
> columns in the master table) in the source tables that are repeated in
> the same table or across the source tables.
> Example:
> Source 1 Source 2
> [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> MSFT STOCK AAPL STOCK
> AA STOCK MER OPTION
> MER OPTION
> MSFT OPTION
> Master
> [SECURITY] -- Unique Key
> [SECURITY_TYPE]
>
> As you can see in the example, MSFT is listed twice in Source 1 -- as
> a stock and an option. MER is listed as an option in both Source 1 and
> Source 2.
> My first solution (when I wrote it in Access) was to read in a source
> row, check if it existed in the master table, and then add it if it
> did not. This of course ran very slow. To fix that I added all the
> source rows to a temp table and then appended that temp table to the
> master. Since I was able to turn off warnings with the SetWarnings
> action in access any row that caused a duplicate key error got ignored
> BUT the query continued to the end.
> (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> row while MSFT-Option is another.)
> I rewrote the query to run in SQL Query Analyzer but cannot seem to
> figure out how to turn off errors so the query runs to completion. It
> may not even be possible to do so in which case I am hoping for a
> solution other than checking each row to see if it exists before I add
> it.|||My bad. When I type this out, SECURITY_TYPE should NOT have been
included in the Master table.

- Jason

Steve Kass <skass@.drew.edu> wrote in message news:<W287b.10380$_26.6571@.newsread2.news.atl.earthlink. net>...
> Jason,
> It's not clear to me what you want, particularly in
> the SECURITY_TYPE column of the master table. If the
> primary key of that table is SECURITY, then you can't put
> MSFT in twice - so what is SECURITY_TYPE? Is it something
> different than STOCK or OPTION?
> If you want the master table to represent what the source
> tables represent, you will need to have (SECURITY,SECURITY_TYPE)
> as the primary key (and you won't have the trouble you're having.
> If SECURITY_TYPE is something completely different, and you
> want MSFT in the table only once, then you can just insert
> select SYMBOL, NULL from [Source 1]
> union
> select STOCK_SYMBOL, NULL from [Source 2]
> -- union will eliminate duplicates
> and then you can update the SECURITY_TYPE column as needed.
> If you want to list MSFT-STOCK and MSFT-OPTION as the values
> in the first column of the master table, then insert
> select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
> from [Source 1]
> union
> ...
> -- Steve Kass
> -- Drew University
> -- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0
> Jason wrote:
> > I will explain (or at least try to) first and then give an example
> > after.
> > I need to append a number of rows from several tables into one master
> > table. Unfortunately there are certain columns (which are UNIQUE
> > columns in the master table) in the source tables that are repeated in
> > the same table or across the source tables.
> > Example:
> > Source 1 Source 2
> > [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> > MSFT STOCK AAPL STOCK
> > AA STOCK MER OPTION
> > MER OPTION
> > MSFT OPTION
> > Master
> > [SECURITY] -- Unique Key
> > [SECURITY_TYPE]
> > As you can see in the example, MSFT is listed twice in Source 1 -- as
> > a stock and an option. MER is listed as an option in both Source 1 and
> > Source 2.
> > My first solution (when I wrote it in Access) was to read in a source
> > row, check if it existed in the master table, and then add it if it
> > did not. This of course ran very slow. To fix that I added all the
> > source rows to a temp table and then appended that temp table to the
> > master. Since I was able to turn off warnings with the SetWarnings
> > action in access any row that caused a duplicate key error got ignored
> > BUT the query continued to the end.
> > (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> > row while MSFT-Option is another.)
> > I rewrote the query to run in SQL Query Analyzer but cannot seem to
> > figure out how to turn off errors so the query runs to completion. It
> > may not even be possible to do so in which case I am hoping for a
> > solution other than checking each row to see if it exists before I add
> > it.

Access <- > SQL Server

Hello!

We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.

Is it better to create query inside aplication (with code) and then pass
it to SQL Server for execution or is it better to have all these queries
saved like stored procedures and then called from aplication?

Witch queries run faster?

Thank's!

P.S. I know that my english isn't perfect:(!!!!!On Sun, 15 Feb 2004 21:30:11 +0100 in comp.databases.ms-access,
"Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?

Stored procedures are compiled with the execution plan so run faster
than dynamic SQL.

--
A)bort, R)etry, I)nfluence with large hammer.|||On Sun, 15 Feb 2004 21:30:11 +0100, "Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?
> Thank's!
>P.S. I know that my english isn't perfect:(!!!!!

Since you say this is an application developed in Access, and there are,
apparently, no other applications sharing the back-end, my recommendation is
to implement all the queries in Access except those that you can't make run
optimally that way by simply modifying the query's approach (chaning
subqueries to outer joins, etc.). Most queries of linked tables on SQL Server
will run well as Access queries.

The reason for keeping the queries in Access as much as possible is that it
makes the particular choice of server easier to change. It would be
relatively (though not totally) trivial, for instance, to switch to a
PostgreSQL or Oracle back-end.

There is actually very little speed penalty, in most cases, for executing
Access queries against linked, ODBC tables because Access can generally
convert these into prepared statements that actually act quite a bit like
stored procedures in that they are compiled once by the server when they are
first used, then reused when the same query is executed again. SQL Server is
also clever enough to reuse the same query plan when additional statements are
prepared with identical definitions.|||I make it a rule to: (well, rules)

1) Create parameterized stored procedures whenever possible, which usually mean
every read-only situation. (If you're using ADPs, however, MS has done a heluva
job making param SPs updatable.)

2) Create Pass-through queries for any must-have dynamic sql (again,
read-only). Also, PT queries must be used to call those SQL Server SPs!

3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
functions, etc. (And strive to make your VBA functions into processes that use
SQL Server if you can)

It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
haven't gone as far as some JET-haters that use UNBOUND forms w/
insert/update/delete classes, but I'm getting there...

I love Access as a front-end building tool, and a reporting tool. But I can
live without JET.

All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!|||On 15 Feb 2004 23:41:03 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>I make it a rule to: (well, rules)
>1) Create parameterized stored procedures whenever possible, which usually mean
>every read-only situation. (If you're using ADPs, however, MS has done a heluva
>job making param SPs updatable.)
>2) Create Pass-through queries for any must-have dynamic sql (again,
>read-only). Also, PT queries must be used to call those SQL Server SPs!
>3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
>updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
>functions, etc. (And strive to make your VBA functions into processes that use
>SQL Server if you can)
>It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
>haven't gone as far as some JET-haters that use UNBOUND forms w/
>insert/update/delete classes, but I'm getting there...
>I love Access as a front-end building tool, and a reporting tool. But I can
>live without JET.
>All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!

I respect yor opinion, but I disagree. There are cases in which I do agree,
and these are when you will have multiple front-ends to the same data, but
when SQL Server is just the data store for an Access application, the
performance benefit to implenmenting everything server-side is more than
overshadowed by the increased complexity of the front-end, and the loss of
independence from a particular server back-end.

I do favor implementing server-side views and stored procedures as an
optimization when required, but only after a performance problem has been
identified, and it doesn't respond to more minor rearrangments of the queries.

Regarding the use of ADPs fopr client-server apps, I guess that worked for
you, but after I successfully implemented several client-server apps with DAO
and MDBs, I worked on one monster of a project with ADPs, and found it an
ecercise in terrible frustration. Error message handling doesn't work right,
Access is so touchy about the actual implementations of things on the back-end
that many techniques of great use from the server perspective are not usable,
the updatability rules change with every new release of MDAC, so that I've had
to adopt a practice of never trying to bind a form to a query on a join
because I can't trust it (I use hidden combo boxes for FK lookups - how's that
for efficiency!), etc. Frankly, I would never use ADPs again for anything
other than prototypig. I'm at least 5 times more productive using MDBs and
DAO. Of course, if I was using something other than Access as a front-end, I
would nearly always us ADO.

Clearly, your experience differs, but to me ADPs are simply far too unstable
for production systems, and I don't see that they provide any performance
boost, what with having to keep asking the server for schema information
instead of caching it locally in links, constantly digging around behind
procedures and views to update individual tables. You have to jumpt through
several hoops to force Access to respect your intended security and
abstractions with views and procedures, etc. I spend most of my time trying
to get the thing to do what I say instead of guessing what it'll do when it
tries to thnk for me and do what it "knows" I must want.|||Steve, we've been through all this before (pro/con of ADP) in the Access
forums.

Even if everything you say is true about ADPs, and the "common knowledge"
becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
if at all possible."

I've never had corruption in 8 years of SQL Server development (It has happened
to others.) I have JET corruption at least once every 6 months. Granted, most
of the time it's caused by faulty hardware, but it scares me that JET is so
"nimble" that way.

Also, I don't know where you get your information on JET query performance, but
in NO WAY is it EVER equal to pass-through performance, except on the smallest
of SELECTS.

I can't begin to tell you how many times Access has either locked up, or I've
gotten impatient at the wait of a mutli-table query designed in the QBE, but
when run right from Query Analyzer returns records in less than 1 second.

Everyone of my combo boxes use Pass-through SELECTS. The performance increase
over JET selects REQUIRES IT, especially when connecting over the internet.
(one-to-one NAT)

Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
tables, specs, QBE), and I may even come up with an absolute must someday. I
even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
with it!

My bottom line is this: I'll listen to criticism of ADPs any day (although my
recent ADP projects have been successful, I have lots more exeperince with
MDB), but I can't take it when Access-lovers (I'm one) actually say with a
straight face that JET can perform in any way "as good as" SQL Server views,
SPs, or dynamic SQL.

Just the fact that an extra layer is involved ought to be enough proof...|||
On 16 Feb 2004 05:41:10 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>Steve, we've been through all this before (pro/con of ADP) in the Access
>forums.
>Even if everything you say is true about ADPs, and the "common knowledge"
>becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
>if at all possible."
>I've never had corruption in 8 years of SQL Server development (It has happened
>to others.) I have JET corruption at least once every 6 months. Granted, most
>of the time it's caused by faulty hardware, but it scares me that JET is so
>"nimble" that way.

I've had Access corruption, too, but the only times I've had data corruption
were when the back-end was an MDB. That has nothing to do with whether
queries are stored as Access objects or server-side objects when the data is
on the SQL Server.

>Also, I don't know where you get your information on JET query performance, but
>in NO WAY is it EVER equal to pass-through performance, except on the smallest
>of SELECTS.

That's simpy wrong. I've tested it, and I've used the moitoring tools to
watch the SQL. Unless you do something on the Access side that forces it to
do processing locally like joining or filtering on a function, or your query
is just too complex for Access to translate (rare) or if you are missing
indexes on joined fields (and why would you do that on purpose),
Access/DAO/Jet quite happily builds server-side SQL and executes it on the
server.

In the rare cases when I find a query is insisting on processing locally, I
turn part or all of it into a stored procedure or view.

>I can't begin to tell you how many times Access has either locked up, or I've
>gotten impatient at the wait of a mutli-table query designed in the QBE, but
>when run right from Query Analyzer returns records in less than 1 second.

Over time, I have gained an intuitive sense of what queries are dangerous that
way and which are not. It is tru that some time can be wasted with this now
and again.

>Everyone of my combo boxes use Pass-through SELECTS. The performance increase
>over JET selects REQUIRES IT, especially when connecting over the internet.
>(one-to-one NAT)

I have not had the performance issues you describe. It soed seem to help to
make the query DISTINCT, though, so Access will use a snapshot, and not a
Dynaset which would make more than the necessary number of round trips for a
read-only result.

>Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
>tables, specs, QBE), and I may even come up with an absolute must someday. I
>even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
>with it!
>My bottom line is this: I'll listen to criticism of ADPs any day (although my
>recent ADP projects have been successful, I have lots more exeperince with
>MDB), but I can't take it when Access-lovers (I'm one) actually say with a
>straight face that JET can perform in any way "as good as" SQL Server views,
>SPs, or dynamic SQL.

Well, I hate to be one of those people (actually, I'm and Access lover/hater,
and I'd take ADO.NET over Jet any day in a C/S project not done in Access),
but what you desccribe has not been my experience. It is true that some
queries need to be translated to run efficiently, but my experience is that
most don't, and in that case, they shouldn't.

I realize we just have to agree to disagree on much of this, but I think it's
important that both of our opinions are fully expressed for the record.

>Just the fact that an extra layer is involved ought to be enough proof...

From what I can tell, ADO/OLEDB is actually a much more complex stack of
layers than DAO/JET/ODBC, and some of what the DAO layer contains is very
beneficial such as the ability to use SQL to join tables from muliple sources,
and the caching of schema data in the front-end rather than looking it up
repeatly from the back-end.

accept user from user to enter a field value for a sql

Hi-

I am trying to achieve a similar query in ORACLE but donno how to do it in Ms SQL Server and MS Access (I have to do it for both these databases)

SELECT * FROM someTable
WHERE someField = &EnterValue

When this query is executed in ORACLE, it will ask user to enter value for &EnterValue and then execute the query using the user entered value.

Can this be done in MS SQL Server and MS ACCESS?

Please help...

ThanksHi-

Found the answer for MsAccess, I need to enter =[EnterValue] in Criteria for a given column.

Still looking for answer for MS SQL Server. Is Stored Proc the only answer?

Please help...

Best Wishes,|||SQL Server will not prompt the user for a parameter value. It needs to be supplied.

It is normally done like this:

SELECT * FROM someTable
WHERE someField = @.someValue

and Parameters are used to provide the query with the parameter value that the user has input from a data entry screen. You do not necessarily need a stored procedure to accomplish this (although I believe that is the best way).

Check out the ASP.NET tutorialServer-Side Data Access, especially the "Performing a Parameterized Select" section, to see how to use Parameters with ADO.NET.

Terri

accept all directory in sql query analizer

Well I try to find why I can not run dtsrun with xp_cmdschell
but I try to put all the directory c:\program files\microsoft SQL\...
and the procedure response that he dont find c:\program command
is there a instruccion for the large names on directories that make
the correct functionality of the path?

Regards. Mirtha.I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar|||There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"|||Originally posted by Cesar Fraustro
I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar
Yes , I do but it does not work. thanks.|||Originally posted by Paul Young
There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"

Yes it works, thanks and regards.

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.developersdex.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***

about the workload of DTA?

Why i could not supply a query as workload to tuning engine?

I found some session using query, but mine only have table and file?

many thanks

DTA allows you to 'tune' a single query, or a saved workload (saved in either a file or db table.)

See this article for working details:

http://download.microsoft.com/download/e/8/b/e8b42814-6a0c-40eb-911f-e7adec87f5d5/SQL2005DTA.doc

|||

You have read up on the documentation, correct?

http://msdn2.microsoft.com/en-us/library/ms173494.aspx

about store proc

Hi i have store proc where i am excuting a query look like
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
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|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
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|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
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|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:

> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:

> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>

about SQL String (security question)

I have a SQL Query String like below..

string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";

in my program it does work perfectly ... but now I just recalled my teacher have told.. it may cause security problem in that way...

maybe I need to change 【UPDATE Member SET Member_pwd = '" + pwd +】 to【UPDATE Member SET Member_pwd = '" + @.pwd +】

is there any difference between pwd and @.pwd?

thank you very much

I think what your teacher suggested is to use Parameterized Queries. There are a few benefits to it - primarily - security, maintenance/ease of coding. Please google for more info.I can type up a couple of lines but you will learn more from the articles already published.|||

Using this dynamic query building with strings is bad. Users can potentially inject malicious code into your queries. I have included a helpful link for you to review:http://www.4guysfromrolla.com/webtech/092601-1.shtml

Good Luck!

|||

thanks for you all (very much)

do you have a example for C#... shame on me I'm not similiar with VB...

I just trying to fix my original code . can you give me some suggestion?

except I have to change pwd to @.pwd.. is something else I've to add or edit?

thank you very much

================start of original code ============================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

================end of original code ============================

================fixed code ===================================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + @.pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

|||The post is marked as answered. So I am assuming you got your solution?|||

no no no... I'm sorry

should I post a new topic for extra question?

I still have many wonder on this filed...

thank you

|||

Hi,

You will need to add the parameters for all the criterias that is used in the query. Not only the pwd.

A typical sample might look like

SqlCommand cmd = new SqlCommand("Select * from Table WHERE [name]=@.name", this.connection);
cmd.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
cmd.Parameters.Value = "Kevin";
SqlDataReader sdr = cmd.ExecuteReader();

About SQL Query

Hi Friends,

I have one Query

select Name from Test1 where ID in (Select ID from (
if ('a' = 'a')
begin
Select DISTINCT ID,Name from Test2
End
) xxx )

and i want the query in same format....but when i execute this it will give an error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'If'.
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near ')'.

And if drop or remove if, begin ,end functionl;ity then it working fine....

Anyone help me solve this...

ASAP

AshutoshThere is no IF available in Transact-SQL. In general, the replacement for "IF" would be "CASE". But simply using CASE in the statement you provided will not work.

Maybe you could explain what it is you are trying to do, along with data examples...

Terri|||Hi Friend,

Actually i have a only query

if ('a' = 'a')
begin
Select DISTINCT ID,Name from Test2
End

Like this and that query

select Name from Test1 where ID in (Select ID from (
if ('a' = 'a')
begin
Select DISTINCT ID,Name from Test2
End
) xxx )

is generated automatically so error came there that query is not proper i can chage only my query and not the above ...so if any possibility to change that query above one only with if and begin then that problem will solve.....else tell me the other way.....i can change only this query it has 3 if statement so.......

select Name from Test1 where ID in (Select ID from (
if ('a' = 'a')
begin
Select DISTINCT ID,Name from Test2
End
) xxx )

might be u clear what i want now...still if not then i can explan u well

Ashutosh|||> There is no IF available in Transact-SQL.

errr... yes there is, though I agree not in the context you're thinking of. check Books Online.|||Hi Friends,

so how can i solve this problem? no way to solve this??

Ashutosh|||Sorry - I should have been clearer. IF is only available as a flow control command, not within a query.

Ashutosh -- I am still not clear. What is the purpose of the "xxx"? Could you please provide data examples to show the data you have and the result you are looking for?

Terri|||> so how can i solve this problem?

IF should be used so

IFsome criteria
BEGIN
sql statements here
END
ELSE
BEGIN
some other SQL statements here
END

GO

not in the middle of the statement as you've tried to use it. it should enclose complete SQL statements. Check the docs!

About SQL Profiler and query

Hi
i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction" but
the TextData is always empty and my application name is JSQL.
This transaction duration is : 10516
The question are :
1- It's a normal that the "textdata" is always empty ?
2- The application name "JSQL" maybe JBOSS on my Apache server?
3- The duration of this transaction is too much ?
Thanks.
What are you expecting to see in the TextData column for the SQLTransaction
event? It's documented behaviour is to display "The savepoint or rollback
name, if provided.", according to SQL Server Books Online:
http://msdn.microsoft.com/library/en..._perf_79tf.asp
Given that it's not common to name savepoints or rollbacks, it's probably a
little unusual to expect to see much in SQLTransaction:TextData. Perhaps you
should be profiling a different event - if you post back with what you're
hoping to see, we can perhaps help by recommending a different event to
profile?
Regards,
Greg Linwood
SQL Server MVP
<io.com> wrote in message news:eu8ETkNnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Hi
> i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction"
> but
> the TextData is always empty and my application name is JSQL.
> This transaction duration is : 10516
> The question are :
> 1- It's a normal that the "textdata" is always empty ?
> 2- The application name "JSQL" maybe JBOSS on my Apache server?
> 3- The duration of this transaction is too much ?
>
> Thanks.
>

About SQL Profiler and query

Hi
i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction" but
the TextData is always empty and my application name is JSQL.
This transaction duration is : 10516
The question are :
1- It's a normal that the "textdata" is always empty ?
2- The application name "JSQL" maybe JBOSS on my Apache server?
3- The duration of this transaction is too much ?
Thanks.What are you expecting to see in the TextData column for the SQLTransaction
event? It's documented behaviour is to display "The savepoint or rollback
name, if provided.", according to SQL Server Books Online:
http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_79tf.asp
Given that it's not common to name savepoints or rollbacks, it's probably a
little unusual to expect to see much in SQLTransaction:TextData. Perhaps you
should be profiling a different event - if you post back with what you're
hoping to see, we can perhaps help by recommending a different event to
profile?
Regards,
Greg Linwood
SQL Server MVP
<io.com> wrote in message news:eu8ETkNnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Hi
> i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction"
> but
> the TextData is always empty and my application name is JSQL.
> This transaction duration is : 10516
> The question are :
> 1- It's a normal that the "textdata" is always empty ?
> 2- The application name "JSQL" maybe JBOSS on my Apache server?
> 3- The duration of this transaction is too much ?
>
> Thanks.
>

about search query

I would like to write query which can
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

1. ignore whether the search keyword is Upper case or lower Case

SQL Queries are case insensitive.

2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

No idea. I am unable to understand your question.
|||

sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that

(a) (b)
1.CASW Casw

2.compare comparing

1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx

|||I am sorry. I am still unable to understand your requirement. Can you try and xplain a little more?|||Thx for your patient!
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?


|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||

ndinakar wrote:

you should get both records without having to worry about case.

Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||

DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))

INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')

INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')

SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------

(1 row(s) affected)

(1 row(s) affected)

colA colB

---- ------------------------------

Tom FOOTBALL,PIANO

Sam football,piano,volleyball

(2 row(s) affected)

|||Thanks for the followup ndinkar. I always that my query didnt worked.

about query ..

Hi all,
Does anyone know what is the following query mean:
select * from abc,def from abc.xx(+) = def.xx
i have no idea about the (+) symbol..what it represent?
Thank you in advance
Best regards,
Ocurnos
Hi,
Its Left outer join in Oracle. SQL Server will give you syntax error.
Thanks
Hari
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:8453A3AC-B95C-45B0-B117-79EE86FCF452@.microsoft.com...
> Hi all,
> Does anyone know what is the following query mean:
> select * from abc,def from abc.xx(+) = def.xx
> i have no idea about the (+) symbol..what it represent?
> Thank you in advance
> Best regards,
> Ocurnos

about query

Hi all,
Does anyone know what is the following query mean:
select * from abc,def where abc.xx(+) = def.xx
i have no idea about the (+) symbol..what it represent?
Thank you in advance
Best regards,
Ocurnos
As I know that + Symbol to used to join string.
example below
SELECT top 10 Title_ID + Title As "Title_ID + Title", * FROM
[pubs].[dbo].[titles]
Regards,
Rajesh
"Daniel" wrote:

> Hi all,
> Does anyone know what is the following query mean:
> select * from abc,def where abc.xx(+) = def.xx
> i have no idea about the (+) symbol..what it represent?
> Thank you in advance
> Best regards,
> Ocurnos