Thursday, September 12, 2013

[SQL Server 2008 issues] Select several subqueries with more than 1 value

[SQL Server 2008 issues] Select several subqueries with more than 1 value

Select several subqueries with more than 1 value

Posted: 11 Sep 2013 06:57 PM PDT

Hi. There is no problem with this query[code="sql"]select (select 'hello'), (select 'world')[/code]But if one select statement return more than 1 value it throw exception.Initialization:[code="sql"]Create table table1(c nvarchar(1));Create table table2(c nvarchar(1));insert into table1 ('1')insert into table1 ('2')insert into table1 ('3')insert into table2 ('a')insert into table2 ('b')[/code]and here is query that generate error:[code="sql"]select (select c from table1), (select c from table2)[/code]I expect output be something like this:c,c------1,a2,b3,nullIt seems impossible, but I want to be sure if there is any trick for it or not.Thank you for help.

String as a date format

Posted: 11 Sep 2013 06:05 PM PDT

Hi All,From other source I am getting a data as string format like 'Sep 6 2013'. I want to see it as a Date format.and the date format should be DD/MM/YYYY. It means 'Sep 6 2013' looks like 06/09/2013.Is it possible to to that in SQL server?Please help!!

Why is my logon trigger blocking logons?

Posted: 11 Sep 2013 08:14 AM PDT

I've created the following trigger to track logons:[code="other"]Use masterGoCreate database AuditDBGoUSE [AuditDb]GO CREATE TABLE [dbo].[ServerLogonHistory]( [SystemUser] [varchar](512) NULL, [DBUser] [varchar](512) NULL, [APP_NAME] [varchar](512) NULL, [SPID] [int] NULL, [LogonTime] [datetime] NULL, [HOST_NAME] [varchar](512) NULL) ON [PRIMARY] GO Grant insert, update, select on ServerLogonHistory to publicGo CREATE TRIGGER [Tr_ServerLogon]ON ALL SERVER FOR LOGONASBEGINif( (ORIGINAL_LOGIN() <> 'sa')and APP_NAME() not like 'SQLAgent%'and APP_NAME() not like '%IntelliSense')INSERT INTO AuditDb.dbo.ServerLogonHistorySELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()END GO[/code]When I run this all is good, table is in place, etc. but I tested it by logging in as another user and I'm unable to logon. I then granted permission to the table explicitly to that user and am still unable to logon. When I check the user it is a member of public. When I check the table under permissions-effective tab I get the following for public: "[i]cannot execute as the database principal because the principal "public" does not exist, this type of principal cannot be impersonated, or you do not have permission[/i]"For the other user it says: "[i]the server principal "domain\username" is not able to access the database "AuditDb" under the current security context[/i]"I'm missing something here - but am stumped as to what it is. Can anyone point out the obvious to me? :-)Oracle is my usual forte, and implementing a logon trigger is a little simpler process.

Max Memory

Posted: 11 Sep 2013 10:12 AM PDT

I have set Max Memory to 6 GB. But SQL Server is using only 2.9 GBQuery used:SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (kB)'So why SQL Server is not using all allocated Max memory i.e 6GB? Is this normal? SQL Server should use all allocated Max memory right?Thanks,Gary

Strange Working of Sql server database mail- Need urgent help

Posted: 11 Sep 2013 04:48 AM PDT

It may be a stupid question/problem,,, But i am struck up in a situation where i am not able to send emails from database email.After much surfing on internet to find some solution, i came cross a script where it checks for the DB mail service status, if it is stopped, then again a script to start the service.It is as Follows:-- See if database mail (DM) is startedEXEC msdb.dbo.sysmail_help_status_sp ------- This Gives Output of STOPPEDGO-- Stop the serviceEXEC msdb.dbo.sysmail_stop_sp ---- This runs successfully.GO-- See if database mail (DM) is stoppedEXEC msdb.dbo.sysmail_help_status_sp ----- This Gives STOPPED (as expected)GO-- Start the serviceEXEC msdb.dbo.sysmail_start_sp ---- This runs successfully.GO-- See if database mail (DM) is startedEXEC msdb.dbo.sysmail_help_status_sp ---- Now this executed just after the previous Query and give the output as STARTED, but after few seconds when i again execute this. output goes back to STOPPEDGOWhy does it STOPPED automatically when once it is started.

