Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.

Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.sql

Invoking a service on firing a trigger

Hi

Im trying to invoke a .Net service when a trigger is fired on a table in SQL Server 2000.

The service is working fine but I need to invoke it when the trigger is fired. How can I achieve this?

Hi!

At first, why do you think your problem is concern with SQL-NS? The second: does you question relate to SQL Server 2005? You have wrote, that the trigger on SQL Server 2000 invokes the service and the services works well.

Then, what do you mean by saying "invokes"? Does the trigger start the service or it just send a message to him?

Invoking a service on firing a trigger

Hi

Im trying to invoke a .Net service when a trigger is fired on a table in SQL Server 2000.

The service is working fine but I need to invoke it when the trigger is fired. How can I achieve this?

Hi!

At first, why do you think your problem is concern with SQL-NS? The second: does you question relate to SQL Server 2005? You have wrote, that the trigger on SQL Server 2000 invokes the service and the services works well.

Then, what do you mean by saying "invokes"? Does the trigger start the service or it just send a message to him?

Invoking a batch file from Report

Hi !

I have to invoke a batch file (.bat) when an ad-hoc report is executed. I've currently written a .Net code for invoking (using System.Diagnostics.Process) the batch file. The rdl file calls the method.

When I run the report from Report Designer the batch file is getting executed. But if I deploy it in the Reporting Server and run the report through Web Interface the batch file is not getting executed.

Looks like some security issue. Can someone provide a solution for this?

Thanks.

Where did you place the batch file ? All commands executed either in Script or custom code or executed on the server not on the client.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just a suggestion on how to execute the .bat file. Could′t you use a SQL Agent job instead and call sp_start_job from a dataset in the report?|||

Hi Jens,

Thanks for the reply.

I placed the batch file in the server. I also moved the code (to invoke a batch file using System.Diagnostics.Process class) to a VB.Net dll and tried invoking the function from the rdl. The function is not getting invoked. I also added SecurityPermissionAttribute to the class. Is there any other Security permission that needs to be added for the code to be accessed.

Please find below the sample:

Public Shared Function fnRunSPX(ByVal x As String, ByVal y As String, ByVal z As String, ByVal w As String)

Dim Pro As New System.Diagnostics.Process

Dim Elog As New System.Diagnostics.EventLog

If Not Elog.SourceExists("BillingDateBatch") Then

Elog.CreateEventSource("BillingDateBatch", "Application")

End If

Dim EventLog1 As New System.Diagnostics.EventLog

EventLog1.Source = "BillingDateBatch"

EventLog1.Log = "Application"

EventLog1.WriteEntry("Success", System.Diagnostics.EventLogEntryType.SuccessAudit)

Pro.StartInfo.FileName = "C:\\batch.bat"

Pro.StartInfo.Arguments = x + " " + y + " " + z + " " + w

Pro.Start()

Pro.WaitForExit()

End Function

Even the evenlogging (present in teh above code) does not happen. Please let me know if you have any questions.

Thanks,

Aravind

Invalid value for key attachdbfilename.

HI,

We upgraded to SQL Server 2005 Standard Edition for our ASP.NET 2.0 website. We were using SQL Server Express 2005. That worked fine. Now we are unable to connect to the database. I have googled, but I just cann't figure out what is going on. Any help is appreciated. Here is the error.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

In our firewall sqlbrowser.exe and sqlservr.exe are allowed.

Thanks Matt

Matt,

I have seen this error everytime I install MS SQL 2005, you need to enable TCP/IP as by default is not

Open the program -> SQL Server Surface Configuration

Go To -> Surface Area Configuration and Connections

Go To -> Remote Connections

and change Local Connections only to

Local and Remote connections

|||

AL,

Thank you for the quick reply. I did what you said and it was configured to,

Local and Remote Connections

Using both TCP/IP and named pipes,

already.

My databases reside inside my App_Data folder in my ASP.NET 2.0 application.

Do you have any other ideas?

