Creating temporary table using a template table dynamically

I'm having an application for users to extract data from sql 2K. Problem is that I need to split the huge amount of data into few pages on the application screen.

Since my order by clause could be dynamic, which is a big problem, the solution I can think of is to populate a temporary table ( within a stored proc ) and assign a column within the temporary table with a running number.

This stored proc would accept among them, an argument, which is the table name of a base table.

However, I would like to create this stored proc in such a way that I could create a temporary table based on given based table name, complete with the field sizes and types. On top of that, would like to add a new column to this temp table to store a running number key.

Opinions appreciated. :)Refer to this linkSQL Performance (http://www.sql-server-performance.com/rd_temp_tables.asp) for information.

Creating SSIS Package to Extract Data from MySQL and Append to SQL 2005

Hi guys,

I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.

I hope you guys can help me with this.

Hoping to hear from you soon.

Thank you so much.

Kind regards,
Neil
I'm sure everyone on the forum will be happy to help. I'd suggest starting by going through the SSIS Tutorials in SQL Server Books Online. Then, as you encounter specific problems or design questions, we'll be happy to answer them.|||

Yep, I agree with John.

Just a hint though, you'll be using a DataReader Source against your MySQL ODBC driver to retrieve the results.

|||

Actually, i have gone through the BOL tutorial and have started creating the SSIS package using DataReader Source. i then provided the connection manager which is in my case using ADO.Net connection (MySQL ODBC 3.51 Driver) but as soon as i enter the SQLCommand and click ok this error displays on the bottom part of the Advanced Editor for DataReader Source.

Error at Data Flow Task [DataReader Source[1]]: System.Data.Odbc.OdbcException: ERROR [HY010]

[MySQL][ODBC 3.51 Driver][mysqld-4.1.12-Debian_1ubuntu3.4-log]

What do u think is causing this?

Thanks,

Neil

|||

If you could provide the query, that would help troubleshoot the issue.

If you are trying to use parameters or a complex query, try building it in a variable and using an expression on the data flow to set the SQL source. Expressions for the data flow can be found by clicking the data flow, opeing the properties window (F4), and looking for the expressions item.

|||

Below is the SQLCommand i am using. What's troubling me is that the DTS for this in SQL 2000 is running smoothly that is why i was thinking maybe i am using the wrong steps in SSIS.

SELECT chatrequestlogs.`chat_session`,
chat_admin.`name`,
chatrequestlogs.`ip`,
FROM_UNIXTIME(chatrequestlogs.created) AS Time_Requested,
FROM_UNIXTIME(chattranscripts.created) AS Time_Created,
chatrequestlogs.status,
LTRIM(substring(chattranscripts.plain, 1, locate(':',chattranscripts.plain, 1) - 1)) AS Name_Registered,
LTRIM(substring(substring(chattranscripts.formatted, locate('<question>', chattranscripts.formatted,1), locate('</question>', chattranscripts.formatted, locate('<question>', chattranscripts.formatted, 1)) - locate('<question>', chattranscripts.formatted, 1)),11)) AS Question,
LTRIM(substring(chattranscripts.plain, locate(':', chattranscripts.plain, 1) + 1)) AS Comments,
chatrequestlogs.deptID,
substring(substring(formatted, locate('<tstamp (', formatted), locate(') tstamp>', formatted) - locate('<tstamp (', formatted)), 10) AS Time_Started,
reverse(substring(substring(reverse(formatted), locate('>pmatst )', reverse(formatted)), locate('( pmatst<', reverse(formatted)) - locate('>pmatst )', reverse(formatted))), 10)) AS Time_Ended,
chat_admin.`email` AS CSCEmail,
chattranscripts.`email` AS CustomerEmail
FROM chatrequestlogs
JOIN chat_admin ON chatrequestlogs.userID = chat_admin.`userID`
JOIN chattranscripts ON chattranscripts.chat_session = chatrequestlogs.chat_session
WHERE FROM_UNIXTIME(chatrequestlogs.created) >= DATE_ADD(NOW(), INTERVAL - '1440' MINUTE)
AND FROM_UNIXTIME(chatrequestlogs.created) < NOW()

|||

Hi guys,

The problem went away after I turned the ValidateExternalMetadata property of the datasource reader to false.

Thank you so much for the replies.

Forums like these are of great help to newbies like me.

Keep up the good work guys.

Kind regards,

Creating SQL 2005 Logins during installation

Hello,

We use one standard account with a password that is never given out.

In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.

That same process does not work for SQL 2005.

Below is a sample of the script that we were deploying for SQL 2000 installations.
-
-- Login: CPAPP

declare @.pwd varchar(50);

SET @.pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @.pwd, @.defdb = 'CPSQL' , @.sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @.encryptopt = 'skip_encryption'

SET @.pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @.pwd,@.defdb = 'CPSQL' , @.sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @.encryptopt = 'skip_encryption'

SET @.pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @.pwd, @.defdb = 'CPSQL' , @.sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @.encryptopt = 'skip_encryption'

-
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

Is this what we should be using for SQL 2005?

Please advise.

Thanks in advance

IN SQL 2005 you should be using

CREATE LOGIN loginname PASSWORD ='hased password' HASHED , SID ='.....'

Have a look at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eb737149-7c92-4552-946b-91085d8b1b01.htm

|||

Simon,

Thanks for your prompt reply.

We were able to run the script and create a login with a encrypted password successfully using "HASHED"

I tried connecting to the server using SQL Management studio by entering the newly created username and the encrypted password (long mix of chars and numerical) in the Connect to Server dialog box in the SQL Server authentication mode.

But we got the error (Microsoft SQL Server, Error :18456)

A couple of quick questions:

1) Is there a way to extract the encrypted password and check our script is working fine by connecting to server --programatically

2)How can we test connection to server using the newly created login name and encrypted password

Are we missing something ?

Could you please throw light on this?

Thanks in advance

|||Probably easiest to ceate your users again usnig the real password and then script them. From there, not sure if the Hash alogorithm has changed.|||

1) You cannot connect knowing only the hash, you need to know the password from which the hash was computed (or some other password that would collide with the original for the same hash value, but chances of finding such a password are slim).

2) To test the connection, you need to know the actual password and use it in the connection test.

The hashing algorithm has not changed, it's still SHA1. What has changed is that in SQL Server 2000, we actually kept two hashes - one for the actual password, the other for the password converted to uppercase. In SQL Server 2005, we dropped the second hash because it was making the hash vulnerable to a dictionary attack (the attack was described in http://www.nextgenss.com/papers/cracking-sql-passwords.pdf). How could this change impact you? It would impact you if you had set the password using a special casing, for example, "PasSworD", but you used to connect with "Password" and forgot the original casing over time. After moving the hash to SQL Server 2005, you must use the original password casing. Other than this, I can't think of any other changes.

Thanks
Laurentiu