I Need To Know Where Mssql Server Stores Its Data.
Jan 19, 2008Hello,
I need to learn where mssql server stores its data. I knew it was in
Program filesCommon files
But I cant find.. Its urgent pls..
Thanx
ADVERTISEMENT
Should I Have A Table That Stores A Subset Of Data From Other Tables???
Mar 21, 2007DB design question
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
What is better practice and what is faster?
Execute SQL Task With Full Result Set Stores Data As What?
Oct 22, 2007Howdy all,
I have an Execute SQL Task that may return a result set. If it returns a result set, I'd like to log a failure in my package with the results visible.
I have logging turned on and that's working great. I've read about assigning results to a user variable of type Object and that's great. I can shred my results, thanks Jamie, with a Foreach loop no problem. Within that loop, I've got some VB that manipulates the values and will call Dts.Events.FireError as appropriate. However, VB is frowned upon here so my boss has asked that I push the VB logic into a Control Flow item.
I've built custom components already so I've got some familiarity with the process. Where I'm stuck at is figuring out _what_ the actual object type is in my code. The Connection manager is Native OLE DBSQL Native client. My Execute SQL Task uses a connection type of OLE DB with a Full result set. Results are stored in a variable named ErrorResultSet. Within the Execute method, I currently have this code set up in an attempt to pick apart the object and discover the available methods.
Code Block
Variables _variableCollection = null;
if (variableDispenser.Contains("ErrorResultSet"))
{
variableDispenser.LockForRead("ErrorResultSet");
}
variableDispenser.GetVariables(ref _variableCollection);
// Iterate through the variables that we were
// able to lock. Assigning values to entities as
// available.
foreach (Variable _en in _variableCollection)
{
switch (_en.Name)
{
case "ErrorResultSet":
Object _rs = _en.Value;
System.Type _type;
_type = _rs.GetType();
System.Data.DataSet _realResults;
_realResults = _rs as System.Data.DataSet;
// My expectation is that the cast of _realResults would
// not fail.
break;
}
}
// unlock before we go
_variableCollection.Unlock();
return DTSExecResult.Success;
At this point, my assumption is that the unboxed type of the recordset is not in the System.Data.DataSet inheritance chain as the cast failed. Anyone have insight into what it is? I can't seem to get any hits on google for what it's using behind the scenes in the Foreach ADO Enumerator.
Beyond the immediate question, anyone have thoughts on how else I can solve the problem? I had thought perhaps the task could raise an event if it returned rows but it didn't seem to have that functionality. Even if that had worked, telling the logging provider to capture the result set into the log might have been too much for native functionality. Another option I was thinking about would be to continue using the Enumerator and my custom component is a pure rewrite of the current Script task with the obvious downside being that I'd lose the generic-ness I was hoping to get with being able to hit my dataset.
Spaced Used By Col In Index Page Is Total Length Of Col Or Data It Stores In It
Apr 9, 2015i have one col in a nonclusted index which is bigint 8 bytes follwoing result shows min_record_size_in_bytes and max_record_size_in_bytes is 20 that is 12+8 page size is 8 kB. does that mean a record will use 20 bytes in a page or it will only use the space equalent to data stored in the col.what i have seen is page count is same weather i put some data in the col or null
select * From sys.dm_db_index_physical_stats(9,343,null,null,'DETAILED')
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_
levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_
space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_
record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_
record_countcompressed_page_count
999208304101HEAPIN_ROW_DATA102.9527559055118116253.187540514.196627131208358500016516516500
9992083041141NONCLUSTERED INDEXIN_ROW_DATA202553.21699.3544600938967585000202020NULL0
9992083041141NONCLUSTERED INDEXIN_ROW_DATA2101115.510254509513221600262626NULL0
Multiple Stores Orders To Sink Up With Main Server
May 4, 2007Hello,
Say there are 10 retail store locations that need to push their daily sales to the main server at the head office.
What techniques are used to accomplish this? I'd imagine the transaction rows would have to be some sort of a GUID.
What options are there? What techniques are used to ensure each stores orders have indeed been pushed to the main server?
Stores In Australia That Sell SQL Server 2005 Developer Edition
Oct 25, 2007Does anyone know which stores in Australia sell SQL Server 2005 Developer Edition. I want to buy it from a store not online, thanks...
View 1 Replies View RelatedHow Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
Nov 13, 2007Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
Importing Data From Btrieve To Mssql Server
Jan 18, 2007simon titi writes "I have a huge data in btrieve. the data includes images.
now I am migrating to sqlserver. Can I used the data in btrieve?
How can I import it to mssql?
Thank you!
Simon.t."
Data Space Used By A Table In MSSQL Server 2005
Apr 4, 2007How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?
Extracting MS Excel Data In MSSQL Server 2005 Express
Aug 26, 2006Is there a way of extracting data from MS Excel using MS SQL Server 2005 Express?
View 1 Replies View RelatedServer Configuration For MSSQL 2000 And MSSQL 2005
Sep 6, 2006Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedStoring Data Lager Than 8000 Charactors In Mssql Server 2000
Feb 20, 2007HI
i want to save data larger than 8000 charactors in a colomn
is there any way to do it in mssql server 2000 desktop addition
plz help me
Thanks in advance
Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?
Oct 26, 1999Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
Stores Procedure
May 5, 2005I am trying to select the HdwId value of a table named "tblHdw" and trying to insert it into the HwdId field in a table named "tblLoc".
If you have any suggestions?
Thanks,
MattyPee
Use VB DLL In A Stores Procedure?
Feb 14, 1999I read in a technical journal (somewhere) that it is possible to create a ActiveX DLL in Visual Basic and use the DLL in a stored procedure. There were some limitations (because of stored procedures) but this is still a very powerful feature. Has anyone ever heard of this or read about this? Is there s source for explanation and examples?
Thanks, Rich.
DTS Import Stores The Last {LF} In Table.
Sep 7, 2006Hi i have a csv file where the last column usually happens to be null. So my file is like this:
1,2,3,,{LF}
4,5,6,,{LF}
7,8,9,,{LF}
{CR}
I run the dts export from csv file to db and it doesnt save the last row (column5) in the table us null. instead it stores the {LF} character. Ascii =10. How can i resove this problem.
the csv comes from a third party application. the DTS needs to un automatically and somehow resolve that last problem on its own. but how??? is there a code that can trim the last character in the last line to delete the last {LF} or some query in DTS that can achive this.....
Table Which Stores Report Name
Feb 5, 2008Hi All,
Please let me know within Reporting server database which table stores the actual report name,keywords and author of the report.
I want to search the report based on the report name using my own custom code.It will be helpfull if i get the table name so that i can fire select query.
Thanks in advance,
Prabhanjana
Returing ADO Recordset From Stores Procedure
Nov 24, 2003I have a Stored Procedure that I am calling from ADO 2.7 in Visual Basic. The process works fine when the stored procedure has records to return. However , when the recordset is return emtpy ADO does not recognize the recordset as being open and empty. For example:
VB Code to open recordset:
Set rstClass1 = New ADODB.Recordset
With rstClass1
.CursorLocation = adUseServer
Set .ActiveConnection = DBConn
.Source = strRecordSource
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
If Not .EOF Or Not .BOF Then
The program bombs on the If Not .EOF statement with Error 3704 -
"Operation is not allowed when the object is closed.
When I run the Stored Procedure In QA using an ID that I know will NOT return any records the result window returns nothing. I noticed that on some of my other SP's the result window will at least return the column titles. These SP's also do not produce the 3704 error in ADO.
Is there a command or a certain method to construct my SP so that is will return enough for ADO to know that the recordset did open its just empty?
The SP is ugly looking but I will post if need be.
Date Format SSRS Stores In DB
Feb 25, 2008
Hi,
I have report scheduled in a SSRS. SSRS creates a job and from MSDB.DBO.SysJobActivity the
Next_Scheduled_Run_Date column provides the next execution date of the report.
Can any one tell me what is the date format that SSRS stores is it a getdate() or a UTC Date??
Pass Sorting Parameter To Stores Procedure
Feb 23, 2004I used Datagrid to show "Title", "Location" and "Date", It works very well.
I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date",
my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.
Here is my stores procedure:
CREATE Procedure JobSearch
(
@Search varchar(150),
@Sort varchar(50)
)
AS
SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate
FROM
Job
WHERE
JobTitle LIKE '%' + @Search + '%'
OR
JobKeywords LIKE '%' + @Search + '%'
IF @Sort = "Title"
ORDER BY JobTitle
IF @Sort = "Location"
ORDER BY JobLocationState, JobLocationCity
IF @Sort = "PostDate"
ORDER BY PostDate DESC
When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ".
Who has experience about stores procedure, please help me to correct this error.
Using A Variable That Stores The Query String For WHERE Condition
Feb 20, 2001Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View 2 Replies View RelatedStores Procedure - Must Declare Scalar Variable
Apr 7, 2015I am creating a SP below and it keep saying must declare scalar variable @comments,I have already declared @comments varchar (255)
error---> (Msg 137, Level 15, State 2, Procedure CheckPatientComments, Line 26
Must declare the scalar variable "@commnets".
)
quote:
CREATE PROCEDURE [dbo].[CheckPatientComments] (@enc_id varchar(36) OUTPUT, @data_ind CHAR(1) OUTPUT)
AS
BEGIN
DECLARE
@comments varchar(255)
[code]....
Help With 2 Datetime Fields-1 Stores Date, The Other Time
Jun 9, 2006Hi,We have a lame app that uses 2 datetime(8) fields, 1 stores the date, theother the time.example query:select aud_dt, aud_tmfrom ordersresults:aud_dt aud_tm2006-06-08 00:00:00.000 1900-01-01 12:32:26.287I'm trying to create a query that give me records from the current date inthe past hour.Here's a script that gives me todays date but I cannot figure out the time:select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,getdate())from orderswhere (datediff(d,aud_dt,getdate()) = 0)results:aud_dt aud_tmdatediff(0=today) timediff (since 1900-01-01)2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 055978689I added this next part to the above query but it does not work since thedate/time is from 1900-01-01and (datediff(mi, aud_tm, getdate()) <= 60)Thanks for any help.
View 2 Replies View RelatedActivated Stores Procedure And Batch Processing
Aug 2, 2006Hi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
Transact SQL :: Inventory Difference Between Rows Between Stores
May 8, 2015how to measure a change in inventory over various stores. Â My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. Â When the new stock arrives it is added to the "OnHand" count. Â I want to measure the delta change per day,per store. Â I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
               Â
TimeStamp Store
OnHand OnOrder
2015/04/22 18 Â Â 1 - Concord
12
   0
2015/04/23 11 Â Â 1 - Concord
11
Â
[code]....
Reporting Services :: Connect 2 Stores Procedures In Report Builder
Aug 1, 2015I have created two stored procedures.....
1) atmWonOpportunities_Users
2) atmWonOpportunities_Data (startdate and enddate parameters)
In (1) I created a filtered list of all user names associated with a certain business unit and saved it to a temp table.
in (2) I create a filtered query listing all won opportunities during a period of time (date start and date end paramters)
In the report, I have 3 parameters, startdate, enddate and username (dropdown list).
When I run the report I select a startdate, enddate and username. I click the View Report button and get the same results for any one of the usernames I pick which is wrong.
Help With MSSQL Data Types
Dec 6, 2006I currently run our company website using MS Access and ASP. I am trying to upgrade the database to MSSQL Server 2000. Unfortunately I'm not finding it that straight forward. I am trying to learn MSSQL but the learning curve is a bit steep.
I have a column in my MSSQL db called tbl_FULL which contains a full description of the product along with html formatting for line breaks, paragraphs and special characters (ie •).
I have set the data type to varchar 1800. All seemed to be well until I tried to enter new data, but unfortunately it only displays the first 50 or so characters.
Can anyone tell me what I am doing wrong?
Image Data From MSSQL 6.5
Jan 20, 2008I would like to use bcp and transfer image data from MS-SQL Server 6.5 to MS SQL Server 2005.
1. Can I use bcp successfully? if so should I mention any option to copy the BLOB?
2. Is there any reliable method to move tables with BLOBs
Regards,
/ash
Data Type That Accept . For MSSQL
Sep 24, 2004Hi,
I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?
I'm using MSSQL Server 2000.
Please advice.
Extracting Data From MSSQL To MSExcel
Jul 13, 2004I recall something about Analysis Services (SQL server tool) that is able to extract data from the DB tables (select multiple tables if one wishes (and to link those tables up ??!?)) then extracted to MS Excel. It has been a while since the course was last taught, I'm not at all sure how the steps were, or how Excel were able to display them. Would someone advice or refer to a tutorial if possible?
Much thanks!
MSSQL DTS Newbie - Data Transfer Between Two DBs
May 15, 2006I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.
I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.
What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.
Any help will be greatly appreciated!
What Must Have Clients To Work With Mssql Data
Mar 10, 2004reading help i know that client for working with mssql remote db must have installed oledbprovider for XXX
i always use ole db provider for ODBC
, this ole db provider is installed with mssql, but what exactly must to have the clients which are not familiar with this ole bd provider, what files ?
Data Replication From MSSQL To MYSQL
Jul 20, 2005Hi all.I need to set a oneway replication of some data from MSSQL (running underWindows 2000 server) to MYSQL (running under Linux).Do you have some ideas to solve the problem?Thank youLuca
View 1 Replies View Related