Could it be my connection string?

Thank you again.

Matt

|||

Matt,

My biggest apologies, I jump to the answer as I have seen this message more than once. Again sorry.

If you already configured that, and you open all the ports on the firewall, I do not remember the port number but you can test from one computer to the other by opening a dos box and doing

telnet servername portnumber

If connects, you are good!! If does not ... open the port in the firewall

|||

Al,

Thank you again for helping me. I think that it is my fault. I don't think I have given you enough information about my situation.

I am trying to move my asp.net 2.0 website from sql server express to sql server 2005 standard edition.

So far all I have done is uninstall sql express and then installed sql server 2005.

I configured sql server 2005 with, what I assume to be the standard setup. I just used the wizard and did not change anything else. Those settings from the previous post were what was already checked when you told me to look at them. I did not set them.

I think from the little bit of research I have done, that I cann't use the AttachDbFile feature that sql express uses for sql server 2005?

At this point I am completely lost and frustrated. I am fairly new to this and trying hard to figure it out.

I do appreciate the help.

Any suggestion or ideas?

Thank You Again

Matt

|||

Actually attaching the database is the best solution. Also check on the logins after attaching that the username and password are correct. You can use VS2005 to connect to a database to see if that test works, there is a great wizard with great errors that might tell you what the problem is.

