Analysis Services

How do you grant just read access to the cube data to an end user?

Thanks, Edwina Derrick

Are you talking about a writeback cube? In that case just disable it in the role the user belongs to.

|||Nope - just read for any dimension. I have tried all combinations and end up giving all access to the cube for them to be able to read the data.

Thanks, Edwina Derrick|||

You are aware of the security features with roles and such in AS?

|||I am not sure I understand what you mean by security features with roles and such in AS.
I set up a role and gave them read access to the dimensions. This was not enough. I gave them read access to everything and they still couldn't view the data. Once I granted full administrator rights to the cube they could read the data.

Thanks, Edwina Derricksql

An unexpected error occurred in Report Processing

Hi,

I am having MSRS 2000 Reports which is using subscription feature to email PDF format reports to user . I am able to get all the reports e-mailed as expected only this report is giving an errror in the status as "Failure sending mail: An unexpected error occurred in Report Processing". I can pull this report properly and also export it in PDF manually. The Report is a 15 page Report with few images. If I try to email only the link of the Report it works fine. Was wondering if anybody would have a clue about the same .

The Log files read as

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred in Report Processing. > System.IO.DirectoryNotFoundException:
Could not find a part of the path "F:\".

I dont know why it is going in F drive and failing.

cheers,

siaj

The more detailed log is :

ReportingServicesService!processing!1790!04/25/2006-14:15:10:: e ERROR:
Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred in Report Processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred in Report Processing. > System.IO.DirectoryNotFoundException:
Could not find a part of the path "F:\".
at System.IO.__Error.WinIOError(Int32 errorCode, String str)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path)
at System.IO.Directory.CreateDirectory(String path)
at Microsoft.ReportingServices.Library.PartitionManager.CreateFile(String folder, String filename)
at Microsoft.ReportingServices.Library.FileChunkWriteStream.OpenFileStream()
at Microsoft.ReportingServices.Library.FileChunkWriteStream..ctor(Guid snapshotDataID, Boolean isPermanentSnapshot, String chunkName, Int32 chunkType, String mimeType, ChunkFlags flags)
at Microsoft.ReportingServices.Library.SnapshotChunkStreamFactory.CreateWriteStream(Guid snapshotDataID, Boolean isPermanentSnapshot, String chunkName, Int32 chunkType, String mimeType, Int32 bufferSize)
at Microsoft.ReportingServices.Library.ReportSnapshot.CreateReportChunk(String name, ReportChunkTypes type, String mimeType)
at Microsoft.ReportingServices.ReportProcessing.a.a()
at Microsoft.ReportingServices.ReportProcessing.a.c()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4, DateTime A_5, CreateReportChunk A_6, ab& A_7)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)

It sounds like you have the feature enabled where we store temporary snapshots in the filesystem. Check this article:

http://msdn2.microsoft.com/en-us/library/ms157439(SQL.90).aspx

Make sure that the FileShareStorageLocation element is set to a path which actually exists. My guess is that it is not.

an In List Parameter

Is it possible (And if it is How do I do it) to create a report
parameter which allows a user to select a list of values and then
passes the entire list into the query?
IE. Parameter query returns a list of State abbreviations and the
user can then choose several which then get passed into the query as:
AND STATE IN ('GA', 'CA', 'NY')
TIA for any assistance anyone can provide me. I'm new to reporting
services and the help files haven't been all that much help.See related post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=aca0f4eb-9766-4437-a5f6-874f7344a827&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Curt" <cmgaviao@.yahoo.com> wrote in message
news:582e9f38.0408190714.544de11c@.posting.google.com...
> Is it possible (And if it is How do I do it) to create a report
> parameter which allows a user to select a list of values and then
> passes the entire list into the query?
> IE. Parameter query returns a list of State abbreviations and the
> user can then choose several which then get passed into the query as:
> AND STATE IN ('GA', 'CA', 'NY')
> TIA for any assistance anyone can provide me. I'm new to reporting
> services and the help files haven't been all that much help.

AMO:How we can handle the transactions in AMO

HI All,

I am working with AMO and .Net i have few queries,

1) how to handle the transactions in AMO

