How do I lengthen the time out for SQL queries?

I'd like to make the connection time unlimited for a certain sql query to SQL Server from ASP.NET, how do I do this? I already tried setting "Connect Timeout=0" in the sql connection string, this doesn't work...

Please help.Have you tried setting the CommandTimeout property for the SqlCommand object?

SqlCommand.CommandTimeout = iSecondsForTimeout

|||Thanks, that was it.sql

How do I know if SQLXML 3.0 is installed

I downloaded and installed (at least I think I did), sqlxml 3.0
However when I go into Visual Basic.NET and try to add a reference, I dont
see in my list of COM objects, SQLXMLBULKLOADLib
HOWEVER, I can do a objBulkLoad =
CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
Can anybody HELP?
It should be in ARP (Add/Remove Programs). If you don't have it there,
download the latest v3, which is SQLXML 3.0 SP3.
Best regards
Niklas Engfelt
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:4135CD77-7669-43EF-901C-D65CE702CDB1@.microsoft.com...
>I downloaded and installed (at least I think I did), sqlxml 3.0
> However when I go into Visual Basic.NET and try to add a reference, I dont
> see in my list of COM objects, SQLXMLBULKLOADLib
> HOWEVER, I can do a objBulkLoad =
> CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> Can anybody HELP?

How do I know if SQLXML 3.0 is installed

I downloaded and installed (at least I think I did), sqlxml 3.0
However when I go into Visual Basic.NET and try to add a reference, I dont
see in my list of COM objects, SQLXMLBULKLOADLib
HOWEVER, I can do a objBulkLoad =
CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
Can anybody HELP?It should be in ARP (Add/Remove Programs). If you don't have it there,
download the latest v3, which is SQLXML 3.0 SP3.
Best regards
Niklas Engfelt
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:4135CD77-7669-43EF-901C-D65CE702CDB1@.microsoft.com...
>I downloaded and installed (at least I think I did), sqlxml 3.0
> However when I go into Visual Basic.NET and try to add a reference, I dont
> see in my list of COM objects, SQLXMLBULKLOADLib
> HOWEVER, I can do a objBulkLoad =
> CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> Can anybody HELP?

How do I insert into existing Temp table?

Hi,

How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.

What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).

I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.

If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

My problem is that #GroupConfidence already exists so in fact I have this code below:

CREATE TABLE #GroupConfidence
( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.

Thanks in advance,

Ian.SELECT .... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.

If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:

CREATE TABLE #GroupConfidence ...

INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

Hope the above helps you.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.

So thanks for this.

Ian.

:D

How do I insert data from an Access db to a empty SQL database

Hi,

I'm new to VS2005 (vb.net) and here my situation

I have form with a dataset1 (tbl1, tbl2, tbl3, tbl4) pulling data from a Access db. and showing it on the form1(databound)

I need to write what is on form1 to the empty dataset2 in SQL 2005 db

I have created a new DB in SQL 2005 with a Table SQL1 which has the same fields as on form1. Please can some one show me how do I do this. Please

Thanks in advance for your response.

-NM

You can use the Export functionality of Access.

See this post for details:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295865&SiteID=1

|||

Thanks, Will do

-NM

How do I implement paging please? - DataReader Object

Hi, I am a beginner in ASP.NET 2.0

I am creating a simple blog website.

I am using a DataReader object to collect all of the entries in the database (in one full sweep) and then display them by adding rows to a table and populating them.

I would like to enable paging so that only 5 blog entries at a time are displayed. The user should be able to page backwards and forwards 5 at a time.

Please can somebody give me some ideas of how I should do this?

Should I read all of the data into some kind of list so that all of the data is then readily available? An arraylist or something?

I realise that the DataReader object reads in one continuous stream so I couldn't expect it to read backwards and forwards from the database.

I include a portion of my code below which reads the database.

Many thanks.

Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objConnection.Open()
objCommand.CommandText = "SELECT BlogID, Date, Blog FROM BlogSite2_Blogs ORDER BY Date DESC"

Dim objReader As SqlDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dateDateTime As Date
Dim strBlogEntry As String
Dim intBlogID As Integer

Do While objReader.Read()
intBlogID = objReader.GetValue(0)
dateDateTime = objReader.GetDateTime(1)
strBlogEntry = objReader.GetString(2)

CreateBlogRow(intBlogID, dateDateTime, strBlogEntry)
Loop

objReader.Close()

check this link

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

|||

Hi

I would do it in Database with stored procedure.

In the SP described bellow you Just need to pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

CREATE PROCEDURE ReturnPage(@.Select varchar(1000), @.OrderByvarchar(1000), @.StartRowint, @.EndRowint)ASBEGIN declare @.ColListvarchar(2000);declare @.Where varchar(2000);declare @.iint;declare @.i2int;declare @.tmpvarchar(1000);declare @.decvarchar(1000);declare @.fvarchar(100);declare @.dvarchar(100);declare @.Symbolchar(2);declare @.SQLvarchar(5000);declare @.Sortvarchar(1000);set @.Sort = @.OrderBy +', 'set @.dec =''set @.Where =''set @.SQL =''set @.i = charindex(',' , @.Sort)while @.i != 0begin set @.tmp =left(@.Sort,@.i-1)set @.i2 = charindex(' ', @.tmp)set @.f =ltrim(rtrim(left(@.tmp,@.i2-1)))set @.d =ltrim(rtrim(substring(@.tmp,@.i2+1,100)))set @.Sort =rtrim(ltrim(substring(@.Sort,@.i+1,100)))set @.i = charindex(',', @.Sort)set @.symbol =casewhen @.d ='ASC'then'>'else'<'end +casewhen @.i=0then'='else''end set @.dec = @.dec +'declare @.' + @.f +' sql_variant; 'set @.ColList =isnull(replace(replace(@.colList,'>','='),'<','=') +' and ','') + @.f + @.Symbol +' @.' + @.fset @.Where = @.Where +' OR (' + @.ColList +') 'set @.SQL = @.SQL +', @.' + @.f +'= ' + @.fend set @.SQL = @.dec +' ' +'SET ROWCOUNT ' +convert(varchar(10), @.StartRow) +'; ' +'SELECT ' +substring(@.SQL,3,7000) +' from (' + @.Select +') a ORDER BY ' + @.OrderBy +'; ' +'SET ROWCOUNT ' +convert(varchar(10), 1 + @.EndRow - @.StartRow) +'; ' +'select * from (' + @.Select +') a WHERE ' +substring(@.Where,4,7000) +' ORDER BY ' + @.OrderBy +'; SET ROWCOUNT 0;'exec(@.SQL)END

Pls refer toEfficient and DYNAMIC Server-Side Paging with T-SQLfor details.

|||

Thank you very much indeed for this Young Fang. I don't understand it all but I shall have a good read and no doubt, I shall learn a lot of new things :)

How do I get the value of a column in the last row of my table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Hard to get the last row in a relational database. Last means many things: last inserted, last in clustered index, last in another sort, last in a particular index, last in the data page.

Does your table have an Identity column, timestamp, CreatedDate that defaults to getDate()? You need something.

|||

In relational databases the data is by definition unordered, therefore the last data row entered is functionally the same as the first row entered.

That being said, if you have an inserted date/time column you could select from the table where the column equals the max(datetime) value in the table.

How do I get the value of a column in the last row of a table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Define last row?

maybe this will do what you want but it is kind of dangerous because you might pick up the inserted row from another connection

Select Max(CallID) from Calls

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||i do want to pick up the last inserted row from another connection|||

There is no "last" row because SQL Server stores data as it comes in within its own storage schema. There is only a last row if you do a order of the query executed. There could be also a last *physical* row if the table includes a clustered index, which is physically ordered.

But if you want to have the last row in a resultsset, you have to order it backwards and get the TOP 1

e.g. SELECT TOP 1 SomeColumn From SomeTable Order by SomeOtherorthesamecolumn DESC

BTW, this is a public newsgroups, as long as you have MSN Alerts activated you will get a notice everytime a new answer arrives. Private communication should only be done if the thread is extended immensly due to details asking and answering back and forth, but also then the answer and the solution should be posted back here, to help other which might be in the same situation with a similar question.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.

How do I get a procedure OUTPUT-parameter...

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier OUTPUT,
@.strHeading nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400)
AS
SET @.uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@.uidArticleId ,@.strHeading,@.strAbstract,@.strText,@.dtDate,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAuthorEmail,@.strKeywords)