Besides that I am pretty lost, should work :-(

|||

Al,

Thank you for the help. I got it to work with one database. I used the wizrd and it worked great.

I also created a user in sql server 2005. I gave it a bunch of permissions just to get it to work. I think I should narrow thoses down to only the ones it needs.

My only other question is, I ran the aspnet_regsql.exe and it created a database in sql server 2005. But I was unable to use the ASP.NET Configuration tool to create roles and users. I made the same type of connection as with my other database. Error below.

The following message may help in diagnosing the problem:A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Any ideas?

Thank You Again

Matt

|||When you use ASP.NET Configuration, make sure you give it a connection string that will work on the NEW DATABASE for users and roles. If you connect to the new database, you'll see the providers tab|||

Al,

Thanks again. I got it all working now. It was the connection string.

Thanks

Matt

|||

Did you figure it by going to ASP.NET Configuration?

Glad you fix it! Stick around!

|||

No, I worked through it on a post by scottgu. I had .mdf in my connectionstring. When I removed .mdf, it made the connection.

Thanks Matt

|||Ah! Mr Guthrie's blog is like a gold mine!

invalid row count

Hi,

I am building a shopping cart program, complete with an admin back end.
I am using ASP.NET, VB. NET and a SQL SERVER 2000 database.

The cart is working well... items can be added, updated and removed. However when trying to access the orders stored in the database from the admin section of the application, I am getting an "invalid record count" error.

I have no idea what this means. I've tried looking it up online, but so far no luck.

The orders are being stored in two tables connected via a foreign key.
I have no problem writing to the tables, it's just a problem reading the tables. I have dropped the two tables twice and rebuilt them, but I get the same error each time.

Any thoughts on what is causing this error?

Thanks,

-MichaelSince this is a sql server forum, maybe some sql would help...

Sounds more like an interface problem though.../|||Originally posted by Brett Kaiser
Since this is a sql server forum, maybe some sql would help...

Sounds more like an interface problem though.../

It is definitely starting to look more and more like an interface problem.
I just checked my SQL server logs and their are not database errors associated with this application.

Thanks,

Michaelsql

Invalid object name while reading data out of an SQL Database

Hi all,
I'm a complete newbie on ASP.Net.

I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.

When I try to open the site, I always get the same error:
Invalid object name 'Members'

I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.

Here is the Code of the page so far:

<%@. Page Language="VB" Debug="True" Strict="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQlClient" %>
<script runat="server">
Sub Page_Load (ByVal Sender As Object, _
 ByVal E As EventArgs)
 Dim connStr As String
 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
 connStr += "database=tempdb;"
 connStr += "Truster_Connection=yes"
 Dim conn As New SQLConnection(connStr)
 conn.Open()
 Dim sql As String
 sql = "SELECT COUNT (*) FROM Members"
 Dim cmd As New SQLCommand(sql, conn)
 Dim ergebnis As String
 ergebnis = cmd.ExecuteScalar().toString()
 Dim t As String
 t = "Die Tabelle Members hat " & _
 ergebnis & " Zeilen. <br>" & _
 "Das Kommando lautet: " & _
 cmd.CommandText & "<br>" & _
 "Der Kommandotyp ist: " & _
 cmd.CommandType
 ausgabe.innerHTML = t
End Sub
</script>
<html><head><title>
Demo zu SQLCommand.ExecuteScalar
</title></head>
<body>
<h3>Demo zu SQLCommand.ExecuteScalar</h3>
<p runat="server" id="ausgabe" />
</body></html>

Thanks for your help an sorry for my english.

Greets
Flash_Prince

The problem is objects in tempdb is valid for limited time so your object the Member table does not exist. Microsoft provided the tempdb for SQL Server to be used to proccess complex queries and test queries so when you create a table if you did not drop it SQL Server will drop it at some point. Try the links below to download sample databases you can create and use with SQL Server 2005. The databases comes with tables but you can create your own tables so you can add the member table to any of the three database. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

|||

The problem is, that i want to use the database without entering the path of the database file in the sourcecode. When I use the nordwind database, all the sql queries are working very well. But if I use the Nordwind Database, I only know to connect by OleDB.

Can you tell me how to use a database in the way decribed above with a sql connection?

I hope, this is described understandable, cause my english is not the best.

|||

You need to create connection string and most of the information you need is covered in the two links below. Hope this helps.

http://www.connectionstrings.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||The problem is in your connection string. You have a typo: "Truster_Connection=yes" should be "Trusted_Connection=yes".

Invalid object name sysperfinfo

When I execute this statement through ASP.NET

select cntr_value FROM sysperfinfo

I get this error,

System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'.

Any ideas why?

It sounds to me like your connectionstring (the string that points to the database which you are querying) is pointing to a database that does not have a table entitled 'sysperfinfo'|||

To add to the previous post some things have changes about sysperfinfo. Try the links below for more. Hope this helps.

sysperfinfo

In SQL Server 2005,sysperfinfo returns abigint value for thecntr_value column. Modify applications that usesysperfinfo to make sure that they can handle thebigint values of thecntr_value column.

In SQL Server 2005,sysperfinfo is a compatibility view. You should use thesys.dm_os_performance_counters dynamic management view instead.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

http://www.sqlservercentral.com/columnists/jsack/troubleshootingsqlserverwiththesysperfinfotable.asp

|||

Maybe you're in the wrong database, try:

select cntr_value FROM master..sysperfinfo

Invalid object name dbo.aspnet_SchemaVersions - what did I ever do to you?

environment: asp.net 2.0, vb, sql server 2000, windows server 2003.

Currently when I try to test to enter information through a CreateUserWizard this error pops up, I have checked my databases and I do have a aspnet_SchemaVersion there, I checked around the net and on the forums and I fixed any problems I thought could be causing it, I gave network services rights, as well as my sql server login, I have both a membership and a schemaversion table in the database.

I ran aspnet_regsql.exe to make a new target for my <forms> login, I just don't see what the problem is and can't seem to find anything out there that is the same as my problem. The following is the web.config:

<configuration>
<appSettings>
<add key="ConnectionString" value="DataSource=mew@.mew.com;UID=mew;pwd=mewmew" />
<add key="ConnectionString" value="Server=xxxxxxxxxx;database=customerlogin;UID=xxxxxxxxx;pwd=xxxxxxxx;Trusted_Connection=True" />
</appSettings>
<connectionStrings>
<add name="SqlServerConnection" connectionString="Server=xxxxxxxxxx;database=customerlogin;UID=xxxxxxxxxx;pwd=oni@.ichi;Trusted_Connection=True" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=xxxxxxxxxx;Integrated Security=SSPI;Initial Catalog=customerlogin;UID=xxxxxxxxx;pwd=xxxxxxxxx;Trusted_Connection=True" />
</connectionStrings>
<location path="secret">
<system.web>
<webParts>
<personalization defaultProvider="MyPersonalizationProvider">
<providers>
<membership defaultProvider="MyMembership">
<add name="MyMembership" type="System.UI.WebControls.WebParts.SqlPersonalizationProvider" connectionStringName="SqlServerConnection" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" requiresPassword="false" />
</membership>
</providers>
<authorization>
<deny users="?" />
</authorization>
</personalization>
</webParts>
<authentication mode="Forms" />
</system.web>
</location>
</configuration>

and this is the register.aspx page:

<%@. Page Explicit="true" debug="true" language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data.Mail" %>
<%@. Import Namespace="System.Net.Mail" %>
<%@. Import Namespace="System.Web.Configuration" %>

<script runat="server">
Sub Page_Load()
If Not Page.IsPostBack Then
Dim dest As String = "~/Default.aspx"
If Not String.IsNullOrEmpty(Request.QueryString("ReturnURL")) Then
dest = Request.QueryString("ReturnURL")
CreateUserWizard1.ContinueDestinationPageUrl = dest
End If
End If
End Sub

Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)
CreateUserProfile(CreateUserWizard1.UserName, txtFirstName.Text, txtLastName.Text)
End Sub

