Error 952 Database is in Transition

Problem DataBase In transition...Error 952 error.

Solution : Restart Management Studio on client machine will solve this problem.

To Find List of Jobs, Last Run Status and Schedules

To find out lsit of jobs and thier details.

SELECT
J.Name
,CASE WHEN J.Enabled=1 THEN 'Enabled' ELSE 'Disabled' END AS JobEnableStatus
,SJS.Command AS Query
,SJS.Step_Name
,LastRunDate
,LastRunTime
,CASE WHEN LastRunStatus =1 THEN 'Success' ELSE 'Failed' END AS LastRunStatus
,Next_Run_Date
,CASE len(Next_Run_Time)
WHEN 3 THEN CAST('00:0'+ LEFT(RIGHT(Next_Run_Time,3),1)+':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 4 THEN CAST('00:'+ LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(Next_Run_Time,5),1) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 6 THEN CAST(LEFT(RIGHT(Next_Run_Time,6),2) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
END AS NextRunTime
,SS.Name AS SchduleFrequency
,CASE LEN(Q1.Run_Duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q1.Run_Duration,3),1)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q1.Run_Duration,5),1)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.Run_Duration,6),2)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
END as 'Job Ran Duration'
FROM MSDB..SYSJobs J
INNER JOIN MSDB..SysJobSchedules S ON J.job_id= S.job_id
LEFT JOIN MSDB..SYSJobSteps SJS ON SJS.job_id = S.job_id
LEFT JOIN MSDB..SysSchedules SS ON SS.Schedule_Id = S.Schedule_Id
LEFT JOIN (
SELECT
job_id
,MAX(run_duration) AS run_duration
,MAX(Run_Status) AS LastRunStatus
,MAX(Run_Date) AS LastRunDate
,CASE LEN(MAX(Run_Time))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(MAX(Run_Time),3),1)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(MAX(Run_Time),5),1)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 6 THEN CAST(LEFT(RIGHT(MAX(Run_Time),6),2)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) AS CHAR (8))
END AS LastRunTime


FROM MSDB..SysJobHistory
GROUP BY job_id
) Q1
ON J.job_id = Q1.job_id

WHERE Category_Id =0 AND J.Enabled=1

ORDER BY LastRunDate,LastRunTime,J.Name


Thanks,

SQL Server FAQ : Single line Answers

Log Shipping: Log Shipping provides a means to maintain a secondary server on an automated basis using a chain of transaction log backups.

Replication: Distribute data from Master database to one or more Secondary databases.

Data Mining: Making predictions about data.

Full Text Indexes: Index that give ability to query large volumes of unstructured data rapidly.

Clustering: To protect system against hardware failures.

Trouble Shooting tools:

Dynamic Management Views (DMV) and Functions (DMF)
System Monitor
SQL Server Profiler
Database Tuning Advisor

Clustered Index :A clustered index is an index where the leaf level of the index contains the actual data rows of the table

Heap: A table that has no index is referred to as a heap

Difference Between Clustered Index and Heap:
A clustered index has the data stored logically in the order of the index key, a heap has no ordering of rows or pages

Difference Between Len () and DataLength ():

Len() does not work on text, N text, image data types. But DATALENGTH() does work

ACID stands for Atomicity, Consistency, Isolation, and Durability

Transaction : A smallest unit of work in database

Lock Modes: The way in which a lock shares, or does not share records it is currently working on, are called Lock Modes

Index :An index is a structure within SQL that is used to quickly locate specific rows within a table

Deadlock: Itrefers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first

Index key : An index is defined on one or more columns

Locks: The SQL default method of controlling data integrity.

Transactions: TSQL's smallest unit of work.

ACID: An acronym used to describe a perfect and consistent transaction.

Lock Modes: The way a lock shares or does not share records its working on.

Blocking: When a transaction must wait for a record.

Deadlocks: An error condition occurring when two locks are stuck in a circular loop.

Lock Hints: TSQL commands to override SQL's default locking behavior.

Row Versioning: The new ability in SQL 2005 to keep copies of data changes.

SQL Server Reporting Services (SSRS) : List of All Reports, Subscriptions, and Their Schedules

Its very common running and managing hundreds of reports as a BI developer. But its very hard to maintain all subscriptions as reporting services doesn't provide a sophisticated user interface. But as alternate we can do work with report server system tables. Even though this script is very easy and known by all I am just writing the Script here just like that. To find list of subscriptions and their schedules.

SELECT
C.Name,
C.[Path] ObjectPath,
U.UserName,
SB.[Description],
S.StartDate,
S.NextRunTime,
S.LastRunTime,
S.EndDate,
S.RecurrenceType,
S.LastRunStatus,
S.MinutesInterval,
S.DaysInterval,
S.WeeksInterval,
S.DaysOfWeek,
S.DaysOfMonth,
S.[Month],
S.MonthlyWeek
FROM ReportServer..Catalog c WITH (NOLOCK)
INNER JOIN ReportServer..ReportSchedule R ON R.ReportID = c.ItemID
INNER JOIN ReportServer..Schedule S WITH (NOLOCK) ON R.ScheduleID = S.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = R.SubscriptionID
INNER JOIN ReportServer.dbo.Users U ON U.UserID = SB.OwnerID


Thanks,

How to do Performance Tuning a SSIS package? or How to troubleshoot a SSIS package?

Sometimes it may happen we are doing required trouble shooting to a SSIS package when it fails. But when we are at interview we cann't remember all the trouble shooting procedures we have done. So here I am hilighting all troubleshooting procedures only at high level , not drill down into detail level

Troubleshooting Package Development

Troubleshooting Design-time Validation Issues

 Work Offline when data sources are not available
 Configure the Delay Validation property on package elements that are not valid until run time
Troubleshooting Control Flow

 Set breakpoints on tasks, containers, and the package
 Use the debugging windows
 Review the information on the Progress tab

Troubleshooting Data Flow

 Test with only a subset of your data
 Use data viewers to monitor data as it moves through the data flow
 Configure error outputs on data flow components that support them
 Capture the count of rows processed
 Review the information on the Progress tab

Troubleshooting Scripts

• Set breakpoints in script in Script tasks
Troubleshooting Errors without a Description

Troubleshooting Package Execution

Catching and Handling Package Errors by Using Event Handlers
• Create an event handler for the On Error event
Troubleshooting Bad Data by Using Error Outputs
• Capture bad data by using error outputs
• Add friendly information to the error outputs
• Add the description of the error
• Add the name of the error column

Troubleshooting Package Execution by Using Logging

 Enable logging
 Select the package's Diagnostic event to troubleshoot provider issues
 Enhance the default logging output
1. Create a parent table that logs each execution of a package
2. Add auditing information to the data flow
3. Consider capturing row count data

Troubleshooting Package Execution by Using Debug Dump Files

Troubleshooting Run-time Validation Issues

Troubleshooting Run-time Permissions Issues

Troubleshooting 64-bit Issues

Troubleshooting the Integration Services Service

Troubleshooting Service Startup

Verify the startup type of the service

Troubleshooting Service Permissions

Finally Working with Debug Dump Files

Thanks,

DelayValidation property and ValidateExternalMetadata property in SSIS

When we are developing packages at times it happen that we cannot connect to our data sources or portions of package cannot validate until prior task in the package have excuted at run time.


When you are developing packages in BI studio the data sources are not available at development time or you have using one table that would create by previuos Execute SQL task. As the table will create at run time it will give validation error at development time.

SSIS includes the following features to help you aviod validation errors that would otherwise resulted from the below conditions.

Work Offline when data sources are not available:

Normally, SSIS Designer tries to connect to each data source that is used by your package to validate the metadata associated with sources and destinations. This causes validation errors when the data sources are not available. To prevent these connection attempts, you can enable Work Offline from the SSIS menu. Unlike the DelayValidation property, the Work Offline option is available even before you open a package. You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.

Configure the DelayValidation property:

on package elements that are not valid until run time. You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors.

For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time.

The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes.

BY Default it will be False. We need to set it true.

Normally you must leave this property set to True on the same package elements when you deploy the package, to prevent the same validation errors at run time.

The DelayValidation property can be set on a Data Flow task, but not on individual data flow components.

You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to false.

However, when the value of this property is false, the component is not aware of changes to the metadata of external data sources


Thanks,

FAQ: Integration services tutorials, video tutorials, real time examples,Blogs, and articles etc.....

Check below post from Phil Brammer. Its very helpful and all source guide to learn and become master in SSIS.


http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/24d6967c-a11a-488a-9811-bd50a1a98322

Thanks,

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

I have one package it runs well when I run from BIDS, If I run the same package through SQL Agent Job, its getting fail. After digging into more details I have found the below KB article from Microsoft Which explains all scenarios regarding all the solutions available for above error.

http://support.microsoft.com/kb/918760



Thanks,

How to concantenate multiple rows into Single Row

Hi,

In many cases we face the issue making multiple rows into single rows. Please go through below script to make multiple rows into single rows.

CREATE TABLE #TEMP
(
EMPID INT,
EMPNAME VARCHAR(100)
)

INSERT INTO #TEMP
SELECT 101,'EMP1'
UNION ALL
SELECT 102,'EMP2'
UNION ALL
SELECT 103,'EMP3'
--- WHEN YOU QUERY THE TABLE YOU WILL GET BELOW OUT PUT---
SELECT * FROM #TEMP

EMPID EMPNAME
101 EMP1
102 EMP2
103 EMP3


DECLARE @EMPNAME VARCHAR(100)
SELECT @EMPNAME =COALESCE(@EMPNAME+',','')+EMPNAME
FROM #TEMP

PRINT @EMPNAME

RESULT:

EMP1,EMP2,EMP3

SQL SERVER 2005 (T-SQL ) Interview Questions - PART 1

1) Differences between Unique index and unique constraint?

· UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design.
· UNIQUE constraints can be created as part of a table's definition and, as a by-product, SQL Server will automatically create a unique index under the hood when the constraint is created.
· In contrast, defining unique indexes are part of designing the physical data model and they are not ANSI standard.
· From a performance standpoint, UNIQUE constraints and unique indexes are effectively the same to the query optimizer.
· Recommended the ANSI standard that implement column uniqueness via constraints.
· UNIQUE constraints help in better documenting a table and use them over defining unique indexes.


2) Differences between Users defined functions and Stored procedures?

· User defined functions can be used in sql statements anywhere in the Where/Select /having sections where as Stored Procedure cannot be used.


3) Differences between Truncate and Delete statements?

· Truncate statement deletes all the rows from table at single execution
· Delete statement deletes specific rows depends upon condition. If, no condition it also delete all rows in row by row
· Where clause can be used in Delete statement, whereas in Truncate cannot be.
· Truncate statement faster than Delete statement
· Truncate statements do not log entries, whereas Delete statement does log entry.
· Because of no log entries truncate statement faster than delete statement.
· Because of no log entries it is not possible roll back data after Truncate statement execution, whereas it can possible roll back data after Delete.
· Truncate operation is a minimally logged operation, whereas Delete operation is fully logged.


4) How many types of Triggers in sql server 2005?


· Sql server 2005 provides 2 types Triggers namely DML Triggers and DDL Triggers


5) Differences between DDL and DML Triggers?

· DML Triggers generally used to perform operations against the data that was modified in a table.
· DML Triggers fire in response to INSERT, DELETE, and UPDATE statements executed against a specific table.
· DDL Triggers fire in response to DDL commands executed on the server.
· DDL Triggers are generally used for security and auditing purposes.


6) What are Recursive and Nested Triggers?

· Only DML Triggers can have recursive riggers or nested triggers.
· When Triggers are fire in response to DML operations, there is a possibility for a Trigger to cause itself to fire or to fire other additional Triggers.
· If a trigger causing itself to fire is called Recursive Trigger
· When a trigger change cause other Trigger to fire it is called Nested trigger.
· Sql Server has a mechanism to control recursive Triggers
· By default Recursive Triggers option of a database is normally set to off. If need turn it on explicitly.
Note:- An Instead of Trigger does not fire recursively


7) What type of commands or constructs cannot use within Trigger?

· Databases cannot be created, altered, dropped, backed up and restored.
· Cannot create, alter, and drop Indexes on Tables which causes structural changes.
· Sql Server does not support Triggers against system Tables and dynamic management views (DMVS).
· Triggers will fire only in response to logged operations ,
· Triggers will not fire in response to Minimally Logged operations such as Write Text and Truncate operations.


8) What is a Trigger?

· A Trigger is a specialized implementation of a Transact sql or CLR batch that automatically runs in response to an event database.
· A Trigger can fire in 2 different modes namely 1)After 2)Instead

9) Differences between After and Instead of Triggers?

· After Triggers fires when sql server completes all action successfully.
· Instead of Trigger causes sql server to execute code inside of Trigger.
· After Triggers can create on tables only
· Instead of Triggers can define on Tables and Views.
· The most common usage is to use Instead of Triggers on views to update multiple base tables through view.
· After Triggers create any number on table
· Only one Instead of Trigger for each type insert, update or delete.

10) What are the restrictions on the select statement with in a view?

· Compute and compute by clause are not allowed.
· Cannot used INTO keyword and OPTION clause.
· Temporary tables and table variables cannot be referenced.
· An order by clause can specified only when TOP clause also used.

11) Differences between regular views and indexed views?

· A regular view is a select statement that is referenced by a name and stored in a sql server.
· A regular view dose not contains any data.
· An indexed view is a view it has a clustered index created against it, which causes sql server to materialize and store the results of the query defined in the view on disk.

12) How to find Top 10 worst performance Queries in a Database?

· Open Management studio
· Connect to required server
· Open object explorer
· Double click on data bases folder to open databases list
· Right click on required database
· Select report à Custom reportsàTop Transactions by age.

13) How to find number of rows per index page?

· Sql server 2005 allows 8060 bytes per index/data page. The data or index rows allowed per page is the result of 8060/ size of single index row
· Number of rows per index/data page =8060/size of index/data page

14) What is external fragmentation?

· External fragmentation is the condition in which physical order of index pages does not match logical order

15) Why are statistics important to query performance?

· During the query evaluation, the query optimizer uses the statistical information to estimate the cost of using an index and determine the optimal plan for a query

rs access denied

HI ,

Recently I have faced the issue with reporting server. I have successfully installed new SQL Server 2008 with allowing default configurations. But when I am rendering reprot server I have got error rs access denied.

Reason:

After googling and digging into more details I have got fixed the issue. The reason behind is I am running Vista Business OS and I am a Administartor ( when we have the right to install any soft ware we are administrators), So I have successfully installed SQL Server 2008 with out any hurdels. Here I have noticed that when I am installing any soft ware first it will ask right permisson to comtinue the installation meaning even though I am administartor by default I am user. So whenever you have installed SQL Server on your local machine with all administartor rights, you also must render the report server URL with admin scope.

Solution:

Right click on browser and click on Run as administartor. Now enter the report server URL and render it, I hope it will work now.

The same thing will happen with Analysis services. You need remember Analysis services only works with windows authentication. When you installed Analysis services with admini rights and you also need to create solution in admin rights.

To create solution in admin rights

Right Click on BIDS icon and select run as administartor
Then create solution and now deploy, it wont give any error.

SSIS Grammer Series - Article 1

• Hi,
  
  I am writing here some SSIS basics and SSIS glossary. I think it may be useful for quick reference.
  
  
  
  Control Flow Items
  Various Control Flow Items:
  · Bulk Insert task: The Bulk Insert task lets you perform a fast load of data from flat files into a target table. It’s similar to using BCP to bulk copy a file into a table, and is appropriate for loading clean data. If you need to transform data that’s stored in a flat file, you should instead use a data flow task that uses the flat file as a source.
  · Execute SQL task: The Execute SQL task, as you might expect, will execute a SQL statement against a specified database. The database need not be SQL Server, but the statement must be written in the appropriate dialect of SQL. Use this task to perform database operations like creating views, tables, or even databases. A common use of this task is to query data or metadata, and to put the query results into a variable.
  · File Transfer Protocol and File System tasks: Use the File Transfer Protocol task to transfer files and sets of files. Use the File System task to perform file system operations like copying, moving, or deleting files and folders.
  · Execute Package, Execute DTS2000 Package, and Execute Process tasks: The Execute Package task executes an Integration Services package. With it you can (and should) break a complex workflow into smaller packages, and define a parent or master package to execute them. Create a separate package to populate each table in your data warehouse database. The use of parent and children packages enables the modularization and reuse of complex logic. If you have DTS2000 packages running already in your production system, you can run them with the Execute DTS2000 Package task. The Execute Process task will run any operating system process. For example, you may have a custom process that will generate the source system extract, or you may invoke a non-SQL Server relational database’s bulk loader.
  · Send Mail task: The Send Mail task sends an email message. You will almost certainly use this task in an event handler, for example to send a message to an operator about a processing failure.
  · Script and ActiveX Script tasks: These tasks are available to perform an endless array of operations that are beyond the scope of the standard tasks. The ActiveX Script task is provided for backwards compatibility to DTS2000; use the Script task for new work. The Script task uses Visual Basic .NET from the Visual Studio for Applications environment. Or, you can use any .NET language to create a custom task that will become available in the list of control flow tasks. Defining a custom task is a programming job, rather than simply scripting, but has the significant benefit of re-use.
  · Data Mining and Analysis Services Processing tasks: The Data Mining task runs an Analysis Services data mining query and saves the results to a table. The Analysis Services Processing task will launch processing on Analysis Services dimensions and databases. Use the Analysis Services DDL task to create new Analysis Services partitions, or perform any data definition language operation. There are Data Mining and Analysis Services Data Flow transforms, as well as these control flow tasks. Use the Analysis Services control flow tasks to fully or incrementally update your databases and models. The use of the corresponding Data Flow transforms is discussed in the next section.
  · XML and Web Services tasks: The XML task retrieves XML documents and applies XML operations to them. Use the XML task to validate an XML document against its XSD schema, or to compare or merge two XML documents. Use the Web Services task to make calls to a web service.
  · Message Queue, WMI Data Reader, and WMI Event Watcher tasks: These tasks are useful for building an automated ETL system. The Message Queue task uses Microsoft Message Queue (MSMQ) to manage tasks in a distributed system. You can use the WMI tasks to coordinate with the Windows Management Interface, and automate the execution of a package or set of tasks when a specific system event has occurred.
  · ForEach Loop, For Loop, and Sequence containers: Use containers like the ForEach Loop and For Loop to execute a set of tasks multiple times. For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics. The Sequence container groups together several tasks. Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence. We used a sequence container in the package, for the Sales Reason and Sales Reason Bridge tables. You can also group control flow objects, and collapse or expand those groups. There’s no task for grouping. Simply select several objects, right-click, and choose Group..
  · Data Flow task: The Data Flow task is where most ETL work is performed. The Data Flow task is discussed in the next section.

SQL Server 2005 BI Video Tutorials

Hi Guys,

I am posting here some of good free SQL Serever BI video tutorials websites. I believe these web sites are good enough one who want to grab basics as well as some tips and tricks. I will keep update of this page as well as I find any new one. If any one knows any other web sites please post your comment here.

http://www.learnmicrosoftbi.com/ ( Only for SSAS )
http://www.jumpstarttv.com/ (All)

The value violated the schema's constraint for the column

HI,

Problem Source:

In general we get this type of error when we try to move data from Sql Server table ( or any other Source) to Excel 2007, If any of column data having more than 255 character length the transfermation gets fail and it will throw the above error.

Reason:

We need to remember we are exporting to Excel 2007 ( Connect thorugh OLEDB Connection Manager) not to Excel 2003 which will connect thorugh Excel connection manager.

There are siginificant differences between OLEDB Connection Manager and Excel Connection Manager.


When we try to export excel 2003 SSIS will convert all source data types to corresponding SSIS data types, becuase excel connection manager internally uses excel driver for its funtionality.

The Excel Connection Manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources

The Excel driver recognizes only a limited set of data types.

For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).

Integration Services maps the Excel data types as follows:

• Numeric double-precision float (DT_R8)
• Currency currency (DT_CY)
• Boolean Boolean (DT_BOOL)
• Date/time datetime (DT_DATE)
• String Unicode string, length 255 (DT_WSTR)
• Memo Unicode text stream (DT_NTEXT)

So when we export data into excel 2003, SSIS first get 8 rows as sample from source, if any column data length > 255 character length in those 8 sample rows the excel connection manager will assign DT_Ntext by default else it assign DT_WSTR 255.

you can check it by click Advanced Editor on Excel Destination task. In the advanced editor on the External column and Properies tab check on External column s list the Column > 255 will assign DT_Ntext data type.

Hence it work in Excel 2003.

But the case in excel 2007 is different because we connect excel 2007 by using OLEDB connction amanager. It doesn't support DT_Ntext data type. So it always assign DT_WSTR 255 irrespective of source column length. So transfer mation will fail and throw above error.


Solution:

Export to excel 2003 instead of excel 2007. It's may be dirty solution, but no option.
If you have more than 65536 rows try to split into multiple sheets.

Note: Excel 2003 supports only 65536 rows per sheet. while Excel 2007 supports 10,00,000 rows per sheet.

Its all my experience only, it may be worng.

Please place any Suggestions and Comments .

Thanks,

SQl Queries to find Last and First date of Current Month, Previous Month, And Next Month

Hi All,

We might have encounter at many places write quries for dates like Last and First date of Current Month, Previous Month, And Next Month. Its very common we may forget some quries which we have written earlier in time, so there is no exception for these above simple quries too.

So I have written here below to find last and first of any month. Hope this will helps at least who are new to Sql.



Declare @InputDate DATETIME

Set @InputDate = GETDATE()

First Day Of Current Month =CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

Last Day Of Current Month = CAST(CONVERT(VARCHAR(10),DATEADD(DD, -1, DATEADD(M, 1, CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01')),101) +' 23:59:59' AS DATETIME)

హాయ్,
మనము ఒక ప్యాకేజీ డెవలప్ చేసినపుడు అది మన లోకల్ సిస్టం లో బాగా వర్క్ అవుతుంది. ఐతే ఎప్పుడయితే మనము ఆ ప్యాకేజీ ని ప్రొడక్షన్ సెర్వెర్స్ లో పెట్టినపుడు అది వర్క్ అవదు ఎందుకు అంటే మన లోకల్ సిస్టం జనరల్ గా 32 బిట్ ఆపరేటింగ్ సిస్టం మరియు 32 బిట్ సర్వర్ వెర్షన్ సాఫ్ట్వేర్ వాడుతుంటము. కాని మన ప్రొడక్షన్ సెర్వెర్స్ లో 64bit ఆపరేటింగ్ సిస్టం మరియు ౬౪ బిట్ సర్వర్ ఎడిసన్ ఊంటాయి. కనుక 32 బిట్ లో డెవలప్ చేసిన ప్యాకేజీ 64bit ఎన్విరాన్మెంట్ లో వర్క్ అవదు. ఇలా వర్క్ అవాలంటే ఫస్ట్ ప్యాకేజీ ని 64bit సర్వర్ లో కి మూవ్ చేసినపుడు మనము బిడ్స్ లో ప్రాజెక్ట్ ప్రోపెర్తీస్ కి వెల్లి సెట్టింగ్స్ చెంజు చెయ్యాలి. స్క్రీన్ షాట్ చుడండి. ఈ సెట్టింగ్స్ తర్వాత ఎర్రర్ రాదు.

ఈ ప్యాకేజీ ని రన్ చెయ్యడానికి జాబు క్రేఅతే చేస్తే అది కూడా ఎర్రర్ వస్తుంది. అప్పుడు ఈ ప్యాకేజీ ని 32 బిట్ కమాండ్ లైన్ టూల్ ద్వార రన్ చెయ్యవచ్చు. జాబు క్రీ అటే చేసే డప్పుడు కంమ్నాద్ లైన్ ఆప్షన్స్ కి వెళ్ళి కంమ్నాద్ లైన్ టూల్ ద్వార ఎక్సెకుతె చెయ్య వచ్చు. "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "C:Sample.dtsx".

ఇది వర్క్ అవుతుంది. ఏమైనా డవుటు ఉంటే నా కు మెయిల్ చెయ్యండి.

మీ,

సతీష్