HELP!!
having on trouble installing, just gettting everything connected.
(xp sp2)
First i loaded MSDE, then realized i needed the web admin tool to create the db...but can't get the web admin tool to connnect to anything..
so I took a stab at SQL 2005 Std ed beta....loaded fine, but then realized i need another admin tool to create db so I loaded the xpress manager...again, loads fine, but doesnt'connec to any DB..
why is this so difficult for me....SQL Svr v6 was so much easier with enterprise manager!!!
HELP!!
DonDon, what do you mean by "the web admin tool"? The utility that comes with Visual Studio .NET 2005?
Can you explain what you mean by "but doesn't connect to any DB."? Are you receiving any error messages?
Showing posts with label newbe. Show all posts
Showing posts with label newbe. Show all posts
Monday, February 20, 2012
Newbe to SPs
Ey,
I need to do few operations on every record in a table. Do I have to use cursor? Do SQL has something like 'FOR' or 'WHILE'? I've read somewere that cursors should be avoided due to their time consumption.
ThnxTell us what you need to do ... before we can comment on whether you need a cursor or not.
It would be helpful if you paste some DDL ... and sample data|||That was qucik :)
Im trying to write proc that would be run on a daily basis. It would have to deal with around 12000 records. What it has to do is to take data from flat, multicolumn table, check for some conditions and spread them into real relational db.
Lets say people input things into that flat table and they often misspell i.e. city names due to fast input. It gotta take a record, chceck if inserted city name exists in cities table in db, if not it goes to the missspelled names table and chcecks if it exists there, if not again it adds a new record to missspelled table and lights a flag to inform admin and he could make a decision if to move it to cities table or leave it in misspelled.
Same thing would happen for few columns in every row.
I hope I made myself clear enough...
Thnx again.
I need to do few operations on every record in a table. Do I have to use cursor? Do SQL has something like 'FOR' or 'WHILE'? I've read somewere that cursors should be avoided due to their time consumption.
ThnxTell us what you need to do ... before we can comment on whether you need a cursor or not.
It would be helpful if you paste some DDL ... and sample data|||That was qucik :)
Im trying to write proc that would be run on a daily basis. It would have to deal with around 12000 records. What it has to do is to take data from flat, multicolumn table, check for some conditions and spread them into real relational db.
Lets say people input things into that flat table and they often misspell i.e. city names due to fast input. It gotta take a record, chceck if inserted city name exists in cities table in db, if not it goes to the missspelled names table and chcecks if it exists there, if not again it adds a new record to missspelled table and lights a flag to inform admin and he could make a decision if to move it to cities table or leave it in misspelled.
Same thing would happen for few columns in every row.
I hope I made myself clear enough...
Thnx again.
Newbe Questions
I am familier with crystal and just starting SSRS.
How do you set the report to print in Landscape?
How do you like combine two fields into a label with some additional text added it to build your custom label?
Salameh,
If you are developing in Visual Studio/Business Intelligence Dev. Studio, you can set the layout of the report by changing the Page width/height to be 11 by 8.5 instead of 8.5 by 11. You can do this in the properties window, or you can right-click below the report and select properties, and change the dimensions on the Layout tab.
To combine two fields with additional text, you could do something like the following...
="Field A is " & Fields!FieldA.Value & ", and Field B is " & Fields!FieldB.Value & "."
Hope that helps
newbe question: need a way to read in large query with .net
Could someone please point me in the right direction on how to read in
a large query with .net.
It sounds like you should use ExecuteReader and loop through the rows.
That is, do not use DataAdapter.Fill. Furthermore, to make it possible
to update the rows as you have read them in, you need to enable MARS,
Multiple Active Result Sets, which I believe you do in the connection
string.
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.
I can't escape asking what's the point? You get the legacy product
converted to .Net, but it will still have the architecutre of the
old product, and risk is that you get a compromise with the worst from
both.
I take it that the other product was using another data store than
SQL Server?
Yes, unfortunately :(
ya, this is the best method for sure, its just that I need to emulate
the old system as accurately as possible. The legacy software does not
expect concurrency errors on the updates.
Fortunately the resultsets that require a lock on each row will
probably be fairly small. Larger resultsets are typically reports
which don't require locks. I can see I am going to have to write a
performance test to see the actual speed of reading say 500 rows one
at a time with a lock on them.
a large query with .net.
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
Select * from invoices where year 1995
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.
And if at all possible I would like to lock one row at a time as the
row is read in.(troy@.makaro.com) writes:
Quote:
Originally Posted by
Could someone please point me in the right direction on how to read in
a large query with .net.
>
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
>
Select * from invoices where year 1995
>
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.
It sounds like you should use ExecuteReader and loop through the rows.
That is, do not use DataAdapter.Fill. Furthermore, to make it possible
to update the rows as you have read them in, you need to enable MARS,
Multiple Active Result Sets, which I believe you do in the connection
string.
However, if your plan is to read one row at a time and update back,
I wonder from where you get the information to update. It's much much
efficient to perform the update in the database on all rows in one
go.
--
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|||Sounds like you need to take a look at this URL:
http://msdn2.microsoft.com/en-us/li...594(VS.80).aspx though
I doubt a newb will understand it much. This is just in case there are
other "MVP's" like myself who are in need of a good reference for a
task like large query input.
HTH,
Carl Tegeder
Master MS-SQL Administrator
MS-SQL MVP
On Feb 26, 12:36 pm, t...@.makaro.com wrote:
Quote:
Originally Posted by
Could someone please point me in the right direction on how to read in
a large query with .net.
>
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query. An example query would be something
like:
>
Select * from invoices where year 1995
>
the query must be updatable and only return say 10 to 100 rows at a
time.
It should also be forward only and discard rows no longer in use to
save memory.
>
And if at all possible I would like to lock one row at a time as the
row is read in.
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.
Here is one an example of what I have to emulate:
get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next
The converted code looks something like:
' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next
My question now is:
How do I lock one row at a time?
One thought I had was to make all connections go through my own
service. That service could keep track of locks. The problem with that
is that future products will want to use sql properly which would
bypass the locking.|||(troy@.makaro.com) writes:
Quote:
Originally Posted by
Great information on MARS and the ExecuteReader. What I am trying to
do is to emulate a legacy product's data access method. The reason I
am doing this is there is just way too much code to convert into
proper sql. I'm talking at least 1 million lines of code. I have
already written a conversion program to convert the code to VB.NET and
now I must right a dll assembly to emulate the legacy data access.
I can't escape asking what's the point? You get the legacy product
converted to .Net, but it will still have the architecutre of the
old product, and risk is that you get a compromise with the worst from
both.
Quote:
Originally Posted by
Here is one an example of what I have to emulate:
>
get #3, key #1 GE "20060101"
while invoiceDate < "20070101"
! The current row is now locked!
! make changes
update #3 ! updates the currently locked row and now unlocked.
get #3 ! read the next row in
next
>
The converted code looks something like:
>
' note: 3 = the registered table
SQL.getGreaterEqual(3, "20060101") ' notice no upper bounds
while invoiceDate < "20070101"
' The current row is now locked!
' make changes
SQL.update(3) ' updates the currently locked row and now unlocked.
SQL.getNext(3) ! read the next row in
next
>
My question now is:
How do I lock one row at a time?
I take it that the other product was using another data store than
SQL Server?
There are a couple of ways to do this, but it is important to understand
that locking a row is nothing you don't really do actively in SQL Server.
This is left to the lock manager.
And it's even less possible in ADO .Net, since ADO .Net uses client-
side cursors only. That is data is read from SQL Server and buffered.
Something like ExecuteReader may not read all million rows at once,
but it will not fetch one row at a time.
One way is to wrap the entire reader in a transaction with the isolation
level REPEATABLE READ. But then rows will remained locked until you
commit.
However, the only reasonable approach is optimistic locking. That is,
don't lock, but check for concurrent updates when you update. This
can be done in two ways:
1) Add a timestamp column: a timestamp column is automatically updated when
the row is updated. If you include the timestamp column in the WHERE
clause, and you see that @.@.rowcount is 0, then you know that the row
was changed since you last read it. 2) Without a timestamp column just
add all columns to the WHERE clause. I believe that the Update commands
that comes with the CommandBuilder includes this.
I can think of a third way: first read all keys into local array. Then
iterate over the array, and read one row at a time as 1) Start transaction
with REPEATABLE READ, 2) read row 3) update and 4) commit. But this
will be slow as I don't know what.
All and all, I think you are fighting an uphiil battle.
--
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|||Thanks for the response. see inline comments:
Quote:
Originally Posted by
>
I take it that the other product was using another data store than
SQL Server?
Yes, unfortunately :(
Quote:
Originally Posted by
However, the only reasonable approach is optimistic locking.
ya, this is the best method for sure, its just that I need to emulate
the old system as accurately as possible. The legacy software does not
expect concurrency errors on the updates.
Quote:
Originally Posted by
I can think of a third way: first read all keys into local array. Then
iterate over the array, and read one row at a time as 1) Start transaction
with REPEATABLE READ, 2) read row 3) update and 4) commit. But this
will be slow as I don't know what.
Fortunately the resultsets that require a lock on each row will
probably be fairly small. Larger resultsets are typically reports
which don't require locks. I can see I am going to have to write a
performance test to see the actual speed of reading say 500 rows one
at a time with a lock on them.
newbe question: calling function inside select
Hi!
I have a scalar function that returns integer:
xview (int)
Now, I'm trying to build a procedure that has the following select
inside:
select atr1, xview(atr2)
from tablename
But, I get the 'Invalid name' error when I try to execute that
procedure.
If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.
I'm a bit confused, so any hint is very welcomed.
Thanks!
Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.
But those are the rules. You must refer to a scalar function with a
two-part name.
I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?
Ok, let's put is this way.
I have 'xview' function.
I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?
It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.
Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.
USER will return userX.
The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.
The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.
Aha. That is very good. Thank you for the information!
Mario.
Newbe question about replication
Hi ppl
I just started with all this 'replication' subject and run into some 'stange ?' problem. I suscesfully set-up publisher and distributor on one computer. create subscriber on another. And seems that this is working with no errors as I see in Replication monitor, details. All agent turned to verbose output and showing no errors at all. but the strange problem that the database does not (!) appear at subscriber (?) I mean - no tables, no stored procedures, no nothing - how it can be possible ? What I'm doing wrong ? Or what I should do ?
P.S. Forgot to add: Both servers running latest SQL server 2005 with latest Service Pack. Subscribtion of type 'push'
I think, you're talking about merge replication.
One question:
Have you generated a snapshot before synchronizing?
kind regards
Aline
Newbe Question - How to install SQL 2000 Cluster
Good day,
I'm looking for a document that shows the steps needed in order to install
SQL 2000 on a windows 2003 cluster.
Thanks in advance
Oren Zippori
Great webcast on the subject:
http://support.microsoft.com/default...lurb061002.asp
http://www.sql-server-performance.co...stall_main.asp
has great information for Windows 2000, just add Q817064 and then 301600 for
MSDTC support and you are good to go.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Oren Zippori" <orenzp@.hotmail.com> wrote in message
news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Good day,
> I'm looking for a document that shows the steps needed in order to install
> SQL 2000 on a windows 2003 cluster.
> Thanks in advance
> Oren Zippori
>
|||Thanks for the Info Rodney.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%238D7pMpxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Great webcast on the subject:
> http://support.microsoft.com/default...lurb061002.asp
> http://www.sql-server-performance.co...stall_main.asp
> has great information for Windows 2000, just add Q817064 and then 301600
> for MSDTC support and you are good to go.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Oren Zippori" <orenzp@.hotmail.com> wrote in message
> news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
>
I'm looking for a document that shows the steps needed in order to install
SQL 2000 on a windows 2003 cluster.
Thanks in advance
Oren Zippori
Great webcast on the subject:
http://support.microsoft.com/default...lurb061002.asp
http://www.sql-server-performance.co...stall_main.asp
has great information for Windows 2000, just add Q817064 and then 301600 for
MSDTC support and you are good to go.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Oren Zippori" <orenzp@.hotmail.com> wrote in message
news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Good day,
> I'm looking for a document that shows the steps needed in order to install
> SQL 2000 on a windows 2003 cluster.
> Thanks in advance
> Oren Zippori
>
|||Thanks for the Info Rodney.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%238D7pMpxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Great webcast on the subject:
> http://support.microsoft.com/default...lurb061002.asp
> http://www.sql-server-performance.co...stall_main.asp
> has great information for Windows 2000, just add Q817064 and then 301600
> for MSDTC support and you are good to go.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Oren Zippori" <orenzp@.hotmail.com> wrote in message
> news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
>
newbe ? re:date format
Hi, I'm developing a page in asp.net using a sqldb and one of the values I'm sending to the database is a date/time value. I only want the table to show the date in short format, no time. Is there a way to restrict or convert the data in the table design mode?
Thanks
RaifSQL Server dates are stored as numeric values that interpreted as dates with time of day. Formatting of output should be handled by your user interface, not the database server.|||Thanks I sort suspected that as I started writing the post.
Thanks again|||Go with a smalldatetime in the sql table so that you are only using a (4). The go with a .ToString() and something like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstostringtopic4.asp
Hope it helps.|||If you're doing straight SELECT from your ASP, you can use CONVERT(char(10), <your_date_field>, 101) on your date/time field.
Thanks
RaifSQL Server dates are stored as numeric values that interpreted as dates with time of day. Formatting of output should be handled by your user interface, not the database server.|||Thanks I sort suspected that as I started writing the post.
Thanks again|||Go with a smalldatetime in the sql table so that you are only using a (4). The go with a .ToString() and something like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstostringtopic4.asp
Hope it helps.|||If you're doing straight SELECT from your ASP, you can use CONVERT(char(10), <your_date_field>, 101) on your date/time field.
NEWBE
I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate that
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.
If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:
>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each request
>was submitted. The only function that i found to automatically populate that
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate that
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.
If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:
>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each request
>was submitted. The only function that i found to automatically populate that
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.
NEWBE
I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate tha
t
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:
>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each reques
t
>was submitted. The only function that i found to automatically populate th
at
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate tha
t
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:
>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each reques
t
>was submitted. The only function that i found to automatically populate th
at
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.
Subscribe to:
Posts (Atom)