Thursday, August 1, 2013

[SQL Server 2008 issues] Login Creation

[SQL Server 2008 issues] Login Creation

Login Creation

Posted: 31 Jul 2013 07:06 PM PDT

Hi Team,While creating a new login, Effective tab is missing in securables window,Please suggest..

Generate a day date according to existing month+year+daydiff

Posted: 31 Jul 2013 06:46 PM PDT

im using sql 2008.i got this 5 columns:StartMonth,StratYear,EndMonth,EndYear,DaysBetweeni dont have the day of these dates and that's what im trying to generate for example:12 2008 1 2009 8now, i want to create a random date (start date and end date , format as dd/mm/yyyy) which will include the day and will make scene upon the data i have under days betweenFOR INSTANCE,if i know that i got 8 days (DaysBetween) and the startmonth is 12, the date must be from 24/12/2008 cause if i add 8 days i get the EndMonth (1/2009)if i would choose the date 2/12/2008 i would get 10/12/2008 and its not good cause the month is still 12....and i need 1 (2009)how can i generate valid dates ?thank you

How To DISTINCT COUNT with Windows Functions (i.e. OVER and PARTITION BY)

Posted: 21 May 2013 04:09 AM PDT

SQL 2012 Reference = http://msdn.microsoft.com/en-us/library/ms175997(v=sql.110).aspxI'm trying to get a unique count of rows in a child table in a multi-table join just like the example shown below from the above Microsoft article on COUNT(). I'm using the Windows functions (as opposed to old fashion aggregation with GROUP BY) because I need both aggregate data and non-aggregated data to be included in the results.In the below Example (from example C in the above Microsoft Document on T-SQL) it shows that you can do the following [code="sql"]USE AdventureWorks2012; GOSELECT DISTINCT Name, COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDeptFROM HumanResources.EmployeePayHistory AS ephJOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityIDJOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentIDWHERE edh.EndDate IS NULLORDER BY Name;[/code]This is using the DISTINCT keyword so as to get a distinct count where as without it the Count() coould be over stated by the number of rows retruend by the other tables in the join.Is there a way to do the equivelant in T-SQL for SQL Server 2008R2? I've tried the above and it errors out under SQL 2008R2 and examplce C above is NOT in the same Microsoft article when you change the platform to SQL 2008R2 so I'm assuming this is a feature nehancement in SQL 2012. How then can you do this under SQL 2008R2?Thanks

Query execution time in Milisecond

Posted: 31 Jul 2013 06:02 PM PDT

HiI have one problem statement to create sq. query. For the solution i have created three different queries, one using CTE expression and two using sub queries. When i execute all three queries in SSMS on local database, takes zero seconds to execute.I want to check performance of each query and want to find exact execution time in milliseconds. [b]How can i check it.Is there any other way to find best query among all?[/b]One way is to assign getdate() in datetime varibale before start of query execution and after the query execution complete and find the difference between both of them.

ADO NET Source works perfectly within BIDS but getting error in a SQL Agent Job

Posted: 30 Jul 2013 08:05 PM PDT

Hi,I have a SSIS Package with a ADO NET SOURCE for capturing data from MS Access DB which is in different Server. So, I have setup the System DSN in 32 bit ODBC Data Source Administrator and used .NET Provider\ODBC Data Provider connection manager in SSIS.This works perfectly within BIDS. However when I put this SSIS package in a SQL Agent Job, it does not work. I am getting the below error :----------------------------------------------------------------------------------------Code: 0xC0047062 Source: DFT_Customer_DLT ADO NET Source [16] Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2013-07-31 03:29:50.19 Code: 0xC0047017 Source: DFT_Customer_DLT SSIS.Pipeline Description: component "ADO NET Source" (16) failed validation and returned error code 0x80131937. End Error Error: 2013-07-31 03:29:50.19 Code: 0xC004700C Source: DFT_Customer_DLT SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-07-31 03:29:50.19 Code: 0xC0024107 Source: DFT_Customer_DLT Description: There were errors during task validation.-------------------------------------------------------------------------------------------As per my analysis there might be problem with 32bits and 64bits.Details of my project Environmenta nd Package :SERVER --> Windows Server 2008 R2 64 bitPACKAGE --> 32 bitODBC data source --> 32 bitSQL Server Agent --> 64 bit. (I have also tried by changing the property with 32bit in Execution Option in SQL Server Agent.)What needs to be done to resolve this error for executing with SQL Agent Job?Any and All help will be greatly appreciated...Thanks