Private Sub CreateUserProfile(ByVal userName As String, ByVal firstName As String, ByVal lastName As String)
Dim conString As String = WebConfigurationManager.ConnectionStrings("Test").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT Test (UserName,FirstName,LastName) VALUES (@.UserName,@.FirstName,@.LastName)",con)
cmd.Parameters.AddWithValue("@.UserName", userName)
cmd.Parameters.AddWithValue("@.FirstName", firstName)
cmd.Parameters.AddWithValue("@.LastName", lastName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub

</script>

<html>
<head>
<title>Untitled Document</title>
</head>

<body>
<form runat="server" id="form1">
<asp:CreateUserWizard id="CreateUserWizard1" OnCreatedUser="CreateUserWizard1_CreatedUser" Runat="server" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" requiresPassword="false">
<WizardSteps>
<asp:WizardStep>
<asp:Label id="lblFirstName" Text="First Name:" AssociatedControlID="txtFirstName" Runat="server" />
<br>
<asp:TextBox id="txtFirstName" Runat="Server" />
<br>
<br>
<asp:Label id="lblLastName" Text="Last Name:" AssociatedControlID="txtLastName" Runat="server" />
<br>
<asp:TextBox id="txtLastName" Runat="server" />
</asp:WizardStep>
<asp:CreateUserWizardStep />
</WizardSteps>
</asp:CreateUserWizard>

</form>
</body>
</html>

The exact error with stack trace is below:

Invalid object name 'dbo.aspnet_SchemaVersions'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'dbo.aspnet_SchemaVersions'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Invalid object name 'dbo.aspnet_SchemaVersions'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85 System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612 System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305 System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105 System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453 System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149 System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Any help is much appreciated, and thanks in advance.

How do you create the aspnet_SchemaVersions Table? did you use the tool come with VS? if not you have to verify all the table names, column names are type correctly. sepcially make sure your table name is "aspnet_SchemaVersions" not "aspnet_SchemaVersion"

Hope this help

|||I figured out the problem, I had changed the ownership of the table and apparantly the createuserwizard control doesn't like you to do that, so that generic error was the result.|||

I am sure this is the case when you are working on local server, on your own machine.

What if you have deployed working application to the ftp server say (godaddy), which has old version of SQL, SQL Server 2000?

I am facing this issue, the application works fine on local machine, but when it comes to godaddy, I have an error:

Invalid object name 'dbo.aspnet_SchemaVersions'.

How to set schema for godaddy or just modify application on local machine so that when uploaded it works on host server. Any views are welcomed.

Cheers

sql

Invalid buffer received from client.

I'm having seriouos trouble after installing SP4. I have an application writ
ten in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after upgrading
to SP4, the application totally crashed while trying to access the database.
It gives an error saying a
severe error has occued on the server. The
event log on the server has the following logged:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
For more information, see Help and Support Center at http://go.microsoft.com/fwlin
k/events.asp.
I'm sure it's SP4 because after I reinstalled SQL Server with SP3, everythin
g worked fine again. The server is running Windows 2003 SP1.
Anyone else having this problem or have a solution?Sp4 reduces the network packet size to 32767. Consider adjusting your app to
avoid the error.
<quote>
In SP4, the maximum value for the network packet size option (set using
sp_configure) is 32767. This is slightly less than half the previous maximum
of 65536. During upgrade, existing values larger than 32767 will
automatically be adjusted to 32767. If a script attempts to use sp_configure
to set a value larger than 32767 but less than or equal to 65536, the value
will also be set to 32767. Setting the network packet size to a value larger
than 65536 results in an error.
</quote>
There's used to be a bug even when the network packet size was set to
65535...
http://support.microsoft.com/kb/875411
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42813312@.duster.adelaide.on.net...
> I'm having seriouos trouble after installing SP4. I have an application
> written in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after
> upgrading to SP4, the application totally crashed while trying to access
> the database. It gives an error saying a severe error has occued on the
> server. The event log on the server has the following logged:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> I'm sure it's SP4 because after I reinstalled SQL Server with SP3,
> everything worked fine again. The server is running Windows 2003 SP1.
> Anyone else having this problem or have a solution?|||Thanks oj...
Do you have an example of how to change the packet size to 32767 - is it thr
ough the connection string? At the moment, it has packet size set to 4096.
oj wrote:
> Sp4 reduces the network packet size to 32767. Consider adjusting your app
to
> avoid the error.
> <quote>
> In SP4, the maximum value for the network packet size option (set using
> sp_configure) is 32767. This is slightly less than half the previous maxim
um
> of 65536. During upgrade, existing values larger than 32767 will
> automatically be adjusted to 32767. If a script attempts to use sp_configu
re
> to set a value larger than 32767 but less than or equal to 65536, the valu
e
> will also be set to 32767. Setting the network packet size to a value larg
er
> than 65536 results in an error.
> </quote>
> There's used to be a bug even when the network packet size was set to
> 65535...
> http://support.microsoft.com/kb/875411
>|||Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
According to the article, you might want to limit each of your insert batch
to 32767 rows.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42817d1b$1@.duster.adelaide.on.net...
> Thanks oj...
> Do you have an example of how to change the packet size to 32767 - is it
> through the connection string? At the moment, it has packet size set to
> 4096.
> oj wrote:|||I tried what you suggested but with no vail.
Here's my connection string:
UID=user;PWD=password;Initial Catalog=DatabaseName;Data Source=ServerName;Pa
cket Size=4096;
I tried the packet size 2048, 1024, 768 but they all return the same error.
Any help is greatly appreciated!
oj wrote:
> Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
> According to the article, you might want to limit each of your insert batc
h
> to 32767 rows.
>|||Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
and be sure the length is not exceeding the allowable for the specified
type.
If you post your code (.Net call + sql), someone will take a closer look.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:428293aa$1@.duster.adelaide.on.net...
>I tried what you suggested but with no vail.
> Here's my connection string:
> UID=user;PWD=password;Initial Catalog=DatabaseName;Data
> Source=ServerName;Packet Size=4096;
> I tried the packet size 2048, 1024, 768 but they all return the same
> error.
> Any help is greatly appreciated!
> oj wrote:|||Thanks oj... I changed my code as you suggested and it is now working! :)
But now it runs extremely slow and frequently timeout. Any solution to this
problem as well?
oj wrote:
> Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
> and be sure the length is not exceeding the allowable for the specified
> type.
> If you post your code (.Net call + sql), someone will take a closer look.
>|||Double check your sql to make sure proper indexes installed. Check out these
to see if they help:
http://support.microsoft.com/?id=308049
http://support.microsoft.com/kb/224587/
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282bc9e@.duster.adelaide.on.net...
> Thanks oj... I changed my code as you suggested and it is now working! :)
> But now it runs extremely slow and frequently timeout. Any solution to
> this problem as well?
>
> oj wrote:|||Thanks oj... It's working fine now...
The problem was caused by creating an SQL parameter using DbType instead of
SqlDbType. I was trying to create a generic data provider that connects to b
oth Oracle and SQL Server. Now I think I have to map the individual DbType t
o SqlDbType.
Anyway, thanks for your help!! Much appreciated!
oj wrote:
> Double check your sql to make sure proper indexes installed. Check out the
se
> to see if they help:
> http://support.microsoft.com/?id=308049
> http://support.microsoft.com/kb/224587/
>|||You're very welcome.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282ce51@.duster.adelaide.on.net...
> Thanks oj... It's working fine now...
> The problem was caused by creating an SQL parameter using DbType instead
> of SqlDbType. I was trying to create a generic data provider that connects
> to both Oracle and SQL Server. Now I think I have to map the individual
> DbType to SqlDbType.
> Anyway, thanks for your help!! Much appreciated!
> oj wrote:

Interview questions

Hello,

I have an interview coming up for a vb.net programmer and work involves Reporting Services. What questions/tests could they possibly ask me?

Thanks in advance

Hi,

The forums is basically a place where you can ask your doubts/queries etc. For interview questions, it would be better if you search Google. There are a lot of inteview questions sites too.

Regards,

Vivek

Internet merge pull subscription server name problems

I recently did a test-run of continuous merge pull replication on my
intranet and it worked wonderfully. Now I am trying to make it work
over the net, and I just can't get server names to ever work
correctly! The server is listening on a non-standard port and this is
open from the firewall.. a test connection and telnet connect just
fine through the firewall.
If someone could please give me a list of everything that must be in
place for the remote server to be able to reference the
publisher/distributor by host NAME not IP, I would greatly appreciate
it.
I assume I need a server alias on the subscriber? Does the servername
here include the instance name? How do i reference this alias in my
sp_addmergepullsubscription and sp_addmergepullsubscription_agent
calls? What about the non-standard port?
The publisher/distributor has a localname, say SERVER, so in EM it
shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
"SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
server?
The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
we can't be sure of the IP, but we know it's always at
server.domain.com
MSDE is semi-disconnected, so this will be a PULL anonymous
subscription. I will be creating it through the above referenced
stored procedures.
Thanks in advance!
you must use SQL client network utilty to talk to the instance. Open up Client Network Utility, add a name for the alias. Then for servername add the name of the server and its instance, ie ServerName\InstanceName.
Then edit your hosts file for the ip address of the server, ie
ServerName xxx.xxx.xxx.xxx
The server name does not have to be a FQDN, ie SERVER.DOMAIN.COM\INSTANCENAME, just a name that has an ip address. For logistical purposes you should call it after your Publisher or Subscriber name, but you don't have to.
Ping this server from your publisher/subscriber to make sure it can resolve correctly and you have connectivity. you may need to use tracert to find out where your connectivity breaks.
Then its a matter of enabling your subscriber. register this server in Enterprise Manager, and then go to tools, replication, configure distributor, publishers, and subscribers, locate your subscriber, click on the check box to the left of the servername,
click on the browse button and enter the sa account (or an account in the sysadmin role), and its password. To replicate over the internet you must use sql authentication. Your publication should be set up for anonymous pull using FTP.
In your procs sp_addmergepullsubscription and sp_addmergepullsubscription_agent you will be referencing the publisher by whatever name you have assigned to this subscriber as its alias in Client Network Utiltiy.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Josh" wrote:

> I recently did a test-run of continuous merge pull replication on my
> intranet and it worked wonderfully. Now I am trying to make it work
> over the net, and I just can't get server names to ever work
> correctly! The server is listening on a non-standard port and this is
> open from the firewall.. a test connection and telnet connect just
> fine through the firewall.
> If someone could please give me a list of everything that must be in
> place for the remote server to be able to reference the
> publisher/distributor by host NAME not IP, I would greatly appreciate
> it.
> I assume I need a server alias on the subscriber? Does the servername
> here include the instance name? How do i reference this alias in my
> sp_addmergepullsubscription and sp_addmergepullsubscription_agent
> calls? What about the non-standard port?
> The publisher/distributor has a localname, say SERVER, so in EM it
> shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
> "SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
> server?
> The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
> we can't be sure of the IP, but we know it's always at
> server.domain.com
> MSDE is semi-disconnected, so this will be a PULL anonymous
> subscription. I will be creating it through the above referenced
> stored procedures.
> Thanks in advance!
>

Internet integration

Is there a way to embed reports in an INTERNET app (ASP.NET) using the following requirements:

- Get reports from Report Server (with toolbar and parameters)
- Security authentication/authorisation comes from ASP.NET web app (single sign-on, not Windows authentication, but custom)

In brief, I just want to host reports in an ASP.NET app, as they appear in the html viewer, but embedded in the app, on the Internet. URL access is perfect, but we require a single sign on with CUSTOM security.

Problem : how to integrate the security context of the web app and the rs, rendering reports WITHIN the web app.
Problems I am experiencing:

- Using an IFRAME to render the reports requires that the browser has direct access to the report server. We don't want this unless we can authenticate/authorise the user once only, through our custom security interface.
- Using the asp.net web app to call the API Render method will not render binary content such as images, unless we use the mhtml rendering format. How do we get the asp.net app to call the api and return an html page with all the correct links to images stored on the server?
Ideas:
Should we develop a custom Security extension?
Any help would be greatly appreciated.

Have you looked at the report execution web service? It seems to fit all of your requirements:

http://msdn2.microsoft.com/en-us/library/ms155071.aspx

|||

Our client has identical requirements - in short, is it possible to fulfill these requirements with RS2000? RS2005 is not an option.

The only compromise appears to be using web-services rendering, but this loses all interactivity functionality such as paging, drill through, document map and collapsible regions - you would also lose the ActiveX control unless you added script code. To me, this represented the best compromise.

Is there a way to fulfill the original requirements? The toolbar is mandatory.

|||

HI,

Thanks for your response. Can I render html (not mhtml) from the web service? How does it deal with binary content such as images, which would be in a separate file in html? I presume you can render only a stream of info, not two streams?

Actually, I admit to overlooking the functionality provided by report viewer. I discovered yesterday that it operates in two "modes" : Remote or local. Remote uses reports straight off the server (.rdl) and shows them beautifully, just as they are rendered in html by report manager (including toolbar and params). Local, however, uses a differnent mode of operation - the reports are rendered from rdlc files within your application without the parameters. You can pass parameters programmatically to local reports, but that's a lot of programming (duplication of effort - hence, my problem).

Incidentally, we solved the security problem by using remote mode with report viewer. The web.config has <identity impersonate="true">. We also set the username and password attributes of the identity tag, and set up a user account on the box, corresponding to the user identified in <identity>. The user account was configured to be allowed to access the report on the report server (report manager security setup) So the ASP.NET web app impersonates for our web clients and retrieves the relevant report, based upon our web app's security system. Our web app could use the SOAP API to iterate all reports from ReportServer and manage the reports through it's custom security interface.
In sum : USE THE REPORT VIEWER IN REMOTE MODE !

I hope this helps other confused souls out there.