Getting duplicate values evenif used distinct

Posted: 11 Sep 2013 12:47 AM PDT

Hi All,I used distinct in my query but then also I am getting almost 100K of duplicate records, below is the detail of count.Expected count : 1769894From select query I am getting : 13883717The contact_ID should be unique and we shouldnt have duplicate contact ID, I used 4-5 tables to join.Below is the query,drop table CnI_Test;create table CnI_Test asselect distinctml.account_id, ml.account_name, ml.NAMED_ACCOUNT_IND, ml.NAMED_ACCOUNT_DESCRIPTION, ml.site_id, ml.org_name, ml.contact_id , ml.FIRST_NAME, ml.LAST_NAME,ML.JOB_AREA_CD, ML.JOB_AREA_CD_desc,ML.job_level_cd, ml.job_level_cd_desc,ML.MKT_REGION_NAME, ML.OVERALL_SALES_REGION, ML.SALES_REGION, ML.ISO_COUNTRY_CD_DESC, ML.TERRITORY,ml.CITY,ML.STATE_PROV_COUNTY,ml.MKTG_CAPABLE_FLAG, ML.MKTG_CAPABLE_EMAIL_FLAG, ml.MKTG_CAPABLE_PHONE_FLAG, ml.MKTG_CAPABLE_MAIL_FLAG, ML.LAST_DT_CONTACT_ACTIVITY,ML.JOB_TITLE,ml.AI_DATE_ADDED,CASE WHEN ML.MKTG_CAPABLE_PHONE_FLAG = 'Y' THEN ML.PHONE_COUNTRY_CD ELSE NULL END AS PHONE_COUNTRY_CD,CASE WHEN ML.MKTG_CAPABLE_PHONE_FLAG = 'Y' THEN ML.CONTACT_PHONE_NUMBER ELSE NULL END AS CONTACT_PHONE_NUMBER,CASE WHEN ML.MKTG_CAPABLE_PHONE_FLAG = 'Y' THEN ML.CONTACT_MOBILE_PHONE ELSE NULL END AS CONTACT_MOBILE_PHONE,CASE WHEN ML.MKTG_CAPABLE_PHONE_FLAG = 'Y' THEN ML.ORG_PHONE_NUMBER ELSE NULL END AS ORG_PHONE_NUMBER,CASE WHEN ML.MKTG_CAPABLE_EMAIL_FLAG = 'Y' THEN ML.EMAIL_ADDRESS ELSE NULL END AS EMAIL_ADDRESS,CASE WHEN ML.MKTG_CAPABLE_MAIL_FLAG = 'Y' THEN ML.ADDRESS_LINE_1 ELSE NULL END AS ADDRESS_LINE_1,CASE WHEN ML.MKTG_CAPABLE_MAIL_FLAG = 'Y' THEN ML.ADDRESS_LINE_2 ELSE NULL END AS ADDRESS_LINE_2,CASE WHEN ML.MKTG_CAPABLE_MAIL_FLAG = 'Y' THEN ML.ADDRESS_LINE_3 ELSE NULL END AS ADDRESS_LINE_3,CASE WHEN ML.MKTG_CAPABLE_MAIL_FLAG = 'Y' THEN ML.ADDRESS_LINE_4 ELSE NULL END AS ADDRESS_LINE_4,no_of_csu_job_areas.job_area as csu_job_areas,Owner_1.csu as CSU_DRIVER,SUBSTR(Owner_1.own,1,1) as CSU_Owner, SUBSTR(Owner_1.own,2,1) as CSU_Interest,Owner_2.NCV_DRIVER as NCV_DRIVER, SUBSTR(Owner_2.own,1,1) as NCV_Owner,SUBSTR(Owner_2.own,2,1) as NCV_Interest, NVL(ep.ACCOUNT_CURRENCY, 'Unmatched') AS ACCOUNT_SEGMENT ,NVL(g.now_in_gov,'No') AS now_in_gov ,NVL(g.release_from_gov_date,'N/A') AS release_from_gov_date,NVL(g.days_to_release_from_gov,'N/A') AS days_to_release_from_gov,sa.sales_req_not_to_call_accountfrom M_LIST MLleft join MRTROLLUP.CSU_JOB_AREAS no_of_csu_job_areas on (no_of_csu_job_areas.contact_id = ml.contact_id ) LEFT JOIN mrtcustomer.ent_parent ep ON ep.ACCOUNT_ID = ml.ACCOUNT_IDLEFT JOIN (SELECT DISTINCT upper(trim(email_address)) AS email_address FROM REPOR01.BLOCKED_EMAIL WHERE email_address IS NOT NULL) blocked ON blocked.email_address = upper(trim(ml.email_address))left join REPOR01.MART_CSU_CONTACT Owner_1 on Owner_1.contact_id = ml.contact_id left join REPOR01.MART_NCV_CONTACT Owner_2 on Owner_2.contact_id = ml.contact_id left join (select distinct email_address, case when segmentation_last_run_date >= current_date - 15 then 'Yes' else 'No' end as now_in_gov, case when segmentation_last_run_date >= current_date - 15 then to_char(segmentation_last_run_date + 15) else 'N/A' end as release_from_gov_date, case when segmentation_last_run_date >= current_date - 15 then to_char(15 - (to_date(to_char(current_date,'YYYYMMDD'),'YYYYMMDD') - to_date(to_char(segmentation_last_run_date,'YYYYMMDD'),'YYYYMMDD'))) else 'N/A' end as days_to_release_from_gov from (select trim(lower(email_address)) as email_address, max(segmentation_last_run_date) as segmentation_last_run_date from mrtaprimoadm.ca_main_comms_log where email_address is not null group by trim(lower(email_address)))) g on g.email_address = trim(lower(ml.email_address))left join sfdcdata.sfdc_accounts sa ON sa.enterprise_id = ml.account_idleft join sfdcdata.sfdc_accounts sa2 on ml.sfdc_account_id = sa2.sfdc_account_id;Can anyone please tell me how can I get expected 1769894 count.M_List table is having 1769894 contacts.Thanks,Abhi

