clear log: 2012

Friday, March 30, 2012

How to conver 24 Hrs. time into 12 Hrs. time

hi
plz help me i have a varchar time like '18:30:15' and i want to convert it into '06:30:15 PM' format
plz help meLook convert statement with it's formats

how to controll user access in comercial deployment?

Hello,
We have used SSAS and SSRS to develop a BI reporting suite for our data
warehouse and would like to deploy this into our customer base, but at the
same time protect our investment.
How do we control or limit the number of users that can use our cubes and
reports? We would like to control user numbers by a licence arrangement -
either concurrent or named - but fear once a user has our developments
installed they only need to increase their MS licences and away they go...
Any help and advice appreciated.
StephenYou could do the following for named users.
1. Have your own routine that authorizes. Put the user names into a table.
Then somewhere else have the total number allowed encrypted.
2. In your reports (or even better in stored procedures) use the User!UserID
global parameter and verify the user. You could do this in a custom assembly
too. You could put a nag message on each report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen Lonsdale" <StephenLonsdale@.discussions.microsoft.com> wrote in
message news:4AE7D682-F6D1-4E60-8641-03EC900EA69D@.microsoft.com...
> Hello,
> We have used SSAS and SSRS to develop a BI reporting suite for our data
> warehouse and would like to deploy this into our customer base, but at the
> same time protect our investment.
> How do we control or limit the number of users that can use our cubes and
> reports? We would like to control user numbers by a licence arrangement -
> either concurrent or named - but fear once a user has our developments
> installed they only need to increase their MS licences and away they go...
> Any help and advice appreciated.
> Stephen

How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

I assume you are using a "table" reportitem. In report designer you can select the entire column of a table. In the properties window (F4), you should then see a "Visibility" property. You could set the Visibility.Hidden property to e.g. =Parameters!HideColumns.Value (assuming HideColumns is a boolean report parameter).

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

how to control the table's column number

Hi,

When I add the table into the report, the default column number is 3. Let's say I need 10 columns on the report, besides right click on the last column and click on "add column right" to add the other 7 columns, is there any easy way?

Thanks.

Sorry, there is no other way of doing this. You have to add additional columns through the popup menu.

-- Robert

sql

How to control the security in this case?

Dear all,
I have a SQL Server 2000 in my head office, it has several databases.
However, one of the databases is maintained by a colleague of a regional
office. He has the all the authority (read / write) to work on this database
.
But there is a problem, we have developed some DTS packages in head office
and this colleague is going to make his own packages. We would like to have
a
control that this colleague is not able to access our DTS packages (not even
read), he can only opens his own the DTS packages.
Is there any control to achieve this purpose? Please advise. Thanks a lot.
IvanOpen the packages in design mode, use "save as" and set the owner and user
password.
AMB
"Ivan" wrote:

> Dear all,
> I have a SQL Server 2000 in my head office, it has several databases.
> However, one of the databases is maintained by a colleague of a regional
> office. He has the all the authority (read / write) to work on this databa
se.
> But there is a problem, we have developed some DTS packages in head office
> and this colleague is going to make his own packages. We would like to hav
e a
> control that this colleague is not able to access our DTS packages (not ev
en
> read), he can only opens his own the DTS packages.
> Is there any control to achieve this purpose? Please advise. Thanks a lot.
> Ivan
>|||Hi, Alejandro
I'm afrais that does not solve the problem. I still keep seeing the DTS's
created by others.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...[vbcol=seagreen]
> Open the packages in design mode, use "save as" and set the owner and user
> password.
>
> AMB
> "Ivan" wrote:
>
database.[vbcol=seagreen]
office[vbcol=seagreen]
have a[vbcol=seagreen]
even[vbcol=seagreen]
lot.[vbcol=seagreen]|||Hi Uri,
It is allowed that the colleague in the regional office to create his own
DTS. I just don't want to view the DTS created in my head office.
Besides setting password in each DTS package, is there any other way?
Thanks.
Ivan
"Uri Dimant" wrote:

> Hi, Alejandro
> I'm afrais that does not solve the problem. I still keep seeing the DTS's
> created by others.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B1A8ECBE-047C-4095-889A-A7DA9C4BFD04@.microsoft.com...
> database.
> office
> have a
> even
> lot.
>
>

How to control the report export to a pdf in Landscape mode?

hi,
I have a report and becuase of too many fields, I want to export pdf in
landscape mode. Is there any way to control this?

Thanks

oh, got it.
To do this, need to set the width of the page to 11inchs. And the width of body mostly should be 10 inchs or less. It will export the pdf document as landscape mode automatically.|||

While I am happy to hear that you set portrait and landscape in the width and height properties, it does not work.

I changed my L and R margins to about .2 and put in the width and height as 11 X 8.5.

I set my Body to at least 1/2 inch smaller that my margins to the paper size, and when I preview it, it's landscape. When I view it in PDF, it's portrait and I either have to set the printer or the pdf everytime. It also prints portrait directly to my printer, unless I change the printer setup.

It doesn't work at all with my installed PDF Writer, I have to Export to PDF thru the RS print preview.

Halp!

How to control the report export to a pdf in Landscape mode?

hi,
I have a report and becuase of too many fields, I want to export pdf in
landscape mode. Is there any way to control this?

Thanks

oh, got it.
To do this, need to set the width of the page to 11inchs. And the width of body mostly should be 10 inchs or less. It will export the pdf document as landscape mode automatically.|||

While I am happy to hear that you set portrait and landscape in the width and height properties, it does not work.

I changed my L and R margins to about .2 and put in the width and height as 11 X 8.5.

It doesn't work at all with my installed PDF Writer, I have to Export to PDF thru the RS print preview.

Halp!

|||same problem.plz plz help !!!!!!!!!!!!!!!!!!!!|||to solve this you should set PageHeight and PageWidth for element Report in *.rdlc file like this:

<PageHeight>8.5in</PageHeight>
<PageWidth>11in</PageWidth>
</Report>

How to control the report export to a pdf in Landscape mode?

hi,
I have a report and becuase of too many fields, I want to export pdf in
landscape mode. Is there any way to control this?I agree. They should also have a SendTo right from the preview and
hopefully add XPS format before next product.
--
William Stacey [MVP]
"Nick" <nick_1394@.yahoo.com.cn> wrote in message
news:1137696432.086601.318380@.g43g2000cwa.googlegroups.com...
| hi,
| I have a report and becuase of too many fields, I want to export pdf in
| landscape mode. Is there any way to control this?
||||Have you tried changing the Report's Page Size? Change the Height to 21cm (8
Inches?) and WIdth to 29,7 (11?) and you get landscape. It will render as
"landscape" in your Report Manager too, but usually the users' screens are
wide enough so it's not really a problem.
Kaisa M. Lindahl
"Nick" <nick_1394@.yahoo.com.cn> wrote in message
news:1137696432.086601.318380@.g43g2000cwa.googlegroups.com...
> hi,
> I have a report and becuase of too many fields, I want to export pdf in
> landscape mode. Is there any way to control this?
>|||That works. Did not see those. Thanks.
--
William Stacey [MVP]
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:uqY6ylTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
| Have you tried changing the Report's Page Size? Change the Height to 21cm
(8
| Inches?) and WIdth to 29,7 (11?) and you get landscape. It will render as
| "landscape" in your Report Manager too, but usually the users' screens are
| wide enough so it's not really a problem.
|
| Kaisa M. Lindahl
|
| "Nick" <nick_1394@.yahoo.com.cn> wrote in message
| news:1137696432.086601.318380@.g43g2000cwa.googlegroups.com...
| > hi,
| > I have a report and becuase of too many fields, I want to export pdf in
| > landscape mode. Is there any way to control this?
| >
|
|