2)if one user is processing the cube then how to prevent other user to access it.

Please let me know.

Your suggestions are valueable to me.

Thanks in advance.

vish.

AMO is inherently transactional. If an object is being processed changes that other users submit will not be committed until after the transaction has completed.

If you need to do a series of operations, like changing a cubes structure and then processing it, but you want either both or neither of these operations to suceed, then you can use explicit transactions. The server object has beginTransaction, commitTransaction and rollbackTransaction methods.

AMO:How we can handle the transactions in AMO

HI All,

I am working with AMO and .Net i have few queries,

1) how to handle the transactions in AMO

2)if one user is processing the cube then how to prevent other user to access it.

Please let me know.

Your suggestions are valueable to me.

Thanks in advance.

vish.

AMO is inherently transactional. If an object is being processed changes that other users submit will not be committed until after the transaction has completed.

If you need to do a series of operations, like changing a cubes structure and then processing it, but you want either both or neither of these operations to suceed, then you can use explicit transactions. The server object has beginTransaction, commitTransaction and rollbackTransaction methods.

AMO: No mapping between account names and security IDs was done for local users

Hi,

Analysis Services 2005 and SQL Server 2005

Using AMO I'm trying to add a local user to an Analysis Services database Role that I created but I keep getting the error "No mapping between account names and security IDs was done". I can add the user to the Role with no problem using SQL Server Management studio, but if I try to add the user using AMO I get that error.

The local user that I'm trying to add belongs to both the local User and Administrator groups. I've added a logon for this local user to the corresponding SQL Server database that the Analysis Services database datasource points to, but still no luck.

Am I missing something? I've been doing this for the last three months with network users without a problem so this is completely frustrating me. Especially the fact that I can add them using the SQL Server Management Studio GUI.

Here's the basic code that I'm doing. Pretty straightforward.

private bool AddUserToAnalysisServicesRole(Role role)

{

string user = @."pdxvincentm02\testuser";

RoleMember rm = new RoleMember(user);

role.Members.Add(new RoleMember(user)); // e.g. redmond\johndoe

role.Update(); // << KABOOM!!! No mapping between ......

Help. Thanks

I have not had a chance to try this in C# yet, but I did a quick test in powershell using the following pattern and it worked.

role.Members.add("pdxvincentm02\testuser")

role.Update()

It's possible that the role.Members.Add() function has another overload that works a bit differently. But another though also occurred to me. Where is this code being executed from? Is it possibly running under an account that does not have permission to verify the account? (eg. is it running from an ASP.NET app running as local system?)

Am I doing this right?

I have a new user that will eventually be another admin on our SQL Server 20
0
installation. I want to give him permission to stop and Restart Scheduled
jobs, yet not have permission to insert/update or delete in specific
databases until he gets more experience. The only predefined server role
that allows access to scheduled jobs is SystemAdministrator, but that also
gives permissions everywhere to everything.
Will creating a User in each database I want to protect based on his login,
and then selecting db_denydatawriter do what I want to do, or will the also
selected SystemAdministrator priviledge override this altogether?A deny for a sysadmin won't do anything. Members of the
sysadmin role bypass security checks - sysadmins are able to
do anything on the server and in any databases.
-Sue
On Thu, 18 May 2006 11:41:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:

>I have a new user that will eventually be another admin on our SQL Server 2
00
>installation. I want to give him permission to stop and Restart Scheduled
>jobs, yet not have permission to insert/update or delete in specific
>databases until he gets more experience. The only predefined server role
>that allows access to scheduled jobs is SystemAdministrator, but that also
>gives permissions everywhere to everything.
>Will creating a User in each database I want to protect based on his login,
>and then selecting db_denydatawriter do what I want to do, or will the also
>selected SystemAdministrator priviledge override this altogether?|||Thanks for the reply. So then how in the world can I create a login that
allows the user to access, start and stop SQL Server Agent scheduled jobs an
d
do liuttle or nothing else? I am stumped on this and it seems like it
*ought* to be easy!
Steve
"Sue Hoegemeier" wrote:

> A deny for a sysadmin won't do anything. Members of the
> sysadmin role bypass security checks - sysadmins are able to
> do anything on the server and in any databases.
> -Sue
> On Thu, 18 May 2006 11:41:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||There just isn't that level of granularity with security
under SQL Server 2000. That changes in SQL Server 2005
though.
The only way to get close is by using an undocumented role
in msdb - TargetServer role. However, the permissions for
this role changes depending on the service pack level and
you'd have to be running at SP 2 or lower to get close to
what you are asking for.
-Sue
On Fri, 19 May 2006 13:11:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply. So then how in the world can I create a login that
>allows the user to access, start and stop SQL Server Agent scheduled jobs a
nd
>do liuttle or nothing else? I am stumped on this and it seems like it
>*ought* to be easy!
>Steve
>"Sue Hoegemeier" wrote:
>|||Hi Sue/Steve
I feel with Sp3 & higher we can achieve these by modifying default Security
Permissions asigned to TargetServer Role. But only quetion is what Security
Risk we have when we enable this option .i.e. TargetServer Role.
I am in scenario where we want to view SQL job history running on more tahn
100 Servers & we don;t have master server scenario. Can you suggest somethin
g
on this?
Regards
--
Rahul
"Sue Hoegemeier" wrote:

> There just isn't that level of granularity with security
> under SQL Server 2000. That changes in SQL Server 2005
> though.
> The only way to get close is by using an undocumented role
> in msdb - TargetServer role. However, the permissions for
> this role changes depending on the service pack level and
> you'd have to be running at SP 2 or lower to get close to
> what you are asking for.
> -Sue
> On Fri, 19 May 2006 13:11:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||No...I wouldn't suggest that and don't think it would work.
Additionally using TargetServer role is undocumented as I
already posted.
I'd look at writing your own application, front end to
manage you needs.
-Sue
On Tue, 25 Jul 2006 21:12:02 -0700, rahulpt
<rahulpt@.discussions.microsoft.com> wrote:

>Hi Sue/Steve
>I feel with Sp3 & higher we can achieve these by modifying default Security
>Permissions asigned to TargetServer Role. But only quetion is what Security
>Risk we have when we enable this option .i.e. TargetServer Role.
>I am in scenario where we want to view SQL job history running on more tahn
>100 Servers & we don;t have master server scenario. Can you suggest somethi
ng
>on this?
>Regards

Am I a sysadmin member?

Hi,

I want to check if the currently connected user is a sysadmin(or securityadmin) member. Could I only check whether the user has the CONTROL SERVER permission or not? Is there a straight way to test it?

Try using

SELECT IS_SRVROLEMEMBER( 'sysadmin', 'UserName' )

Returns 1 for true, 0 for false.

|||Thanks

always show subreport

I have a report with many subreports. Recently the user requested that
they would like the subreport to show even if no data is returned in
the subreport. (They want the space on the report to show) Can this be
done without redoing the whole report and taking out the subreports? I
have tried everything I can think of. Any ideas?
Thanks in advance.
Missyaggiechick717 wrote:
> I have a report with many subreports. Recently the user requested that
> they would like the subreport to show even if no data is returned in
> the subreport. (They want the space on the report to show) Can this be
> done without redoing the whole report and taking out the subreports? I
> have tried everything I can think of. Any ideas?
> Thanks in advance.
> Missy
I have a similar issue but I am not able to figure it out please let me
know if you have found an answer
Thanks
shri|||I think i found a soln .......not confident about it
though.......try placing the subreport in a rectangle .let me know if
this helps
Thanks
shri|||I didn't get it to work...did you?
shri wrote:
> I think i found a soln .......not confident about it
> though.......try placing the subreport in a rectangle .let me know if
> this helps
> Thanks
> shri

always show subreport

I have a report with many subreports. Recently the user requested that
they would like the subreport to show even if no data is returned in
the subreport. (They want the space on the report to show) Inside the
subreport is a table where the data is returned. Can this subreport be
shown spaces and all
without redoing the whole report and taking out the subreports? I
have tried everything I can think of. Any ideas?
Thanks in advance.
MissyNo, BUT, what you could do is have text that appears and says something like
No Data Available or something like that. You could make it verbiage that is
specific to the subreport. In your main report click on the subreport. There
is a proberty called No Data or something like that where you can set it to
some text.
See if that will be enough for them.
Note depending on what you are doing, subreports might be the only way to do
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1164732075.527332.124750@.80g2000cwy.googlegroups.com...
>I have a report with many subreports. Recently the user requested that
> they would like the subreport to show even if no data is returned in
> the subreport. (They want the space on the report to show) Inside the
> subreport is a table where the data is returned. Can this subreport be
> shown spaces and all
> without redoing the whole report and taking out the subreports? I
> have tried everything I can think of. Any ideas?
> Thanks in advance.
>
> Missy
>|||Yes I have done that, but they still want space to write (a couple of
lines) is there any way to add lines to the no data comment?
Bruce L-C [MVP] wrote:
> No, BUT, what you could do is have text that appears and says something like
> No Data Available or something like that. You could make it verbiage that is
> specific to the subreport. In your main report click on the subreport. There
> is a proberty called No Data or something like that where you can set it to
> some text.
> See if that will be enough for them.
> Note depending on what you are doing, subreports might be the only way to do
> it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "aggiechick717" <mhoppe@.chkenergy.com> wrote in message
> news:1164732075.527332.124750@.80g2000cwy.googlegroups.com...
> >I have a report with many subreports. Recently the user requested that
> > they would like the subreport to show even if no data is returned in
> > the subreport. (They want the space on the report to show) Inside the
> > subreport is a table where the data is returned. Can this subreport be
> > shown spaces and all
> > without redoing the whole report and taking out the subreports? I
> > have tried everything I can think of. Any ideas?
> >
> > Thanks in advance.
> >
> >
> > Missy
> >

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.
One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.
|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:
> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> > We have been using the textcopy.exe tool since SQL7 (we were able to still
> > use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> > can
> > use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> > record
> > in our database. Basically, the VB6 code calls a stored proc and passes
> > it
> > the name and location of the file. The stored proc will then use the
> > textcopy.exe to upload this file into an image column in a database table:
> >
> > SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> > password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> >
> > EXEC Master..xp_cmdShell @.cmd
> >
> > where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> > test.pdf).
> >
> > When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> > this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> > to
> > work in 2005.
> >
> > What is the best way to insert a file into an image column in the database
> > in 2005? I'd like to not have to re-architect this piece of our
> > application,
> > but I may have to...is there an alternative similar to textcopy.exe for
> > SQL
> > 2005? Indexing the file is not important - we just need to save the file
> > in
> > the database so that it can be viewed from a web application.
> >
> > Thanks!
> > Jeff M.
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or recor
d
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe t
o
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application
,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from you
r
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>

Alternative to Reporting Services

Hi,

I need a report writer in which I can create some standard reports and also let end user create their own against an SQL Server 2005 database. The writer must have a web interface for users to create and run reports. Crystal Reports was considered too complex for end users.

I've been trying with Reporting Services and MS's Report Builder, but it's been crashing on large data sets.

Can anyone recommend an alternative that I could look at?

Thanks,

Frank

I understand that the concept of allowing end-users to design reports is a popular one in the industry. However, there is a problem, end-users don't know squat about designing databases, data, or reports in general. There are end-user tools that allow them to do this, but if they want something more than a simple list of data from a single table they are all still difficult to use, not to mention dangerous. Imagine the impact on server based reports if an ignorant user tries to pull out large sums of data to create a gigantic report (because they dont have a clue). I can just a imagine a user trying to create a sales report by loading every invoice or general ledger item out the business database. Meanwhile, no one else can work because everything has slowed to a crawl while the server is trying to process their 50 million row report! I think you ought create reports for users and make sure to enable interactive sorting so they can see the data the way they want. Unleasing users into the database is just a bad idea if you ask me.

Any way, I guess you really didnt ask for my opinion, you asked for an application to make it possible. Nearly all versions of SQL Server 2005 (Workgroup, Standard, Enterprise) ship with a program called 'Report Builder'. It is a ClickOnce deployed Winforms app that allows end-user to build their own reports. If I were you I would simply tell your users that no such product exists and save your self from the fiery hell you are sure to enter once they begin meddling with your database.