optimize pivot query

Posted: 11 Sep 2013 01:00 AM PDT

Hi,I did this code to select 2 best selling prodID (by quantity) for each Year period and put it in pivoting format needed for report (ssrs)Just curious how it can be optimized (or left formatted inside of ssrs)this is my sample[code="other"];with t4 as (select '2011-01-02' as dd, 123 as prodID unionselect '2011-01-03' as dd, 345 as prodID unionselect '2011-01-04' as dd, 345 as prodID unionselect '2011-01-05' as dd, 123 as prodID unionselect '2011-01-06' as dd, 123 as prodID unionselect '2012-01-02' as dd, 567 as prodID unionselect '2012-01-03' as dd, 567 as prodID unionselect '2012-01-04' as dd, 567 as prodID union select '2012-01-04' as dd, 777 as prodID union select '2012-01-05' as dd, 777 as prodID union select '2012-01-06' as dd, 888 as prodID )--select * From t4select *, RANK () over (partition by year order by numb desc) as Rankinto #tt from (select COUNT(*) numb, DATEPART(year,dd) year, prodID from t4 group by DATEPART(year,dd), prodID ) b ---select * from #ttselect t1.prodID as best_11 , t2.prodID as best_12, t1.Rank from #tt t1 join #tt t2 on t2.rank = t1.rank where t1.year = '2011' and t2.year = '2012' and t1.Rank < 3 order by 3[/code]TxMario

sp_register_custom_scripting

Posted: 11 Sep 2013 02:02 PM PDT

Hi All,I have replication configured using a third party tool (power exchange). I get issues whenever i execute schema level changes. Do we have to register stored procedures that does only schema changes or do we have to register stored procedures that exectues DML changes as well?Please let me know.Thanks,

Calculate totals in each category by day

Posted: 11 Sep 2013 05:26 AM PDT

