Anyone has any idea on how to insert 2 strings into a row with 2 lines created?

<p>Hi ,

I would like to know anyway to insert two strings into a row with 2 lines created?
My codes are as below

If NodeName = "subProductPrice" Then
If xmlrder.NodeType = Xml.XmlNodeType.Text Then
SubPrPriceList.Add(xmlrder.Value)

For i = 0 To SubPrPriceList.Count - 1
SubPrPrice = CStr(SubPrPriceList.Item(i))
PriceBrkDownStr &= SubPrPrice

Next

PricePerDay = SvcDate & PriceBrkDownStr
dr("dailyPrice") = PricePerDay
End If
End If

Although both the SvcDate and PriceBrkDownStr are inserted into the same row but they are displayed into one line such as below:

<u>dailyPrice </u>
02/03/2007 03/03/2007 120 230

Any idea how to make the date and price separate into two rows in the same table grid row? thanks =)
</p>

store <br> between the lines and let me know if it worked...

|||

Hi, sorry I dont get what you mean. Insert <br> between 2 lines? I am inserting the string into the table grid row. How can I insert the tag <br> ? =P

|||

I mean store them as a single line with <br> between them and when you will diplay them they will be shown like two lines... i understood thats what you want to do... if it is not the case then my mistake :)

Any way to bold certain words in data field?

I'm working on cleaning up a report and got a request to bold certain words within a data field. I have no idea where to start or what to search on to do this. For instance, I would want to bold any occurance of "History:" or "Technique:" but not bold "history". The data is coming over as plain text in one large data field. Any help would be greatly appreciated!

Thanks!We used to have a system that fed us the data in seperate fields of
History:
Technique:
Comparison:
Findings:
Conculsion:

but now we have a system that is unable to split the data elements up before feeding it into our system. We can't add any formating on the upstream system either unfortionately. Now our report looks like garbage lol. Hopefully one of you gurus will be able to help.

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bing
sp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:
> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any news on SP2 release?

Hello,
Any idea on when we can expect the release of SP2?
Thanks!End of the month has been mentioned by Tom Rizzo.
Regards
Chris
tokio wrote:
> Hello,
> Any idea on when we can expect the release of SP2?
> Thanks!|||http://www.msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fbef92c1-9d8a-4438-b0c8-40aa7103364d
--
Adrian M.
MCP
"tokio" <ricoche2@.yahoo.com> wrote in message
news:ujAWg2SJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Any idea on when we can expect the release of SP2?
> Thanks!
>

Any Idea's on this !

