kill running query: basic

Showing posts with label basic. Show all posts

Friday, March 30, 2012

How to make the report show fields in multiple columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

There is an option in Crystal Reports -- "Format with multiple columns". I couldn't find the similar configuration in SQL reporting. There is a "Columns" setting in Body Properties, but it doesn't work as making the report in multiple columns. Please help!!

Wednesday, March 28, 2012

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database. Sad

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response. Smile

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
Sad I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan
|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! Big Smile It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window. Smile

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response. Smile

I hope my questions are not too many. Thanks a lot in advance for your help!

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

- Done -

Thanks & Best regards,
Henry Li

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

Thanks a lot again!

Stevan|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! Big Smile It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window. Smile

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:
osql -U user -S server\instancename
then
1> create database test
2> GO
1>use test
2>GO
then you're inside of test database and you are ready to generate your table according you're needed

how to make report parameter value change dynamic when run time?

Dear all,

i have 2 parameter [startDate] [endDate].
startDate will basic on the day run report to get the start date from system(follow the store procedure) as below.

CREATE PROCEDURE [dbo].[CFSRep_spGetLastWeekMonDate]
AS
BEGIN
DECLARE @.dtStart DATETIME
-- SET @.dtStart = GetDate() --Set Date to Todays Date
--Set Start Date to 6 days before. If its a Sunday it will get this weeks Monday Date, else any day before will get the past weeks Monday Date
SELECT @.dtStart = dateadd(dd, -6, Getdate())
WHILE (datepart(dw, @.dtStart) != 2) --While its not Day 2 (Monday)
BEGIN
SELECT @.dtStart = dateadd(dd, -1, @.dtStart) --Set the Start Date to a day before
END

SET @.dtStart = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.dtStart))) --Set the time to 00:00:00 AM

SELECT @.dtStart as LastWeekFirstDay
--

END
GO

endDate will get the date after the startDate is process. let say the answer as below.
SELECT DATEADD(dd, 4, @.startDate) as endDate

when i get the startDate = 30th Jan 2006, then the endDate = 3rd Feb 2006

then when i preview report, and i change the parameter of the date on start date to 23rd Jan 2006 but the endDate still the same 3rd Feb 2006.

anyway for me to refesh the endDate to 27th Jan 2006?

Hi Terence,

first of all you can use visual basic functions like Today etc. to get the startdate. In case of that you must not go to the database.

then you can try the following steps:

1. define 2 parameters (startdate + enddate)

2. in the enddate-dataset define a parameter @.startDate and set it to =Parameters!StartDate.Value.

3. make your 'SELECT DATEADD(dd, 4, @.startDate) as endDate' statement.

Now the reportservice should set in the endate-parameter the startdate-param +4 days.

hope this will work for you

sql

How to make part of the report show in 2 columns?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

In Crystal Reports, there is an option called "format with multiple columns". Could not find the similar configuration in SQL reporting. Please help!

|||

HI, MiaF:

You can manualy select the two columns in the data section. Right click and select merge cells.

|||

Rex,

Can you explain a little bit more please? I wanted the results to show in multiple columns, how can I make it happen by merging cells? Also, when you say merging cells, you mean in a table?

I found this article that seems saying that what I asked is impossible:

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

"A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report. " Is there anyway to get around this? Thanks.

Monday, March 19, 2012

How to log any activity

Dear friends,
I'm working on an entity relationship diagram about a mice farm.
Let's say we have a very very basic diagram like this:

MOUSE "is in" CAGE

For example, this gives

Mouse "n1500" is in cage "AAA"
Mouse "n1501" is in cage "BBB"

One operator can take one mouse and put it into another cage, for
example the first line becomes

Mouse n1500 is in cage CCC

Now, I was asked to track any movement... I ask myself: do I need to
add

OPERATOR "moves" MOUSE

so that I have

Operator "John" moves Mouse "n1500" on "Monday 3rd, 2007" at "5 PM"
from "AAA" to "CCC"?

or this kind of logging is provided some way by SQL server?

Thanx a lot for any hint.Not automatically.

You could add a trigger and an audit table to your database, so that every time a record is inserted updated, the pertinent data would be logged to the audit table.

ref Using TRIGGERS to add an audit trail to a table (http://www.xtremevbtalk.com/showthread.php?t=217047)

Note that the example shown doesn't audit the data which was changed, but that is easy to add.

Note: Since you're asking specifically about SQL Server, I'm moving this thread there (to the SQL Server Forum).

Friday, March 30, 2012

How to make the report show fields in multiple columns?

Wednesday, March 28, 2012

How to make tables & stored procedures in MSDE?

How to make tables & stored procedures in MSDE?

how to make report parameter value change dynamic when run time?

How to make part of the report show in 2 columns?

Monday, March 19, 2012

How to log any activity

kill running query

Blog Archive

About Me