preconfigure port number while command line installing

Hi,

I want set a specific port number of SQL Server(express) during installation not by Configuration Manager after install.
Is it possible?

hi,

probably becouse of the multi instances support of SQL Server up to version 2000, which introduced the SQL Server Resolution Service listening on UDP 1434, now SQLBrowser, this feature has been removed from the configurable settings..

but you can interact via SMO as well, but probably after your installer has finished..

regards

precision

Precision is the number of digits in a number. Scale is the number of digits
to the right of the decimal point in a number. For example, the number
123.45 has a precision of 5 and a scale of 2.
Is the above statement true? or Is the precision 3 and the scale is 2?
Thank you in advance.In your example 123.45 the precision (p) is 5 and scale (s) is 2.
For more information:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
--
Rohtash Kapoor
http://www.sqlmantra.com
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> Precision is the number of digits in a number. Scale is the number of
digits
> to the right of the decimal point in a number. For example, the number
> 123.45 has a precision of 5 and a scale of 2.
> Is the above statement true? or Is the precision 3 and the scale is 2?
> Thank you in advance.
>|||So on this one 1234567890.1234 the precision is 14 and the scale is 4 same
as (14,4)?
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
> In your example 123.45 the precision (p) is 5 and scale (s) is 2.
> For more information:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_3grn.asp
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
> > Precision is the number of digits in a number. Scale is the number of
> digits
> > to the right of the decimal point in a number. For example, the number
> > 123.45 has a precision of 5 and a scale of 2.
> >
> > Is the above statement true? or Is the precision 3 and the scale is 2?
> >
> > Thank you in advance.
> >
> >
>|||Yes
>--Original Message--
>So on this one 1234567890.1234 the precision is 14 and
the scale is 4 same
>as (14,4)?
>"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in
message
>news:%230BQSWP1DHA.540@.tk2msftngp13.phx.gbl...
>> In your example 123.45 the precision (p) is 5 and scale
(s) is 2.
>> For more information:
>> http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-
dz_3grn.asp
>> --
>> Rohtash Kapoor
>> http://www.sqlmantra.com
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in
message
>> news:O7cosLP1DHA.832@.TK2MSFTNGP09.phx.gbl...
>> > Precision is the number of digits in a number. Scale
is the number of
>> digits
>> > to the right of the decimal point in a number. For
example, the number
>> > 123.45 has a precision of 5 and a scale of 2.
>> >
>> > Is the above statement true? or Is the precision 3
and the scale is 2?
>> >
>> > Thank you in advance.
>> >
>> >
>>
>
>.
>

Precedence of MAX and WHERE

Hi
I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :
SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username
Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?
Thanks
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||You could compare these queries and see which one yields the results you
want. Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 . Also, "date" is a really bad name
for a column. Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT? Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings. The safest approach here is to use
YYYYMMDD format, then this can't be by software or humans.
CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO
DROP TABLE dbo.myDataTable
GO
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||Andrew,

> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:

> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>
>

Powershell?

Hi,
Do any folk here use SMO from PowerShell?

I've found a limited number of web postings about the subject, but not much newsgroup/forum chat.

Specifically, I'm wondering how to capture exceptions raised by SMO calls.
For example, I wanted to drop all logins on a server, so thought I'd try this:
$sql = new-object 'microsoft.sqlserver.management.smo.server' 'servername'
$sql.Logins | %{$_.Drop()}
which throws this error for every login:
Exception calling "Drop" with "0" argument(s): "Drop failed for Login 'BloggsJ'. "
At line:1 char:24
+ $sql.Logins | %{$_.drop( <<<< )}

Is there more information to be had? I've tried defining an object as microsoft.sqlserver.management.smo.smoexception but I'm not quite sure what to do with it...

TIA,
Robin.

Do a search on the web for "PowerSMO" and you will find a number of tutorials I wrote on using SMO and PowerShell.

Dan

possible uses and impact of using xml datatype

I'm looking at this for an application I'm working on right now that is currently using SQL 2000 and a huge number of meta data files.

We synchronize externally using different technologies to items which cannot be pre-defined at all. We don't know what they will look like, what attributes they will have, or even if a new item might pop up.

So. we have a database just to record that "x" exists, and there is an applicaiton layer to interpret with the meta data what x actually means and looks like, then display it to the user. We track changes to "x" once we know it is there, over time. IT's attribute values will change over time.

I'm thinking with 2005, we could stuff the fact that "x" exists into a row, and it's corresponding definition in an XML column. It seems that this is the exact situation the XML data type was invented for.

My question is: am I right in the above assumption, and what would we really gain by moving that information from the filesystem into the database? Better performance? Easier to manipulate the XML? Easier association of a particular XML file to database data? Would it degrade performance of a system that is currently kind of slow but working?

I think if we used this correctly and in a limited way, we could have something pretty spiffy.

How easy is it to read and manipulate the elements in the XML using SQL?

I want to stay away from CLR and continue using the application layer, just have the attributes for the object available to the application layer.

Could someone give me an example of the ideal situation this datatype was invented for? I believe it would be wrong to invent the whole "database-in-a-database" thing, but for our purposes the datatype might work, since we have no control over the entities or attributes but need to store their existence for the UI.

Obviously, I have a lot of research to do but I thought I might ask if it's worth my time at this point.

Yes, it's worth your time to investigate, but it's hard to know what the impact might be on your particular situation.

Tagged data formats and markup languages generally, of which XML is a member, are ideal for metadata situations.

But the thing is, you must already *have* a metadata language in your app, so it's hard to say what benefit would come from changing it to XML ... except for exactly this point, that the xpath and xquery capabilities in XML generally, and the excellent integration with SQL in SQL Server 2005, are very likely to be helpful. Also that XML as a language, is simple, straightforward, and very widespread.

Possible to get column number on a bcp_sendrow failure?

I've tried SQLGetDiagRec, which tells me that there was an invalid date
format on a column, but there's no indication of *which* column, and my
table has several date columns in it.
I then spotted some references to SQLGetDiagField with
SQL_DIAG_COLUMN_NUMBER, but I don't get any records back from that
call-- should I be able to get something, or is there some other way or
just *no* way to get the column number that failed?
SyncIt looks like no one knows the answer to this. I'm using VS 2005 & SQL
Server 2005, and after my bcp_sendrow error, have tried all the
SQL_DIAG fields with SQLGetDiagField, and have so far found that
SQL_DIAG_DYNAMIC_FUNCTION returns a null string on records 0 & 1,
SQL_DIAG_NUMBER returns 1, SQL_DIAG_RETURN_CODE returns -1,
SQL_DIAG_DYNAMIC_FUNCTION_CODE returns 0 and ignores record number, and
none of the rest return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. And
SQL_DIAG_SS_MSGSTATE and SQL_DIAG_SS_SEVERITY both return SQL_ERROR.
SQLGetDiagField appears completely useless here. And SQLGetDiagRec
doesn't include the column info.
On the other hand, bcp.exe used on the same data produces an error
message that includes column number. The question of the day is, how
does it do it? I turned on ODBC trace, and let bcp.exe do its thing.
In the trace log, I found a bunch of calls to SQLColAttributes, one for
each column, then a bunch of calls to SQLGetInfoW, then a call to
SQLSetConnectAttr with what appears to be a bogus attribute (-28236)
which then returns the first error-- it then calls SQLErrorW and gets
the message I'm able to get (invalid character for cast specification)
and then it does an SQLGetConnectionOption with 112 (packet_size) and
then disconnect and frees. I can't see where it's getting the
row/column info from that it writes to the error output.
Then, I tried turning ODBC trace on and running my program that uses
bcp_sendrow. It does a *single* call to SQLBindCol (though I'm
calling bcp_bind about 35 times), then a bunch of SQLGetInfoW's like
the bcp.exe version, then the same SQLSetConnectAttrW with the odd
attribute value (-28236) that returns the first error, and then my
SQLGetDiagRec/SQLGetDiagField tries that never get me the column
information.
One thing, is bcp_sendrow does not operate with a statement handle, but
a connection handle. bcp.exe is calling SQLColAttributes with a
statement handle, which it is using for all the SQLColAttributes calls,
after having issued a "select * from <table> where ?\ 0" which looks
like it could be a method for determining the column type of all the
columns, in which case the sanity checking of the data and perhaps even
the conversion may be done in bcp.exe itself, different from
bcp_sendrow which handles the conversion somewhere in the API. If
that's the case it may be that bcp_sendrow doesn't make column
information available on an error, which is really annoying. I'm
trying to produce a relatively generalized bulk import capability that
needs to flag what's wrong when a user tries to import bogus data. I'm
using bcp_sendrow because there is a lot of associated data tweaking on
the way in and I want to minimize the overhead-- eliminating writing an
intermediate form to disk and then calling bcp.exe to do the import. I
presume it is that kind of thing the bulk-copy API is for.
At least I know the row that is affected, as bcp_sendrow operates a row
at a time. I suppose I could use bcp_sendrow until I get an error,
then throw that row out to a file and run bcp.exe on it and let IT
detect the specific column information, but that's pretty darn kludgy.
SQLSetConnectAttrW with an attribute code of -28236 seems to be doing
something special-- perhaps even initiating the row import, as it
appears to be *that* call in both cases that is throwing the error I'm
trying to get the column number for. Haven't been able to find a
define for the value in the includes, and don't know how it would
appear anyway, possibly in hex as 0x91b4 or -0x6e4c or some kind of
ORing together multiple values...
Sync|||SUCCESS!
I'm talking to myself here but perhaps it will benefit others. I found
out how to get the row/column information on a bcp_sendrow error.
First, you have to specify an error file in the bcp_init call. I did
that and ended up getting a null error file, initially. Searched the
newsgroups and found several people had that problem. But then I
remembered that Windows is not like Unix in that file data sometimes
doesn't get written to disk if a close isn't done when the program
exits. I've seen that before. Open a file, fprint some stuff to it,
then exit. File exists, but is null. So, I figured I probably have to
call bcp_done to get the error file closed. Sure enough, now I'm
getting the error info.
One issue is, what if I don't want to "commit" the partial batch to the
database on an error? I figured by not doing a bcp_done I would
achieve a rollback. Haven't verified that though, and it looks like I
can't do it that way anyway because I need the errors. There may be
another way to do a rollback, which I'll look into and is a subject for
another day...
Sync|||If you don't have a blog up yet, maybe this is a good opportunity to start.
ML
http://milambda.blogspot.com/|||(kdd21@.hotmail.com) writes:
> I'm talking to myself here but perhaps it will benefit others. I found
> out how to get the row/column information on a bcp_sendrow error.
> First, you have to specify an error file in the bcp_init call. I did
> that and ended up getting a null error file, initially. Searched the
> newsgroups and found several people had that problem. But then I
> remembered that Windows is not like Unix in that file data sometimes
> doesn't get written to disk if a close isn't done when the program
> exits. I've seen that before. Open a file, fprint some stuff to it,
> then exit. File exists, but is null. So, I figured I probably have to
> call bcp_done to get the error file closed. Sure enough, now I'm
> getting the error info.
> One issue is, what if I don't want to "commit" the partial batch to the
> database on an error? I figured by not doing a bcp_done I would
> achieve a rollback. Haven't verified that though, and it looks like I
> can't do it that way anyway because I need the errors. There may be
> another way to do a rollback, which I'll look into and is a subject for
> another day...
Thanks for posting this! I don't relly have anything to add. But as I
have a module for Perl users that exposes bcp_sendrow et al, this is
useful information. My module uses DB-Library, so I need to port it to
ODBC one day...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your welcome. Thanks for the non-snide remarks. Also, WRT "commits,"
it appears that the most suggested way to handle imports is to import
everything into a "staging" table, then use SQL to move from the
staging table into the "live" table. Then, the "better" constraint
checking & transaction handling features are available. I found this a
pretty unsatisfactory solution, as the whole point of my project is to
reduce the overhead of large imports, as the app I'm working on is a
database analysis program that will be constantly doing large imports
of a good-sized schema's worth of tables exported from another server.
Seems to be mostly working now however, though I had to make use of
indicators on the bind variables so that I could pass in NULL flags
where necessary...
Sync

Possible tempdb problems

I have a simple TOP X select statement that is returning
one less than the TOP X number. If I check the records
affected verses records returned it is always 1 less. I
ran DBCC CHECKDB on all databases and I got some errors
when I ran it on tempdb. I can't set single user access to
tempdb so I can't repair it. Any suggestions? Here are
the errors:
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB
processing.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:106) in database ID 2 is allocated in the SGAM
(1:3) and PFS (1:1), but was not allocated in any IAM. PFS
flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3096) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3176) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 3 allocation errors and 0 consistency errors
not associated with any single object.
CHECKDB found 3 allocation errors and 0 consistency errors
in database 'tempdb'.
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKDB (tempdb ).Restart SQL Server, and tempdb will re-create itself from scratch.
"Brian Tax" <brian.tax@.milliman.com> wrote in message
news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
> I have a simple TOP X select statement that is returning
> one less than the TOP X number. If I check the records
> affected verses records returned it is always 1 less. I
> ran DBCC CHECKDB on all databases and I got some errors
> when I ran it on tempdb. I can't set single user access to
> tempdb so I can't repair it. Any suggestions? Here are
> the errors:
> Server: Msg 8999, Level 16, State 1, Line 1
> Database tempdb allocation errors prevent further CHECKDB
> processing.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:106) in database ID 2 is allocated in the SGAM
> (1:3) and PFS (1:1), but was not allocated in any IAM. PFS
> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3096) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3176) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> CHECKDB found 3 allocation errors and 0 consistency errors
> not associated with any single object.
> CHECKDB found 3 allocation errors and 0 consistency errors
> in database 'tempdb'.
> repair_allow_data_loss is the minimum repair level for the
> errors found by DBCC CHECKDB (tempdb ).|||We rebooted twice yesterday and it was still doing the
same thing. Now this morning the SELECT TOP X was working,
but it still gave errors in tempdb.
>--Original Message--
>Restart SQL Server, and tempdb will re-create itself from
scratch.
>
>"Brian Tax" <brian.tax@.milliman.com> wrote in message
>news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
>> I have a simple TOP X select statement that is returning
>> one less than the TOP X number. If I check the records
>> affected verses records returned it is always 1 less. I
>> ran DBCC CHECKDB on all databases and I got some errors
>> when I ran it on tempdb. I can't set single user access
to
>> tempdb so I can't repair it. Any suggestions? Here are
>> the errors:
>> Server: Msg 8999, Level 16, State 1, Line 1
>> Database tempdb allocation errors prevent further
CHECKDB
>> processing.
>> Server: Msg 8906, Level 16, State 1, Line 1
>> Page (1:106) in database ID 2 is allocated in the SGAM
>> (1:3) and PFS (1:1), but was not allocated in any IAM.
PFS
>> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3096) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3176) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> not associated with any single object.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> in database 'tempdb'.
>> repair_allow_data_loss is the minimum repair level for
the
>> errors found by DBCC CHECKDB (tempdb ).
>
>.
>

Position List at bottom of last page.

I have a report that can run 1 to x number of pages. I need to be able to put
list at the bottom of the last page. This list will need to include
disclaimer message in a text box plus several fields from my data source also
in text boxes. I've tried several different ways to do this, my last being
putting it in the footer of the detail table and using padding of an empty
text box in a footer row above the last footer based on total records and how
may I get on a page. This comes close but still does not place this
information at the very bottom all the time.
Access and Crystal Reports have a report footer to accomplish this but, I'm
not seeing anything close in Reporting Services.
Anybody have any thoughts?
Thanks JoeHi Jeo,
Reporting Services has page footer, but this appers on each page of the
report. My idea would be to use this page footer and add the content you want
to show in the page footer(in a tex box in your case) and then set textbox's
visibility:hidden property based on the page number.
An expression in the Hidden property of the textbox would be:
=( Globals!PageNumber <> Globals!TotalPages)
This is obviously a work around. If you have more than one piece of
information to show in the page footer, I suggest you to use a rectangle as a
place holder and add the text boxes to it for the different information you
want to show and then assign the hidden property to the rectangle.
Hope this solves your purpose.
"Joe Kurtz" wrote:
> I have a report that can run 1 to x number of pages. I need to be able to put
> list at the bottom of the last page. This list will need to include
> disclaimer message in a text box plus several fields from my data source also
> in text boxes. I've tried several different ways to do this, my last being
> putting it in the footer of the detail table and using padding of an empty
> text box in a footer row above the last footer based on total records and how
> may I get on a page. This comes close but still does not place this
> information at the very bottom all the time.
> Access and Crystal Reports have a report footer to accomplish this but, I'm
> not seeing anything close in Reporting Services.
> Anybody have any thoughts?
> Thanks Joe