how to control the number of row?

i want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.

For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?

Thanks for any help...

You can try something like:

SELECT top 500
...
from TableName

another more archaic form is to execute

SET ROWCOUNT 500

However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:

SET ROWCOUNT 0

The TOP option is in general the better option. Look the two up in books online.

sql

How to control the loading of SqlDataSource?

I have an SqlDatSource that I have fully setup at design time, but I don't want it to open and load as soon as the page loads, instead I want to open it based on a condition.

How do you do that with a DataSource?

I know that I can simply remove the Select query, and then set it at run time, but I'm looking for something better, that allows me to have the Select query set at design time, in part because I have a lot of parameters.

Usually you have a control such as GridView or DetailsView that is connected to the SqlDataSource through its DataSourceId attribute. This will cause it to execute the query. If you leave the attribute empty the query will not be executed. From code you can set the DataSource and then call DataBind(). That will cause the SqlDataSource to execute:

GridView1.DataSource = SqlDataSource1;GridView1.DataBind();// Now the query is executed

|||

Within the SqlSataSource.Selecting event, you will have access to the Cancel property which you can set based on your conditions. If Cancel is set to True, then the Select event will not occur.

|||

while michielvoo's way is probably better, another way you can get the date to not display is to just set the GridView's Visible="false" initially, if you want the datasourceID property of the sqldatasource to be defined in the contol creation. Then, in your button_click event or whatever you do to get the data, you do:

VB:

GridView1.DataBind()
GridView1.Visible = True

C#:

GridView1.DataBind();
GridView1.Visible = True;

how to control the distance between the two matrix? or (matirx and table )

hi everyone:

the report show two tables two matrixs

how can i control the distance between them

I want to set the same distance between the table and matrix

or (table and table )

Younger,

You could possible place your tables and Matrix each inside of a rectangle and then maintain the distance between your rectangle should get you to your desired results.

Ham

|||

Ham

Thank you very much

Your idea is good, that is worked well.

Younger

How to control SqlDataSource if we know only DataSourceID ?

Anyone can help me ? Smile

Can you explain your issue? The DataSourceID points to the ID of your SqlDataSource. The SqlDataSource defines a SelectCommand which builds the collection of items which are bound to your databound control.

You can even have more than 1 databound control on the page with the same DataSourceID, like a GridView and a DetailsView. As you select a row in the GridView you can use the DetailsView to edit the row. There are many examples online for databound controls.

Try this...

http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

|||

In context I have an DataSourceID, I want to change SelectCommand of SqlDataSource(before I don't know how to do this-this is my problem)

But now I know -the decision is using method FindControl

anyway Thank you very much !

Now I have a new question !

The question is how to know column's name of DataSource(not DataSet) ?

Do you know this ?

|||

To update the SelectCommand you can do it manually in the code view as opposed to design view. You can also use the wizard to update the SqlDatasource.

For knowing the column's name, that all depends on the Datasource. If the Datasource is not a DataSet and is instead a collection of objects the column name could be one of the properties on the objects.

Or are you thinking of a DataTable? When a SQL result is bound to a GridView using a SqlDatasource it does return a DataSet and it assumes it will use the first DataTable on the DataSet.

ds.Tables[0]

Is this what you needed to know?

|||

offwhite:
To update the SelectCommand you can do it manually in the code view as opposed to design view. You can also use the wizard to update the SqlDatasource.
For knowing the column's name, that all depends on the Datasource. If the Datasource is not a DataSet and is instead a collection of objects the column name could be one of the properties on the objects.
Or are you thinking of a DataTable? When a SQL result is bound to a GridView using a SqlDatasource it does return a DataSet and it assumes it will use the first DataTable on the DataSet.
ds.Tables[0]
Is this what you needed to know?

reality I don't understand you !

All I have :that is SqlDataSource

All I need :that is names of columns

But I don't know how...

How to control SQL Server 2005 Express installed as a "prerequisite"

Suppose I check "SQL Server 2005 Express Edition" as one of the prerequisites for a custom app in a Windows Setup project.

From reading whatever docs I could get my hands on, it's still not clear to me how to control the installation of SQL Server Express with respect to things like instance name, service account, etc, etc.

In other words, this is stuff that's normally the domain of template.ini. However, there's no clear indication of where I should put template.ini so that the install of "prerequisite" SQL Server Express will see it and configure accordingly...

Thanks in advance to anyone who can help solve this...

Josh

Josh, template.ini is not used for configuration settings (as the .iss files were in SQL 2000). This file merely is used as documentation for creating silent installs by describing which flags are used in various situations.

As for another application installing SQL 2005 Express, I would assume that the application is controlling the install (instance name, accounts, etc) so that it can connect after install. If they are not exposing these install features during their install, they probably are using some default values for all installs.

Thanks,
Samuel Lester (MSFT)

|||

Samuel:

Yep, it looks like the parameters (at least, all the ones I tried) are recognized when supplied on the command line.

One parameter I haven't been able to find, though, is one that tells SQL Server Express Edition to use port 1433 instead of dynamic ports. I know about DISABLENETWORKPROTOCOLS (and it, too, works as expected), but it only activates TCP -- it doesn't affect the port-assignment scheme.

Thanks,
Josh

How to control SQL Server 2000 login and logout Method / Event.

Thanks !
Is there a question somewhere in that post?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:
|||Hi,
Did you mean the Enable auditing in SQL Server 2000 Login/ Logout, Then
there is no control to control those.
Could you please explain us in details about your actual requirement?
Thanks
Hari
SQL Server MVP
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>
|||Sorry!
Thanks !
"SOHO" <hkwin2000@.hotmail.com> glsD:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx .gbl...
>
> --
> Thanks !
>
>
sql

How to control SQL Server 2000 login and logout Method / Event.

Thanks !
Is there a question somewhere in that post?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:
|||Please don't cross post
Greg Jackson
PDX, Oregon
|||Hi,
Did you mean the Enable auditing in SQL Server 2000 Login/ Logout, Then
there is no control to control those.
Could you please explain us in details about your actual requirement?
Thanks
Hari
SQL Server MVP
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>
|||Sorry!
Thanks !
"SOHO" <hkwin2000@.hotmail.com> glsD:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx .gbl...
>
> --
> Thanks !
>
>

How to control SQL Server 2000 login and logout Method / Event.

--
Thanks !This is a multi-part message in MIME format.
--080702060202030100030105
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is there a question somewhere in that post?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:
--080702060202030100030105
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is there a question somewhere in that post?</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
SOHO wrote:
</body>
</html>
--080702060202030100030105--|||Hi,
Did you mean the Enable auditing in SQL Server 2000 Login/ Logout, Then
there is no control to control those.
Could you please explain us in details about your actual requirement?
Thanks
Hari
SQL Server MVP
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>|||Sorry!
--
Thanks !
"SOHO" <hkwin2000@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>

How to control SQL Server 2000 login and logout Method / Event.

How to control SQL Server 2000 login and logout Method / Event.Is there a question somewhere in that post?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:|||Hi,
Did you mean the Enable auditing in SQL Server 2000 Login/ Logout, Then
there is no control to control those.
Could you please explain us in details about your actual requirement?
Thanks
Hari
SQL Server MVP
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>|||Sorry!
Thanks !
"SOHO" <hkwin2000@.hotmail.com> glsD:%23$0ZCplcFHA.3032@.TK2MSFTNGP10.phx.gbl...[vbc
ol=seagreen]
>
> --
> Thanks !
>
>[/vbcol]

How to control SQL Server 2000 login and logout Method / Event.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

How to Control Sheet Name of Exported Excel Report?

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

How to Control Sheet Name of Exported Excel Report?

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

How to control security to parameter level in Report server?

I am developing a web application which need control security to parameter level. For example, salesman just only view report which belong to himself, but manager need to view all sales reports.
what should I do? thanks

newmanYou can achieve this in several ways:

Solution 1) use the User!UserID global in your report definition. This allows you to scope query results, parameter values, etc based on who the user actually is. This provides a single report that lists all the data for

Solution 2) use linked reports. If, for example, you have 2 groups, one which needs to see North region and the other South region, you can create a single report that takes the region parameter. Then create 2 linked reports that submit only the parameter value that each group is supposed to see. You can then ACL each linked report to be visible only to the appropriate group.

Solution 3) use database security with integrated security. You can just always access the underlying data source as the current users. If you underlying data source supports row level security, then the user will automatically get only those values to which they have permission.

-Lukaszsql

How to Control Print Button in SP2 ?

I want control print button for user to use print button one time per one
user account
How i do ?
Thank youHi,
You can control the print button with the report viewer style sheet
"htmlviewer.css".
Change the style
.ToolbarPrint
{
display: inline;
}
to
.ToolbarPrint
{
display: none;
}
Make sure u take a backup of the existing css and give a new name like
"noprint.css".
So have a logic in your system where you have a printer counter in the user
session, and if the counter is > 1, pass the following parameter to the
report URL
"rc:StyleSheet=noprint"
eg:
<report url >&rc:StyleSheet=noprint
This will hide the print button from the viewer.
Hope this will give a workaround to solve the problem.
rgds,
Kolitha
dkolitha@.gmail.com
www.dkolitha.com
"Zong" wrote:
> I want control print button for user to use print button one time per one
> user account
> How i do ?
>
> Thank you
>
>

How to control number precision?

Hi all,
There is a table like this:
item quantity
-
A 1
B 3
C 7
D 6
Now I want to get a table like this:
item Percent
-
A 1/17
B 3/17
C 7/17
D 6/17
For script:
select item,
quantity/sum(quantity)
from table
but the number has a very long precision, like 0.0588235456456, I only want to keep two number after the point as like 0.06,what should I do?
Thanks!

Coeus:

Maybe something like:

select convert (numeric (9,2), 0.0588235456456)

|||

Thanks for help. :)

I also can use round(variable, 2).

How to control MS SQL Server DataBases permissions through visual basic program

Hello,

Could someone help me to view, add, delete, and modify users of SQL Server DataBase and permissions of them on this database through Visual Basic Programming

Because I want to do VB application through which I will view, add,delete, and modify users of SQL Server DataBase and their permissions on this database.

Thank you very very muchYou can use the system stored procedures.|||Originally posted by rnealejr
You can use the system stored procedures.

Thank you very much Mr. rnealejr for your reply.

Would you like to send me a sample code in visual basic to view ,for example, users of SQL Server DataBase and their permissions??

I will be thankful|||Which version of sql server are you using 7 or 2000 ?|||Originally posted by rnealejr
Which version of sql server are you using 7 or 2000 ?
Hello, Mr. rnealejr
I use MS SQL Server 2000.
Also, I have a problem with SQL Server.
I have about 50 tables and views in an Oracle DataBase and I have tried to translate them to SQL Server DataBases.
They were translated succefully but there was two problems :
1- Key constraints were not translated? (i.e. the destination SQL Server database became without primary key and foreign key constraints and aother constraints)
2- Also, views are translated into tables not into view ??

Could you slove these problems?

After I get more information I will be able to serve your good forum with good posts becuase until now I am student

Thank you very much|||How did you translate from oracle to sql server ?|||You can use sp_helpuser, sp_helplogins - use the ado command object.|||Originally posted by rnealejr
How did you translate from oracle to sql server ?

Yes
I have used DTS Wizard to do this translation but tables were translated without constraints and also views were translated into tables instead of corresponding views although all tables, from whcih views get data, are available in the translated database.

Thank you very much Mr.

How to control layout of report parameters

I am trying to have lay 3 report parameters in one single row and have
teh next line have just one. In other words, I want to control the
layout of these parameters. How can I do that? I am using Sql Server
2000 Reporting Services.
Also, I wanted to create a checkbox type report parameter.
Thanks.You have no control over this (with any version). You can create your own
front end but if you are using Report Manager then you are pretty much
stuck. There are a few things you can do with style sheets but nothing like
this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kulsum" <arif.kulsum@.gmail.com> wrote in message
news:1157051938.689515.234850@.i42g2000cwa.googlegroups.com...
>I am trying to have lay 3 report parameters in one single row and have
> teh next line have just one. In other words, I want to control the
> layout of these parameters. How can I do that? I am using Sql Server
> 2000 Reporting Services.
>
> Also, I wanted to create a checkbox type report parameter.
>
> Thanks.
>|||OK. Thanks Bruce.
Bruce L-C [MVP] wrote:
> You have no control over this (with any version). You can create your own
> front end but if you are using Report Manager then you are pretty much
> stuck. There are a few things you can do with style sheets but nothing like
> this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "kulsum" <arif.kulsum@.gmail.com> wrote in message
> news:1157051938.689515.234850@.i42g2000cwa.googlegroups.com...
> >I am trying to have lay 3 report parameters in one single row and have
> > teh next line have just one. In other words, I want to control the
> > layout of these parameters. How can I do that? I am using Sql Server
> > 2000 Reporting Services.
> >
> >
> > Also, I wanted to create a checkbox type report parameter.
> >
> >
> > Thanks.
> >

how to control it's right of a sysadmin roles menbers

i have a sql 2005 server, I caeat i login accout & give it db_owner roles
for a application. but the application still can't work well unless i
give the account sysadmin server roles.
so ,i give the accout sysadmin roles. but i don't the user to view other
database.
I have refuse some right like " view any database " for the account on
the server property-- right.
but i don't know why the accout staill have all the sysadmin right , to
view any database.
. how can i control it's right of a sysadmin roles menbers
i donn'n know how to do?
Ryan.You can't restrict permissions of sysadmin role members. The purpose of the
sysadmin role is to provide administrators with full access to SQL Server.
It is not intended for routine application use.

> but the application still can't work well unless i
> give the account sysadmin server roles.
Can you elaborate on why the application won't work as a non-sysadmin role
member? I would think you could create your own role(s), grant the role the
permissions needed to execute your application and then add the application
users to the role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:48867756-671D-4CCB-8E26-C7581999EEA5@.microsoft.com...
>i have a sql 2005 server, I caeat i login accout & give it db_owner roles
> for a application. but the application still can't work well unless i
> give the account sysadmin server roles.
> so ,i give the accout sysadmin roles. but i don't the user to view other
> database.
> I have refuse some right like " view any database " for the account on
> the server property-- right.
> but i don't know why the accout staill have all the sysadmin right , to
> view any database.
> . how can i control it's right of a sysadmin roles menbers
> i donn'n know how to do?
> --
> Ryan.sql

How to control format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?

How to control DB size growth?

Hello,
My database is 1gig big and it has so far one table with 1 melion records.
Once I tried to create the primary key in this table but it was rejected by
MS SQL since it was not unique. That's fine with me but I noticed that my
database doubled in size to just over 2 gigs. Then I created clastered index
and the size of the file was around 3 gigs. When I deleted newly created
index the size of the file stayed the same almost reaching the limites that
I set up before. I would never expect that the my database would grow so
fast since the same data in the Access database is just 450 MB.
Why the data grows when the key or index fails to be created?
How to control the size of the Database?
Any help is greatly appreciated,
Les
> Why the data grows when the key or index fails to be created?
It is growing because sql is creating index to the point of failure. Of
course creation takes some disk space. But after failure it cleans the data
in failure but by default it does not shrink the database file. So the file
stays as big as it was on failure.

> How to control the size of the Database?
the best way to control it is to set a maximm size, but the you should set
an alert to monitor the size and notify you database size is near full.
Best way to see how much space your data is taking is to set a TaskPad view
in Enterprise Manager.
(Select Database, go to menu View -> TaskPad).
Danijel
"Tom" <tom@.killspam.com> wrote in message
news:UjSFd.48178$TN6.1797082@.news20.bellglobal.com ...
> Hello,
> My database is 1gig big and it has so far one table with 1 melion records.
> Once I tried to create the primary key in this table but it was rejected
> by MS SQL since it was not unique. That's fine with me but I noticed that
> my database doubled in size to just over 2 gigs. Then I created clastered
> index and the size of the file was around 3 gigs. When I deleted newly
> created index the size of the file stayed the same almost reaching the
> limites that I set up before. I would never expect that the my database
> would grow so fast since the same data in the Access database is just 450
> MB.
> Why the data grows when the key or index fails to be created?
> How to control the size of the Database?
> Any help is greatly appreciated,
> Les
>

how to control data types

i have sp that insert fileName and fileType

how can i forbidden in the sp other types then word doc and images?

Code Snippet

if not exists(select Number

from dbo.Freezrelease

where FileName1 like @.FileName1 and

FileType like @.FileType and

FileSize = @.FileSize)

/*{*/ begin

if @.FileSize<500

/*{*/ begin

insert into dbo.Freezrelease(FileName1,FileType,FileSize)

values(@.FileName1,@.FileType,@.FileSize)

select @.@.identity

/*}*/ end

else

select -1

/*}*/end

else

select 0

So you are just inserting the file names, not the bits? If so, just add a line:

if @.fileType not in ('doc','jpg','...etc')

begin

raiserror ('Filetype must be doc, jpg, or etc')

return -100

end

And the procedure will stop there after raising an error

|||

You could add a CHECK constraint to the table that limits the values in the FileName field to only values ending with '.doc', '.docx'*, or '.jpg'. (And of course, any other image file extensions required...)

Using this method, it would not be necessary to 'control' every stored procedure or application query. Programmers and users will not be able to thwart your determination that a 'proper' FileName be supplied. The CHECK constraint would 'police' the inserts/updates for you.

* .docx for Word 2007

Code Snippet

CREATE TABLE #MyTable
( RowID int IDENTITY,
FileName varchar(50)
CHECK ( FileName LIKE '%.doc%' OR FileName LIKE '%.jpg' )
)
GO

-- These succeed
INSERT INTO #MyTable VALUES ( 'MyFile.doc' )
INSERT INTO #MyTable VALUES ( 'MyPic.jpg' )
INSERT INTO #MyTable VALUES ( 'MyFile.docx' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile.txt' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile' )

SELECT *
FROM #MyTable

RowID FileName
-- --
1 MyFile.doc
2 MyPic.jpg
3 MyFile.docx

DROP TABLE #MyTable

|||

What Arnie says is true too, though I would prefer having a fileType column to hold the extension also, for all of the basic normalization reasons:

1. You can add information about types of files easily and join to it without the substring

2. Listing files by type will not require a substring

3. Searching for files by type can be indexed

Really it is all about the need to avoid dealing with parts of a column value.

How to control CLR memory threshhold?

Please help!

I'm running a lengthy (about 5 minutes) transformation process on SQL Server 2005, which consumes about 300-400 MB. The code is a C# SP. The same code can be run as a part of WinForm application and works fine. If executed as a stored procedure e.g. from Management Studio it breaks after consuming certain amount of memory above roughly 200MB with

.NET Framework execution was aborted by escalation policy because of out of memory.

The SP works fine on smaller amount of data. The assembly has UNSAFE permission and DB's TRUSTWORTHY attribute is OFF/false. Timeout is set to 10 minutes.

If it's memory limit, how can it be increased? Consumption is well below server limitations. The server is Windows 2003 with 3.5 GB of RAM on Intel Xeon.

Thanks, Andrei Kuzmenkov.

Hi Andrei,

I wrote a blog entry touching on this topic here: http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

You can increase the amount of memory available to SQLCLR via the -g flag on Sql Server start-up, as described in BOL: http://msdn2.microsoft.com/en-us/library/ms190737.aspx. Be aware of the trade-offs however, as increasing memory_to_reserver decreases the amount of memory available to the main SQL Buffer pool.

Steven

|||

Thank you Steven for the prompt response.

That'd helped immediately.

Andrei Kouzmenkov.

How to control caption for the bit attribute?

Hi Gurus,

Have anybody idea how to control the caption of bit attribute?

The default is True/False.

1. How to force the AS to show Y/N or Yes/Now еtс.
2. How to force the AS to show localized bool string (Wahr/Falsch, Истина/Ложь).

I can create table like

ID Caption

0 Yes
1 No

Or calculated fields in UDM

but all it awkward.

I have about 50 bit attributes in all my dimensions. It would be too expensive to devote so much attension to every bit attributes.

If you have a table as described, with a bit column (ID in your example) and a character column with the value you want displayed (Caption in your example), then you can create the attribute in your dimension so that it uses ID as the KeyColumn for the attribute and Caption as the NameColumn for the attribute. When you do this, AS manages the attribute internally using the values of the ID column, but it will display to the end user the values of the Caption column.

For localized versions, extend your table design to include additional localized captions. Then, in the dimension design, go to the Translations tab and set up a translation for the dimension. This would include defining the Windows locale that the translation applies to and identifying the column that contains the localized captions for the attribute in question.

HTH,

Dave Fackler

|||

but all it awkward.

I have about 50 bit attributes in all my dimensions. It would be too expensive to devote so much attension to every bit attributes.

For every bit attribute I have to JOIN a "caption" table, then I have to set separate Name column. It's to expansive.

It would be made no pleasure :-(

|||

It would be too expensive to devote so much attension to every bit attributes

Can you devote this attention just to one such attribue, and make all other dimensions role playing with this one ?

|||

Hi, Mosha

If it was a dimension then there was no problem. But I speak about attributes of one dimension. I seems to be inpossible to have "role plaing" attribute. I have made one DSV virtual table and have made correspondents virtual foreign keys to it in order to "translate" more then one bit attribute of my dimension, but AS doesn't make it right :-(

sql

how to control a column limited to display?

Hi,

Can I control a column to displayed to a specifal user or specifal role?

If not the specifal user or specifal role, the column will can not be displayed.

Thank you.

Jeffers

You can set a conditional column visibility using User!UserID. For more involved scenarios, you may need to whip out some code to find the role/group the user belongs to given the user logon name.

How to continue package execution during error?

We have a package that loads the data from several excel files into database in a forloop.

Everything works files until the package hits the bad file.

My goal is to continue the loop to process the rest of the files by skipping the bad file and error. In each task OnError I am creating custom error message to send an error/ sucess summary email out at end of the process.

How can force the for loop to continue when there is an error?

Is there any way to reset the errors?

Thanks

The behavior is mostly controlled by three properties: MaxErrorCount, FailPackageOnFailure and FailParentOnFailure. You can increas error count and set "fail" properties to false to make package continue inspite of an error.

how to continue on error

How can I cause my insert statement to skip over (without failing) rows
where there's a primary key constraint violation?

I've got a case where daily I insert >500k rows of daily data, where
the data is date and time stamped. So, for example, I have an insert
statement with constraint: WHERE date >= '5/20/05' AND date <
'5/21/05'. That takes care of one day's data (5/20).

However, the next day's data (5/21) will still have some time stamps
from the previous day. Therefore the statement needs to be something
like WHERE date >= '5/20/05' AND date <= '5/21/05'. The 5/20 data is
already loaded but I need to take the 5/21 data which just happens to
contain just a few rows of data marked 5/20 and insert it without
generating a primary key error from all the other 5/20 rows that are
already inserted.

-DaveINSERT INTO TargetTable (key_col, ...)
SELECT S.key_col, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL
AND S.date >= '20050520' AND date < '20050522'

--
David Portas
SQL Server MVP
--|||The easy way is to limit the insert query to 11:59 59 of the previous
day. Then you tell your users, "this report contains all the data from
yesterday" In fact, if you're doing a report of some kind, this is
really the best way to do it because otherwise, you have incomplete
(and therefore bad) data for the current day.

Another way is to delete yesterday's data right before you run the
insert.|||Should the join run very slowly? If I do the insert with a standard
insert query it takes about 7 minutes. With the join query it runs and
doesn't seem to be able to finish. If I run the query on dates with no
data it finishes ok. Is my join incorrect since I can't use S.keyrow?

insert into final(keyRow, cell, recordDate, high_set )

SELECT CONVERT(CHAR(16),dateadd(hh,datepart(hh, .access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id) AS keyRow,
(CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)) AS cell,
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)as recordDate, SUM(S.high_set_int) AS high_set

from SourceTable AS S
LEFT JOIN TargetTable AS T
ON keyRow = T.keyRow

WHERE T.keyRow IS NULL
AND S.record_date >= '5/06/2005' AND S.record_date < '5/07/2005' AND
convert (char(8), S.access_time,108) != '00:00:00'

GROUP BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+
CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)

ORDER BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+
CONVERT(CHAR(1), S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id),
S.cell|||
christopher.secord@.gmail.com wrote:
> The easy way is to limit the insert query to 11:59 59 of the previous
> day. Then you tell your users, "this report contains all the data from
> yesterday" In fact, if you're doing a report of some kind, this is
> really the best way to do it because otherwise, you have incomplete
> (and therefore bad) data for the current day.

Yes, I agree but the way the data is generated results in "today's"
data flat file containing some of yesterday's data. So although 99% of
yesterday's data is already in the db, the last little bit needs be
added for completeness. It's not that the nearly all users can't use
the 99% data for their purposes but still the missing 1% needs to be
added for later complete, accurate reports.

> Another way is to delete yesterday's data right before you run the
> insert.

This puts the problem back 1 day because I would still need to add
yesterday's data which is in its own flat file which contains data from
the day before yesterday.

-David|||David Portas wrote:
> INSERT INTO TargetTable (key_col, ...)
> SELECT S.key_col, ...
> FROM SourceTable AS S
> LEFT JOIN TargetTable AS T
> ON S.key_col = T.key_col
> WHERE T.key_col IS NULL
> AND S.date >= '20050520' AND date < '20050522'

I'm thinking maybe the best thing to do is add another column to my
table that uniquely identifies the data from a particular day. Some of
the data from the particular flat file will be from the day before but
it won't matter because I'll use the new field in the where criteria
instead of the actual record dates.

Also thought about using NOT EXISTS somehow.

-Dave|||Make sure you have indexes on the columns that are being joined.|||(wireless200@.yahoo.com) writes:
> Should the join run very slowly? If I do the insert with a standard
> insert query it takes about 7 minutes. With the join query it runs and
> doesn't seem to be able to finish. If I run the query on dates with no
> data it finishes ok. Is my join incorrect since I can't use S.keyrow?

I don't understand that last question. What do you mean, you cannot
use S.keyrow?

A clustered index on S.record_date would be a good thing.

I would also replace the LEFT JOIN with NOT EXISTS. Not because this
is faster, but because expresses what you mean.

Does the target table have an IDENTITY column? Else there is no reason at
all to have the ORDER BY clause. Removing that could also gain some
performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:
Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX
@.OPTION1 INT
AS
DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)
SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END
EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:
Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:
You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

How to construct table with unique PK

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks

What you describe isn't really applicable to a 'normal' primary key.
In the 'normal' case, a PK doesn't care about the values of other PK's in other tables.

There are cases where one would want to distribute PK values like you describe, though I don't know if that's your reason.
(eg some replication / distribution strategies)

However, if you want to read some about keys, here's a link that mayshed some light.
http://www.datamodel.org/DataModelKeys.html

=;o)

/Kenneth

|||

have a composite primary key

key1 and key2

have key1 to have a default value of "A" for the first table, "b" for the second and "c" for the third

key2 would be an identity

your pk would be a combination of key1 and key2

and that would definitely be unique across the enterprise

|||

hi KeWin,

its implemented in a CRM thats why we're try to do the same. Any ideas how?

joeydj,

How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

|||

Well, personally I'm not too keen on keys like 'A3457' - where 'A' has some significant meaning.
This is what's called an 'intelligent key' or 'concatenated key' (because the key itself is made up of several parts that has independent meaning), and is ususally something you want to avoid.

What a certain key should look like - well, it depends.
It's a desginer's choice, and it's part of the datamodel.

There may be a number of reasons behind any PK's design, the most important thing about it (imo) is that the designer really understands what a primary key is, and why he/she want it implemented in a certain way. Be it natural or a surrogate, it should be deliberate with some thought behind it.

This leads to the next question, how to decide?
There's no simple answer to that question, unfortunately, it's one of those 'it depends' things....

Your best tool is understanding, both of the database in question, the business it should support, and the concept of primary keys etc.
(I don't have any good links handy, but google usually manages to come up with something =;o)

/Kenneth

|||

portect wrote:

hi KeWin,
its implemented in a CRM thats why we're try to do the same. Any ideas how?
joeydj,
How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

try this. this is called a composite pk solution. the advantage is that you dont need to worry whats

the next number in sequence. the problem is joining with other table.

CREATE TABLE [mytable] (
[key1] [char] (1) NOT NULL CONSTRAINT [DF_mytable_key1] DEFAULT ('A'),
[key2] [int] IDENTITY (1, 1) NOT NULL ,
[data1] [char] (10) NULL ,
[data2] [char] (10) NULL ,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[key1],
[key2]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into mytable(data1,data2)values('mydata1a','mydata2a')
insert into mytable(data1,data2)values('mydata1b','mydata2b')
select * from mytable

|||

an alternative solution here.

remember that an identity has a seed and increment parameter.

seed Is the value that is used for the very first row loaded into the table.

this is called an identity seed and contraint solution.

first table shall be between 1 and 10M

pk type bigint, identity on, seed=1
data type bigint
constraint: pk between 1 and 10,000,000

second table shall be between 10M+1 and 20M

pk type bigint, identity on, seed=10,000,0001
data type bigint
constraint: pk between 10,000,001 and 20,000,000

third table shall be between 20M+1 and

pk type bigint, identity on, seed=20,000,0001
data type bigint
constraint: pk between 20,000,001 and 30,000,000

if your not happy with 10M difference make it 100M.
this solution seems better to me.

|||Thanks all. I think surrogate keys will be appropriate.|||

follow up question:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=857019&siteid=1

thanks.

sql

How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:


SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
 Field2 LIKE '%NEW%' OR
 Field3 LIKE '%NEW%' OR
 Field4 LIKE '%NEW%'

Regards,

how to construct dynamic sql in stored procedure

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this

CREATE PROCEDURE P_SAMPLE

(

@.P_ONE NVARCHAR(240)

)

BEGIN

DECLARE

@.TMP_CNT INT

BEGIN

EXEC ('SELECT @.TMP_CNT = 1')

-- PRINT @.TMP_CNT

END

Regards

Mani

Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.

Code Snippet

DECLARE @.TMP_CNT INT

DECLARE @.SQL as NVarchar(4000)

DECLARE @.Params as NVarchar(4000)

Set @.SQL = N'SELECT @.TMP_CNT = 1'

Set @.Params = N'@.TMP_CNT as Int OUTPUT'

Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT

Select @.TMP_CNT

|||

here is one of the best resource in Dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Madhu

Wednesday, March 28, 2012

How to construct a like compariso when there are special characts

How do I construct a like compariso query when there are special characts
such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
--
bicbic,
use a scape character.
Example:
select *
from
(
select 'abc[Y]def' as c1
union all
select 'abc[Y]def' as c1
) as t1
where c1 like '%\[Y\]%' escape ''
go
AMB
"bic" wrote:

> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic|||You can use the ESCAPE clause. In this example I'm using '!' as the escape
character:
DECLARE @.x VARCHAR(100)
SELECT @.x = '[Y]'
SELECT
CASE
WHEN @.x LIKE '![Y!]' ESCAPE '!' THEN 'Match'
ELSE 'No Match' END AS Answer
"bic" <bic@.discussions.microsoft.com> wrote in message
news:B548D713-0022-47D1-A0E4-47630561159C@.microsoft.com...
> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic

How to construct a like compariso when there are special chara

select *
from
(
select 'abc[Y]def' as c1
union all
select 'abcdef' as c1
) as t1
where c1 like '%\[Y\]%' escape ''
go
AMB
"Alejandro Mesa" wrote:
> bic,
> use a scape character.
> Example:
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abc[Y]def' as c1
> ) as t1
> where c1 like '%\[Y\]%' escape ''
> go
>
> AMB
> "bic" wrote:
>"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149792727.734355.86450@.u72g2000cwu.googlegroups.com...
> Another way is to double the brackets
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abcYdef' as c1
> ) as t1
> where c1 like '%[[Y]]%'
> go
>
Be careful with the bracket doubling thing - it can get confusing on complex
comparisons with lots of [ ]'s in them.

How to construct a cell value

My users have decided they want to use an alpha-numeric concatenation
for an identifier in their projects. The DB will use a bigint for a
record number, but I need to construct the value they'll use for their
"human"-readable value.
It'll go like this: A000, A001, A002, A003... B000, B001... AA00, AA01,
etc. Where each place is cycled through the alphabet first then 0-9.
I figure the first 2 places will give them enough project ids to last 10
years or so, but programming this thing needs to plan for longevity and
maintenance.
What is the best way to build this? Regular expressions, pure SQL in a
sproc? I need to have rules to build it appropriately and
systematically. I'll be writing the front ends in C#.NET.
Thanks in advance.
_E
*** Sent via Developersdex http://www.examnotes.net ***Why A001 is more human readable than 1?
Creating that kind of column will stop you from using bulk inserts without
traversing the set to calculate that value.
AMB
"Esteban404" wrote:

> My users have decided they want to use an alpha-numeric concatenation
> for an identifier in their projects. The DB will use a bigint for a
> record number, but I need to construct the value they'll use for their
> "human"-readable value.
> It'll go like this: A000, A001, A002, A003... B000, B001... AA00, AA01,
> etc. Where each place is cycled through the alphabet first then 0-9.
> I figure the first 2 places will give them enough project ids to last 10
> years or so, but programming this thing needs to plan for longevity and
> maintenance.
> What is the best way to build this? Regular expressions, pure SQL in a
> sproc? I need to have rules to build it appropriately and
> systematically. I'll be writing the front ends in C#.NET.
> Thanks in advance.
> _E
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks for the suggestions, Jim, leoinfo y el estimado Sr. Mesa, but this is
a central ID for all applications across multiple locations, so they need to
be unique.
What I've decided to do it use a table of seed values containing the low and
high values along with a "row" identity. I'll do the check in my object code
.
I tried to create a new numbering system, which would have been great, but
they do not want alpha in the places below 1000, i.e. A999 goes to B000, not
to A99A as in a number system. See the last one in the A999 example should b
e
AYYY then it goes to B000. The seed values fix that, but it's a cludge. No
doubt.
I'm sure it'll have at least 15 changes before it's finished, but I think
I've anticipated most of them. I've written some DOD stuff so version and
revision control code already exists that I can modify. I'll just act like
it's a life altering change. :-]
_E
"Jim Underwood" wrote:

> Why not just use the identifier that you already have and let them input a
> human readable description or short description?
> "Esteban404" <esteban> wrote in message
> news:eaB%23cc$VGHA.2040@.TK2MSFTNGP15.phx.gbl...
>
>sql

How to consolidate the count?

Reg code Cntry code Area code Count
--- ---- --- --
AF AO CAB 15
AF AO LAD 20
AF BF OUA 23
AF BI BJM 11
AF BW GBE 72
AF CD FIH 30
AF CD MNB 8

I need the result like this

Reg code cntry code Total

AF AO 35
AF BF 23
AF BI 11
AF Bw 72
AF CD 38

consolidated total of area code 15+20 = 35, 30 + 8 = 38

table structures(two tables I am using)

create temp table country (ccode char(5),
acode char(4), rcode char(4));

create temp table report(count char(5),acode char(5));

how to write the sql?This is a simple sum and group by:

select c.rcode, c.ccode, sum(t.count)
from country c, temp t
where c.acode = t.acode
group by c.rcode, c.ccode;

BTW, why is column count declared as char(5)?

How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

How to consolidate duplicate records

Hello,
I'm assigned a task to clean up a table which has the data like below:
col1--col2--col3--col4
103 20 606 $50
103 20 606 $60
I was told to consolidate the sales$ and only keep one record, the primary
key is on col1,col2 and col3.
How can I do this?
Thanks,
SarahWhat about
SELECT col1,col2,col3,sum(col4) as sales
From YourTable
Group by col1,col2,col3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SG" <sguo@.coopervision.ca> schrieb im Newsbeitrag
news:%23DMa7GoRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm assigned a task to clean up a table which has the data like below:
> col1--col2--col3--col4
> 103 20 606 $50
> 103 20 606 $60
> I was told to consolidate the sales$ and only keep one record, the primary
> key is on col1,col2 and col3.
> How can I do this?
> Thanks,
> Sarah
>|||You can use GROUP BY like:
SELECT col1, col2, col3, SUM( col4 ) AS "col4"
FROM tbl
GROUP BY col1, col2, col3 ;
Anith|||Thanks Anith and Jens for your quick response. How could I forget this? I
made a mistake to group by sales column which I should use sum()so it gave
me the wrong result.
Thanks again,
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eChkdLoRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> You can use GROUP BY like:
> SELECT col1, col2, col3, SUM( col4 ) AS "col4"
> FROM tbl
> GROUP BY col1, col2, col3 ;
> --
> Anith
>

How to connection to ODBC data source ?

How to connection to ODBC data source ? Tongue Tied

In the Connections pane, right-click and select New Connection.... You will now have the complete list of available connections rather than the top few, choose ODBC - Connection Manager for ODBC connections.

How to connection MS Access Porject (.adp) with SQL server?

Hi all,
I got the question, how can i connection MS Access Project to a SQL Server
in vba in runtime.
The situation is i create a new MS Access Project, it's didn't have any
connection with any SQL-server. It's just consist a couple of form. What i
want is that user can via an inlog form to get in the database. The
informations about the server...etc is save in the code. The user just need
to give the SQL-login and password. My code to make the connection is as
follow.
'----
--
Function GetADPConnection(strServername, strDBName As String, Optional strUN
As String, _ Optional strPW As String)
Dim strConnect as string
strConnect = "Provider=SQLOLEDB" & _
";Data Source = \10.0.0.4\" & strServername & _
";Initial Catalog =" & strDBName
strConnect = strConnect & ";UID=" & strUN
strConnect = strConnect & ";PWD=" & strPW
Application.CurrentProject.OpenConnection strConnect
End Function
'----
--
It doesn't seems to work and i get the following error messagge:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
Can anyone help?
Best Regards,
PatData source should be in the form of:
Servername (or IP - 10.0.0.20)
For a named instance, you can use:
Servername\InstanceName
You can find sample connection strings at:
http://www.carlprothman.net/Default.aspx?tabid=81
-Sue
On Thu, 6 Apr 2006 07:49:02 -0700, Pat
<Pat@.discussions.microsoft.com> wrote:

>Hi all,
>I got the question, how can i connection MS Access Project to a SQL Server
>in vba in runtime.
>The situation is i create a new MS Access Project, it's didn't have any
>connection with any SQL-server. It's just consist a couple of form. What i
>want is that user can via an inlog form to get in the database. The
>informations about the server...etc is save in the code. The user just need
>to give the SQL-login and password. My code to make the connection is as
>follow.
>'----
--
>Function GetADPConnection(strServername, strDBName As String, Optional strU
N
>As String, _ Optional strPW As String)
>Dim strConnect as string
> strConnect = "Provider=SQLOLEDB" & _
> ";Data Source = \10.0.0.4\" & strServername & _
> ";Initial Catalog =" & strDBName
> strConnect = strConnect & ";UID=" & strUN
> strConnect = strConnect & ";PWD=" & strPW
> Application.CurrentProject.OpenConnection strConnect
>End Function
>'----
--
>It doesn't seems to work and i get the following error messagge:
>[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
>denied.
>Can anyone help?
>Best Regards,
>Pat
>sql

HOW TO CONNECTED MS SQL 2005?

I had just install MS SQL 2005 at my server computer ( OS windows 2000
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi H
Try this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H
|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
>
thanks for your help...

HOW TO CONNECTED MS SQL 2005?

I had just install MS SQL 2005 at my server computer ( OS windows 2000
server sp4). I have LAN in office with workgroup, and I have a problem
that I can't connect to MS SQL 2005 from client mechine. Previously, I
used MS SQL 2000 server.
Please anybody can help me to resolve this problem?
Regard's
Rudi HTry this:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/defaul...kb;EN-US;914277
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"RUDI HARSONO" <binapusat@.nusa.net.id> wrote in message
news:%23k1wt7LJHHA.780@.TK2MSFTNGP03.phx.gbl...
>I had just install MS SQL 2005 at my server computer ( OS windows 2000
>server sp4). I have LAN in office with workgroup, and I have a problem that
>I can't connect to MS SQL 2005 from client mechine. Previously, I used MS
>SQL 2000 server.
> Please anybody can help me to resolve this problem?
> Regard's
> Rudi H|||Arnie Rowland wrote:
> Try this:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/defaul...kb;EN-US;914277
>
thanks for your help...

How to connect?

Hi All,
I'd like to connect to a SQL Server 2000 instance which listens to not the
default 1433 port. Can anyone help me with how to register the server in EM
and how to connect to the server with QA?
Thanks.
Kolos
Hello,
1 option:- Create a client Alias using Client Network Utility defining the
correct PORT and then use the Alias to register and use in Query Analyzer
2 option: While connecting give IPADDRESS,Port number in the server window.
(Eg: 10.222.1.100,1522)
Thanks
Hari
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:0AE8AA45-09F8-43F3-9CE7-ADCD224DB393@.microsoft.com...
> Hi All,
> I'd like to connect to a SQL Server 2000 instance which listens to not the
> default 1433 port. Can anyone help me with how to register the server in
> EM
> and how to connect to the server with QA?
> Thanks.
> Kolos

How to connect?

Hi All,
I'd like to connect to a SQL Server 2000 instance which listens to not the
default 1433 port. Can anyone help me with how to register the server in EM
and how to connect to the server with QA?
Thanks.
KolosHello,
1 option:- Create a client Alias using Client Network Utility defining the
correct PORT and then use the Alias to register and use in Query Analyzer
2 option: While connecting give IPADDRESS,Port number in the server window.
(Eg: 10.222.1.100,1522)
Thanks
Hari
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:0AE8AA45-09F8-43F3-9CE7-ADCD224DB393@.microsoft.com...
> Hi All,
> I'd like to connect to a SQL Server 2000 instance which listens to not the
> default 1433 port. Can anyone help me with how to register the server in
> EM
> and how to connect to the server with QA?
> Thanks.
> Kolos

How to connect....HELP

How can I connect to database from a remote computer in the same domain?
I developed many database applications using Access, but now I need a code
snippet to make some of my existing application to run in a remote Sql server
with the same structure (databases, views)...and I can't figure how...
Anybody can help me?
http://www.able-consulting.com/ADO_Conn.htm
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:8E8F6961-D166-45D4-8C8E-3E3CD032B768@.microsoft.com...
> How can I connect to database from a remote computer in the same domain?
> I developed many database applications using Access, but now I need a code
> snippet to make some of my existing application to run in a remote Sql
server
> with the same structure (databases, views)...and I can't figure how...
> Anybody can help me?
sql

How to connect....HELP

How can I connect to database from a remote computer in the same domain?
I developed many database applications using Access, but now I need a code
snippet to make some of my existing application to run in a remote Sql serve
r
with the same structure (databases, views)...and I can't figure how...
Anybody can help me?http://www.able-consulting.com/ADO_Conn.htm
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:8E8F6961-D166-45D4-8C8E-3E3CD032B768@.microsoft.com...
> How can I connect to database from a remote computer in the same domain?
> I developed many database applications using Access, but now I need a code
> snippet to make some of my existing application to run in a remote Sql
server
> with the same structure (databases, views)...and I can't figure how...
> Anybody can help me?

How to connect, after all ?

Hi All,

i have a problem, so lets to it.

Situation:

I have a COM that connects to SQL2K with that string connection:
"Provider=SQLOLEDB;Persist Security Info = False;DATABASE=MyDatabase;SERVER=MyServer;User Id=myuser;Password=123456;"

that works fine, but we have a problem: the person that compiles de DLL knows user and password, so we decided to change the way the application connects to SQL, and start using AppRoles.

Things we did:
1-I created an approle and gave the permissions to the role.

2-I grant connect permission to domain\user1, only connect, that will be used when registering the COM as domain\user responsible to be used to stablish the connection.

3-Developers changed string connection to: "Provider='SQLOLEDB';Data Source='MyServer';Initial Catalog='Mydatabase';Integrated Security='SSPI';"

New behavior:

The connection goes OK, but the way we did before, when traced with profiler, the sp_reset_connection has been invoked by sqlserver, and with the knew connect string was no more executed.

Another problem is that when the COM is called in a second time, the previous connection is logged out and it stops, not connecting again.

Any ideas ??

thanks a lot.

Leandro.What's the source code of sp_reset_connection ( are you sure this is a standard MS sp)|||i dont know, sp_reset_connection is invoked by sqlserver that controls pooling connection.

the fact is that with the string connection used before the sp_reset... has been invoked, and with the new configuration was no more.

we setup a domain\user responsible to the COM and granted connect permission to it and the code of the COM is executing sp_setapprole.

the first time the COM is called it works fine, but the second time the sp_reset_connection is not invoked, and yhe connection is logged out.

the third time the COM is invoked all works fine again, when i trace the connection the select runs OK, ando so on.

any ideas ?|||I would try adding "Persist Security Info = False;" back into your connection string

how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

How to connect with SQL database?

I am new learner of ASP.NET and coding in C#.I am not able to connect the SQL database.I have written the code for connection of database but to write the connection string web.config file is neccessary which is not apearing in my application. Please help me .Here is code I have written.

string sCon =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection oCon =new SqlConnection(sCon);

try

{

oCon.Open();

string sQuery ="Select Offering1,Offering2,Offering3 from Offering";SqlCommand oCmd =new SqlCommand(sQuery, oCon);

oCmd.ExecuteNonQuery();

DataSet oDs =newDataSet();

SqlDataAdapter oDa =new SqlDataAdapter(oCmd);

oDa.Fill(oDs);

DataList1.DataSource = oDs;

DataList1.DataBind();

}

catch (Exception ex)

{

ex.ToString();

}

What kind of error are you getting?

Can you post a copy of the connectionString settings from your web.config?

|||

Hii Jimmy,

Actually I do not see the web.config file in my solution explorer. So that I am unable to write the connection string. Can you please tell me why it is so ?

|||

Looks like you may have started with the empty web site template. When you try to start/debug the application, does it not automatically add a web.config for you?

Well, you can manually add one. Right click on your web site and select ADD NEW ITEM then select the WEB CONFIGURATION FILE

|||

Hey thanks,

As there are errors because I have not added the connection string into the web.cofig file it is not able to debug the file.Right now I have added web.config manually.

What the connection string format to add to web.config?

<addname="ConnectionString"connectionString="Server=(local)\SQLEXPRESS;Integrated Security=True;Database=Knowledgebase;Persist Security Info=True"/>

is it right?

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

|||

Bluestar123:
Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

That would because you did not import the namespace

Add this to the top of your code behind for your default.aspx

using System.Data.SqlClient;

|||

The connection string can look something like this

"Data Source= xxx ;Initial Catalog=xxx; uid=xxx ;pwd=xxx"

where datasource is your database server; could be your machine name or I.P. address, Initial Catalog is the database name, UID is the login name, PWD is the login name's password

If your SQL Server is set up for windows authentication then you can do replace the UID and PWS with "Integrated Security=True"

Have a look at this link for connection string examples

http://connectionstrings.com/?carrier=sqlserver2005

|||

Hi Jimmy,

Now I have added using System.Data.SqlClient to my default.aspx.cs and that error has gone thanks for the same.

I am still not able to fetch the data to my datalist from database, is still there any problem with my connection string? in my web.cofig I have used windows authentication.

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

Please help ..........

|||

Bluestar123:
<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

for the connection string, the data source should be [your machine name]\SQLEXPRESS assuming SQLEXPRESS is the server instance name

update your code to this and give it a go

string sCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection oCon =new SqlConnection(sCon);try
{
string sQuery ="Select Offering1,Offering2,Offering3 from Offering";
 SqlCommand oCmd =new SqlCommand(sQuery, oCon);
 DataSet oDs =new DataSet();
 SqlDataAdapter oDa =new SqlDataAdapter(oCmd);
 oDa.Fill(oDs);
 DataList1.DataSource = oDs;
 DataList1.DataBind();
}
catch (Exception ex)
{
 ex.ToString();
}

Add some break points and step through the code, if there any exceptions, take not of the exception message.

|||

I have added break points at Select command and line below that ,control goes there when I debug the default.aspx and then it display the page without fetching thedata from database.So i have added the break point to the line

DataSet oDs=new DataSet();

at this line control donot go and directly display the page without showing the data from the database.

|||

So when you put a break point at the DataSet oDs=new DataSet(); and when you step over it F10, it does not proceed to the next line? Then that means an exception was raised and it should be caught in your catch. So put a break point in your catch statement, so you can check the exception being thrown if there is one at all and give us the error message

|||oops! Column name I had given was wrong .......Its OfferingName. I made that change but now when I put brk point at

DataList1.DataSource = oDs;

after pressing F10 control goes to next line i. e.

DataList1.DataBind();

but data from database is not getting displayed on the page though the page has been displayed......

oh no whats the problem ?am I doing any mistake again?

Follwing output I got in Output window

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_d4ehzmko.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_oq4mi2vf.dll', Symbols loaded.

The program '[3368] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Well the obviously question is have you tried executing the SQL statement against your database to make sure it does return data?

after you have stepped over this line of code oDa.Fill(oDs); check it the dataset has returned a datatable with datarows.

|||

No I didnt,how can I check it ?I am using Microsoft SQL server management Studio. When go to line

oDa.Fill(oDs);

it did not return the table contents......

I tried to execute the query by using Execute SQL but not able to execute ,How to do?

Did you get fed up of my questions Jimmy??

|||

Bluestar123:
Did you get fed up of my questions Jimmy??

Not at all, just trying my best to understand your problem which is at times a little hard.

Lets try execute you select statement first in SQL Server Management Studio.

Log onto your database, and select the NEW QUERY option.

Now paste your SQL statement into this window , "Select Offering1,Offering2,Offering3 from Offering" and click execute

Does it return any results?

sql

Friday, March 30, 2012

Wednesday, March 28, 2012

Blog Archive

About Me