SQL Server in questions and answers

V: At me on different computers it is established two sql server 7.0. For data exchange between them I used data transformation services (dts) to import the data on the computer - addressee. Having chosen the necessary data, I started to pass them, and dts have given out the message, that all objects and the data are imported successfully. However fields such as varchar, earlier containing 500-1000 words, now, on the computer - addressee, have been established in null. Some recordings contained inverted commas, and I have assumed, that the server "has stumbled" on it. However if put so-called sql server should import correctly other data - not containing inverted commas. In what my mistake?

O: Really, at dts there are failures at processing long symbolical fields from the big files of the entrance data. Sometimes this problem can be solved, having cleaned{removed} a tag fast load on a bookmark advanced dialogue transformation task properties. If not will help, it is necessary to try to use for import of the data bulk copy program (bcp). Though bcp not so it is simple in application as dts, it will allow to avoid similar mistakes. For an example it is necessary to read clause{article} microsoft " bug: dts transfer does not report error when input file is missing text qualifier in last row " http://support.microsoft.com/support/kb/articles/q241/7/61.asp where proves to be true, that at data transfer dts does not give out the message on a mistake at import of last line of an entrance file the table sql server if the entrance file contains text descriptors, and in the last stolbce such descriptor is missed. To bypass this obstacle, it is necessary to edit an entrance file manually.

Besides system adjustments on processing values null could call the described problem. Contrary to the standard ansi, sql server by default creates stolbcy, not accepting values null if only obviously to not use corresponding announcements in operators create table or alter table.

Strange, but also odbc and ole db bypass this installation. It is possible to try to use various options for thin adjustment of job with null. At a level of a database for a choice of a method of job (ansi, or a mode of return compatibility sql server) it is possible to apply function sp_dboption. Besides there is an opportunity to change parameters of installations of a concrete session of communication{connection} with base, with the help of transact sql (t-sql set ansi_null_dflt command {on | off}, and for definition of the current installations of processing null to use function getansinull ().

V: What is the utility scriptpkg in data transformation services (dts) and where her{it} to find?

O: The standard editor of packages packag designer from dts has the graphic user interface and is constructed on technology com. Unfortunately, this means not such floppy as it would be desirable the majority of developers. The greatest censures absence causes in consumers in dts the convenient editor of scripts. At occurrence of problems with packages dts it is found out, that error messages dts which sql server writes to a file of mistakes, - rather foggy and almost useless. The utility scriptpkg allows to integrate a package dts into Wednesday visual basic (vb.) scriptpkg - freely distributed means vb which can be used for generation of files vbscript for a package dts. This utility is delivered on sql server 7.0 cd-rom, but she so is well hidden, that the majority of users about her do not know. To find and establish the given utility (on a computer with vb 6.0), it is necessary to make the following.

To unpack a file dtsdemo.exe from the catalogue devtoolssamplesdts on sql server 7.0 cd-rom.

In a subdirectory designer to start the project vb - scriptpkg.vbp.

From the menu file in vb to create an executed file under a name scriptpkg.exe, as shown in the Screen 1.

To generate a package dts in sql server 7.0 enterprise manager, to save it{him} on the local sql-server.

To start scriptpkg.exe, and to enter a name of a package. scriptpkg will create the script (for example, packagename.txt) in the catalogue temp.

To copy a code from the file - script in the vb-application. To add in the application of library microsoft activex data objects (ado) versions 2.1 or is higher and microsoft dts package designer, or to experiment with three examples vb dts in the catalogue devtoolssamplesdts folder.

scriptpkg writes down all package dts as one procedure, and the size of procedure in vb there can not be more than 64 Kb. Therefore, if the script scriptpkg is more than 64 Kb, are necessary to break it{him} into some parts manually.

scriptpkg is a powerful diagnostic means which besides allows to learn dts-programming with application of technology com. You can use scriptpkg to study not documentary opportunities dts. Further the knowledge vbscript is useful to you in the decision of problems{tasks} of administration sql server.

V: I have opened small telecommunication firm. microsoft visual foxpro it was used by me as Wednesday of development of applications and a data storage. I would like to import a number{line} of applications in sql server. However my applications are connected with udf, and I do not know how to define{determine} them in sql server. I looked through newsgroups and read sql server 7.0 books online (bol.) whether so supports sql server 7.0 udf whether or not?

O: Just as in a joke - two news: good and bad. I shall start with bad - sql server 7.0 functions udf do not support. Now good news: udf will support sql server 2000 which now is in a stage of beta-testing. Meanwhile it is possible to emulate udf by means of the stored{kept} procedures written on t-sql, but this method is represented to much labour-consuming enough and inefficient.

Developing the subject mentioned by you, I shall note, that users foxpro can meet the following two error messages: " the microsoft foxpro driver is no longer supported and has been replaced by the microsoft visual foxpro driver " and " could not find installable isam. " For clearing a situation read clause{article} microsoft " prb: foxpro odbc driver replaced by visual foxpro odbc driver " (item " the Driver odbc foxpro is replaced with the driver visual foxpro ") http://support.microsoft.com/support/kb/articles/q235/3/57.asp.

V: With what help of means the data for the application foodmart which is delivered together with microsoft sql server olap services (msolap) have been created? Why for the data foodmart the database access, instead of tables sql server was used?

O: microsoft delivers the data for the application foodmart in a format .mdb because not everyone who wishes to use msolap, has established sql-server. Many overlook, that msolap works not only with the sql-server, but also with other databases, for example with such, as oracle8i. Delivery foodmart in a format .mdb allows to experiment with msolap without installation of the sql-server. At last, if you plan to create the application on the basis of storehouse of the data from microsoft can practise, using the data foodmart in a format of the sql-server. On a web-site of the sql-server (http://www.microsoft.com/sql/productinfo/evalexercises.htm) it is possible to take a little samoraspakovyvajuhhikhsja and self-adapting exercises (see. Table 1). On the same site there are fact-finding exercises which will help to study methods replikacii and upgrades.

V: I have established a new copy sql server 2000, and all utilities sql server 7.0 were gone. How to me them to return?

O: Though on one computer it is possible to start some copies of the sql-server, to leave on him different versions of utilities it is impossible. With the new version sql server it is necessary to use corresponding versions of utilities (query analyzer, sql enterprise manager, sem, microsoft data access components, mdac) for job with sql server 7.0. do not establish new copies if not want to lose old versions of utilities.

It is theoretically possible "to crack" executed modules and dll or to make necessary changes to the system registry, but in this case you risk to break stable job of system. While we do not learn{find out}, how it is correct to bypass this restriction, it is not necessary to be solved on similar experiments.

V: I would like to store{keep} descriptions of fields of tables, their basic properties, and also business - properties. For example, if I have column orderdate in the table orders, I should know, whether contains a field date of accommodation of the order the client or date of execution{performance} of the order is specified in him. How it to make with the help microsoft repository?

O: sql server 2000 allows to store{keep} additional properties of many types of objects of a database. Additional properties are defined{determined} by the user and have type sql_ variant. The programmers working with vb, are familiar with type of the data variant. Similarly to type of the data in vb, sql_variant allows to store{keep} various types of the data in a field, parameter or a variable. Each copy of a column sql_variant will consist of two parts: actually the data and the metadata describing value (for example base type of the data of a field, the maximal size, accuracy and collation - comparison). For reception meta-dannykh a copy sql_variant it is possible to use function sql_variant_ property.

For example, to save the description of a column au_id in the table authors in a database pubs, it is necessary to click the right button of the mouse on a name of a column in a window object browser (the new interface query analyzer), then to choose extended properties. Now it is necessary to add new property whatami and to bring in value " i am the author id column!!! ". The same can be made, using procedure sp_addextendedproperty:

sp_addextendedproperty ' whatami2 ',' this is a new property value ',' user ', dbo, ' table ', authors, ' column ', au_id

Then it is possible to apply the standard operator select with new function fn_listextendedproperty to take the information:

select * from:: fn_listextendedproperty (null, ' user ',' dbo ',' table ',' authors', 'column', default)

objtype objname name value

column au_id whatami i am the author id column!!!

column au_id whatami2 this is a new property value

select * from:: fn_listextendedproperty (null, ' user ',' dbo ',' table ',' authors', 'column', default)

V: I have the certificate mcse and I am going to receive the certificate of the manager of a DB (mcdba). I know, that for expansion of applications sql server it is not enough to read the special literature. You nevertheless could recommend any sources of the information for beginning{starting}? At me already is " microsoft sql server 7.0 system administration training kit " (microsoft press, 1999) and william robert stanek's " microsoft sql server 7.0 administrator's pocket consultant " (microsoft press, 1999).

O: It is the best way to develop the application (or the prototype) which solves real problems{tasks}. I advise to think up the application for myself or to create a database for the small organization.

To get additional experience, import the raw statistical data to a new database ms sql or olap a cube and present, that you are an end user who wants to analyse the data. Study olap and the application food mart. It is possible to experiment with the application "duwamish" - you will find it{him} in microsoft developer network (msdn). Establish and investigate it{him} as a whole and pokomponentno, peresoberite. You will find more full information and examples on a site microsoft: http://msdn.microsoft.com/voices/sampleapp.asp and http://msdn.microsoft.com/library/techart/d35vbaroot.htm.

Besides it, it is necessary to join a newsgroup news: // msnews.microsoft.com/microsoft.public.msdn.duwamish, and also to see new examples of applications - fitch and mather stocks on a site http://msdn.microsoft.com/library/techart/fm2kintro.htm

V: I wrote stored{kept} procedure and have faced a problem at use of the operator top from a local variable instead of the fixed number. For example, when I write:

declare @counter int

select @counter=5

select top @counter * from

Procedure returns a mistake. But a line

select top 5 * from

Works. What to do{make} in that case?

O: It agrees sql server books online (bol), it is possible to use n in section top to limit quantity{amount} of the lines returned as a result of execution{performance} select of search. But n should be number such as integer. In sql server 7.0 language transact sql (t-sql) does not allow to involve a local variable in section top n even if that has type integer. Local and global variables can be identified with the help of prefixes: - for local and @@ - for global variables. It is possible to use also the operator set to appropriate{give} value of a local variable, or to define{determine} local variables, referring on them in the list of fields of the operator select. The following example, probably, will help to solve your problem{task}:

declare @counter int

declare @sql varchar (255)

set @counter=5

select @sql = <select top <+ str (@counter) + <* from authors>

exec (@sql)

It t-sql-predlozhenie dynamically builds and carries out a line t-sql, returning the first n lines of search. Dynamic t-sql allows to create such t-sql commands which cannot be applied at use standard t-sql methods.

V: How to use the functions determined by the user (udf) with sql server 2000?

O: Use udf in stored{kept} procedures allows to move additional business - logic of the application on the server. By development sql server 2000 experts microsoft were going to to include support for independent of language udf (for example, udf, written down in vbscript). Unfortunately, by virtue of existing program restrictions, udfs while it is possible to create only in language sql (t-sql). I shall result an example from sql server 2000 books online (bol), showing as it is used typical udf, written on t-sql:

create function cubicvolume

- The entrance sizes in centimeters.

(@cubelength decimal (4,1),

@cubewidth decimal (4,1),

@cubeheight decimal (4,1))

returns decimal (12,3) - cubic centimeters.

as

begin

return (@cubelength * @cubewidth * @cubeheight)

end

The given script creates function (stored{kept} on the server) which can be used in any context where sql server supposes application of decimal expression.