|
If you use .net see this article. You can do it with WMI:
http://www.codeproject.com/csharp/wmi.asp[^]
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
|
Hello All,
I have 2 SQL2000 DBs on the same server, in DB#1 I have a stored procedure from which I need to get a field from a table that exists in DB#2.
Any Ideas,
Thanks in advance
|
|
|
|
|
Syntax is: [ServerName].[DatabaseName].[Owner].[TableName].[FieldName]
So in your Stored Proc your request might look like:
SELECT myField
FROM myServer.myDatabase.dbo.myTable
WHERE myField = 'some value'
ServerName can be an ip address, or if it is on the same server - just a different database - you can omit ServerName altogether.
Owner is usually 'dbo' -- otherwise substitute the table's owner
|
|
|
|
|
Quick Question:
I have a number of SQL/MSDE databases on the local company server.
Each database uses identical stored procedures for quick updates to their record items.
Can I use just one copy of the stored procedure for use by all the databases, instead of duplicate in each one? If yes, does this have to go in the master database. How would you distinguish the active database (perhaps using Using dbo.???)?
I Dream of Absolute Zero
|
|
|
|
|
You must to inform the database by parameter....
I worked in a proyect where each database had the data of an specific mounth and Year.
To solve that we had a central DB with the stored procedures and a set of parameters, for example, the name of the last DB, and the name of the previous DBs, etc....
Regards....
Carlos Antollini
Do you know piFive[^] ?
|
|
|
|
|
try adding the new stored procedures to the model database
all new databases are a copy of the model database so they will contain the stored procedures you created.
|
|
|
|
|
True, but if one of the stored procedures needs to be updated, it will then have to be applied to all databases.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Colin Angus Mackay wrote:
but if one of the stored procedures needs to be updated, it will then have to be applied to all databases
Exactly.
What I was after is a central location where all the databases could access these SPs. Changes to the SP would then immediately be reflected to all the databases.
I have searched, but to no avail. I can only assume that this is not [easily] possible.
I Dream of Absolute Zero
|
|
|
|
|
There are several Computers with SQL Server 2000, only connected while replication with dial-up connections (ISDN Router). One Computer is the Publisher, this one also initiates the Replication.
Today we only provide Data from the Publisher to all the subscribers with a Snapshot replication.
On the Subscribers the Application generates Log Data. We want to collect the Data from all the Subscribers to the Publisher.
My idea was to do a merge replication and filter the rows by a new field that contains the Hostname of the subscriber. But because the Publisher initiates the replication, the HOST_NAME() function is set to the Publishers name.
Is there a possibility to get the hostname of the subscriber or does anybody know a better solution to this problem?
Greets
Roland
|
|
|
|
|
I have a problem that I wonder if you all can help me with. I have a table that could be updated every second, or every ten minutes (or more). I need to get the data from that table to provide real-time representation on a web page.
To minimize the round trip to and from the db on the network, I had suggested that we retrieve data from the table and hold onto it in the Application Cache which refreshes itself every x seconds so that if many users are trying to view the data, they all pull from the Application Cache, not from SQL Server. However, it has also been suggested to me (by my supervisor) that we code a Trigger or a Job in SQL Server to generate a static HTML page (either on a schedule, or every time one of the fields in the table is updated) so that all requests for the page get handled by IIS and don't require a trip to the db.
I can't imagine that this is a good programming practice... and I don't expect for more than about 50 users at a time (probably more like 10-20). Can anyone provide me with good arguments for either method?
Thanks
|
|
|
|
|
I have to agree with your supervisor on this one. Always go to the source when you can, otherwise you risk presenting invalid data. I would not build new HTML on every update. I would use a version based system for speed.
Example
HTML_Table
===============
HTMLid int
Updater int
HTMLData NTEXT
Every time you rebuild the HTMLData you set the Updater to zero.
UPDATE HTML_Table
SET
Updater = 0,
HTMLData = "whatever"
WHERE HTMLid = 1
Every time you update a table that can change your HTML, increment the Updater:
UPDATE HTML_Table
SET Updater = Updater + 1
WHERE HTMLid = 1
Every time a page needs the HTML:
(1) If the updater > 0 the rebuild HTML
(2) return HTML
This part would best be implemented in a Stored Proc. Remember, SQL Server will cache the last few requests on its own - no need to build your cache system that never knowns when it is out-of-date.
|
|
|
|
|
Hi, guys.
How can I know MSDE 2000 has been installed successfully or not?
I think I install it. But I cann't use it.
Why?
vigorous
|
|
|
|
|
Take a look in the services applet for MSSQL service.
What can't you use?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
First create a typed-dataset with an integer ID primary-key field and a string Name field.
Then create a form with a datagrid and a reference to the dataset on it. set the grids datasource to dataset1.table1.
If you then enter 1 and Mark on the first line, all is OK.
If you enter 1 and Mark2 on the second line, you get the following error: "Error when committing the row to the original datastore" - "Column ID is constrianed to be unique, Value 1 is already present. Do you want to correct the value? Yes/No.
I would like to put my own message up for the user or do something else, but I can't seem to find a way to hook into this internally-handled ADO error.
Any answers would be greatfully received.
|
|
|
|
|
Morning Boys and Girls....
Have a little VB6 app which I need to add the functionality to run an SQL DB update script through.
Runnign the script is not a problem - am parsing the lines one by one until I hit a GO, then executeing on an ADODB connection.
The problem I have is trapping the server messages (such as "Query Batch Completed" or table already exists etc), but can find no way of doing so.
Does anyone have any ideas - have tried the connection WithEvents, but not really anything useful coming back...
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hello. Does anyone know how to concatenate two text strings in MS SQL data base. I have two tables with ntext data type fields and I need to concatenate this two and write to the therd ntext field. The sign '+' doesn't work for ntext format. I couldn't find anything about it in SQL book online. Please help. Is there any solution?
My best regards
|
|
|
|
|
Hi there. I understand that concatenation is not support with ntext data types at all. If the amount of text isn't too much, you could try converting it to a varchar datatype, and concatenate that... something like:
convert(varchar(4000), myNText1) + convert(varchar(4000), myNText2) Of course, if your ntext fields are that small, maybe you should use varchars anyway.
|
|
|
|
|
The problem is that strings are longer then 6000 symbols so I have to use "ntext" data type... 
My best regards
|
|
|
|
|
I've found a solution to the problem. There is "UPDATETEXT" expression in T-SQL. It works with pointers so it doesn't look at the length og strings. More info in SQL Book online. There are examples as well.
My best regards
|
|
|
|
|
I think I have installed MSDE 2000 on my pc.
I set SAPWD="123456" and INSTANCENAME="test". After installation, in the services, there are MSSQL$TEST and SQLAgent$TEST exiting and I have started both of them. But I can not see Server and Services available in SQL Server services Manager. So I can not use MSDE.
Do you think I install it successfully? If yes, how can I use the MSDE?
Thank you very much!!!
vigorous
|
|
|
|
|
Your service manager is probably pointing at "\\LocalMachine"
The problem is that you specified an instance name. Since you did this instead of installing a default instance, you can't connect to the instance using just the machine name, you have to provide the instance name, as well.
Open the service manager, and in the Server box, type \\LocalMachine\test. That is how you will have to refer to the instance that you installed.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
I type \\LocalMachine\test, but the message is "The network path was not found". Do you know how to confirm whether MSDE 2000 has been installed successfully or not?
Thanks
vigorous
|
|
|
|
|
What operating system?
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Windows 2000 professional
vigorous
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
