• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not locate entry in sysdatabases for database 'MSISDN_Master'. No entry found with that name. Make sure that the name is entered correctly.

Any Idea Why it is doing this? I am trying to export data from SQL to Excel
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
use master
 
go
if object_id('spExportData') is not null
    drop proc spExportData
go
 
CREATE PROCEDURE spExportData 
 
(
    @dbName varchar(100) = 'master', 
    @sql varchar(5000) = '',     
    @fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
    select 0 as ReturnValue -- failure
    return
end 
-- if DB isn't passed in set it to master
select    @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
    drop table ##TempExportData
if object_id('##TempExportData2') is not null
    drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + 
     substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
    select 0 as ReturnValue -- failure
    return
end 
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT    @columnNames = COALESCE( @columnNames  + ',', '') + column_name,
        @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000),' 
        + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
                             when data_type in ('numeric', 'decimal') then ',128'
                             when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
                             when data_type in ('datetime', 'smalldatetime') then ',120'
                             else ''
                        end + ') as ' + column_name
FROM    tempdb.INFORMATION_SCHEMA.Columns
WHERE    table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]        
from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select    @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql
if @@error > 0
begin 
    select 0 as ReturnValue -- failure
    return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
go
declare @sql varchar(6800),    @dbName varchar(100), @fullFileName varchar(100)
select    @dbName = 'MSISDN_Master', @sql = 'select * from MSISDN_Exceptions', @fullFileName = 'c:\test.xls'
exec    master..spExportData @dbName, @sql, @fullFileName

Open in new window

0
AxleWack
Asked:
AxleWack
1 Solution
 
AxleWackAuthor Commented:
Sorted it out.

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs)
       
        Dim style As String = "<style> .text { mso-number-format:\@; } </style> "
   
        Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
        Response.ContentType = "application/excel"
   
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
   
        GridView2.RenderControl(htw)
   
        ' Style is added dynamically
        Response.Write(style)
        Response.Write(sw.ToString())
        Response.[End]()
       
    End Sub

    Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(1).Attributes.Add("class", "text")
        End If
    End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now