does MSSQL have the ability to do "named row types?"

Hi all. We have a mix of informix and mssql server and I want to know
if something we do in informix has an analogous feature in MSSQL. We
can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we
do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids
having different developers build the same type of field in more than
one way, different lengths, etc.

Is there a similar function in MSSQL server?sumGirl (emebohw@.netscape.net) writes:
> Hi all. We have a mix of informix and mssql server and I want to know
> if something we do in informix has an analogous feature in MSSQL. We
> can define a "row type" in informix, like so:
> create row type name_1(fname char(20),lname char(20));
> The when we create any table that includes a first and last name, we
> do so using this row type like so:
> create table sometable(name name_1, some column,...etc)
> This allows us to set a standard for certain common fields and avoids
> having different developers build the same type of field in more than
> one way, different lengths, etc.
> Is there a similar function in MSSQL server?

In SQL 2000, no.

In SQL 2005, which currently is in beta, you can define structured types
in CLR languages like Visual Basic or C#, but it's not really the same
thing as the Informafix feature. I assume that in the Informix case you
can still say:

SELECT fname FROM sometable WHERE ...

and thus refer to the individual columns directly. With a CLR type in
SQL2005 you would have to say:

SELECT name.fname FROM sometable WHERE ...

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

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

Does format file for bulk insert allow mix of native and character format?

I tried to place this question to the .Net framework Data Access and Storage forum and got no answer, so I am trying to move it on this forum.

So I have a module which require me to import big amount of data. I believe that the native format data files with format files will be the most efficient way of implementation. I am trying to programmatically produce a BCP like exported files of native format(it means without type conversion) from tables with nullable and nonnullable values.I prefere to be able to not produce computed or identity or rowguid fields,so I need format files.

I don't have problems producing different kinds of int or float( which are the majority of fields) or char or nchar fields.

Problems are emerging with the datetime or smalldatetime or decimal fields because I don't know how to convert to them from the strings or from the CLR types.

So I trying to find a way to find a native format of those fields or to find if a bulk insert will accept mixed format files with some of the fields in the native format without field terminators and some with field terminators or to use char format with field terminators only plus maybe format files.

So if the answer to above question is positive I can partially resolve the problem, if negative I will have to use the character format.

Unless you can educate me on the convertion to the SQL server internal formats of datetimes and decimals from the CLR types.

See SQL Server 2005 Books Online topics:

SQL Server Data Types and Their .NET Framework Equivalents
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
Specifying File Storage Type by Using bcp
http://msdn2.microsoft.com/en-US/library/ms189110.aspx

Data Type Conversion (Database Engine)
http://msdn2.microsoft.com/en-us/library/ms191530.aspx

Does anyone know a utility/batch script to Warn when close to Max DB size

Hello Group
Does anyone know of a utility or Batch script (osql or Windows cmd or mix)
to Warn an Admin when a MSDE DB is closing in on 2 GB in size (or 2005
Express closes in on 4 GB).
Any leads or tips will be appriciated highly
Best regards
Uffe
hi Uffe,
Uffe Bak wrote:
> Hello Group
> Does anyone know of a utility or Batch script (osql or Windows cmd or
> mix) to Warn an Admin when a MSDE DB is closing in on 2 GB in size
> (or 2005 Express closes in on 4 GB).
> Any leads or tips will be appriciated highly
> Best regards
> Uffe
in MSDE you can perhaps schedule a job defined to include a way to gather
that kind of data, using direct access to the sysfiles database table or via
DBCC SHOWFILESTATS..
you can then validate that data against a userdefined treshold and notify
someone via NET SEND or mail via SMPT alternative, like exploded in
http://www.karaszi.com/sqlserver/info_no_mapi.asp,
http://www.dbmaint.com/SmtpAlerter.asp , thus defining a kind of alert..
PRINT 'MSDE';
PRINT '--';
USE Northwind;
PRINT 'sp_spaceused';
EXEC sp_spaceused @.updateusage = 'TRUE';
PRINT '--';
PRINT 'DBCC SHOWFILESTATS';
CREATE TABLE #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
);
INSERT INTO #tmp_sfs
EXECUTE('DBCC SHOWFILESTATS');
SELECT LEFT(DB_NAME(),10), (SUM(totalextents) * 64) / 1024 AS [totalextents
in MB], (SUM(usedextents) * 64) /1024 AS [usedextents in MB]
FROM #tmp_sfs;
DROP TABLE #tmp_sfs;
PRINT '--';
PRINT 'sysdatabase query';
SELECT LEFT(DB_NAME(),10) AS [Database], sum(convert(float,size)) * (8192.0
/1024.0) /1024.0 AS [Size in MB for Data files]
FROM dbo.sysfiles
WHERE (status & 0x40) <> 0x40;
SQLExpress does not provide the SQL Server Agent, so you can perhaps rely on
the native OS scheduler for something similar.. as regards the SMPT
integration, you have to (possibly) write your own CLR based mailer
solution.. I personally will... then, from the OS scheduler, you'll execute
SqlCmd command line tool to perform the SQLExpress connection and data
retrival...
just an idea..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks to Andrea and Satya for quick answers
I will look into your suggestions and return if I find a solution.
I also looked up some Scripting options, so there are as usual more than one
road to Rome
Best regards
Uffe Bak