Well I have to implement Mail Merge ...user defined Letters in short...
basically like this..
The user can enter any custom letter with Fields we will provide them to put
into the letter ...We will them store this in database(right now thinking on
storing in HTML format).
For eg Letter template would be like
To,
<Name>
<Address1>
Hello <Name>
Thank you
Regards,
<MyName>
<MyPhone>
When the user then selects to generate a letter selecting this Template,then
we need to subsitute appropriate fields and generate the letters for all the
selected user clients by the user.
Please how can i achieve this with RS...any suggestions welcomed...
Thank uUnfortunately, this is not really supported in Reporting Services. We had
planned to add support for rich text (paragraphs, justification, formatting,
etc.) in SQL 2005 but ran out of time.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> Well I have to implement Mail Merge ...user defined Letters in short...
> basically like this..
> The user can enter any custom letter with Fields we will provide them to
> put
> into the letter ...We will them store this in database(right now thinking
> on
> storing in HTML format).
> For eg Letter template would be like
> To,
> <Name>
> <Address1>
> Hello <Name>
> Thank you
> Regards,
> <MyName>
> <MyPhone>
> When the user then selects to generate a letter selecting this
> Template,then
> we need to subsitute appropriate fields and generate the letters for all
> the
> selected user clients by the user.
> Please how can i achieve this with RS...any suggestions welcomed...
> Thank u
>|||Thanks for the reply !!
Well i am ready to give up on Rich text formatting here...but how can I
actually achieve the working of such templates as letters.How can i do this
with RS.
Is Inner Html supported in RS.
Thank u
"Brian Welcker [MSFT]" wrote:
> Unfortunately, this is not really supported in Reporting Services. We had
> planned to add support for rich text (paragraphs, justification, formatting,
> etc.) in SQL 2005 but ran out of time.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> > Well I have to implement Mail Merge ...user defined Letters in short...
> > basically like this..
> > The user can enter any custom letter with Fields we will provide them to
> > put
> > into the letter ...We will them store this in database(right now thinking
> > on
> > storing in HTML format).
> > For eg Letter template would be like
> >
> > To,
> > <Name>
> > <Address1>
> >
> > Hello <Name>
> > Thank you
> >
> > Regards,
> > <MyName>
> > <MyPhone>
> >
> > When the user then selects to generate a letter selecting this
> > Template,then
> > we need to subsitute appropriate fields and generate the letters for all
> > the
> > selected user clients by the user.
> > Please how can i achieve this with RS...any suggestions welcomed...
> >
> > Thank u
> >
> >
>
>|||You can't - embedded HTML is part of the rich text feature that was cut. If
you think about it, since we support multiple output format, we would have
to parse the HTML and represent it in other formats (Excel, PDF, etc.)
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:F8F014D9-7D93-4BFA-97CB-A1F990BADA83@.microsoft.com...
> Thanks for the reply !!
> Well i am ready to give up on Rich text formatting here...but how can I
> actually achieve the working of such templates as letters.How can i do
> this
> with RS.
> Is Inner Html supported in RS.
> Thank u
>
> "Brian Welcker [MSFT]" wrote:
>> Unfortunately, this is not really supported in Reporting Services. We had
>> planned to add support for rich text (paragraphs, justification,
>> formatting,
>> etc.) in SQL 2005 but ran out of time.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
>> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
>> > Well I have to implement Mail Merge ...user defined Letters in short...
>> > basically like this..
>> > The user can enter any custom letter with Fields we will provide them
>> > to
>> > put
>> > into the letter ...We will them store this in database(right now
>> > thinking
>> > on
>> > storing in HTML format).
>> > For eg Letter template would be like
>> >
>> > To,
>> > <Name>
>> > <Address1>
>> >
>> > Hello <Name>
>> > Thank you
>> >
>> > Regards,
>> > <MyName>
>> > <MyPhone>
>> >
>> > When the user then selects to generate a letter selecting this
>> > Template,then
>> > we need to subsitute appropriate fields and generate the letters for
>> > all
>> > the
>> > selected user clients by the user.
>> > Please how can i achieve this with RS...any suggestions welcomed...
>> >
>> > Thank u
>> >
>> >
>>

Any idea why the update won't run?

I have this store proc that ends at the update command. No matter where I
move it to, the whole thing seems to quit there.
CREATE PROCEDURE sp_LPModUser
@.ModFName NVarChar(100),
@.ModLName NVarChar(100),
@.ModLogon NVarChar(100),
@.ModPassword NVarChar(100),
@.ModLPUserID Int,
@.ModPageAccess Int,
@.ModChangePW Int,
@.Database NVarChar(100)
AS
Declare @.OldLogon nvarchar(100)
set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
Declare @.Check integer
Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
@.ModLogon)
if @.Check = 0
Begin
exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
exec sp_grantdbaccess @.ModLogon
exec sp_addrolemember 'db_datareader', @.ModLogon
exec sp_addrolemember 'db_datawriter', @.ModLogon
End
If @.OldLogon != @.ModLogon
Begin
exec sp_revokedbaccess @.OldLogon
exec sp_droplogin @.OldLogon
End
Begin
exec sp_password NULL, @.ModPassword, @.ModLogon
update lpusers set fname = @.ModFName, lname = @.ModLName, login = @.Modlogon,
[password] = @.ModPassword,
PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
@.ModLPUserID
print 'test'
endNevermind, I got it.
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:uXUEsGg6FHA.564@.TK2MSFTNGP10.phx.gbl...
>I have this store proc that ends at the update command. No matter where I
>move it to, the whole thing seems to quit there.
> CREATE PROCEDURE sp_LPModUser
> @.ModFName NVarChar(100),
> @.ModLName NVarChar(100),
> @.ModLogon NVarChar(100),
> @.ModPassword NVarChar(100),
> @.ModLPUserID Int,
> @.ModPageAccess Int,
> @.ModChangePW Int,
> @.Database NVarChar(100)
> AS
> Declare @.OldLogon nvarchar(100)
> set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
> Declare @.Check integer
> Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
> @.ModLogon)
> if @.Check = 0
> Begin
> exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
> exec sp_grantdbaccess @.ModLogon
> exec sp_addrolemember 'db_datareader', @.ModLogon
> exec sp_addrolemember 'db_datawriter', @.ModLogon
> End
> If @.OldLogon != @.ModLogon
> Begin
> exec sp_revokedbaccess @.OldLogon
> exec sp_droplogin @.OldLogon
> End
> Begin
> exec sp_password NULL, @.ModPassword, @.ModLogon
> update lpusers set fname = @.ModFName, lname = @.ModLName, login =
> @.Modlogon, [password] = @.ModPassword,
> PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
> @.ModLPUserID
> print 'test'
> end
>

