SQL Server 2012 :: Query To Fill RAM?
Apr 28, 2014
I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?
View 8 Replies
ADVERTISEMENT
Jan 24, 2015
As the title says I re-indexed all of my databases using the wrong fill factor. Instead of using 90% as the fill factor I misunderstood and set this at 10%. So I believe my databases are now packed with a ton of unused space. The DB sizes should be about 5-6 GB but have since grown to 20-40GB. I am very new to SQL administration and don't know of a safe way to remove this unused space so that my databases return to their normal sizes. The databases do not grow very much at all so the free space is not really that necessary.
View 9 Replies
View Related
Jan 21, 2013
I have three queries that I use to determine what the party type of a person.
They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.
I would like to fill a temp table with the results so I can use it in reporting.
Here is my code:
Code:
SELECTvap.partyID, relationship
INTO#Relationship
SELECTvap.partyID, Relationship = 'Child'
FROMVolunteerActivityParty vap JOIN
VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
[code]....
View 5 Replies
View Related
Jan 16, 2007
Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.
View 2 Replies
View Related
Mar 6, 2007
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
View 5 Replies
View Related
Mar 15, 2007
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
View 1 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Jun 14, 2015
I am about to rebuild all my indexes on a database that is very heavily fragmented. In looking at the report, seems that 80% or more tables are 90%+ fragmented.
My understanding is that fill value value is used for performance reasons. Our shiny new backend SAN is 100% SSD. If solid state can provide a sub-millisecond response, is fill factor still necessary at the cost of additional space being used used?
View 0 Replies
View Related
Jul 8, 2013
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
View 1 Replies
View Related
Feb 7, 2014
Below is what I have so far Also I am getting other FRID besides 0 as well.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V
On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C
[Code] ....
View 4 Replies
View Related
Nov 21, 2013
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies
View Related
Dec 18, 2013
I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
[code]....
View 5 Replies
View Related
Nov 13, 2013
I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.
View 9 Replies
View Related
Feb 4, 2014
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2.
Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause.
@p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401.
if object_id('tempdb..#tbl') is not null
drop table #tbl;
create table #tbl
(
[No] nvarchar(20)
,[Description1] nvarchar(250)
[Code] ....
View 9 Replies
View Related
Feb 14, 2014
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
View 5 Replies
View Related
Feb 17, 2014
i've following data in a recursive query:
Par_IdIDABCDEFLevel
Null022646000100022646
02264602242632000002264622426
02264602253232100002264622532
02264602254082000002264622540
02264602255751100002264622557
[code]....
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
View 6 Replies
View Related
Aug 18, 2014
trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
[code].....
View 3 Replies
View Related
Dec 1, 2014
I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).
declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
[Code] ....
--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL
[Code] ....
View 8 Replies
View Related
Jan 21, 2015
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
[code].....
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),
[Code] .....
View 2 Replies
View Related
Apr 15, 2015
What I am trying to recreate is:
<value version="5" type="database">
<name>master</name>
<server>servername</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
[Code] ....
with this query:
SELECT
'version="5" type="database"' AS 'value',
'master' AS 'name',
LTRIM(RTRIM(([Server Name]))) AS 'server',
'True' AS 'integratedSecurity',
[Code] ....
BUt my output is not correct, it is creating this:
<value>
<value>version="5" type="database"</value>
<name>master</name>
<server>ServerName</server>
<integratedSecurity>True</integratedSecurity>
[Code] .....
So my question is how to I get <value>version="5" type="database"</value> as the first 'value' node?
I've tried multiple ways, but no success.
View 2 Replies
View Related
Apr 23, 2015
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
View 2 Replies
View Related
Jul 2, 2015
I am creating a dynamic query where i am appending a where clause something like -
IF (@CurCaptureDate IS NOT NULL)
SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate
PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime
but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.
i am not able to use convert here with these quotes.
i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )
but it makes it to -
AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.
View 4 Replies
View Related
Jul 9, 2015
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
View 9 Replies
View Related
Aug 5, 2015
I want to know if it is possible to do the following;
I have patients that may have been transferred to different locations(see below)
location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02
i know i can do a min max to get my first and last values. I want to label the columns something like
1st location, 2nd location, 3rd location, 4th location, discharge location.
there could be 1 location or 20.
is there a way to do this?
i can do a temporary table and then an update query to add the values to those columns.
just not sure how to get the next value and then the next etc.
View 9 Replies
View Related
Sep 21, 2015
I have an issue with Sql Server 2012 and Datastage which extracts the table(just select * from table) and loads into one file without any transformation in between. Source table has around 45million rows. Datastage Process extracts upto 22million rows and then it stalls forever(running state). I had investigated into the Database and i could see the transaction runs on database for some time(runnable and suspended state) for sometime and it disappears suddenly. I am surprised to see Datastage Process still shows rows are being extracted even after transaction is disappears and Datastage process up to 22million rows and then do nothing.
View 3 Replies
View Related
Sep 28, 2015
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
[Code] ....
View 6 Replies
View Related
Nov 22, 2013
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
View 3 Replies
View Related
Nov 27, 2013
I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.
View 5 Replies
View Related
Dec 17, 2013
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
View 2 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Feb 11, 2014
I have database tables for
Stores
StoreId, Name
Products
ProductId, Name
Transactions
TransactionId, StoreId, ProductId
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
View 9 Replies
View Related
Feb 12, 2014
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------
I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),
[Code] ....
View 4 Replies
View Related
