SQL SQLExpress: sql2k

Showing posts with label sql2k. Show all posts

Sunday, March 25, 2012

connect to distribution server

Need to be able to add data to a mobile with SQL2K
desktop edition and then manually replicate to SQL2K on
server. Can't access the distribution server from the
mobile.
Thoughts suggestions etc?
thanks
Sorry to ask the obvious, but is this when you are connected to the network?
Assuming it is, please can you clarify a few things:
the complete text of the error message,
the type of security configuration of your agents - are you logged onto the
domain, using pass-through security etc
can you 'see' the distribution server - pinging it by name will test this
Regards,
Paul Ibison
|||The distribution server is running on a SQLServer2K SE
machine running W2K OS. The remote is a laptop with
SQLServer2K PE. Both the remote and the server are
registered in EM respectively. They are both part of the
same workgroup but not a domain.
The problem arises when I attempt to use the Configure
Publishing, Subsribers and Distribution Wizard for the
laptop instance (SQLServer2K PE), The Select Distributor
dialogue has the "Use the following server..."
option "grayed-out" as is the "Add Server" button.
The only choice is to use the local machine as the
distributor which I don't want to do.
Is this a limitation of SQLServer2K PE or am I doing
something wrong with my configuration?
In the final anaylsis I need to be able to add data on
the laptop when not conncected to the server and then
manually synchronize when connected.
Thanks for your help
Thanks
>--Original Message--
>Sorry to ask the obvious, but is this when you are
connected to the network?
>Assuming it is, please can you clarify a few things:
>the complete text of the error message,
>the type of security configuration of your agents - are
you logged onto the
>domain, using pass-through security etc
>can you 'see' the distribution server - pinging it by
name will test this
>Regards,
>Paul Ibison
>
>.
>
|||The only restriction I was previously aware of is that SQL Server 2000
Personal Edition cannot be used as publisher for transactional replication.
However if you have a look at this link in BOL :
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\rep
wizrd.chm::/repwizrd_97oi.htm
it seems you are correct. This is a limitation of Personal Edition.
However,
if you are running merge replication, the distinction between publisher and
subscriber in this regard is not so big, so having the mobile as a
subscriber is a posibility.
HTH,
Paul Ibison
|||Thanks. I suppose I will have to work with merge
replication.
>--Original Message--
>The only restriction I was previously aware of is that
SQL Server 2000
>Personal Edition cannot be used as publisher for
transactional replication.
>However if you have a look at this link in BOL :
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\rep
>wizrd.chm::/repwizrd_97oi.htm
>it seems you are correct. This is a limitation of
Personal Edition.
>However,
>if you are running merge replication, the distinction
between publisher and
>subscriber in this regard is not so big, so having the
mobile as a
>subscriber is a posibility.
>HTH,
>Paul Ibison
>
>.
>

Tuesday, March 20, 2012

::FN_VIRTUALFILESTATS doesn't work in Sql2k critical

Hi everyone,

I perfectly know that this post is bad allocated but it's a critical one. We've a cluster and there it don't get rows.

I'm trying this sentence: SELECT * FROM ::FN_VIRTUALFILESTATS and from our production server doesn't return results. Why the hell not?

However from other servers it returns data.

Thanks for your time,

Have you tried examining query execution with SQL Server Profiler?|||That's a good idea.

Monday, March 19, 2012

.rss methods no longer supported

I am having a problem running a sql2k report service script on my new sql2005 server. It seems that the methods SessionHeader() and rs.render are no longer suport. can someone help me figure out what I need to do to continue automating my report running?

rss file contents: (the parts that should matter)

Report Parameters
Dim branch as string = nothing

Dim skipreport as string = nothing
dim skip as integer = 0

dim allbranchreport as string = nothing
dim allbranch as integer = 0

dim report as CatalogItem
dim repname as string = nothing
dim specificreport as string = nothing
dim filepath as string = nothing
dim errorpath as string = nothing
dim basefilepath as string = nothing
dim baseerrorpath as string = nothing
dim errorlog as integer = 0
Dim parameters() As ParameterValue = nothing
Dim paramcount as integer = 0