my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@.strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@.strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@.strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();

How do I do if I want to catch the OUTPUT-parameter (@.uidArticleId)?

anyone?Start by defining another parameter in the Parameters collection:

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.uidArticleId", SqlDbType.UniqueIdentifier));
sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Direction = ParameterDirection.Output;

Then, after you call ExecuteNonQuery, grab the value of that parameter:

Dim id as GUID = sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Value;

I didn't test this code so you may need to tweak it, but that's the idea.

Don|||thanks... that would probably work if I just could get the %#&¤ connection to work... :)

(see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=553656 )

How do I format mailing address fields for display in reports

I am very new to development with SQL Server but I have lots of
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>

How do I find the server name?

I need to deploy a multi-user application to several different customers. My app was built on VB.net and uses SQL Server Express. For remote client to connect to the server I understand that I need to use a connection string something like this:
“Server=ServerName;Database=myDB;Trusted_Connection=True;”

My question is, how will I know what the server name is? Can that be different for different customers? When I test this on my PC, I use (Server=.\SQLEXPRESS;), but what do I use for deployment?

Thanks.

hi,

for each "remote" client, you have to store (on each client) the server instance name they are pointing to...

if I have to connect form my machine to your SQLExpress instance, I should connect to YourMachineName\SQLExpress, as you installed a named instance...

you can perhaps have a look at http://www.sqldev.net/misc.htm for 3 different way to enlist SQL Server instances on the lan...

regards

|||

Thank you Andrea for your response. I would like to make the installation an automated process using InstallShield. I will have two install packages - one for Server and the other for Clients. How can I find the ServerName during the client install?

P.S: I was trying to locate a relevant article/info at the site you mentioned, but could not find any. I would appreciate it if you can point me to a specific article.

|||

hi,

unfortunately, usually you can't...

say you install the "clients" before SQL Server ()... how can you tell what the server name is?

you can perhaps, in your InstallShield code, try instantiating a SQL-DMO.SQLServer object to perform a network scan of installed instances, but this will fail if SQL-DMO has not been already locally installed on the client workstation..

but you can find "smart" solutions.. you can define a share where all your client will point to in order to read some "config" file where they can find the SQL Server they will connect to, but you are there again.. how can you define the network share your app should look for at install time?

so just provide, in your client application, a tool\dialog to define the server name.. at very first startup it could be void and the dialog can automatically pop up, so that the user can finally "browse for installed SQL Server instances" the way you like or is just required to type it's name as the local admin already reported him by internal mail

regards

|||

Thank you for your reply. I have written below what I am thinking as to how I may go about it. Please review and let me know if you have suggestions or comments.

On the install CD, there will be only a Server Install package. During the server installation, it creates a "Client Install" folder and copies Client Install files to that folder. Also it creates a text file called "ServerName.txt" in the same folder and writes the Server's Computer name on it.

To install a client, from the client machine user needs to access the folder on the server and run install. The Client install process reads the Server Computer Name and creates a registry entry on the client.

My application, when run, it reads the "Server's Computer Name" from the registry and coins the connection string and connects to the server.

Please let me know if I can make this process more efficient.

Thanks.

|||

hi,

yes, this can be a "smart" solution...

regards

How do I export a report using c#?...

Hi there.
Can anyone tell me how to export a report to PDF using only c#? I have seen lots of examples of how to do it using asp.net but I am only interested in using c#.
Thanks in advance to anyone who can help me out!Hi
Try this code

CrystalReport1 cr = new CrystalReport1();
ExportOptions exportOpts = new ExportOptions();

DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();

// Set the export format.
exportOpts.ExportFormatType = ExportFormatType.WordForWindows;
exportOpts.ExportDestinationType =ExportDestinationType.DiskFile;

// Set the disk file options.
diskOpts.DiskFileName = "C:\\Documents and Settings\\ceeb\\Desktop\\111.doc";
exportOpts.ExportDestinationOptions = diskOpts;
cr.Export(exportOpts);

All the best
:thumb:|||Thanks for the help.

In the end i did it like this:

public void exportReport(String reportName, String exportName)
{
try
{
//Create a ReportDocument and load in the location of the report
ReportDocument report = new ReportDocument();

report.Load(reportName);

//Create a DiskFileDestinationOptions object so you can set where the
//pdf is written to
DiskFileDestinationOptions diskFileDestinationOptions = new DiskFileDestinationOptions();

//append a filename to the export path and set this file as the filename property for
//the DestinationOptions class
diskFileDestinationOptions.DiskFileName = exportName;

//set the required report ExportOptions properties
ExportOptions exportOptions = report.ExportOptions;
exportOptions.DestinationOptions = diskFileDestinationOptions;
exportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

//Once the export options have been set for the report, the report can be exported. The Export command
//does not take any arguments

// Export the report
report.Export();
Console.WriteLine(reportName + " exported successfully as " + exportName);
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
}

}|||I am trying to do the same thing, the only issue is , I either want to pass a data set (only a few records , not the whole table) or pass a parameter to the crystal report (like employee id) so I can filter what the report/pdf file end up displaying.

I posted the code in asp.net - scroll to crystal reports threads.

any suggestions??

bev

How do I embed and retrieve Crystal Reports from my app

Using VB .Net 2005 with Crystal Reports XI Release 2. I have a form that has a CrystalReportViewer control on it. When someone prints I run a sub that create a new instance of the form, creates a new reportdocument, loads the report to the new document, then passes the document to the report viewer on the form. This works great except I would like to either embed my crystal reports directly into my app or better yet embed them into thier own reports dll file. I want to do this so updates are easier and I can use the "Publish" function of VB 05 which doesn't seem to like publishing all the seperate files. The embedding should be the easy part, just set them as embedded resource. But I'm having trouble with the syntax to pull them out. For example you can use this to pull out a embdded icon:
Function GetEmbeddedIcon(ByVal strName As String) As Icon
Return New Icon(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream(strName))
End Function

But I cannot get the syntax right to pull out and return a crystal report. Anyone have any ideas on how to pull the reports back out or a better way to do this. I dont have a lot of reports, about 15 right now, but that could grow.

-AllanWell...I couldn't find the answer anywhere and no one replied here. But I figured it out so I might as well help someone down the road.

Set your crystal reports to "embedded resource" and the copy to "do not copy". Then in your code you can clal them like any other object with a "New (reportname)". For example I have a crystal reports viewer control (apptly named CrystalReportViewer) on a form called ReportViewerForm. I made a small sub to print reports:

Friend Sub PrintReports(ByVal myReportFileName As ReportDocument, Optional ByVal mySQLFormula As String = "")
' Prints out reports pass to it by other procedures and forms.
Dim PrintPreview As New ReportViewerForm
Try
If mySQLFormula <> "" Then myReportFileName.RecordSelectionFormula = mySQLFormula
PrintPreview.CrystalReportViewer.ReportSource = myReportFileName
PrintPreview.ShowDialog()
Catch ex As CrystalDecisions.CrystalReports.Engine.EngineException
MsgBox("A error was generated by the Crystal Reports engine. Error details: " & ex.Message, MsgBoxStyle.Critical, "Engine Error")
Finally
myReportFileName = Nothing
PrintPreview = Nothing
End Try
End Sub

Then when I need to print a report, for example my Log report (called LogEntryReport.rpt) I call it like this:

PrintReports(New LogEntryReport, "{qLogEntryReport.LogIdNumber} = " & CurrentLogId)

passing my report as a new ReportDocument and my sql command (which is optional for those reports that don't need it). Now I can have one routine that will display reports and call it from anywhere passing the report name. Works dandy and the reports are embedded in the app...less clutter.

Hopefully this helps someone and its not a waste of bandwidth.

-Allan.

How do I display a SSRS report in a web page?

I have developed a web site with Visual Web Developer 2005 Express, and SQL Server 2005. After 18 months I have a reasonable understanding of VB.net and SQL Server programming, but I am a complete newbie with Sql Server Reporting Services. SSRS seems to be pretty easy and mostly intuitive for the simple tasks that I've tried, although so far I haven't progressed beyond developing a report and displaying it in the preview tab.

I am now trying to display my first report from within my web site, so that clicking a button on the prior page displays the report, presumably with Response.Redirect("~/Reportpage?Query") where "ReportPage" is the url of a page to display the report, and Query gives the report parameters in the usual style of a request query. The target page could either be a normal .aspx page with the report embedded in the design, perhaps as an IFrame, or it could be a page produced directly by SSRS. I'd prefer the .aspx approach as it would allow me to use my usual master page with its standard on-entry logic, unless the direct-SSRS approach is much easier.

On clicking around in the HELP trying to find how to do this, the HELP seems to imply that I should embed a reportviewer control in my page? Is this right? If yes, where do I get this from? It is not in the VWD 2005 toolbox, but perhaps I have to import it. I am using SSRS within Visual Studio 2005: have I reached the point where I have to move beyond VWD 2005 Express and upgrade to the full Visual Studio?

Hi,

You can either give the report url, but the best way is to use Report viewer control. Its a microsoft inbuilt control. Include the below name space b4 using it

<%@.RegisterAssembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"Namespace="Microsoft.Reporting.WebForms"TagPrefix="rsweb" %>

Hope this is helpful to u

|||

OK, I've added this into my web page, so that it now starts: -
<%@. Page Language="VB" MasterPageFile="~/GDBMaster_P.master" AutoEventWireup="false"
CodeFile="GDB_PR2des.aspx.vb" Inherits="UploadedDocs_GDB2" Title="GDB2" %>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

and continues with the XML describing the page.

Now what? How do I use the report viewer control? Do I need to add it to the toolbox, or do I code something like "<rsweb parameters/> " into the source of my page.

If toolbox, how do I add it? If source, where do I find out what the properties are?"

|||

Hi,

You can follow the steps below to add your report viewer.

In Solution Explorer, right-click Default.aspx, select View Designer.

Open the Toolbox window. From the Data group, drag a ReportViewer control onto the page.

If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the triangle in the upper right corner of the ReportViewer control on the Web page. In the Choose Report box, select the fully qualified file name for Report.rdlc.

When you select a report, instances of data sources used in the report are created automatically. Code is generated to instantiate each DataTable (and its DataSet container) and an ObjectDataSource control corresponding to each data source used in the report. This data source control is configured automatically.

For more information, see:
http://msdn2.microsoft.com/en-us/library/ms252123(VS.80).aspx

Thanks.

|||

Thank you for your reply, but there is still a problem. Your reply describes exactly the process that I had expected, and so I had searched the toolbox for a ReportViewer control before I posted this question (see my first post of this thread). I had expected that "Data" would be the most likely group, but I checked them all anyway. My data group contains only these controls: -
Pointer
Gridview
DataList
DetailsList
Formview
Repeater
SQLDataSource
AccessDataSource
ObjectDataSource
XMLDataSource
SiteMapDataSource

So the key question is, How do I get a reportviewer control into this group?

Regards, Robert

|||

Hi, If u could not able to find reportviewer in your toolbox add the following references

Microsoft.ReportViewer.Common.dll

Microsoft.ReportViewer.WebForms.dll

u can find these dlls in the path

C:\Program Files\Microsoft Visual Studio 8\ReportViewer

Then u right click the tool box and select new items to add in it. Add Report Viewer (Name Space Microsoft.Reporting.Webforms).

U can c the reportviewer control in ur toolbox now

|||

Thank you! That's put the ReportViewer into the toolbox, and now my test page has a ReportViewer control.

Next problem: selecting the report. This is not going smoothly. When I open the smart tag, options are "Choose Report" and "Design a new report".

If I click "Choose Report" the only option is <Server Report> so I select that. The ReportserverURL appears as http://localhost/reportserver, and I am asked to enter the report name.

I enter C:\Documents and Settings\Robertb.ROBERTB\My Documents\Visual Studio 2005\Projects\GDBReports\GDBReports\GDBReport1.rdl (Nai-Dong Jin's post said to select "report1.rdlc", but there are no .rdlc files).

Now I test my web page, by clicking on

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: --

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3042.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <meta name="StackTrace" content=" at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() at Microsoft.ReportingServices.Library.Global.get_SharePointIntegratedFlagFromCatalog() at Microsoft.ReportingServices.WebServer.Global.RunOnlyOnceStartReportServer() at Microsoft.ReportingServices.WebServer.Global.StartApp() at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline} </style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) <a href="http://links.10026.com/?link=http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsReportServerDatabaseUnavailable&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00" target="_blank">Get Online Help</a></li><ul> <li>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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)</li> </ul> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </body> </html> --.|||

Hi,

and I am asked to enter the report name. I enter C:\Documents and Settings\Robertb.ROBERTB\My Documents\Visual Studio 2005\Projects\GDBReports\GDBReports\GDBReport1.rdl (Nai-Dong Jin's post said to select "report1.rdlc", but there are no .rdlc files).

Here, what the system is asking your to enter a rdlc file. This file must be created in your website application by adding new items in your previous steps. Now the following are the whole steps for you, which comes from that link in my previous post:

From the Website menu, select Add New Item.

From the Add New Item dialog, select the Report template, enter a name for the report file, and then click Add. This creates a report definition file (the default is Report.rdlc), launches the Report Designer, and displays the Website Data Sources pane.

Thanks.

|||

How do I add the report template to the "add new item" dialog?

Thanks, Robert.

|||

Hi,

Let's see the whole procedure, our aim is to create a report for a Microsoft Visual Studio 2005 ASP.NET Web site and add a ReportViewer control to a Web page so users can view the report.

1. First, you should create a new Web site, and create a default.aspx page, switch it to the design mode.

2. You may define a data connection and DataTable by adding a DataSet.

3. It's an important step Design the report.

1). From the Website menu, select Add New Item.

2). From the Add New Item dialog, select the Report template, enter a name for the report file, and then click Add. This creates a report definition file (the default is Report.rdlc), launches the Report Designer, and displays the Website Data Sources pane.

3). Open the Toolbox. Click on a textbox and then on the report design surface.