checking the setup of your discs

Posted: 30 Jul 2013 10:00 PM PDT

I need to check what disc configuration we use to store our log files and data files. We use a third party cloud company to host our servers and i want to check that things are set up for optimal performance.When i log onto the server and go into computer managment i just see 'basic disc 100gb' for the disc that hold my log files. If it was using RAID would it show up here ? When i go to 'My Computer' and click on the properties of the same disc then click on the harware tab i see something different:Floppy disc driveNECVMWare IDE CDR10ATA....Then under that 3 virtual disc which say:VMware Virtual disk SCSI DISC DEVICEVMware Virtual disk SCSI DISC DEVICEVMware Virtual disk SCSI DISC DEVICEIf i then click on each of these and click properties, i see:device type: Disc drivesManufacturer (standard disk drives)Location Location 160(BUS Number 0, Target id0, Lun 0)Each one reads the same, except Target Id changes with each disc ( 0 - 2 )Can i tell from this what RAID (If any ?) is being used ?

Autoshrink enabled on a database

Posted: 31 Jul 2013 07:04 AM PDT

I understand that it is highly undesirable to have autoshrink on a database, since this would cause severe fragmentation.I have now inherited databases with autoshrink turned ONOnce I turn OFF autoshrinkI imagine that the index fragmentation could be reduced by running the reorganize/rebuild indexesMy question here would be , what about the fragmentation in the datafiles that was caused by the autoshrink ON.Could this be removed somehow ??

SSRS fillfactor on indexes in reportServerTempdb

Posted: 31 Jul 2013 11:43 AM PDT

Anyone have any recommendations on what fill factor should be for indexes on tables Segment and ChunkSegmentMapping in SSRS' reportservertempdb?

Baseline Configuration Analyzer Error

Posted: 08 Oct 2010 01:07 AM PDT

Hi Folks,I have another question today. I was trying to get analysis by Microsoft Baseline Configuration Analyzer 2.0 for our SQL Server 2008 installation using [b]SQL Server 2008Rs BPA[/b].Now, I am running this BCA from my workstation, so I am selecting "connect to another computer" option, I am sure that I am administrator of that remote machine and this local machine. But to be sure, I feed my ID and PWD in required fields too.After this much configuration when I try to run BPA, it gives me error [b][i]The specified module "baselineconfigurationanalyzer" Was not loaded because no valid module file was found in any module directory[/i][/b]I have checked that everything is 64bit (my workstation, remote server, BPA). I have also setup WinRM to accept remote connection to remote server. I tried to google this error but it seem that nobody had this before (or it displays lots of wrong results because of my long string)Is there any suggestion for me ??Thanks,-Jack

Send Email Alerts immediately depending upon specific data in a column

Posted: 31 Jul 2013 10:05 AM PDT

Hi Experts,We do custom logging into a table. The requirement is that the developers should receive email immediately whenever there is [b]specific data[/b] in that columnAny Input is AppreciatedThanks a ton in adavnce!

Bizarre Database Mail problem

Posted: 31 Jul 2013 09:41 AM PDT

I'm having a very strange problem related to Database Mail. I wonder if anyone can help me with.I have a SQL Server 2008 database that supports our help desk. Users create trouble tickets, which send out various emails.To send an email, my app inserts a record into a ticket email queue table. The table contains a createdate field autopopulated with getdate(), and a senddate field that's initially null. Meanwhile I have a SQL job that runs once a minute, its job being to execute a stored procedure. The SP queries for all queue records having a null send date then loops thru the result set, sending each email in turn. Our helpdesk is pretty busy, so typically multiple records will be inserted into the queue in a minute. Normally, you will see that each went out within a minute or two of being put in the queue.This morning, however, the email job history suddenly began reporting that the job failed. The reason given was that I needed to specify @recipient or @copy_recipents or @blind_copy_recipients. My SP specifies @recipients.Then I noticed that even tho the job was failing, some emails were going out. Then I noticed that the time between the email queue record's createdate and senddate was growing. The difference grew from the usual 1-2 minutes to, at this moment, three and a half hours. I could see too from the senddate field that exactly 1 email is being sent per minute. So I'm thinking that the SP starts working thru its result set, sends the first email OK, then dies, causing the job to report failure.I have made no changes to the SP, job, or anything else in ages. I have no idea how it happened or how to fix it. Restarting SQL Server did not help.Can anyone shed some light?