' Render arguments
Dim result As Byte() = Nothing
Dim reportPath As String = nothing
'Dim format As String = "PDF"
Dim format as string = "EXCEL"
Dim historyID As String = Nothing
Dim devInfo as string = Nothing
Dim credentials As DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader()
rs.SessionHeaderValue = sh
Dim omitdocmap as string = "True"
'**************************************

'**************************************
'Repset run
Dim repset as string = ""
for each repset in repsetlist
specificreports = specificreportsstart
if specificreports = 0 then
select case repset.tolower
case "all"
specificreports = 0
case "brkctr"
specificreports = 1
reportcount = reportset_brkctr.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_brkctr(reportloop)
next
case "cashmgmt"
specificreports = 1
reportcount = reportset_cashmgmt.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cashmgmt(reportloop)
next
case "com"
specificreports = 1
reportcount = reportset_com.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_com(reportloop)
next
case "com_vw"
specificreports = 1
reportcount = reportset_com_vw.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_com_vw(reportloop)
next
repset = "com"
case "comcons"
specificreports = 1
reportcount = reportset_comcons.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_comcons(reportloop)
next
case "cons"
specificreports = 1
reportcount = reportset_cons.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cons(reportloop)
next
case "cons_vw"
specificreports = 1
reportcount = reportset_cons_vw.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cons_vw(reportloop)
next
repset = "cons"
case "dep"
specificreports = 1
reportcount = reportset_dep.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_dep(reportloop)
next
case "staff"
specificreports = 1
reportcount = reportset_staff.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_staff(reportloop)
next
omitdocmapbranch = "False"
case "staff2"
specificreports = 1
reportcount = reportset_staff2.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_staff2(reportloop)
next
omitdocmapbranch = "False"
case else
specificreports = 1
redim specificreportslist(0)
specificreportslist(0) = "Undefined"
end select

if repset.tolower = "staff2" then repset = "staff"
End if

'Base values
basefilepath = basepath & client & "\" & repset & "\"
baseerrorpath = basepath & client & "\" & "error_" & repset & "_"

'Reports List
Dim reports() as CatalogItem
reports = rs.ListChildren(basereportpath, False)

'Execution
if datasourceok = 1 then
for each report in reports

skip = 0
allbranch = allbranchrun

for each skipreport in skipreports
if report.name.tolower = skipreport.tolower then skip = 1
next

if skip = 0 then
if specificreports = 1 then
skip = 1
for each specificreport in specificreportslist
if report.name.tolower = specificreport.tolower then skip = 0
next
end if
end if

if skip = 0 then
for each allbranchreport in allbranchreports
if report.name.tolower = allbranchreport.tolower then allbranch = 1
next

Redim parameters(1)
parameters(0) = New ParameterValue()
parameters(0).name = "branchall"
parameters(1) = New ParameterValue()
parameters(1).Name = "branch"

'5 parameters reports
repname = report.name
if (repname.substring(0,5).tolower = "loans" or repname.substring(0,4).tolower = "locs") then paramcount = 4
if (repname.length >= 13) then
if repname.substring(0,13).tolower = "loans_beacons" then paramcount = 1
end if

if paramcount = 4 then
paramcount = 0
redim preserve parameters(4)

parameters(2) = New ParameterValue()
parameters(2).name = "comconsall"
if repset.tolower = "cons" or repset.tolower = "com" then parameters(2).value = 0 else parameters(2).value = 1

parameters(3) = New ParameterValue()
parameters(3).Name = "comcons"
if repset.tolower = "com" then parameters(3).value = "commercial" else parameters(3).value = "consumer"

parameters(4) = New ParameterValue()
parameters(4).Name = "brokered"
parameters(4).value = 0
if repset.substring(0,3).tolower = "brk" then parameters(4).value = 1
end if

'All Branches Reports
if allbranch = 1 then

parameters(0).value = "1"
parameters(1).value = "admin"
if client = "CSCU" then parameters(1).value = "newwst"
if clienttype = "vw" then parameters(1).value = "1"

errorlog = 0
filepath = basefilepath & report.name & ".xls"
errorpath = baseerrorpath & report.name & ".txt"
reportpath = basereportpath & "/" & report.name
omitdocmap = omitdocmapall
devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"

Console.WriteLine (repset.toupper & " " & report.name & " All branches")

Try
result = rs.Render(reportPath, format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Catch e As SoapException
errorlog = 1
Console.WriteLine(e.Detail.OuterXml)
Catch f as Exception
errorlog = 1
Console.writeline(f.message)
End Try

' Create an error log
If errorlog <> 0 then
Try
Dim stream As FileStream = File.Create(errorpath, 1024)
Console.WriteLine("Errorlog created: " & errorpath)
stream.Close()
Catch g As Exception
Console.WriteLine(g.Message)
End Try
End if

' Write the contents of the report to a file.
If errorlog <> 1 then
Try
Dim stream As FileStream = File.Create(filepath, result.Length)
stream.Write(result, 0, result.Length)
Console.WriteLine("Result written to file: " & filepath)
stream.Close()

Catch e As Exception
Console.WriteLine(e.Message)
End Try
end if

'Other Reports
else
parameters(0).value = "0"

for each branch in branches
parameters(1).value = branch

errorlog = 0
if branch <> "h/o" then
filepath = basefilepath & branch & "\" & report.name & ".xls"
else
filepath = basefilepath & "ho\" & report.name & ".xls"
end if
if branch <> "h/o" then
errorpath = baseerrorpath & branch & "_" & report.name & ".txt"
else
errorpath = baseerrorpath & "ho_" & report.name & ".txt"
end if
reportpath = basereportpath & "/" & report.name
omitdocmap = omitdocmapbranch
devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"

Console.WriteLine (repset.toupper & " " & report.name & " " & "Branch: " & branch)

Try
result = rs.Render(reportPath, format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Catch e As SoapException
errorlog = 1
Console.WriteLine(e.Detail.OuterXml)
Catch f as Exception
errorlog = 1
Console.writeline(f.message)
End Try

Catch e As Exception
Console.WriteLine(e.Message)
End Try
end if

next
end if

end if
next
end if
next

I get this error message from cmd when I try and run my batch:

J:\PRA Publisher>rs -i pra_publisher.rss -s http://localhost/reportserver -l 0

The specified script failed to compile with the following errors:
J:\PRA Publisher> "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe" /t:exe
/main:MainModule /utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web
.Services.dll" /R:"C:\Program Files\Microsoft SQL Server\90\Tools\binn\rs.exe" /
out:"C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.exe" /debug-
"C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.0.vb" "C:\Docu
ments and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb"

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(253) : error
BC30002: Type 'SessionHeader' is not defined.

Dim sh As New SessionHeader()
~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(254) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

rs.SessionHeaderValue = sh
~~~~~~~~~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(434) : error
BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005
.ReportingService2005'.

result = rs.Render(reportPath, format, historyID, de
vInfo, parameters, _
~~~~~~~~~

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(436) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(496) : error
BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005
.ReportingService2005'.

result = rs.Render(reportPath, format, historyID
, devInfo, parameters, _
~~~~~~~~~

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(498) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~

Any advice on how to fix this would be graetly appreciated. Thanks!

The version of rs.exe that ships with SQL 2005 is configured to run against the new 2005 management SOAP endpoint by default. The old 2000 endpoint is still supported. Just add -e mgmt2000 to the command line to use the old endpoint. You can get more information on this option by running rs.exe /? or in the MSDN help files.|||

Thank you so much!!!

That seems to be working beautifully. Just started the batch and its running smoothly so far!

|||

Hi,

my opinion is that this is not a solution, only a workaround. How would a script look like, which is using the ReportingServices2005 endpoint and methods.

I could use the endpoint by this

Dim rs As New ReportingService2005()

But now the debugger tells me that the render method is not a member of Microsoft.SqlServer.ReportingServices2005.ReportingServices2005 .

I want to render a report to the file system. This is the whole script:

' Export report to pdf format to the specified path under filePath and to the file which is specified under fileN
Dim format as string = "PDF"
Dim filePath as String = "D:\temp\"
Dim fileN as String = "reportx"
'Dim Datum as String = CString(Date)
Dim fileName as String = filePath + fileN + ".pdf"
Dim reportPath as String = "/reportx/reportx

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "application/pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
'Windows Credentials were send to Reporting server service
Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(0) As ParameterValue
results = rs.Render(reportPath, format, _
Nothing, Nothing, Nothing, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

'End of script