Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Monday, March 19, 2012

Cant able to exprot data to excel formate

Hi,...

I ma using reproting service tools fpr my reports.

And when I export large data to excel format what i get isSERVER NOT AVALIBLE eroor.

and the same export works fine when the size of data is less.

i want to export 13-17k of rows to excel format...

and i am using 2 GB of Ram.

pls help ...

waiting for ur rply

On the client, in Internet Explorer options, try increasing the size of your Temporary Internet File setting to over 100MB and see if you continue to get the error. If not, then it is related to the size of the temp file that reporting services creates in the temporary internet files in the windows folder.

Thursday, March 8, 2012

Cannot update. Database or object are read-only

I have to build application Excel (interface) - SQL Server (backend)

I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.

And error message I am geting is "Cannot update. Database or object are read-only".

Dim cn As ADODB.Connection
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\pd\Desktop\Development\test.xls;" & _
"Extended Properties=Excel 8.0"


strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"


cn.Execute strSQL

cn.Close
Set cn = Nothing

Read-only of databse properties is not selected.If I get it right you're attempting to insert a row into excel. As long as I know it's not possible because you cannot update, insert records throught Jet provider. mojza|||Your query is unclear . Understanding you are trying to update region table In sql database .
if that's case you should then rather open an ODBC or OLE DB connection to SQL server

cn.Provider = "sqloledb"
cn.Properties("Data Source") = ServerName
cn.Properties("Initial Catalog") = DatabaseName

' If Windows NT authentication.
cn.Properties("Integrated Security")= "SSPI"

'If SQL Authentication
cn.Properties("User ID")= UserID
cn.Properties("Password") = pwd

' Open the database.
cn.Open

Dim strSQL as string

strSQL "INSERT INTO dbo.Region Values(5, 'ab')"

cn.execute strSQL

cn.close
set cn = nothing

Thursday, February 16, 2012

Cannot select CSV XML format in Export Format.

Hi,
in my report viewer export format, i only can select 2 type format. one is
pdf another one is excel.
i already check the rsreportserver.config.
the csv xml type no have set to visible false.
but i still can't see the csv and xml
could someone give me some idea to solve the problem,
thank youAre you running Express? or Workgroup ? One or both of these limits the
export types.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ng" <Ng@.discussions.microsoft.com> wrote in message
news:87BDC13A-1521-4EE2-8579-77B3361D2120@.microsoft.com...
> Hi,
> in my report viewer export format, i only can select 2 type format. one is
> pdf another one is excel.
> i already check the rsreportserver.config.
> the csv xml type no have set to visible false.
> but i still can't see the csv and xml
> could someone give me some idea to solve the problem,
> thank you
>

Sunday, February 12, 2012

Cannot run SQL2005 stored procedure from excel 2003

Usually have no problems pulling data into excel via SQL stored procedures until now.

Created an sp that contains a

declare @.tbl table(

Customerno varchar(6)

,InvoiceDate varchar(25)

,CustomerType varchar(10)

,CustomerRegion varchar(50)

,CustomerTypeName varchar(50)

)

Followed by a insert then a select statement on this @.tbl variable.

whenever i try to call this sp i get an error message saying "The query did not run, or the database table could not be opened"

This is what i'm using to connect:

With Sheets(1).QueryTables.Add(connection:="OLEDB;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=UnityBI;" & _
"Data Source=SQLdatabase;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Workstation ID=ACER-DAVEW;Use Encryption for Data=False;" & _
"Tag with column collation when possible=False", Destination:=Sheets(1).Range("A1"))
.CommandType = xlCmdSql
.CommandText = "usp_gordonsnodrops "
'.Name = "proclarity UnityBI_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

The stored procedure runs fine from other applications, just excell seems to be the problem.

Any help would be greatly appreciated.

Ok, after a very frustating week with this problem the answer turns out to be very easy.

Kinda deflating really after the amount of hair i've pulled out.

The stored procedure needed the statement "Set NoCount On" adding.

Works like a charm now Smile

eg

ALTERPROCEDURE [dbo].[usp_GordonsNoDrops]

@.MonthYear asvarchar(50)='march06/07'

--@.End as varchar(10)

as

begin

setnocounton

sql statements etc....

end