The txt file is not being created from the SQL table.

Posted: 31 Jul 2013 04:19 AM PDT

Hi, I am getting the following error when running the following script. I am trying to copy the table 'MAMI.EngravingsForVendor' into a flat file 'C:\JohnTest\EngravedProducts.txt' and I am getting an error. I am including the script, the error, and the content of variable "@sql" which I copied during debugging.-- SCRIPT THAT IS NOT WORKING...DECLARE @Table NVARCHAR(MAX)DECLARE @filename VARCHAR(MAX)SET @table = 'MAMI.EngravingsForVendor'SET @filename = 'C:\JohnTest\EngravedProducts.txt' IF OBJECT_ID(@table) IS NOT NULL BEGIN DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N''; SELECT @cols += ',' + name FROM sys.columns WHERE [object_id] = OBJECT_ID(@table) ORDER BY column_id; SELECT @cols = STUFF(@cols, 1, 1, ''); SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT ''''' + REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT ' + 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM ' + DB_NAME() + '.' + @table + '" queryout "' + @filename + '" -c -T'''; EXEC sp_executesql @sql; END ELSE BEGIN SELECT 'The table '+@table+' does not exist in the database'; END-- THE ERROR THAT IS BEING PRODUCED...SQLState = S1000, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-fileNULL-- THE CONTENT OF VARIABLE @SQL.EXEC master..xp_cmdshell 'bcp "SELECT ''VendorID'',''VendorEMailAddress'',''DocumentNumber'',''ProductCode'',''Quantity'',''VendorProductCode'',''Description'',''EngravedText'',''EngravedText2'',''ShippingAddressLine1'',''ShippingAddressLine2'',''ShippingAddressLine3'',''ShippingAddressLine4'',''ShippingAddressLine5'',''ShippingAddressLine6'',''ShippingAddressLine7'',''Country'',''ShipToTelephoneNumber'',''ShipToEMailAddress'',''Status'' UNION ALL SELECT RTRIM(VendorID),RTRIM(VendorEMailAddress),RTRIM(DocumentNumber),RTRIM(ProductCode),RTRIM(Quantity),RTRIM(VendorProductCode),RTRIM(Description),RTRIM(EngravedText),RTRIM(EngravedText2),RTRIM(ShippingAddressLine1),RTRIM(ShippingAddressLine2),RTRIM(ShippingAddressLine3),RTRIM(ShippingAddressLine4),RTRIM(ShippingAddressLine5),RTRIM(ShippingAddressLine6),RTRIM(ShippingAddressLine7),RTRIM(Country),RTRIM(ShipToTelephoneNumber),RTRIM(ShipToEMailAddress),RTRIM(Status) FROM DS_DEV.MAMI.EngravingsForVendor" queryout "C:\JohnTest\EngravedProducts.txt" -c -T'

"Failed to retrieve data for this request" error when trying to add feature

Posted: 08 Dec 2010 12:02 PM PST

Hi everyone, I wish to check if anyone has encountered this similar issue.I am on Windows Server 2003 SP2 and using SQL Server 2008 R2.When trying to add in a feature using the installer, the following error was encountered:SQL Server Setup has encountered the following error:"Failed to retrieve data for this request. ."When I tried applying the cumulative update, this exact same error also appears.Any suggestions will be appreciated !Thanks !Regards,Al

SSIS Import - Delimited File with Fields > 8k Characters

Posted: 29 Jul 2013 05:46 AM PDT

Hello all,I'm attempting to import a text delimited file with a few fields that have greater than 8k characters. I'm running into this error on import, "Text was truncated or one or more characters had no match in the target code page.". So, how do I get around this? I've attempted to import those columns as text, varchar(8000), and varchar (max) with no luck so far. I'm hoping that I don't have to split those fields into multiple rows if they are larger than 8k characters.

How to separate the address?

Posted: 30 Jul 2013 11:57 PM PDT

Hi Friends, [code="sql"]Create Table Adrtbl (Address varchar(80))Create table SpltAdr (Prefix char(2),StreetName varchar(50),StreetType varchar(10))GOinsert into Adrtblselect 'WESTLAKE DR'unionselect 'W BLK BROWN DEER RD'unionselect 'JOHNSON ST'unionselect 'FREDONIA AVE'unionselect 'BANK ONE'unionselect 'N MARTIN LUTHER KIND DRIVE'GOinsert into SpltAdrselect '','WESTLAKE' , 'DR'unionselect 'W', 'BLK BROWN DEER', 'RD'unionselect '', 'JOHNSON','ST'unionselect '','FREDONIA', 'AVE'unionselect '','BANK ONE',''unionselect 'N', 'MARTIN LUTHER KIND DRIVE',''GOselect * from Adrtbl -- Source formatselect * from SpltAdr -- OutPut format[/code]I am looking for splitting the address. I have provided the sample script in here. I tried my own way but i failed. I have some listed Prefix and StreetType..Let me give you some examples...StreetType - Aly, Ave, Bch, Blf, Blvd,Rd,Dr,StPrefix - E,W,N,S,NE,NW,SE,SWthe prefix and street type must come under these categories....Gimme your suggesstions, friends....Any function or any T-SQL statement can do this?

Transactional Replication issue

Posted: 31 Jul 2013 06:35 AM PDT

Hi,I am having issues with transactional replication. yesterday I reinitialized the subscription with new snapshot. As I know once the bulk copy of data into tables and creating the Primarykeys, it has to say completed and start the delivery of transactions. Instead of that, again it is reinitializing the tables. In one day three times it reinitialized the tables . Please suggest me.Thanks

Snapshot Agent will not start

Posted: 17 Aug 2011 04:34 AM PDT

I'm working on a 2008 R2 Enterprise SQL Server. There are other working transactional replication publications working on this server.The distributer sql server is also a 2008 R2 Enterprise SQL Server.Today I created a new Publication, added a subscriber (going to a different instance) and the snapshot agent will not start.Here are the errors that it gives me:Error messages:The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failedHere are the details from the job:MessageThe replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.Executed as user: (bob). Agent shutdown. For more information, see the SQL Server Agent job history for job (bob's job). [SQLSTATE 01000] (Message 20557) Replication-Replication Snapshot Subsystem: agent (bob)failed. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.I have verified that the security is the same as the rest of the replication and the SQL Account is a db_owner of the destination and source databases.Thoughts?

Create User

Posted: 31 Jul 2013 02:34 AM PDT

Hi Team,how to create a new user with PasswordUser Name : STLOKPassword : STLOK and it should be read - only user.

Data import

Posted: 31 Jul 2013 12:51 AM PDT

Greetings friends,I have a .dta file that's formatted in a strange way and I would like to discuss ways of importing into a staging database.The first field of each record consists of a record type. For each entity there will be one record of type 01. Also, for each record type 01 there will be one or more record type 02. There maybe zero or more record type 03, 04, 05 and 06For example[quote]01*9517469000*117844165*0335*The Occupier* CRICKET CLUB***02*1*Ground*Tea Room*35.40*9.50*33602*2*Ground*Changing*28.00*9.50*26602*3*Ground*Kitchen*16.40*9.50*15602*4*Ground*Scorer*3.80*9.50*3602*5*Ground*Mower Shed*15.00*2.38*3603*Cricket Square*2.0*100.00*+20003*Cricket Ground*2.0*850.00*+172601*9517470000*200027165*0335*The Occupier* CRICKET CLUB***02*1*Ground*Club Room*32.60*19.00*61902*2*Ground*Changing Room*21.60*12.67*27402*3*Ground*Internal Storage*2.80*9.50*2702*4*Ground*Bar*13.90*19.00*26403*Cricket Square*1.0*100.00*+10003*Cricket Pitch*2.8*500.00*+142001*9517471000*203509165*0335*The Occupier*CRICKET & FOOTBALL CLUB***02*1*Ground*Pavilion*86.20*20.00*172402*2*Ground*Garage*23.40*10.00*23402*3*Ground*Changing Room*25.00*13.33*33303*Cricket Square*1.0*100.00*+10003*Playing Field*1.8*500.00*+91001*9517472000*200736165*0335*The Occupier*PT GNDOUSE****ROWOSE*02*1*Ground*Kitchen*37.14*16.67*619[/quote]I would like to create 6 staging tables to hold the different types but I'm not sure if it's best to load all the data into SQL first then split it out or go down the SSIS route for this kind of thing? Obviously once the data is split there must be a unique ID which links the records together. Any suggestions?Thanks in advance.

Change query from sql 2000 to 2008

Posted: 31 Jul 2013 02:18 AM PDT

Hi,I want to modify the below query which exists in 2000 to sql 2008. Can u please help me?select * FROM company_role_match crm, company_role_match crm2, company_role_match crm3, access_control_doc acdWHERE crm.id =* crm3.id AND crm2.id =* crm3.idAND crm.user_id = 547 --@query_user_idAND crm2.user_id = 4397 --@profile_idAND acd.ac_type = 'User Roles' --@access_control_typeAND acd.ac_id =* crm3.id AND ( crm.granted = 1 OR crm2.granted = 1 )AND crm3.id = 32 --@access_id

Disk space used last year

Posted: 30 Jul 2013 09:01 PM PDT

Hi,Can anyone please provide me the query to find the how much disk space used in last year?I need to provide approximate space for one more year as disk space for particular drive is about to full.Any immediate assistance will be a great help.

Query from SSRS to SSMS

Posted: 30 Jul 2013 09:29 PM PDT

Hi Team,We have a Report (SSRS) with select query, when we generate the report, Database is hanging,how to find the query which is executed inside the report.is there any possible way / query to find the query which is executed thru Report (SSRS).Please suggest..

Find executed scripts

Posted: 30 Jul 2013 09:50 PM PDT

Hi Team,am having 10 select statements and 10 Insert statements, and executed all the 20 statements individually.Now i want to get the report or result to display all the 20 executed statements.latest executed queries, am using below code, but that is not corrent.SELECT top 10 DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query]FROM sys.dm_exec_query_stats AS DMExQryStatsCROSS APPLY sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxtORDER BY DMExQryStats.last_execution_time DESCPlease sugest...

Connect to sever by name rather than IP address

Posted: 30 Jul 2013 08:05 PM PDT

HiWhen i first open SSMS the 'Connect to Server' logon box appears. In it the server name drop down box is a whole bunch of different ip address of all the servers we use in our production environment. If i click 'cancel' the 'Connect to server' box goees away and i am left with ssms open, and, at the bottom left i can see my 'Registered servers tab' and my 'Object Explorer' tab.In the Registred servers tab is a list of servers by name which is far more easier when i need to connect to the required server.The problem is when i want to change connection by clicking the 'Connect Object explorer' it opens up the 'Connect to Server' box again with all the IP address.I have tried typing in the names of the servers in replacement of the ip address but it fails to connect. How do i get this list of ip address to display the server names so its easier to switch when i want to connect ?This is probably really basic but its one of those things you probably only set up once and then its done ! Any tips ?

Export Calendar items from Exchange 2010 Public folder to MS SQL 2008

Posted: 30 Jul 2013 09:45 PM PDT

Overall situation: We are now migrating from a Windows 2003 Server domain to a Windows 2008 Server R2 domain. At the same time, we are migrating mailboxes from a Exchange 2007 to an Exchange 2010.Current situation: we use an Access 2003 API (a front-end MS Access application) to get the calendar items of a public folder and place them in an MS SQL 2008 database. This public folder resides on an Exchange 2007. The MS Access "application" is on the D drive of the pc's of persons who have the permissions to put items into this public calendar and it uses the Outlook 2003 client to get the calendar data.Problem: we don't find an apropriate Access 2010 API to use with MS SQL 2008 databaseDesired situation: a solution (via Powershell or other scripting) that reads the calendar items directly from the public folder on Exchange 2010 (without the need to use an Outlook client) and copy/sync it with the MS SQL 2008 database (on another server) on several timepoints (e.g. every 6 hours or every day at 18:00 PM).

How to set publication compatibility level for transactionnal replication ?

Posted: 30 Jul 2013 09:18 PM PDT

Hello,I'm trying to set up transactional replication from an SQL Server 2008 R2 instance to another.The publication created using the wizard causes the snapshot agent to fail, with the error :[i][...]Column [col name] in object FeaturedAgent contains type [col type], which is not supported in the target server version, SQL Server 2000[...][/i]I'm getting the same problem as the one is this post : [url=http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dd88985a-5e47-4904-8eda-d41d1e276bdd/replication-failed-on-one-table]replication failed on one table[/url].The solution in this post was : [quote]Changing the publication database compatibility level to 100[/quote]But how to change the publication compatibility level for [b]transactional[/b] publications ?- The publication wizard only proposes to set the subscribers type for [b]merge[/b] publications.- And publication_compatibility_level property seems to be available only for [b]sp_changemergepublication[/b], not for [b]sp_changepublication[/b].Thanks for any help or information.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog