Help With TSQL Statement To Check For Table Size
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated
select Name=left(c.name,20),
c.prec, scale = ISNULL(c.scale,0),
t.name from syscolumns c inner join systypes
t on c.xtype = t.xtype where id in
(select id from sysobjects where name = 'mh_demographic2_')
order by c.colid COMPUTE sum(c.prec)
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL Statement Extracting Data From One Table Through Another Table
Hi, I have 2 tables, MembersTemp and Organisations I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this. Initiallt I only have the ExecID for the MembersTemp table MembersType table: ExecID 3013 OrganisationID 4550 Organisation table: ID 4550 (PK) Name "Microboff" Any ideas??
View Replies !
Check For Column Existance Before Alter Table Statement
Hello all, I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Check Actual DB Size
Hi, How do I check what is the actual DB size that is currently being use? e.g. I set 10 GB as initial size. A few days and a few transactions later, how can I know how much was used since it is still under 10GB and checking the physical DB file will not tell me anything.
View Replies !
I Need Help With This Tsql Statement
Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this. SELECT 'Quarter 1' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 2' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 3' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 4' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_cOMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE Order By 'QTR' asc
View Replies !
TSQL - WITH Statement
Hi guys, I need help with this one... Iam Trying to understand how to use the statement WITH I am running the code below, but getting error. note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server) Thanks in advance, Aldo. Code Snippet WITH MyCTE (FILTER, SORTGROUP) AS ( SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS ) SELECT * FROM MyCTE AS CTE_01; Error Messages: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'.
View Replies !
Check The Size Of All Tables In My Database
Hi I am trying to check the size of each table in my database? SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE I can't for the lif of me figure out how this is done. Any help would be greatly appreciated Kind Regards Carel Greaves
View Replies !
How Can I Use Variables In This TSQL Statement
Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg
View Replies !
TSQL From An Access SQL Statement
Good morning one and all, I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql. Any and all help appreciated, Thanx Gurmi
View Replies !
What Is Wrong With This TSQL Statement
hello, I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null --code EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1)))) AND c.column_name = ''to_be_transferred'' AND t.table_type = ''BASE TABLE'' ) BEGIN SELECT * FROM ? WHERE to_be_transferred IS NOT NULL END' But I am getting the following error Msg 207, Level 16, State 3, Line 12 Invalid column name 'to_be_transferred' I thought the if statement filters all those tables with the specified column and do select statement fot those tables only what did I do wrong, any suggestionm? Thanks
View Replies !
Tsql Case Statement
Hi, Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table. And below is the syntax. Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(a.net_sale_Dollars) as sale_TY , sum ( b.net_sale_dollars ) as sale_LY , a.division_name, a.department_number fromFact 1 as a ,Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The current table from this query is showing like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded. The structure of table that I want to create must look like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 2 1 15 0 ABC 2 >>>> want to put 0 value where only one side ( current or parrallel period) has sales info. So, I'm thinking the case statement like: Case statement logic like: ------------------------------------------------------------ if a. dept not exist in b.dept then Sale TY -> a.net_sale_dollars Sale LY -> 0 if b.dept not exist in a.dept then sale TY -> 0 sale LY -> b. net_sale_dollars ------------------------------------------------------------- below is the syntax which doesn't work (it's wrong): Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(case when a.department_number = b.department_number then a.net_sale_dollars else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end) as sale_TY , sum ( case when b.department_number =a.department_number then b.net_sale_dollars else case when a.department_number NOT IN (b.department_number) as sale_LY, a.division_name, a.department_number fromFact 1 as a Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key , ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Is it possible to create these kind of structure? Please give me some comments. Thanks.
View Replies !
TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View Replies !
How Check File Size For 2005 Express Database
When checking the database size for an SQL Server 2005 Express database, I notice that the Windows file size is around 103 MB, while "Properties" in Management Studio Express says it's 156 MB. I restarted the SQL Server service before checking the Windows size to make sure everything is written to disk. Which one is correct? For SQL Server 2000, I noticed that the Windows file size corresponds to the database size in Taskpad. (Select * from sysfiles gives some totally different value.)
View Replies !
How Can I Check File Size Equal Zero Or Not With Scrip Task ?
Dear all, Could you tell me which code of vb scrip that i can check file size as follow ? Dim FSO, Fyl Set FSO = CreateObject("Scripting.FileSystemObject") Set Fyl = CreateObject("File") If FSO.FileExists("C: emplcbseg.log") Then Set Fyl = FSO.GetFile("C: emplcbseg.log") If Fyl.Size > 0 Then Main = DTSStepScriptResult_ExecuteTask Else Main = DTSStepScriptResult_DontExecuteTask End If End if Thanks Best regards, Kusuma
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
Please Help Quick Statement Check
Hi, I was hoping someone could confirm the following about thefollowing number 2 SQL query:1. This extracts the data required given that there is a recording tomatch the records2. This should, but im not convinced extract all the remaining recordsthat do not have a matched recording, but fulfil the rest of thecriteria. If not please help...3. As the two querys produce the same layout of tables if I did a UNIONALL, this should work shouldnt it?Query 1:------------SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai,leads l,recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDl.last_call_date between #04/24/2006 12:00 AM# and #04/25/200611:59 PM# ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaignORDER BY ai.agent_login, ai.entry_dateQuery 2.-------------SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,'' as AgreementRecordingFileFROM agent_input ai,leads l,recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDl.last_call_date between #04/24/2006 12:00 AM# and #04/25/200611:59 PM# ANDcount(SELECT rec.*WHERE rec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign) < 1ORDER BY ai.agent_login, ai.entry_dateThanks in advance for any help, its greatly appreciated.David
View Replies !
Creating A Common Table Expression--temporary Table--using TSQL???
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View Replies !
Syntax Check Needed On Case Statement
Hello experts! I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using.... Code Snippet WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore) Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected. Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter. Any help is appreciated.
View Replies !
The INSERT Statement Conflicted With The CHECK Constraint
Hi, I am new to MS SQL Server; as I know Access, MYSQL. I made a form though which I want to insert data to SQL SERVER 2005 Database but i during submission I get the below problem, can any one help. Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]The INSERT statement conflicted with the CHECK constraint "SSMA_CC$Bcast$msgHTML$disallow_zero_length". The conflict occurred in database "x485i", table "dbo.Bcast", column 'msgHTML'. /html/n_.asp, line 193 Best Regards, Imran
View Replies !
SQL Statement With More Than 64k In Size
Hi guys, We have an asp which builds the query string in more than 64k in size. we can't execute this query in Sql query analyzer. Can anyone suggest any workaround for this problem. It is an urgent requiremet and I would appreciate your valuable help. Thanks in Advance. _Jesica
View Replies !
Table Size And Database Size
Hi, i use this script that show me the size of each table and do the sum of all the table size. SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name] the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from? THX
View Replies !
How To Do-table 1 That Check Table 2 And Adding Missing Dates
can sql server do this ? table 1 that check table 2 and adding missing dates this my employee table table 1 table Employee on work ------------------------ empid basedate shift ---------------------------- 12345678 01/04/2007 1 12345678 02/04/2007 1 12345678 03/04/2007 1 12345678 04/04/2007 1 12345678 05/04/2007 1 12345678 06/04/2007 1 12345678 07/04/2007 1 12345678 08/04/2007 1 12345678 09/04/2007 1 12345678 10/04/2007 1 98765432 20/04/2007 1 98765432 21/04/2007 3 98765432 22/04/2007 3 98765432 23/04/2007 5 98765432 25/04/2007 4 98765432 26/04/2007 4 98765432 27/04/2007 4 98765432 28/04/2007 4 98765432 30/04/2007 4 ----------------------------------------------------------------------------------- and i need to see the missing dates lkie this in table 2 ------------------------------------------------------ table 2 (adding missing dates with zero 0) table Employee_all_month ------------------------ empid basedate shift ---------------------------- 12345678 01/04/2007 1 12345678 02/04/2007 1 12345678 03/04/2007 1 12345678 04/04/2007 1 12345678 05/04/2007 1 12345678 06/04/2007 1 12345678 07/04/2007 1 12345678 08/04/2007 1 12345678 09/04/2007 1 12345678 10/04/2007 1 12345678 11/04/2007 0 12345678 12/04/2007 0 12345678 13/04/2007 0 12345678 14/04/2007 0 12345678 15/04/2007 0 12345678 16/04/2007 0 12345678 17/04/2007 0 12345678 18/04/2007 0 12345678 19/04/2007 0 12345678 20/04/2007 0 .................................and adding missing dates with zero 0 until the end of the month ................................. 12345678 31/04/2007 0 98765432 01/04/2007 0 98765432 02/04/2007 0 98765432 03/04/2007 0 98765432 04/04/2007 0 98765432 05/04/2007 0 98765432 06/04/2007 0 98765432 07/04/2007 0 98765432 08/04/2007 0 98765432 09/04/2007 0 ..............................and adding missing dates with zero 0 only whre no dates in this month ....................... 98765432 20/04/2007 1 98765432 21/04/2007 3 98765432 22/04/2007 3 98765432 23/04/2007 5 98765432 25/04/2007 4 98765432 26/04/2007 4 98765432 27/04/2007 4 98765432 28/04/2007 4 98765432 30/04/2007 4 TNX
View Replies !
Check Db Size Data File And Log File
Hi, i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB. i try to write this a got stuck. select sysdb.database_id,sysdb.name,sysdb.compatibility_level, sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster where sysdb.database_id = sysmaster.database_id can anyone help me with this script? THX
View Replies !
Unable To ALTER A Temporary Table In TSQL
Is it possible to ALTER a temporary table in TSQL (SQL2000)?The following TSQL reports a syntax error at the ALTER TABLE line:DECLARE @Test TABLE([Col1] [int] NOT NULL ,[Col2] [int] NOT NULL)ALTER TABLE @TestDROP COLUMN Col2I was hoping this would work the same way as a normal table. For example:CREATE TABLE [dbo].[Test] ([Col1] [int] NOT NULL ,[Col2] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE TestDROP COLUMN Col2EXEC sp_help TestDROP TABLE Test
View Replies !
TSQL - Updating Multiple Rows In A Table W/ Different Values
Using MS SQL 6.5, code below is in a Trigger or Stored Procedure. This query is from our developers still learning TSQL. We want to update a column in a Table (tblHourlyNoms)with a value from a View (vwCalc). Each record in vwCalc should correspond to exactly one record in tblHourlyNoms. The join is running off the primary key of tblHourlyNoms. The view is built out of 2 tables, as we thought this might be simpler. UPDATE tblHourlyNoms SET tblHourlyNoms.Allocation = vwCalcs.Expr1 FROM tblHourlyNoms INNER JOIN vwCalcs ON tblHourlyNoms.GasHour = vwCalcs.GasHour AND tblHourlyNoms.CON_ID = vwCalcs.CON_ID AND tblHourlyNoms.capID = vwCalcs.capID AND tblHourlyNoms.DeliveryPoint = vwCalcs.DeliveryPoint which ought to be the same as UPDATE tblHourlyNoms SET tblHourlyNoms.Allocation = vwCalcs.Expr1 FROM tblHourlyNoms, vwCalcs WHERE tblHourlyNoms.GasHour = vwCalcs.GasHour AND tblHourlyNoms.CON_ID = vwCalcs.CON_ID AND tblHourlyNoms.capID = vwCalcs.capID AND tblHourlyNoms.DeliveryPoint = vwCalcs.DeliveryPoint but it neither works! We get an error complaining of multiple rows returned via a subquery. yet this select does exactly what we require (except update the tblHourlyNoms table!). Help? Thanks, Matt.
View Replies !
TSQL - Copy Table As New Table And Get The Sum Of Specific Field Group By Other Field
Hi guys, I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once. The desired result should by something like: Stock.REFERENCE JurnalTransMoves.SUF SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE 5752 10 60 5752 20 5752 30 5753 400 3000 5753 500 5753 600 5753 700 5753 800 5754 7 15 5754 8 Is there any chance to do that? Thanks in advance, Aldo. Code Snippet SELECT Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME', Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY', Accounts.FILTER AS 'ACCOUNTS.FILTER', Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP', AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME', Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode', CreditTerms.DETAILS AS 'CreditTerms.DETAILS' CreditTerms.CURRENF AS 'CreditTerms.CURRENF' CreditTerms.MONTH AS 'CreditTerms.MONTH', CreditTerms.DAYS AS 'CreditTerms.DAYS', CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC', CreditTerms.TEMF AS 'CreditTerms.TEMF', CASE WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x' WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y' ELSE 'Undefined' END AS 'VAT', Stock.DOCUMENTID AS 'Stock.DOCUMENTID', DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME', CASE WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE ELSE '' END AS 'Invoice No', Stock.VALUEDATE AS 'Stock.VALUEDATE', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.REF2 AS 'JurnalTrans.REF2', JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF', JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID' FROM JURNALTRANSMOVES AS JurnalTransMoves_1 INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY LEFT OUTER JOIN ITEMS AS Items INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID WHERE Accounts.SORTGROUP Between '3001' And '3020' AND Accounts.ACCOUNTKEY IN ('123456') ORDER BY Accounts.ACCOUNTKEY
View Replies !
How To Check Other DB's Table
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
View Replies !
Help Fixing &&"The UPDATE Statement Conflicted With The CHECK Constraint&&"
Hi all,While using ACCESS and asp for years, I have just had to move my site onto a new server and the opportunity to move my 50MB access DB to msSQL was too good an opportunity to pass up! I used the Migration tools from MS and the data migrated nicely, I can do everything that I used to be able to do on the forum, except when a new user signs up or a member tries to edit their profile, the following error message comes up: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]The UPDATE statement conflicted with the CHECK constraint "SSMA_CC$PORTAL_MEMBERS$M_ICQ$disallow_zero_length". The conflict occurred in database "DATABASENAME", table "dbo.PORTAL_MEMBERS", column 'M_ICQ'. I have checked the database and the dbo.PORTAL_MEMBERS table and except for the autoincrement number, uname and pwrd, everything else is set to accept nulls. I also went though and filled in everything on the form then I could update. the same error applies when signing up - the only difference is the name of the table I then tried to change the data straight in the table by opening it in Server Management Studio Express and got the same error if I did not fill in everything. This tells me that it is not the code, much to my relief! So I am figuring that there is something somewhere in the DB that I have to change? could someone point me in the right direction - and as I am VERY NEW to this database system, and do not know my way around (it took me about 10 minutes to figure out how to open a table! *LOL*) very clear instructions would be appreciated. Thank you for your time :-)
View Replies !
|
TRACKER
