What is a good simple way to scan the servers in your network for SQL
Servers?
Does anyone have any scripts or code that would show me how to do
this?
I think WMI might be able to do this but don't know where to start.
If possible I'd like to be able to get the version (2000 or 2005) if
possible.
Thanks,
Kelly Greer
Join Bytes!
change nospam to yahoo
Hello all,
I have 2 networked PC's both running vista ultimate
1st is Laptop and is running its own SQL Server at laptoplaptopSQL
2nd is Desktop and is running its own SQL Server at desktopdesktopSQL
Now both machines have seperate windows login accounts.
When I go SQL Server management studio I go to browse and each machine can see the other machines SQL Server, but when I go to login I get SQL Login falied for users" The user is not associaed with a trusted SQL server connection".
So I then go to logins new login and try to add my other pc's user account.
The problem I see is that when I go to search and then location it only shows its own PC's location and not the location of my other networked pc? So if I am on Desktop and in my theory want to add laptopuser to the desktop SQL Server logins I get:
"create failed for login laptopuser
An exception occurred while executing Transact SQL statement laptopuser is not a valid windows NT name. give the complete name
Not sure on where to go from here.
Any help would be great
So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months. Short hand as COGS/AvgInventoryValue = Turns
In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2
Then divide AvgInventoryValue
To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost)
The purchased documents are represented with DOCUMENTTYPE = 6
The purchases will be positive since we are putting value in. The sales are negative since value is going out. I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.
--CREAT THE TEMP TABLE
create table #VALUEENTRY
(
ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATEDATETIME NOT NULL
, DOCUMENTTYPEINT NOT NULL
[code]....
Is there another approach I should take to obtain the inventory turns?Also how do I account for periods earlier than 12 months where inventory is left over?In the sample data this item happened to be at zero inventory on hand at the start of the period. Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?
Hi everyone,
I am a novice programmer. I want to develop a Inventory software using
C# 2005, which will run on a LAN environment. There will be three machine
which will store and retrive data from a common database. Which version
of SQL server is appropiate for this ? Is it possible to provide the
networking facilily using MS Access ? I don't know how to configure SQL
Server for this. Please help me.
Thank You.
Question - is there anyone out there running SQL Server on machines in the 2GB-4GB of RAM Range who has quick sec for a ? The documentation I have found for running SQL Server(native 32bit 2 GB RAM max) on 2GB+ machines is a little confusing (to me) if anyone can answer my question that would be great. I am running Ent SQL Server on WIN2K Adv box with 8 Xeons and 4 GB of RAM. I have enabled the /3GB switch in the boot.ini file but have not enabled AWE memory management in SQL. I have set the MAX amount of RAM avialble to my SQL box at 3GB and SQL Serve appears to be using all of the 3 GB under load. Is this the right way to set this or is there another more efficient way? Any answer explenations would be great. Thanks in advance
PS The server is only a SQL box no other apps / servers are running it.
I have a sharepoint on a windows internal database server (some version of sql express 2005). When I run the app, it says that I need SQL server reporting services 2005. I dont think that this component is bundled with "windows internal database server". Is there anyway I can download this component or otherwise make this work?
Seems a bit silly that they would make a tool requiring a component that doesnt need to be installed for sharepoint to run.
Good morning,
I am looking for some first-hand experiences from fellow DBA's where they had SQL Server running on a Win2K3 VM. What sort of issues (or successes) did you find re: resource sharing, swap files, etc? Are there any experiences where using a VM negatively affected your environment?
Thanks so much. What a great forum!
I have a server with a sqlexpress database on it.
It has 'Allow Remote Connections' checked
It has the Browser Service enabled and started utilizing Surface Area Configuration
It has Local and Remote Connections Using both TCP/IP and named Pipes.
I have used http://support.microsoft.com/kb/914277
And one machine can connect to it fine.. .
but others can't?
Any Ideas?
Casey
I am trying to copy DTS Packages and Jobs from two different Servers to one new server. I know I can script the jobs, but they won't run without the DTS packages. I have backed up and restored the MSDB database from Server 1 to my Main server and therefore I have all the jobs from that machine. My problem is how do I get the jobs stored in SERVER 2's MSDB over to my main server without replacing the tables I already have thus losing the jobs and packages that I imported? I have thought about backing up Server 2's MSDB then importing to another database,on the MAIN SERVER and then importing with an append to the 'live" msdb, but I believe the table names are the same and I may end up with duplicate entires. I don't know what problem this will cause. Any suggestions will be great. Thanks in advance
View 1 Replies View RelatedI'm specifically referring to 2008 R2, and TomCat in particular (not sure of the *Nix variant involved), where I was asked if there were any configuration settings on the database side (meaning SQL Server) that can be viewed to show how long a connection to the server, once idle, will last, before it is forcibly closed. I when Googling, but as I'm not very knowledgeable on that topic, I'm not really sure what I'm looking for, and the only results I came across didn't identify what I'm looking for. Of course, it may be that such a config setting doesn't exist, but it would be useful to know how such things are handled.
View 0 Replies View RelatedHi,
How do I get a list of sql server 2005 machines from the network please?
Thanks
Hello,
I have to find out whether my servers have hyperthreading enabled or not???? I am running Windows server 2003 Standard edition on many of my machines. I have to configure the SQL Server, server configuration values according to the Hyperthreading. I know about the CPUcount.exe utility but is there anything else apart from it??????
Hi,
Can I access from App machine (on WinXP) to SQL 2005 Exp machine (on WinXP) ?
I installed SQL 2005 Exp SP2 on Win XP SP2 on my PC. Please consider following environment:
- My PC name: MyPC
- Database name : MyData
- Database role for MyData without any permissions: MyDBRole
- Application role for MyData having all permisions: MyAppRole
- Database user: MyDBUser
- SQL Login: MyLogin (WinXP Limited Login) mapped to MyDBUser
- Application Name to access MyData: MyApp
- Using Application Role MyRole in MyApp
I am accessing data from MyApp within MyLogin. When I want to access from other tool like Management Studio Express, I can not access it and that that is what I want.
Till now it is OK when I am doing all these things on MyPC.
Now question is : Is it possible to connect from other PC (say CleintPC) running Windows XP SP2 use MyApp ?
I think ClientPC should access MyPC through MyPC's MyLogin.
[ I have tried successful simple SQL server connection with MyPC's Guest Login]
Please tell me is it possible ? (Running SQL Express on one machine, Application on second machine and both machine's operationg system is Windows XP)
Thanks
I am new to SQL server 2005
Windows 2003 Server and all client PCs are all on the same network. I installed SQL 2005 std version on windows server 2003 and created a database and tables in it.
I have two users, who want to connect to SQL server 2005 from their PCs (they have windows XP):
These are two things that I need to facilitate:
1) They want to be able to write queries using Query Designer and run from their PCs.
2) I created an MS Access database on my PC, I want to use Access database as a front end to the SQL server database. I will create queries, forms in Access DB, and I want to be able to get the data from SQL server Database to do this. I guess I can use ODBC to connect to tables in SQL server database.
3) I want to connect SQL server to Oracle database, and run oracle stored procedures. I want to load the results of oracle stored procedures into SQL server database tables. I guess I will use IIS for this.
Any suggestions are appreciated, especially with item number 1.
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
For security reasons customer wants to put a SQL database on an encrypted thumb drive (IronKey). Here's the rub though. He wants to be able to work with the data on a workstation. Then, if he takes his laptop out of the office he wants to be able to simply plug that thumb drive into the laptop and fire up SQL on the laptop and use that same database. Procedurally this would work in that the database can be created so that the location is the same from both machine viewpoints, however will the two different SQL instances allow moving the database back and forth like this?
View 9 Replies View RelatedI am currently running a windows 2000 machine with asp, sql server,mail server, ftp server etc all on the same box.The site runs several hundred ecommerce stores. Recently theprocessor utilization has been spiking and I have decided to getanother server and use sql server on one and asp on the other.So now I have a new windows 2003 server that I have setup all of theasp code on. Problem is that when I run the asp code from the newwindows 2003 server it is extremely slow compared to the code runningon the old windows 2000 server which is where the sql server databaseis also located.From everything I have read the best way to optimize your site is touse 2 separate servers one for iis/asp and one for sql server.Am I doing something wrong here or is this normal??Could this possibly be just because the old server is still servingmany requests and is pushing the requests from the new server to theback of the line?Does anyone have any ideas?The syntax I am using to open the connection string is:db_ConnectionString = "Driver=" & db_Driver & ";Server=" & db_Server &";UID=" & db_UIN & ";PWD=" & db_pwd & ";Database=" & db_Database & ";"conn_store.Open db_ConnectionStringwhere db_server is the ip address of the windows 2000 serverIs there a better way to do it across a network??Any help or ideas would be much appreciated.
View 3 Replies View RelatedHello,
Is it possible to send message from SQL Server Express to Applications using it on different machines ?
For example When an important record is inserted or updated, a message appears on Application user's screen.
If yes then how ?
Thanks
Hi all,
What is client network and server network alias in SQL server.
when and why we should use that mean what is the requirement of ALIAS in SQL Server.
Thanks
Hi Folks,
I have a 3 tier app and want to build a dedicated network between the app server and it's DB server. Both machines have an interface on the main network and also an interface on the dedicated network. I want to force all the SQL traffic (or even all traffic if that is the only solution) between the app and it's SQL server onto the dedicated network......?
We're using SQL Server 2000.
Any thoughts would be welcome.
Cheers
I am developing a web application. The database required for the application is in SQL Server 2005 installed machine. In another machine where the IIS 6.0 alone is installed, the compiled application is present. How do I connect web server with SQL Server 2005 so as to access data?
View 4 Replies View RelatedHi! I'm kinda stuck in getting a particularly tricky qurey...
SQL Code:
Original
- SQL Code
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/1995# And #12/31/1995#))
GROUP BY Categories.CategoryName, Products.ProductName;
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, SUM(CCur([ORDER Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSalesFROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [ORDER Details] ON Orders.OrderID = [ORDER Details].OrderID) ON Products.ProductID = [ORDER Details].ProductIDWHERE (((Orders.ShippedDate) BETWEEN #1/1/1995# And #12/31/1995#))GROUP BY Categories.CategoryName, Products.ProductName;
The above SQL statement is from the famous Northwind DB and it is called the "Product Sales for 1995" query...
I have Transactions instead of Orders and TransactionDetails instead of [Order Details]
Now, I want something similar... I want to render Dead Stock and Fast Stock... I'm using ComponentOne's Reporting solution and it pretty much like Access.
Anyways, I want to supply a date for TransactionDetail.TransactionDetailDetail as starting date (ending date is always Today()) and within this range, i want to get a list of products that are sorted ascending on Products.ProductUnitSold. This should return a list of products (grouped by category name) showing SubCategoryName, ProductName, ProductUnitPrice, TotalSold...
If total sold is ascending = Dead Stock
If total sold is descending = Fast Stock
Hi all, i don't know where to post these, and so i posted in here about sql stuff... I want to do a stock inventory for my restaurant, and i don't know how to start building the database, so, I want to ask if anyone knows if they have a database diagram for stock inventory... any kind of database diagram will helps, so I get and Idea how to start... thanks...
View 2 Replies View Relatedi want to make business portal intellegence with sql2000 as my final project but i don't have datasets. i need iventory datasets like northwind or foodmart or middlewest. somebody help me please!!
View 1 Replies View RelatedI am designing a BI solution for a large manufacturer. They process big ticket items. They track their inventory by each piece. I need to know where each item is located, where it came from, when it arrived, has it been processed, ect. Along with that I have to be able to run reports by day/week/month/year that shows current inventory levels at any point in time.
What I arrived at for table design is each item will get a row which will include a column for date and another for Add_Remove. When the item is added to inventory it gets a date and a +1 for Add_Remove. When it is removed it gets a new row with a date and a -1 in the Add_Remove column. I should be able to add the Add_Remove column for the selected rows (by location, type, etc) from the beginning of time to any point in time and get a current total. If I'm reporting by month for the year I should be able to add from time(0) to lastitem in January to get January levels, then the same for each successive month.
The problem I can't figure out the MDX code to do that. My basic data set is as follows:
SELECT NON EMPTY { [Measures].[Total], [Measures].[Add Remove], [Measures].[Inventory Weight] } ON COLUMNS, NON EMPTY { ([Ship From].[Ship From Region].[Ship From Region].ALLMEMBERS * [Ship From].[Ship From].[Ship From].ALLMEMBERS * [Date Received].[Date].[Date].ALLMEMBERS * [Date Received].[Year].[Year].ALLMEMBERS * [Date Received].[Month].[Month].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOMEMBER(@FromDateReceivedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateReceivedMonth, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@InventoryType, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))))
WHERE
( IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ),
IIF( STRTOSET(@InventoryType, CONSTRAINED).Count = 1, STRTOSET(@InventoryType, CONSTRAINED), [Inventory Type].[Inv Type].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any ideas? I've tried adding ordered sets to the query with no success. I've tried calculate measures in the cube with less success. My time dimension includes every day from 1/1/1980 to 12/31/2050.
The one calculated memeber i tried - AGGREGATE ([Date Shipped].[Date].[Date].Item(0):Tail(existing [Date Shipped].[Date].[Date]).Item(0)) took 20 minutes to run in VS Browser and produced an error for every date in the time dimension......
I have this script which pulls in all products from a product inventory with a sku range starting in 9 and ending in BK
select Product.ProductID,Product.Name,Product.SKU,ProductVariant.Inventory
From Product
Inner join ProductVariant
on Product.ProductID=ProductVariant.ProductID
where Product.SKU like '9%BK'
If I wanted to add something to this script where the inventory for all those products in the results above was changed from 0 to 20 how would I add this? Just changing those who had a a SKU starting with 9 and ending in BK
Hello, I'm looking for a little input on this situation.I'm working on an inventory system and was thinking that I'd like tobuild it similiar to the way a bank keeps track of your funds.If you look at at bank statement, for each line item, you'll normallysee at least date/time, description, amount (plus or minus value), andbalance.In my inventory system, the amount and balance would be a count of theitems used or transferred instead of money.Does this sound reasonable? Or is there a better design? I am planningto connect the inventory system to a few different ordering interfaces,and I think it would work well if I can dump transactions into theinventory instead of incrementing and decrementing counts.The only problem I see with this, is if I want to implement the balancepart of it with each record....I can't think of a way to do that currently.I can certainly use a trigger in the database to retrieve the mostrecent transaction and update the balance field for one record insertedat a time, but if a list of transactions is dumped into the inventorysystem at once, I'd have to compensate for that in the trigger.Ideas?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedHi,
I would like to know how does CMS stores content inventory in SQLserver.?
Example-If I create or migrate an application to CMS in sqlDB how does information is stored ?
Regards
T.Gunaseelan
Hello..
I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?
1st Design
PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
PURCHASE TABLE
PurchaseID
Date
...etc...
PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...
and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE
2nd Design
PRODUCT TABLE
ItemID
ItemName
Price
...etc...
INVENTORY TABLE
ItemID
QtyBegin
...etc...
SALES TABLE
SalesID
Date
...etc...
SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...
and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:
QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ........
And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.
According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?
Thanks a lot.
I have to caculate the fifo inventory aging from purchase date, for example I have one item with 100 units in stock, and purchase this month 20 units, and 200 units three month ago, this item has 20 units this month and 80 units from 3 month ago.
Data is as follows:
<<<<< ORIGINAL DATA >>>>>>>>>>>>>>>>
Code:
Code StockPO Date PO_Qty
ao0-123100OC001010-08-2014 8
ao0-123100OC001110-08-2014 4
ao0-123100OC002011-10-2014 50
ao0-123100OC002111-10-2014 2
ao0-123100OC002211-10-2014 20
ao0-123100OC003012-11-2014 10
ao0-123100OC003112-11-2014 1
ao0-123100OC003212-11-2014 20
And the reuslt must be something like this:
<<<<< ORIGINAL DATA >>>>>>>>>>>>>>>> <<<<< CALCULATED DATA >>>>>>
Code:
Code Stock PO Date PO_QtyDays_Old Qty_From_PO Residue
ao0-123100OC001010-08-2014 8 135 8 92
ao0-123100OC001110-08-2014 4 135 4 88
ao0-123100OC002011-10-2014 50 73 50 38
ao0-123100OC002111-10-2014 2 73 2 36
ao0-123100OC002211-10-2014 20 73 20 16
ao0-123100OC003012-11-2014 10 41 10 6
ao0-123100OC003112-11-2014 1 41 1 5
ao0-123100OC003212-11-2014 20 41 5 0
I need to built a select to make this calculation, not using cursors, and build a view, only a basic select.
I am developing an inventory database in SQL Server. I realize there aremany commercial (as well as some non-commercial) inventory offerings, but myclient has specific requirements that would necessitate significantcustomization of any off-the-shelf application. In the end, we decided itwould be more feasible to build one to our specifications.What I am looking for are a list of best practices/recommendations for thearchitecture and modeling of an inventory database. This inventoryapplication will be for managing a wholesale products operation. It mustmanage purchase orders to the manufacturer of the products, sales invoicesfor the retail customers of the products, as well as manage product stocklevels in the warehouse. We will need a number of reports, including:1. What products are in stock.2. What products are on order from the manufacturer.3. What products are needed to fulfill outstanding sales.4. Warehouse operations (e.g., receipt of delivery, inspection, add toinventory, pull from inventory, packing lists, invoices, return toinventory, etc.)5. Sales analysis (e.g., product velocity, sales by associate, etc.)My main question is regarding the managing of products in stock. As I see itthere are three ways of accomplishing this:1. Track only transactions (products received, products shipped, etc.) andcalculate the stock based on the sum of all transactions. But as transactionvolume accumulates over time, this would get very slow and cumbersome.2. Have a table of "units in stock" and add and subtract to it astransactions occur. This has the advantage of always providing an instantsnapshot of inventory levels. But it makes it more difficult to managechanges or corrections to a transaction once it has been entered.3. A combination of the above two concepts. Choose a point in time (forexample, when the warehouse inventory is audited) and capture that in a(semi) "static" table. After that, transactions are summed as needed againstthose static numbers. The next time the warehouse is audited, the "static"table is updated and all prior transactions are archived. New transactionsare once again summed against the most recent audit numbers. Of course, thiswould mean halting operations at the time of the audit (probably not anissue for my client's business).Recommendations? Suggestions? Comments?(Btw, I've tried to find this info in books and online, but so far have notfound anything that makes specific inventory system design recommendations.If you know of a good reference, please let me know.)Thanks in advance!Michael
View 4 Replies View RelatedI would like to know how I could code so that I can get the ending inventory of each day. The following is my data
CREATE TABLE #TEMP
(
DATERCVD DATETIME
,ACCTID INT
,DATESENT DATETIME
)
INSERT INTO #TEMP VALUES ('01/01/2015', 1 , '01/05/2015')
[Code] ....
So my query results should be something like this:
InventoryDate Received Sent Ending
1/1/2015 4 1 3
1/2/2015 5 0 8
1/3/2015 3 2 9
1/4/2015 0 4 5
1/5/2015 0 5 0
TOTAL 12 12
Hi,
I'm trying to deploy Reporting Services on an Internet-facing web server using a custom security extension (forms authentication). We will be putting Report Manager on the Web Server outside of the firewall, and Report Server on another machine inside the firewall. When testing the solution in a development environment with Report Manager and Report Server on the same box, the solution works fine.
When testing it using the Internet-facing environment described above, it almost works but there seems to be an issue with the authentication cookie that is generated. Here's what happens:
1. User enters credentials on UILogon.aspx and submits form.
2. In the code-behind, LogonUser() is called through a web service proxy and the authentication cookie is returned successfully. The code used here to set the cookie is the same code used in the Forms Authentication sample that ships with RS2005.
3. The user is redirected to Folder.aspx, and the request hangs for a while, and eventually kicks you back out to UILogon.aspx, as if it lost the authentication cookie. When doing a trace on the HTTP header, the cookie is still there.
Is there anything special I need to do to make the Report Server "see" the cookie when I have Report Manager and Report Server on different machines?
Any help would be much appreciated!
Mike