Do not Display "NULL" in Results tab

Is there a setting in Management Studio to NOT display "NULL" in the
Results tab for either Grid or Text view? I didn't see anything under
Options. I'm currently having to do search and replace to remove it
before passing on to users or using for imports to other apps. Third
party import programs typically treat this as text.
MarkChange the query to say COALESCE(stringValue, '')
"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
> Is there a setting in Management Studio to NOT display "NULL" in the
> Results tab for either Grid or Text view? I didn't see anything under
> Options. I'm currently having to do search and replace to remove it
> before passing on to users or using for imports to other apps. Third
> party import programs typically treat this as text.
>
> Mark|||This is what I was afraid of. It seems for every two steps forward in
Management Studio, you have to take one step back. So now if I have
100 varchar columns, I'll have to create 100 alias columns
(ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
carry over features in previous versions to the next. It seems they
could have added an option to "Query Results" to display NULL or blank
for the output. This wasn't a problem in QA.
On Fri, 30 Jun 2006 08:45:49 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Change the query to say COALESCE(stringValue, '')
>"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
> news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
>|||> (ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
> carry over features in previous versions to the next. It seems they
> could have added an option to "Query Results" to display NULL or blank
> for the output. This wasn't a problem in QA.
It's quite possible that they got a lot of complaints about that
"feature"... to me, at least, there is a big difference between NULL and
blank. If I can't visibly tell the difference, I'm going to have to change
something.
It's kind of like a car manufacturer choosing to put the cup holder on the
dash or in the center console. No matter which option they choose, they're
going to piss someone off.
A

Do I need to shrink my database?

Hello,

I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.

Basically I have a webpage that when I create a new item it populates a new ID, which is the primary key and read only when I go into the database backend so i cannot manually change this.

When I have been doing some testing adding fields to the table and deleting this has obviously given me new ID's such as 113, 114 etc. But on my live site I need these ID's to be sequential, so on my live site the last ID is 108, but now when I add another item the ID is 115 because the number between this and 108 have been taken up.

So my question is really do I need to shrink my database or files to take this down to 109, if so is database shrink or file the best to do? Also i have had someone add a new live item and this has been assigned 113, would I need to delete this, shrink and then re-enter?

Any suggestions would be greatley appreciated.

Many thanks.

shrinking a database does not effect IDENTITY values, you need to look at the following

DBCC CHECKIDENT (Transact-SQL)

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

you could also look at running a SET IDENTITY INSERT ON;

and then updating the ID values manually to your desired values.

Derek

|||

Thank you very much for you reply. Guess I need to run a query on something like the follwoing from the link you sent:

USE Assets;
GO
DBCC CHECKIDENT ('Assets.ID', RESEED, 30);
GO

But how do you specific which row to replace?

Also how does the SET IDENTITY INSERT ON command work, sorry quite new to SQL.

|||

the easiest way for you to do this (assuming you have some form of consistency to your IDs) would be to run the following code:

SET IDENTITY_INSERT dbo.Table ON

GO

UPDATE dbo.Table SET ID = ID -30 --this is the consistency I am referring to, if your IDs are all over the place it going to not be fun lol

GO

SET IDENTITY_INSERT dbo.Table OFF

|||

You cannot use IDENTITY column if you want to ensure no gaps in the values. For example, if a particular transaction rolls back an insert then the generated identity value will be consumed and a subsequent insert will get the next higher value. If you want guaranteed sequential numbers generation then you need to do it using a sequence table yourself.

Please take a look at the link below for an implementation that shows how to use sequence table.

http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx

|||Thank you for your help I have now sorted the problem. Much appreciated.

Do I need to install SQLXML?

I am trying some excerxises in a self-paced training kit.I am supposed to communicate with SQL Server 2005 from Visual Studio. In Visual Studio I am supposed to add references to, and import a library called:

Microsoft.data.sqlxml.

The problem is that I don't find it on my system. Do I have to install new components?

I use :

WinXP,
SQL Server 2005 Express,
Visual Studio

Regards

Fredrik

Quote:

Originally Posted by Fredrik Cerha

I am trying some excerxises in a self-paced training kit.I am supposed to communicate with SQL Server 2005 from Visual Studio. In Visual Studio I am supposed to add references to, and import a library called:

Microsoft.data.sqlxml.

The problem is that I don't find it on my system. Do I have to install new components?

I use :

WinXP,
SQL Server 2005 Express,
Visual Studio

Regards

Fredrik

Yes, you will need to download SQLXML. SQLXML enables XML support for your SQL Server Database. Heres the link to download it from Microsoft: (it's free, too!)

SQLXML
Hope that helps,

-Cody-

Do I have to install SQL Express?

I'm using the login/roles from Visual Studio 2005 in a website.

Is MS SQL Express mandatory? I already have MS SQL 2000 on the box. Is there any way to use that instead?

And if so, will I need to install MS SQL on the box I'm building the webiste on?

Thanks,

oilcan

You dont't have to have MS SQL 2005 express on your developer machine.

There is a build-in database schema behind the memebership function in ASP.NET 2.0. By default, if you have MS SQL 2005 express installed, you will get a database named as ASPNETDB.MDF installed under App_data folder in your application when you start using membership.

It would be easy to play with samples and test simple things. However, you need to know how to move this database to another database if you want to host your application somewhere. For example, SQL 2005 or SQL 2000.

You can mannuly install this database by using a tool in this location(most likely): C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

You can pick where you want to install this database from this tool's interface.

After you install this database, you need change your connection string to point to your new database which includes all tables from ASPNETDB.MDF

You may need to read through some articles from searching this topic about membership.

This is just a start and you will get there. If you have problems, you will easily get answers from here.

Enjoy the journey.

|||Any idea where I might find some of those articles you mentioned? I don't feel real comfortable just experimenting, it is a live web server.|||

Hello there:

This article talks about how to merge this ASPNETDB.MDF to your database.

http://aspnet.4guysfromrolla.com/articles/040506-1.aspx

Please follow this article, you will bepretty close. I think you better to test on your developer machine before you move on.

Also I came across this one from DisccountASP forum to show users how to move the starter kits to their server. Just for you as a reference.

http://community.discountasp.net/default.aspx?f=25&m=8785

Let me know if I can help more.

Limno

Do I Get the GUI with SQL Server 2005 Developer Edition

Hi,

Do I Get the GUI (Query Analyser and Enterprice Manager) with SQL Server 2005 Developer edition (which is available as free with Visual Studio 2005). I will be thankful if u give information.

SQL Server Developer edition have all the features of SQL Server Enterprise edition... But the enterprise manager equivalent in SQL Server 2005 is Sql Server Management Stuiod...

Refer these link for an overview

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

http://www.microsoft.com/sql/editions/developer/default.mspx

Madhu

|||

SSMS is included with the installation media, however, it is not installed with the default installation.

You will need to install the 'Client Tools' from the installation media in order to obtain Management Studio.

|||

K. Ravinder Reddy wrote:

Do I Get the GUI (Query Analyser and Enterprice Manager) with SQL Server 2005 Developer edition (which is available as free with Visual Studio 2005). I will be thankful if u give information.

Are you sure you have the Developer Edition? I know that Visual Studio 2005 comes with SQL Server Express Edition (which is different than Developer Edition). The tool you are looking for is called SQL Server Management Studio. It combines and extends most of the functionality of Query Analyzer and Enterprise Manager (SQL Server 2000 tools).

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

sql

DNS problem with reports (server URL)

I'm developing a SSRS site using SQL Server 2000 and Visual Studio 2003.
The target device is a Nokia 770 Internet tablet over a wireless network
using VPN for authentication (corporate requirement).
The 770 currently has a problem resolving server names to a fully-qualified
URL. It's been identified as a DNS-oriented bug that may take time to be
fixed. In order to enable the 770 tablet to navigate to my site, instead of
using http://myserver/Reports I have to instruct users to enter
http://myserver.country.company.com/Reports. This in and of itself is not a
problem. The trouble starts when the user clicks a report or chart; the url
generated contains a reference to http://myserver/Reportserver.
Unfortunately the tablet can't resolve this.
What I am looking for is a way to override whatever setting is limiting the
Reportserver URL and replace it with a fully qualified URL.
Anyone have any ideas? I've looked into it some but haven't figured out
what I can do.
Thanks,
Randall Arnold
NokiaRandall,
If you're able, I think the simplest solution would be to create a little
one page aspx program in which you can display your reports. Then you can
pass the full domain name to the reports server as part of the program.
Tim
Pharos Innovations
"Randall Arnold" wrote:
> I'm developing a SSRS site using SQL Server 2000 and Visual Studio 2003.
> The target device is a Nokia 770 Internet tablet over a wireless network
> using VPN for authentication (corporate requirement).
> The 770 currently has a problem resolving server names to a fully-qualified
> URL. It's been identified as a DNS-oriented bug that may take time to be
> fixed. In order to enable the 770 tablet to navigate to my site, instead of
> using http://myserver/Reports I have to instruct users to enter
> http://myserver.country.company.com/Reports. This in and of itself is not a
> problem. The trouble starts when the user clicks a report or chart; the url
> generated contains a reference to http://myserver/Reportserver.
> Unfortunately the tablet can't resolve this.
> What I am looking for is a way to override whatever setting is limiting the
> Reportserver URL and replace it with a fully qualified URL.
> Anyone have any ideas? I've looked into it some but haven't figured out
> what I can do.
> Thanks,
> Randall Arnold
> Nokia
>
>sql

DMX vs Visual Studio

Hi

Are there any (important?) advantages of using data mining through DMX instead of Visual Studio 2005 on the SQL server 2005?

/Dennis

DMX is the only solution for querying the mining models, and BI Dev Studio also uses DMX in the prediction query builder.

In general, as far as mining object manipulation is concerned, DMX and the XMLA-based Analysis Services Scripting Language (aka DDL or ASSL), the script used by BI Dev Studio, have similar features.

There are a few differences, however, mostly deriving from the fact that DMX is designed as an extension for SQL, for application developers and, therefore, it is more concise (so that it can be embedded or generated by applications).

Some DDL features not supported by DMX:

full control over the metadata of mining objects. Properties such as Description, bindings, name bindings are only supported in DDL and not in DMX

Distributing/importing semantic models

Hi,
i'm using SQL Server 2005 Reporting Services to create ad-hoc reports.
Using the BI Dev Studio i'm defining a DSV and adding queries to it
before refining in the semantic model. i need to be able to distribute
these to our customers, who use an SS DB with known schema.
In a simulated customer environment i've successfully imported the DSV
file (using SMSS) to RS. When i subsequently try to import the SMDL
file i get an error complaining that the DataSourceView element is
missing from the SemanticModel. Quite correct. But how do i specify
the DSV element or otherwise make the association?
It is quite acceptable to me to make a simple text edit to the SMDL
file.
TIA for any help.OK, slight correction - i haven't successfully imported the DSV.
Import appeared to succeed but there's actually no data visible for it
in SMSS.|||Since there are no replies i'm either very stupid or nobody knows. If
it's the latter then for the benefit of all here's how i did it in the
end.
DataSourceView is a valid element of the SemanticModel element. So
manually edit your SMDL file and paste the entire content of your DSV
file (which is a single DataSourceView element) just above the
SemanticModel closure tag ("</SemanticModel>").
The Management Studio will now happily import the modified SMDL file.
The customer just needs to manually change the Data Source to their own
database using Management Studio (or Report Manager).
Cheers all.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.