4). Enter a report title in the textbox: # of Employees per Shift per Dept. Expand the textbox if necessary.

5). From the Toolbox, drag a Matrix report item onto the report below the textbox. The dotted white background is your report page. To adjust the matrix location on the page, click the left corner to select it, and drag or use arrow keys to move the item as needed.

6). From the Website Data Sources window, expand the DataTable1 node until you see the columns from your query. Drag Dept onto the Rows textbox in the first column, second row of the matrix.

7). Drag Shift onto the Columns textbox in the second column, first row of the matrix. While the textbox is selected, in the Properties window, set the TextAlign property to Right. Click in the textbox to activate the Report Formatting toolbar, and click the Bold button.

8). Select the matrix by clicking anywhere in the matrix. Right-click and select the matrix by name (the default is matrix1). An outline will appear around the matrix report item. Right-click the outline and select Properties. Check that the title of the dialog that opens is Matrix Properties.

9). Click the Groups tab. In the Columns section, click Edit.

10). In the Sorting and Grouping dialog, click the Sorting tab.

11). Click in the first box under Expression to activate the textbox. From the drop-down list, choose =Fields!Dept.Value. This ensures the report data will be sorted by Department name. Click OK.

12). Click OK to close the Matrix Properties dialog.

13). From the Website Data Sources window, drag the EmployeeID field onto the Data textbox in the second column, second row of the matrix. Right-click this textbox and select Expression.

14). Edit the default Sum aggregate function and change it to Count. Click OK.

15). Click OK to close the Textbox Properties dialog.

16). On the File menu, select Save All.

4. The last step: Add a ReportViewer control to the application

1). In Solution Explorer, right-click Default.aspx, select View Designer.

2). Open the Toolbox window. From the Data group, drag a ReportViewer control onto the page.

3). If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the triangle in the upper right corner of the ReportViewer control on the Web page. In the Choose Report box, select the fully qualified file name for Report.rdlc.

Thanks.

|||

I have not created a new website, as everything should work the same way on a new page within my existing website. Apart from that difference, I have been trying to follow the procedure above. Where I'm stuck: -

From the Add New Item dialog,select the Report template, enter a namefor the report file, and then click Add.

There is no report template within my Add New Item dialog. In my previous post I asked how to add it. I expect that once I have this, it will be easy, but without it I'm stuck.

Thanks, Robert