I issued a query request to sql server with "FOR XML AUTO, type, elements, root('root')" appended to the end of my query. I issue a da.Fill(ds) [to a datset] which works without any problems. Now that I have the data, I want to transform it into HTML. So I used: Dim XMLString As String = ds.GetXml() that converts my data into a str...
Hi I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but could not find one. I even tried to remove the "MOVE .LOG" from restore query but got an error message "could not find .LOG file use WI...
Hi, So I am a first time user here, and still relatively new to SQL. I am attempting to take 2 tables, and join them in a sense. In Table1 I have the data: House_Key ---Other Fields--- Customer_ID House_Key is not unique, and as such I can have multiple Customer_IDs per household. House_Key is a numerical code, and Customer_ID i...
Looking for an elegant way to workaround this... DECLARE @ZIP INT SET @ZIP = 55555 IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) PRINT 'All people of type 1 have the same zip!' ELSE PRINT 'Not All people of type 1 have the same zip!' The issue is that, if (SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1) returns no reco...
I'm looking at sprocs right now that seem to follow the behavior demonstrated below DECLARE @tablevar TABLE ( FIELD1 int, FIELD2 int, FIELD3 varchar(50), -- etc ) INSERT INTO @tablevar ( FIELD1, FIELD2, FIELD3, -- etc ) SELECT FIELD1, FIELD2, FIELD3, -- etc FROM TableA Inner Join TableB on TableA.F...
When adding an item in my database, I need it to auto-determine the value for the field DisplayOrder. Identity (auto-increment) would be an ideal solution, but I need to be able to programmatically change (UPDATE) the values of the DisplayOrder column, and Identity doesn't seem to allow that. For the moment, I use this code: CREATE PROC...
I am beginning to design a new laboratory test data management system with many (about 30) test stations. The system must be able to collect data offline in the event of a network outage. Each station would keep an up-to-date, read-only copy of test structures (specifications, entity types, business rules/workflows, etc) but not test d...
I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005. When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same...
I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the DELETE of records from a table with millions of rows (big_table has only 3 columns), but now the DELETE execution time is even longer! (1 hour versus 13 min for example) I have a relationship between to tables, and the col...
I'm trying to implement sqldependency in my vb.net application as per: http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/ I'm having problems with the involking code, when a notification is recieved the application locks up with no errors. (When MyOnChanged is called via the deligate) H...
I read an article that says: The (x-min,y-min) and (x-max,y-max) coordinates determine the placement and dimensions of the bounding box. The space outside of the bounding box is treated as a single cell that is numbered 0. I took this to mean that everything "outside" the index is actually indexed in a special place, so i...
Pick your version of SQL Server... if it has changed between version please specify if you know. Build a table Add a non-clustered index on 1 or more columns. If I could dump a leaf block, what would I find as the pointer to the row in the table with the data? ...
The following script I have tried to run in MS server management studio and this is work perfectly fine. SELECT ucb.UserFirstName, ucb.UserLastName, ucb.userid, c.caseid FROM Cases c LEFT JOIN Users ucb ON (c.ConfirmedBy=ucb.UserID) JOIN RealtorStaff rs ON c.realtorstaffid=rs.realtorstaffid WHERE c.ConfirmedBy is not null AND...
If I have two table of data. One has a clustered index CINDEX, the other is a heap HEAP. Both also have a non-clustered index on the same column - SEARCHCOL Assume my clustered index columns are the same size as a rowid and therefore the depth of both of the non-clustered index is the same. Which would take fewer I/O's to fetch a tabl...
I have an important problem running ETL Process in production environment. While my ETL is running, the OLAP Server turns extremely slowly, I think this is because the ETL is updating several existing rows in the fact table and adding new ones. I tried to avoid this problem having a whole data base replication and ETL writes in DB1 and O...
I have a legacy web-site that I am maintaining (built mostly in classic ASP with vbscript). I am making some modifications to it, and got tired of waiting 5 minutes everytime I had to go through one of the pages that was loading 99000 records from the database before displaying the first 20 of them, so I rewrote the code as: sSQL = "WIT...
Does anyone know how can I do a count in SQL Server based on condition. Example: How can I do a column count for records with name 'system', and total caseid records in the table?? Customer table userid caseid name 1 100 alan 1 101 alan 1 102 amy 1 103 system 1 ...
Scenario Web server running an ASP.NET site that's connected to a sql server instance for all the web related DB needs (products, pricing, users, etc.)... The companies accounting, inventory control(FIFO, etc.) and whatnot are mainly done on another system which uses a different SQL server...much more complex, for obvious reasons. Wha...
I have a stored procedure which gets data from another database on the same server. I will not be hard-coding which database to use, instead this will be configurable. Is this possible without dynamic sql? The best solution I could come up with so far is to first run a stored procedure which uses dynamic sql to generate a bunch of view...
How to migrate sql server 2008 to my sql 5.0 programmatically? any link? EDit:- Sorry for not asking question properly,See we will be getting sql server backup from client every night,and we have to load it to my sql every night.so we my solution is t0 try transtional replication.so i am trying heterogenous subscriber as my sql and prov...