Any idea why OpenRowSet to open Excel file doesn''t work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?

Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?

Any idea where SQL store the synonyms? thanks

I need read defination of my synonyms, but I could not find which system
table it stores, I know they get one entry in sysobjects table, but I have
no idea where is the defination.
Thanks!
The sys.synonyms catalog view returns the base_object_name on which the
synonym is based. Is that what you're looking for?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"davidw" <davidw@.affinisys.com> wrote in message
news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
>I need read defination of my synonyms, but I could not find which system
> table it stores, I know they get one entry in sysobjects table, but I have
> no idea where is the defination.
> Thanks!
>
|||Nice!
That is exactly what I am looking for.
I only searched on sys tables, didn't think about sys view.
thanks!
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OX2781aOGHA.1288@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The sys.synonyms catalog view returns the base_object_name on which the
> synonym is based. Is that what you're looking for?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "davidw" <davidw@.affinisys.com> wrote in message
> news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
have
>

Any idea where SQL store the synonyms? thanks

I need read defination of my synonyms, but I could not find which system
table it stores, I know they get one entry in sysobjects table, but I have
no idea where is the defination.
Thanks!The sys.synonyms catalog view returns the base_object_name on which the
synonym is based. Is that what you're looking for?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"davidw" <davidw@.affinisys.com> wrote in message
news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
>I need read defination of my synonyms, but I could not find which system
> table it stores, I know they get one entry in sysobjects table, but I have
> no idea where is the defination.
> Thanks!
>|||Nice!
That is exactly what I am looking for.
I only searched on sys tables, didn't think about sys view.
thanks!
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OX2781aOGHA.1288@.TK2MSFTNGP09.phx.gbl...
> The sys.synonyms catalog view returns the base_object_name on which the
> synonym is based. Is that what you're looking for?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "davidw" <davidw@.affinisys.com> wrote in message
> news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
have[vbcol=seagreen]
>

Any idea where SQL store the synonyms? thanks

I need read defination of my synonyms, but I could not find which system
table it stores, I know they get one entry in sysobjects table, but I have
no idea where is the defination.
Thanks!The sys.synonyms catalog view returns the base_object_name on which the
synonym is based. Is that what you're looking for?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"davidw" <davidw@.affinisys.com> wrote in message
news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
>I need read defination of my synonyms, but I could not find which system
> table it stores, I know they get one entry in sysobjects table, but I have
> no idea where is the defination.
> Thanks!
>|||Nice!
That is exactly what I am looking for.
I only searched on sys tables, didn't think about sys view.
thanks!
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OX2781aOGHA.1288@.TK2MSFTNGP09.phx.gbl...
> The sys.synonyms catalog view returns the base_object_name on which the
> synonym is based. Is that what you're looking for?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
> "davidw" <davidw@.affinisys.com> wrote in message
> news:OTeJq6YOGHA.964@.tk2msftngp13.phx.gbl...
> >I need read defination of my synonyms, but I could not find which system
> > table it stores, I know they get one entry in sysobjects table, but I
have
> > no idea where is the defination.
> >
> > Thanks!
> >
> >
>

Any idea when the next version of SQL is out ?

I know the executives at MS have promised for a 2 year release moving
forward and if thats true, we should be having one in the coming year. But I
dont see any sight of the same happening. Can someone confirm ? I believe
the code name is Katmai.The code name is Katmai, that is correct. But no dates have been announced.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eclyo5RKHHA.1424@.TK2MSFTNGP04.phx.gbl...
>I know the executives at MS have promised for a 2 year release moving
>forward and if thats true, we should be having one in the coming year. But
>I dont see any sight of the same happening. Can someone confirm ? I believe
>the code name is Katmai.
>
>|||I never read about a promise per se. I read that it was a goal to have between 2-3 years between
releases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <Hassan@.hotmail.com> wrote in message news:eclyo5RKHHA.1424@.TK2MSFTNGP04.phx.gbl...
>I know the executives at MS have promised for a 2 year release moving forward and if thats true, we
>should be having one in the coming year. But I dont see any sight of the same happening. Can
>someone confirm ? I believe the code name is Katmai.
>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0wxuHTKHHA.4712@.TK2MSFTNGP04.phx.gbl...
>.
>I never read about a promise per se. I read that it was a goal to have
>between 2-3 years between releases.
It is absurd to put that kind of pressure on this company...I give you
Vista -:)
I am particularly looking forward to your discussions with users concerning
the fascinating interactions (that I forsee in my crystal ball) between the
User Account Control and sql server -:)
Best wishes for the new year,|||> It is absurd to put that kind of pressure on this company...I give you
> Vista -:)
My biggest problem with Vista has little to do with Microsoft: none of the
video card manufacturers have been able to provide an adequate driver that
supports all the features and doesn't blue screen!
My second complaint is a licensing issue, not a quality issue. I bought
Windows Live OneCare for an XP desktop. When I upgraded to Vista, my one
year license became a 3-month license that had now expired, because the
software refuses to run on Vista. They have some beta program for 1.5 but
since the system is already unstable (mostly due to video drivers as
mentioned above) I am not willing to try it.
I know, this is not the forum for these complaints, but you brought it up!
:-)|||Many companies purchase software assurance, specifically to take advantage
of regular upgrades.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OMt6PiTKHHA.3552@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:O0wxuHTKHHA.4712@.TK2MSFTNGP04.phx.gbl...
>>.
>>I never read about a promise per se. I read that it was a goal to have
>>between 2-3 years between releases.
> It is absurd to put that kind of pressure on this company...I give you
> Vista -:)
> I am particularly looking forward to your discussions with users
> concerning the fascinating interactions (that I forsee in my crystal ball)
> between the User Account Control and sql server -:)
> Best wishes for the new year,
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23lroFmTKHHA.5000@.TK2MSFTNGP03.phx.gbl...
>.
> I know, this is not the forum for these complaints, but you brought it up!
> :-)
Yes I did...somebody had to do it :)
IE7 is released as an unfinished product driving users to FF. OneCare is a
joke. They have no business releasing Vista now for many reasons. The mind
boggles at the price to be paid for using this OS with the security model
being a prime example. They are driving people to question their leadership
or lack thereof.
Best wishes for the new year,

Any idea what the purpose of this SPROC might be? (no prizes, sorry!)

I've been asked to document an application and I'm going through all
the Stored Procedures and trying to work out what they're supposed to
do.

Can anyone give me an idea of what the Stored Procedure
wsBookingListsGetAll below is trying to achieve? Is it incomplete? I
can't see any reason to pass in the Parameter, and what is the UNION
SELECT 0 all about?

Many thanks

Edward

CREATE Procedure wsBookingListsGetAll
@.DebtorIDvarchar(15)
As
set nocount on

SELECT
fldBookingListID
FROM
tblWsBookingList
UNION
SELECT 0

return

GO

/* Table def */
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblWSBookingList]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblWSBookingList]
GO

CREATE TABLE [dbo].[tblWSBookingList] (
[fldDebtorID] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
,
[fldAddressCode] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldEmail] [varchar] (250) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingListID] [int] IDENTITY (1, 1) NOT NULL ,
[fldInvoiceNumber] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[fldPayeeID] [char] (15) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GOHi Edward,

It is obvious the SP is returning list of booking list ids. Maybe there are
two scenarios:

1). Incomplete: The SP parameter was intended to filter the list by debtor
id but the code was never completed.
2). Obsolete: In the past the parameter was used properly in the SP but
something required to return the full list all the time and the condition
was dropped from the SQL code. Since the parameter may be passed from the
client application, the developer did not bother to change properly the
client code to drop the parameter, but rather did the change only in the SQL
code and did not comment the change.

As for the union with SELECT 0 it seems like the return list required a
placeholder for some special value (maybe in the client application the list
needs to show "Not Selected" which will be mapped to the 0 value).

Probably the best would be to look at the places where this SP is called and
that will help you to figure out the reasons.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

Any idea on this!

How can execute sqlscript stored in a folder.
i.e D:\SQLSCRIPT\cs_posting_exc.PRC
What can I write in query analyzer so that I can run that procedure in that
folder is a specified server and database.Have you looked into using osql.exe with xp_cmdshell? See SQL Server Books
Online for more details & post back if you have questions.
Anith