I need to calculates for each of a category by day, and I need to carry over those totals where a day isn't present. Just to clarify this is not a running total - This is "how many people were in __ status by date?"Here is some sample data:[code="sql"]CREATE TABLE #StCg (StDt DATE NOT NULL, StID VARCHAR(25) NOT NULL, NwStStg VARCHAR(75) NOT NULL)INSERT INTO #StCg(StDt, StID, NwStStg) VALUES('2013-08-01', 'a', 'New'),('2013-08-03', 'a', 'Call'),('2013-08-07', 'a', 'Start'),('2013-08-02', 'b', 'New'),('2013-08-07', 'b', 'Call'),('2013-08-04', 'c', 'New'),('2013-08-06', 'c', 'Canc')SELECT *FROM #StCg[/code]Here is the output with an extra column to explain things further. I put the output on an Excel sheet to make it easier to see.[img]https://dl.dropboxusercontent.com/u/7186315/SS.PNG[/img]

One sa password to rule them all?

Posted: 11 Sep 2013 12:38 AM PDT

Just wanting to get your views on the use of a common sa password for all instances vs individual sa passwords. Obviously the sa login is to only to be used in emergencies and the password will be really complex.What do you do?

SSIS PACKAGE DATA DUMP FROM SQL VIEW TO CSV AND PUT IT ON A FTP SERVER

Posted: 11 Sep 2013 05:54 AM PDT

Hi,I have a scenario where I need to create a SSIS Package and make it run automatically every night.What the package should do is that it should pull data from a sql view and dump it into a CSV file on the local server. The CSV file should have a date stamp appended to the file name.Now I have to dump the latest csv file from the local path on to the FTP Server.Can anyone give me step wise as to how this can be achieved?Example:I have csv files saydata_2103_09_08data_2013_09_09data_2013_09_10data_2013_09_11 on the local path c:\timesheetswhen the package is run on the night of 2013_09_11 the csv file data_2013_09_11 should be picked and be placed on the FTP Server.Thank You all in Advance

Disk io calculation issue .

Posted: 11 Sep 2013 05:51 AM PDT

I am trying to fetch the spid consuming highest disk io , I am getting the desired result from sysprocesses but I am not able to fetch the SQL text and SQL plan from that spid . Reason I am not able to is because I am using below approach to get the sql text and query plan which results all spids from history too . SELECT query_plan,session_id,text FROM sys.dm_exec_query_stats qs WITH (nolock)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpCROSS APPLY sys.dm_exec_sessions siWHERE si.session_id = <SPID>Any one has other work around to find the SPID consuming highest diskio with SQL text and SQL plan.

Use data from Flat File for Variable in SSIS 2008

Posted: 11 Sep 2013 02:27 AM PDT

I am trying to use a text file to provide a variable in an SSIS package so that the end user can input different invoice numbers to be run through a process. I have found some help online which says to use a Script Component to pull the data from the file into the Variable however I have not written much Visual Basic code in the past and I have got a bit stuck. I am using Visual Studio 2008 however the article I have found online is using Visual Basic for Applications and I keep getting errors when I use that code. Help!

sys.dm_db_stats_properties WHERE modification_counter &lt; 0

Posted: 11 Sep 2013 03:43 AM PDT

Hi,In SQL Server 2008 R2 with SP2, we can get statistics that are [b]NOT modified[/b] using the query [i]SELECT * FROM sys.dm_db_stats_properties WHERE modification_counter < 0[/i]But I want find the same information in SQL server 2008 R2 SP1. Please advise what query I can use?Thanks,Gary

How to generate sequence of rows?

Posted: 11 Sep 2013 01:21 AM PDT

Hi Friends,I have a table having some data where i want to generate sequence of rows based on premise value. I have given here the samples and the results that i am looking for..please give me your suggestions if there is any T-SQL can do this. Any suggestions would be really appreciated. Friends, If i am not making you clear, please let me know, i can brief you. [code="sql"]-- My Dataselect '12th Main St' as StreetName, 1 as FromPremise , 5 as ToPremiseunionselect '10th Main St' as StreetName, 10 as FromPremise , 12 as ToPremise-- Expecting Resultselect '12th Main St' as StreetName, 1 as FromPremise , 1 as ToPremiseunionselect '12th Main St' as StreetName, 2 as FromPremise , 2 as ToPremiseunionselect '12th Main St' as StreetName, 3 as FromPremise , 3 as ToPremiseunionselect '12th Main St' as StreetName, 4 as FromPremise , 4 as ToPremiseunionselect '12th Main St' as StreetName, 5 as FromPremise , 5 as ToPremiseunionselect '10th Main St' as StreetName, 10 as FromPremise , 10 as ToPremiseunionselect '10th Main St' as StreetName, 11 as FromPremise , 11 as ToPremiseunionselect '10th Main St' as StreetName, 12 as FromPremise , 12 as ToPremise[/code]

Combine fields

Posted: 10 Sep 2013 11:23 PM PDT

Hello,I have to create a table and fill it with data from another tables.My question is can you held me with a script for a field in this table, which should be sum of the values of 7 other fields.Thanks,

Possibly Dumb Question

Posted: 11 Sep 2013 02:01 AM PDT

Why would you want to add SSAS or SSRS as a feature of a database engine instance as opposed to creating a new instance on the same box. What's the use cases?

set SET FMTONLY ON/OFF at database level

Posted: 11 Sep 2013 12:45 AM PDT

Developers are having an issue with BizTalk and SQL Server . They asked me if we could SET FMTONLY ON at the database level. Their are several 3rd party apps which run on this server and I have reservations about doing this

Total calculated in stored procedure for report sometimes returned as 0

Posted: 11 Sep 2013 01:22 AM PDT

This is a problem that I thought was a Reporting Services issue but now realize is something different, possibly in a SQL Server driver.We have some reports where we compute report totals for head count and various compliance percentages in the stored procedure. There have been intermittent problems where the head count incorrectly displays as 0 on the SSRS report. The stored procedures, executed in SQL Query Analyzer, always return the correct value. SSRS problem, right?Well...I was recently directed to begin porting our reports to a new web site using CakePHP. As an interim reporting solution we're using the existing stored procedures (the new website will have a new database but for some period of time the old & new databases will be synced). I'm finding that the same reports rendered to the browser using PHP intermittently display 0 for the head count on these same reports!Has anyone else ever run into this?

Backup Incomplete

Posted: 10 Sep 2013 09:36 PM PDT

Hi GuysWe have a VB application which includes SQL Express 2008R2. As part of that, we include an application which runs on the server and allows non-technical users to perform various admin tasks including backup. The problem is that occasionally the backup appears to be created but is in fact marked *** INCOMPLETE ***The sql that performs the backup is created like this: sql = "BACKUP DATABASE " & dbname & " TO DISK = '" & buPath & "' WITH COPY_ONLY"The strings are held in the application and not just typed in by the user.I have searched on here and via Google for clues as to why this happens but I can only find suggestions about what to do with an incomplete backup when you've got one, but nothing on what might cause it. Any suggestions much appreciated.Thanks

DBCC FREEPROCCACHE

Posted: 10 Sep 2013 07:45 PM PDT

What are the best practices around using DBCC FREEPROCCACHE to free up space in TEMPDB when it won't shrink?TEMPDB ran out of space on one of our systems, and therefore the query was cut short. It now won't shrink. sys.dm_exec_requests is showing that nothing is happening on tempdb, there are no locks either.It is a shared, production system so we do not want to restart the instance.

Difference

Posted: 10 Sep 2013 09:39 PM PDT

What is the difference between T-sql and sql server?

different IP address can connect SQL?

Posted: 10 Sep 2013 11:22 PM PDT

Hi,we have two IPs configured in server one is Primary IP and Backup IP..whenever install SQL Server by default taking & assign primary IP only also application ODBC configured.Is it possible for connect backup IP address for ODBC?can we do that thru configuration manager to remove the primary IP and assign backup IP, then restart the server?rgdsananda

SQL Server 2008 using more than Max Specified Memory

Posted: 25 Aug 2011 02:31 AM PDT

We have a newer SQL Server 2008 R2 machine running on Server 2008 x64, and we noticed that the sql service was eating up all the RAM on the box. I then applied a maximum server memory of 45056, and the service has since surpassed this number and is now consuming over 47 GB out of the 48 GB on the box. Any ideas as to why it would ignore the specified number?Thanks!

Format equivalant function in sql server 2008

Posted: 10 Sep 2013 07:43 PM PDT

Team,I have used format function sql server 2012 to get month number as "09"select format(dateadd(m,-11,'2013-08-01'), 'MM')Could you please help me to get same output in SQL server 2008.Thanks in advance,Keerthy
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog