Ms Sql Server Technology Discussions

[pachimirchi]

[quote name='loveindia' timestamp='1363536504' post='1303439181']

nenu next batch start chesinappuudu istaanu.. free gaaney.. I take only money from consultancy people not students..
[/quote]

[chelsea]

[quote name='loveindia' timestamp='1363536504' post='1303439181']

nenu next batch start chesinappuudu istaanu.. free gaaney.. I take only money from consultancy people not students..
[/quote]


Thanks ba

[loveindia]

[quote name='pachimirchi' timestamp='1363538976' post='1303439311']

[/quote]

[img]http://www.desigifs.com/sites/default/files/sd_blush.gif?1290372289[/img]

[loveindia]

[quote name='chelsea' timestamp='1363539153' post='1303439322']


Thanks ba
[/quote]

np.. em chestuntaav nuvvu.. [img]http://www.desigifs.com/sites/default/files/nag_style1.gif?1290370831[/img]

[ghazni]

ole Db2 providers ani mudu unayi in ssis connection ki.... ibmda400 . ibmdasql, ibmdarla ani so indulo edina slect chesukovacha evarikaina telistey simple ga ee three providers gurunchi chepandi.........

[ghazni]

[img]http://www.andhrafriends.com/uploads/profile/photo-thumb-24005.jpg?_r=1346362665[/img]

[deals2buy]

[quote name='ghazni' timestamp='1363642394' post='1303445370']
ole Db2 providers ani mudu unayi in ssis connection ki.... ibmda400 . ibmdasql, ibmdarla ani so indulo edina slect chesukovacha evarikaina telistey simple ga ee three providers gurunchi chepandi.........
[/quote]
OLEDB kante ODBC connection better anukunta kadaa DB2 ki

btw ee ibmda names eppudu vinalede

[ghazni]

[quote name='Kaarthikeya' timestamp='1363700754' post='1303448955']
OLEDB kante ODBC connection better anukunta kadaa DB2 ki

btw ee ibmda names eppudu vinalede
[/quote]
ivi i series AS 400 vi......... avi odbc ela avtayi........

[mtkr]

long back chelsea adigaadu e Q... ( but ippudu avasaram vachindi naaku)

whts diff b/w (SSIS OLEDB connections)
microsoft OLEDB provider for oracle
and
oracle provider for OLEDB
???

nen execute SQL task use chesi oka oracle tbl nundi total count query chestunna ...
later ade exec sql task lo result in oka variable ki assin chestunna...

its working good for microsoft olrdb for oracle
kani ade process oracle prov for oledb tho chesthe raavadam ledhu..
task run avutundhii but variable update kaavadam ledu

[deals2buy]

[quote name='ghazni' timestamp='1363704654' post='1303449217']
ivi i series AS 400 vi......... avi odbc ela avtayi........
[/quote]
ok..ya may be...naaku Legacy lo ODBC ani chustinattu gurthu

[mtkr]

[quote name='mtkr' timestamp='1363712904' post='1303450134']
long back chelsea adigaadu e Q... ( but ippudu avasaram vachindi naaku)

whts diff b/w (SSIS OLEDB connections)
microsoft OLEDB provider for oracle
and
oracle provider for OLEDB
???

nen execute SQL task use chesi oka oracle tbl nundi total count query chestunna ...
later ade exec sql task lo result in oka variable ki assin chestunna...

its working good for microsoft olrdb for oracle
kani ade process oracle prov for oledb tho chesthe raavadam ledhu..
task run avutundhii but variable update kaavadam ledu
[/quote]

found a wrk around...

eve update chestha clr gaaa...

[deals2buy]

[b] How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008)[/b]


I was posed the question today, how do I migrate my SQL Server 2000 DTS packages to a SQL 2005 Server without upgrading them, thus leaving them as legacy DTS 2000 packages within SQL 2005?
In other words, how do I copy or move DTS 2000 packages into SQL Server 2005...
If you did an in-place upgrade from SQL 2000 to SQL 2005 (right on top of the old instance) this isn't a big deal, because the DTS packages are still there. However, if you aren't doing an in-place upgrade, the MSDB database where the DTS packages are stored wasn't kept as part of your upgrade, so maybe you don't have any DTS packages on your new SQL 2005 instance and need to get them over there.
To my suprise, there wasn't much official documentation on the subject, and didn't seem to be any wizard to help accomplish the goal, so its time to get creative...
============
[b]Upgrading packages to SSIS would be best for long term![/b]
The ideal long term solution is to not keep DTS 2000 packages, and instead upgrade your DTS 2000 packages to SSIS 2005 packages either using the [url="http://msdn.microsoft.com/en-us/library/ms143496.aspx"]Package Migration Wizard[/url] (just right click on Data Transformation Services in 2005 or 2008 object explorer), or using the MVP solution DTS XChange from [url="http://www.pragmaticworks.com/"]http://www.PragmaticWorks.com[/url] (good tool by the way) which has a much better chance of upgrading your DTS package cleanly to SSIS.
If that's not ideal to you... SQL 2005 and SQL 2008 can continue to run DTS 2000 packages in legacy mode (DTSRun.exe to run them, or Execute DTS 2000 Package Task within an SSIS control flow), but keep in mind the next version of SQL will likely not include the ability to run and edit DTS 2000 packages.
In SQL 2005 and 2008 you can even use the DTS 2000 designer within Management Studio to edit such existing DTS 2000 packages, by simply installing the backward compatibility DTS Designer Components (see the latest feature pack download page for "Microsoft SQL Server 2000 DTS Designer Components" [url="http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en"]http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en[/url] )
But for long term - beware, Microsoft will only support parts of the DTS 2000 legacy features in SQL 2005 and SQL 2008 for the product lifecycle of SQL Server 2000, so if you run into a bug with DTS 2000 itself, could be it is not eligable to be fixed in SQL 2005 and SQL 2008 as part of the DTS 2000 legacy components. There are special considerations and details to read about [url="http://msdn.microsoft.com/en-us/library/cc707786.aspx"]here[/url]
A more complete story of the usage of legacy DTS components in SQL 2005 and 2008 is described in SQL Books Online [url="http://msdn.microsoft.com/en-us/library/bb500440.aspx"]http://msdn.microsoft.com/en-us/library/bb500440.aspx[/url]
============
[b]Let's not worry about upgrading for now... let's just copy what packages I have[/b]
So in the short term if you are not prepared to upgrade all your DTS packages to SSIS, the question is how to move your DTS 2000 package and leave them in the old legacy format and continue to run them, in case that you may not have time to immediately upgrade your packages and test them out.
The "Package Migration Wizard" is not the tool for this task, because it converts DTS 2000 packages into SSIS 2005 Packages, thus changing the format to the new format. Migrate in this tool means, really migrate + upgrade.
Never fear, we can think of a few fairly easy solutions on how to move DTS 2000 packages from SQL 2000 (MSDB) to SQL Server 2005 (MSDB) and keep the legacy format:
===========
[b]METHOD 1. You can save each package one by one from 2000 to 2005... [/b]maybe a bit tedious, but if you've only got a couple, its simple enough.
+ Open SQL 2000 Enterprise Manager
+ Expand the Server name
+ Expand Data Transformation Services
+ List each package in the right pane
+ Doubleclick each package to open the designer
+ Within the designer, click the Package Menu > save As
[b]METHOD 1A.[/b] Save directly to SQL 2005
+ In the Save DTS Package dialogue, select location "SQL Server" and point
to your SQL 2005 Msdb database
[b]METHOD 1B.[/b] Save to structured storage files, and copy those files to SQL 2005, and import into Legacy 2000 Packages one at a time.
+ In the Save DTS Package dialogue, save to location "Structured Storage File"
+ Provide a location and name for the .dts file.
+ Copy the file to a location accessible to the SQL 2005 Server
+ Find the Data Transformation Services icon in Object explorer in 2005. (Object Explorer connected to SQL Database Engine, under the Server name > Management > Legacy > Data Transformation Services)
+ Right click > "Import Package File..." and point to the structured storage file.
(NOTE: I got a few errors when the DTS 2000 package had multiple versions stored inside when using this method B. I didn't have time to investigate, but its worth mentioning there could be some complications with this method.)
===========
[b]METHOD 2. Copy the rows in the sysdtspackages table from the old msdb to the 2005 msdb. Seems easy enough... [/b]and this is better when you have a lot of packages to move.
Similar to: [url="http://www.sqldts.com/204.aspx"]http://www.sqldts.com/204.aspx[/url]
The packages live mainly in MSDB database in the table "sysdtspackages". The actual contents of the DTS package live in the column "packagedata", which is an Image column. Its hard to "see" the contents in the table, but you can see the names of the packages and dates and such.
I tried to use the Import Export Wizard to achieve this method, but since the wizard doesn't list System tables in MSDB as being eligable for transfer, that's not a viable option.
Therefore I see two easy methods
A. Restore a renamed copy of MSDB to the new server and copy the rows from the old table to the new table.
B. Use a linked server from 2005 pointing back to 2000 to copy the msdb sysdtspackages table rows.
===========
[b]METHOD 2A: Use backup/restore to get the msdb table over to the 2005/2008 server[/b]
[b]Warning:[/b] I'm not suggesting you restore a SQL 2000 MSDB database over top of (replacing) a SQL 2005 or SQL 2008 MSDB database, since that will cause a major problem. System databases cannot be copied between SQL versions like that, and SQL Agent will not start if you attempt that. Instead, the suggestion for this method is that you restore the SQL 2000 MSDB database with a NEW NAME and it will become a user datbase (not a system database) in SQL 2005/SQL 2008.

-- on SQL 2000 machine
backup database msdb to disk='c:\sql2000msdb.bak'
-- Copy the backup file to a folder where SQL 2005/2008 can get to it (UNC share or local disk, but local disk is less error prone)
-- on the destination SQL 2005/2008 machine, restore the 2000 msdb with a [b]new name[/b] and rename the .mdf and .ldf files too
RESTORE DATABASE my2000msdb
FROM DISK='c:\sql2000msdb.bak'
WITH MOVE 'msdbdata' TO 'c:\temp\msdb_2000.mdf',
MOVE 'msdblog' TO 'c:\temp\msdb_2000.ldf'
-- copy rows from the restored 2000 table into the 2005 msdb. Run on the destination 2005 machine:
INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM my2000msdb.dbo.sysdtspackages
-- check the results
SELECT * FROM msdb.dbo.sysdtspackages
===========
[b]METHOD 2B: Linked servers make life easy - no file copy needed.[/b]
Make a linked server (pointing back from 2005/2008 to the 2000 servername)
+ Connect to Object Explorer in 2005/2008 Management Studio.
+ Expand the SQL 2005/2008 Server name
+ Expand the Server Objects folder
+ Expand the Linked Servers folder
+ If you don't already have a linked server pointing to your SQL 2000 server, right click and make a new one (Linked Server name is your SQL 2000 server name (server\instance) and the server type is "SQL Server" with security "Be made using the login's current security context")
-- Use 4-part naming in your FROM clause to copy the rows from the 2000 server msdb into the 2005 msdb table. Note the square brackets for my sql 2000 server name, which are helpful if you have a space in your server name, or a backslash for the instance name such as [servername\instancename]. Add a where clause if you only want certain packages moved.

INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM [linkedserver2000].msdb.dbo.sysdtspackages


INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM [linkedserver2000].msdb.dbo.sysdtspackages WHERE name LIKE '%packagename%'
[size="2"][color="#ff0000"][size="2"][color="#ff0000"] [/color][/size][/color][/size]
+ Consider dropping the Linked Server (right click - delete) after your DTS package copy (if you aren't using it for other purposes) since that can leave a security opening if you forget to lock it down, but don't do that if other apps depend on it!
===========
[b]METHOD 3. Use DTS package API's to do the DTS package move:[/b]
There is a tool called "DTS Backup 2000" from a 3rd party website which utilizes a bit of magic behind the scenes to copy some or all of your DTS Packages. Read about it:[url="http://www.sqldts.com/242.aspx"]http://www.sqldts.com/242.aspx[/url] Download it: [url="http://www.sqldts.com/272.aspx"]http://www.sqldts.com/272.aspx[/url]
Seems simple enough, and though I don't endorse it on behalf of Microsoft, but on the surface seems very nice and simple.
+ Point it to your source SQL 2000 server.
+ Pick the packages you want to copy. Provide a password if needed.
+ Point it to your destination SQL 2005 server.
+ Voila you are done... just need to test.
===========
[b]After Thoughts...[/b]
Now that you got them copied into SQL 2005/2008 MSDB, you should check them all and make sure they got copied successfully and that they work.

DTS 2000 legacy packages appear in the list in SQL 2005/2008 Management Studio Object Explorer under the Server name > Management > Legacy > Data Transformation Services.

Packages copied in the above manners will NOT show up in Object Explorer connections to the SSIS (Integration Services) , because they have not been migrated to SSIS.

SSIS packages live in either the file system, or in a different table. The Integration Services service and runtimes (DTExec.exe, DTExecUi.exe) allows you to browse only these kinds of locations:

- XML documents .dtsx files) on your hard drive,
- in the sysdtspackages90 table in MSDB (select * from msdb.dbo.sysdtspackages90) for SQL 2005
- in the sysssispackages table in MSDB (select * from msdb.dbo.sysssispackages) for SQL 2008
- SSIS Service pointers to files and MSDB from the (MsDtsSrvr.ini.xml) file to the above locations
DTS legacy packages live in the msdb.dbo.sysdtspackages (minus the 90 ending) table and are displayed in SQL Server object explorer under the Management > Legacy components in object explorer.

===========
[b]After copying them, make sure to test them.... and think about server names, OLEDB providers and ODBC drivers needing to be installed on this new machine, and test connectivity. If you DTS Package needs to access any flat files on shares, make sure those shares are set up and working for the security context which will be running the package (SQL Agent or a SQL Agent ***** Account)[/b]

Consider that after moving from one old 2000 server to a new 2005 server, the Connection String references within the DTS 2000 package have not changed. If they point to the SQL 2000 database server, that didn't change, and will not point to your new SQL 2005 server, so you made need to open each DTS 2000 package and edit those connection strings and server names to point to your new server if your design requires.

Alternatively you can build an "Alias" via the "SQL Server Configuration Manager" in the start menu to redirect ALL requests to a certain server name (old server name) to a different location (the new server). Beware that all client apps on the machine where you set this (presumably the server in this scenario where you run your DTS 2000 packages in SQL 2005) so this may be undesirable since it affects much more than just DTS 2000 legacy packages, but if you can't open each and every package to do the edit, then it may be the easiest route.

Consider, since DTS packages can only run in 32-bit mode (there is no 64-bit DTSRun.exe) any connectivity libraries settings and providers and any Aliases installed would be the 32-bit versions.

If your DTS packages point to 3rd party databases, using ODBC drivers or OLEDB providers, those providers and drivers have to be installed on whatever machine the DTS packages will run from. You might have to take inventory, and make sure that connectivity works before setting up any SQL Agent jobs to run these newly moved packages on the destination machine. You can test ODBC from the DSN ODBC Datasources (in administrative tools) and to test OLEDB, I like to rename a notepad .txt file to extension .udl and doubleclick the .udl to step through the Universal Data Link wizard to point to an OLEDB provider and make sure it at least establishes a test connection successfully before worrying about DTS package internals.
FYI, I won't get into the details today, but I imagine you can script any SQL Agent jobs from your SQL 2000 machine (right click the job in Enterprise Manager > All Tasks >Generate SQL Script...) and run those scripts on SQL 2005 msdb to get a similar job set up for scheduling your legacy DTS packages.

If you find your DTS Package fails after being moved, the best way to get a good detailed error report is to open the DTS Designer, click the background of the design surface, Select the "Package" menu > Properties. Fill in the box with an "Error file:" name and location. Run the DTS package and the output log will be informative (hopefully) on what caused the failure.

===========
In summary, I hope this helps you save your DTS 2000 packages in a way that's painless until you can spend more time and upgrade the packages to the newer SSIS format.

[ChampakDas]

sort of similar thing(below) which I had experienced....theoretical explanation of paging in tempdb


[b] When a TempDB Starts Dragging a Leg[/b]

The TempDB database is available to all users of a SQL Server instance to house temporary objects such as cursors and tables, and it is where SQL Server creates various internal objects for sorting and spooling operations, as well as index modifications. It can get busy in there, especially if there are unruly processes. Of course, as developers and DBAs we must do all we can to mitigate potential problems by shying away from complex and unwieldy routines that overuse TempDB. However, we must also look out for configuration problems that can cause TempDB to start "dragging a leg".
Common causes of TempDB problems, in terms of configuration, include:[list]
[*]Only one TempDB data file
[*]Too many TempDB data files (like one per core on an 80-core system)
[*]Multiple TempDB data files, but with uneven sizes or uneven auto-growths
[/list]
In order to understand why TempDB contention issues might arise, if we have too few TempDB files, let's go back to storage basics for a minute. SQL Server stores data, in disk or in memory, in 8KB pages, usually stored in groups of 8 KB pages (an extent).
[img]http://www.red-gate.com/products/dba/dba-bundle/entrypage/assets/images/Lesson3_image1.png[/img]

Usually, SQL Server stores one object per extent but not always. If we're creating and dropping many small TempDB objects (for example, temporary tables, table variables, cursors), we will end up with [b]mixed extents[/b], where data for several objects is stored in the same extent.
There is only one page, the Shared Global Allocation Map (SGAM), which tracks space usage in extents for each 4GB of space. The following figure represents a single TempDB data file, with the black shaded blocks representing the mixed extents.
[img]http://www.red-gate.com/products/dba/dba-bundle/entrypage/assets/images/Lesson3_image2.png[/img]

Every time we drop, create or expand an object in TempDB, SQL Server first has to access this single SGAM page. It then scans the Page Free Space (PFS) page to find out which mixed page it can allocate. After allocation, SQL Server must update the PFS and SGAM pages appropriately. If we have many mixed extent allocations, it can cause massive contention on the initial, single SGAM page.
As you can imagine, this is the reason why we might need to add more TempDB files. If we have four files for TempDB, then we have 4 SGAMs tracking extent usage and, assuming each file is evenly used, we spread the load and ease contention.
However, this only really works if we make sure to keep the TempDB files all the same size, meaning setting the same initial size for each file, and the same auto-growth increment. If we don't, and we end up with one TempDB file that is bigger than all the others, then we'll return to a situation of SGAM contention, as most of the action will hit the largest file.
[quote name='GatisKandis' timestamp='1363281555' post='1303424105']
I came across this issue since yesterday and with some research here's what I got. Might be useful: In case if you ever come across this error
Attempt to fetch logical page (1:3048) in database 2 failed. It belongs to allocation unit 6845477241323323392 not to 1657330478129152000.

If you are familiar with page allocation then you surely must have understood the issue by now...So, basically the problem is if you are using inserting records into temporary table using the select query and if that select query includes this temporary table then boom, you get this error. How to get rid of this? - there's a patch available for download on sql server 2008 ([url="http://support.microsoft.com/kb/960484"]http://support.microsoft.com/kb/960484[/url]) and if you don't want to install this patch just like me then all you got to do is add a clustered primary index to the temporary table....now it makes sense right? how science and technology are related..sweet science
[/quote]
[quote name='GatisKandis' timestamp='1363281681' post='1303424121']
one more point even if you run the console command procedure it wont give any errors thats tricky part here
no errors ani chupistundi..even though there is allocation problem

[size=3]CHECKDB found 0 allocation errors and 0 consistency errors in database[/size]
[/quote]

[ChampakDas]

xml documents meda neeku paga [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]
[quote name='Kaarthikeya' timestamp='1363874799' post='1303462058']

SSIS packages live in either the file system, or in a different table. The Integration Services service and runtimes (DTExec.exe, DTExecUi.exe) allows you to browse only these kinds of locations:

- XML documents .dtsx files) on your hard drive,
- in the sysdtspackages90 table in MSDB (select * from msdb.dbo.sysdtspackages90) for SQL 2005
- in the sysssispackages table in MSDB (select * from msdb.dbo.sysssispackages) for SQL 2008

[/quote]

[flavours]

help for my friend......1st proj (non-tech bkgrnd).........

[color=#000000][font=arial, sans-serif][size=3]create a job to copy tables from one sql server to the other and then copy it to the oracle db.[/size][/font][/color]
[color=#000000][font=arial, sans-serif][size=3]google chesthe evo evo vasthunayi....nadhi sql server tech kaadu.....telisins experts please help cheyandi.......[/size][/font][/color]