putting Word docs, etc in DB

We have a project when multiple people in the company will
want to look at Word documents, other text files, maybe
Excel spreadsheets.
Would it be best to put the location of the files in a
database and have our network mappings set up for anyone
in the company can see these files
or
Has anyone put the actual Word document in a SQL Server
(store the binary data in an Image data type) and then
tried using that to bring up the Word document? I think
storing the data from the file into an Image data type
should be fine, but I'm not sure how you'd get Word to
work with the data in the database and display the
contents.
The first option will work but there are some networking
issues. I'm just wondering if a database solution might
work and how you'd do that.
TIA,
Doughttp://www.aspfaq.com/show.asp?id=2149
"Doug" <anonymous@.discussions.microsoft.com> wrote in message
news:5a3e01c4009a$e6919280$a501280a@.phx.gbl...
> We have a project when multiple people in the company will
> want to look at Word documents, other text files, maybe
> Excel spreadsheets.
> Would it be best to put the location of the files in a
> database and have our network mappings set up for anyone
> in the company can see these files
> or
> Has anyone put the actual Word document in a SQL Server
> (store the binary data in an Image data type) and then
> tried using that to bring up the Word document? I think
> storing the data from the file into an Image data type
> should be fine, but I'm not sure how you'd get Word to
> work with the data in the database and display the
> contents.
> The first option will work but there are some networking
> issues. I'm just wondering if a database solution might
> work and how you'd do that.
> TIA,
> Dougsql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>sql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this :(
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this :(
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
.......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this? Any suggestions?
David
Hi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this? Any suggestions?
> David
>

Pushing data from Excel to a SQL server

Hello,
Is it possible to feed a SQL server with data from an Excel sheet? If so -
how?
Thanks in advance,
Christian DavidssonHi,
DTS (Data transformation service) will be the easiest option to load data
from Excel file to SQL server table.
Thanks
Hari
MCDBA
"Christian Davidsson" <christian [dot] davidsson [at] skanemejerier
[dot]
se> wrote in message news:OyGFFA4$DHA.3048@.tk2msftngp13.phx.gbl...
> Hello,
> Is it possible to feed a SQL server with data from an Excel sheet? If so -
> how?
>
> Thanks in advance,
> Christian Davidsson
>|||Hari's right, DTS would work great. Another option would be to create a
linked server.
306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
326839 Support WebCast: Microsoft SQL Server: How to Configure, Manage, Use,
http://support.microsoft.com/?id=326839
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Pulling Data into one column

I don't know if this is possible, but here goes;

I need to pull sales data out of SQL into Access or Excel to run in a Pivot Table. I am currently doing this by querying SQL with an Access Database and then imoprting that data into Excel to pivot. It takes literally hours to refresh this pivot. I would like to create a SQL View to help speed this process along. The problem is that Debits and Credits are both put into my SQL database as positive numbers. I am pulling only these two types of sales entries.

What I would like to do is this: iif(dbo.table.type = "credit", dbo.table.dollaramount, dbo.table.dollaramount * -1)

This would pull all of my credits as positive numbers and my debits as negative numbers. Is there a way to write this in a SQL query instead of using Access?

Something like this, using CASE:

Code Snippet

SELECT MyField = CASE

WHEN dbo.table.type = 'Credit' THEN ( dbo.table.dollaramount * (-1) )

ELSE dbo.table.dollaramount

ELSE
FROM MyTable

|||

Excellent! With a little massaging, this seems to be what I am needing. This is what I used:

Code Snippet

SELECT table.type, table.docnumbr, table.itemnmbr, table.itemdesc, 'GROSSSALE' =

CASE

WHEN type = 4 THEN (table.XTNDPRCE * (-1) )

ELSE (table.XTNDPRCE)

END

from table

where type = 3 or type = 4

I think I am going to try to incorporate an Inner Join now.

Again, thanks for your help with this.

-Jody

Pulling data from an excel sheet

I need to pull data from an excel sheet and load the data into a SQL table. This job should be done as a part of a SP. Because there is a lot of SQL code that needs to execute right after the file is loaded. We get like 20 of these files everymonth. I created a job and executed it using xp_cmdshell and DTSRun from my SP. But then the user needs be in sysadmin role or the account under which MSSQLServer runs should be a part of local administrators group. Both are not a very good from a security standpoint. So what are my options? What if I use opendatasource ?? I would doing some dyanmic SQL, but looks like there is no other option. Looking for ideas.

Your time and thoughts are greatly appreciated.Why not use dts and execute the sp within dts after the file has been loaded ?|||Or within the sp you can use sp_start_job.|||How he could load the information to table. Sorry Im not familiar with dts or sp, but are those kind of ready-made scripts?|||jeremas - When creating a dts package, you can either use a wizard or create one from scratch. When using the wizard, basically you select a source, destination and any transformations. The source is the file (in this case) - the destination is the database where you want the source loaded - the transformations allow you to map the columns in your source file to columns in your table ... also allowing you to manipulate the data as well before it is stored in the table.

Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus

Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus