from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/artic...ditorialsID=838
"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a PIVOT
relational operator and more."
ROW_NUMBER???? What possible purpose could that have???
Hi,
I got an issue with encrypt and decrypt. I encrypted a data, say 'ok', then I decrypt the encrypted data, but what I got is not 'ok'. but if I encrypt a column, then after decryption, those data asr same with the original data in that column.
In my case,
...
create table mytable ( column Password varbinay(128) )
...
create symmetric key with certificate
...
OPEN SYMMETRIC KEY Sym_Key_01
DECRYPTION BY CERTIFICATE Cert;
UPDATE mytable
SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')
select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable
here, I didn't get the value 'ok' but a another wierd word (like a chinese word).
does someone know the reason?
Thanks,
Jone
When using @original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :
<UpdateParameters>
<asp:Parameter Type="String" Name="ProductType" />
<asp:Parameter Type="String" Name="OpportunityType" />
<asp:Parameter Type="Double" Name="Value" />
<asp:Parameter Type="String" Name="Probability" />
<asp:Parameter Type="Int32" Name="OpportunityID" />
</UpdateParameters>
And my stored procedure like this :
CREATE procedure dbo.UpdateOpportunity
( @ProductType int, @OpportunityType int, @Value money, @Probability int, @OpportunityID int)
as
update Opportunity set ProductType = @ProductType, OpportunityType = @OpportunityType,[Value] = @Value, Probability = @Probabilitywhere OpportunityID = @OpportunityID
Do I need to change both cases of @OpportunityID in my stored procedure to @original_OpportunityID for it to work?
Hi All,
How can I find out the original size of the database?
Thanks.
How can I find out the original size of the Data and Log files?TIARob
View 2 Replies View RelatedI managed to get fuzzy grouping working. The relevant output (_key_in and _key_out) are stored in a new table that is a copy of the old table + fuzzy grouping columns.
How do i get SSIS to store the _key_in and _key_out in the original table?
The new matching column _key_out refers to the new key: _key_in. How could i get SSIS translate that to a matching column that refers to my original key?
I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?
I have tried shinkfile and shrink database with no luck.
Thanks Jeff
Hello, everyone:
I have a table like:
ColName
b
b
b
d
d
d
a
a
c
c
c
I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c
However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d
Can any one have the idea to handle that? Thanks
ZYT
Hi all,
I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.
Has anybody seen this behavior?
Thanks for your help
Luis Torres
Does anyone know how to get the original Login name, while a context switch is in effect.. For example during the use of Original login to SQL is Wayne
Then Execute as Test
Now I need to know that Wayne is the Login and not Test
none of the standard things work, they all use the current context (suser_sname, etc)... Books online includes the following text
SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER will return the name of the impersonated context. Use the LOGIN() function to return the name of the original login during impersonation.
But I cannot find any function called LOGIN() anywhere?
Suggestions>?
I am trying to use SQL Express to support unit testing of functionality which requires database access.
I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.
Here's what I do today:
Before Test:
The original database is copied to a unique location in the temp directory
A unique database name is generated (GUID-based)
I connect to the new database file with:
Server=.SQLEXPRESS;AttachDbFilename="PathToTempFile";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true
During the test all database access is routed to the temporary database GuidDBName
After Test:
Close all active database connections and clear the connection pool
Connect to the master database and detach the database:
Server=.SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'
Delete the temporary file
When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.
Can anyone explain why this is happening and/or provide a solution?
Say I have a result set with two fields numbers and letters.
1 A3 A1 B2 B
The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet
it will reorder the new result set by the Number field and return
123
However, I'd like maintain the Letter order and return
132
Hi,Good morning to All.
While doing practice I did some modifications to some tables in Northwind database.Now, how can I get my original tables back again?
Take Oracle for example:We can modify table data. But when we run demobld.sql script then all tables will replaces with original entries.
Like this I want in SQL Server also. Is there any such facility available in SQL Server?
Thanks in advance,Ashok kumar.
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,
View 2 Replies View RelatedI have been using the below query to copy a record of data that exists in several draft tables to the original tables. how do i change this query such that i can input multiple records at one time so multiple records get copied to original tables from the draft tables
At this time when i feed mutiple requestids to my query it simply errors out
SET NOCOUNT ON
GO
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT RequestId from report_request_draft where requestid in (320762)
[Code] ....
phanindra writes "Hello all,
I am phanindra....
I have a question sir/madam,
suppose we have an view named "VIEW" for a table named "TABLE"
will the values in the TABLE alter if we change it in VIEW
thanks in advance
phanindra"
I created a linked report and I see that the original height and width settings which were set for landscape have changed into portrait:
View 7 Replies View RelatedI have a database called PrimaryJunk that is being log shipped to another location, secondary database is secondaryjunk. From PrimaryJunk to SecondaryJunk logs ship and apply fine with no issues. So I figured lets make sure that I am able to perform a role change and swap the roles and that is not working well. My original primary db is stuck in restoring state.
I manually backed up the active transaction log on primary server by performing a transaction log backup with the option 'backup the tail of the log and leave the db in restoring state'
MS site has the same step but mentions NORECOVERY. I am not sure if my step above does that automatically. http://msdn2.microsoft.com/en-us/library/ms191233.aspx
Wonder if thats the reason my original primary db is still in restoring state. any idea?
When I drill down from one report to another and then depress the back button to return to the initial report, the message 'Generating Report" spins forever without actually running. However, if I depress the regenerate button during this time, it will run the report.
I believe that I may have inadvertingly reset a field attribute to have caused this condition. Ironocally, I have the same set of database installed on another server, and the this does not happen.
I forgot to mention that I am using Report Manager to deploy the reports.
Please assist.
I have as simple IS package with a Flat File input and SCD Transform. The package is being used to load a Dimension Table, obviously.
The Table was designed in Analyical Services using the Dimension Wizard which very nicely adds End Date, Start Date, Status and Original ID to the Dimension Table design. I then Generate Relational Scheme... and Viola there is a Dim Table in my data warehouse database!
I next attempt to use the IS package with SCD Transform to load said Dim Table. The SCD Transform Wizard does a nice job of using the End and Start dates and Status fields in my new Dim Table, but I can not find any way to use the Original ID field?
I guess I was expecting that since the SCD Transform Wizard 'knows' how to use all the other fields in this Dim table why not the Original ID field? I know what the Original ID field is for - that's not the issue. I just can not figure out how to 'make' my new IS Data Flow with said SCD Transform use the Original ID field ?
Anyone ?
When I drill down from one report to another and then depress the back button to return to the initial report, the message 'Generating Report" spins forever without actually running. However, if I depress the regenerate button during this time, it will run the report.
I believe that I may have inadvertingly reset a field attribute to have caused this condition. Ironocally, I have the same set of database installed on another server, and the this does not happen.
I forgot to mention that I am using Report Manager to deploy the reports.
Please assist.
I have a query that I created in SqlServer and then merely copied the code to a sqldatasource. They are both connected to the same db, however the sqldatasource query only returns 6 records and when run in SqlServer, it returns 52 rows which is the correct number.
Any ideas on what might be causing this? There are no filters on the sqldatasource. It only has that one query. Do they differ in the way they execute sql code?
Thanks for any help!
I have several reports that are set to landscape but then lose this layout when a linked report is created from the original report. I found the link below describing Brian Welcker's workaround and my question is where do you place the vb.net code he provided?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137986&SiteID=1
So i am trying to make a simple database and with one table and use a gui to make rows.
When i run the program in debug mode it creates a new copy of my DB in the debug folder and saves only their, i cannot find a way to save back to the original DB no matter what i do including changing the Copy to Output Directory.
Now the more i try to fix this the more new DB it creates one in the Release Folder, one in the bin/Debug/bin/Debug
one in bin/Release/bin/Release.
I am going crazy trying to scoure the entire project looking for someplace i can fix this.
All i want is to create a DB and be able to make changes to it and only it.
Does anyone have any solutions to this?
Thank you for your time
What's the diffrence between the Built-In account 'SERVICES' and the 'NT SERVICEALL SERVICES' ?I want to dploy a group poicy for 'user right assignment' - 'log on a service' to a server, which curretnly has among other things the 'NT SERVICEALL SERVICES' however i can't replicate this account for my GPO, (even locally on the server),I deploy the GPO and overwrite the server settings for 'user right assignment' - 'log on a service' and won't be able to revert back to the original settings..
View 5 Replies View Related
Hello all...
I am working on a project that contains several databases in SQL and many settings. The problem I am encountering is that I want to be able to update the program for all my customers. That is no problem.
The problem I am meeting is where, when the program is updated, all the settings and all the information in the databases reverts to the default values, or the values that were compiled with the new update.
I want to be able to update their program without deleting all the settings they have.
Thanks,
Andreas Renberg
Hi,
I am looking to implement a custom event handler that will also retain the original event arguments (in addition to several custom arguments).
Specifically, I am looking to pass custom arguments into a SqlDataSourceStatusEventHandler, but also want to be able to access the Command.Parameters.
I have implemented a new Event Arguments class (derived from System.EventArgs), new Event class and delegate, but do not know how to retain the SqlDataSourceEventArgs. I would really appreciate your suggestions!
Thanks!
I have added a page refresh in combination with 'Command Notification' to ensure that my data is fresh,( it is updated on the sql server database approx. every 5 mins).
However, I also allow filtering on the table contents and every time I refresh the filtering is lost. Is it possible to maintain the filtering through the refresh?
<meta http-equiv="refresh" content="30"/>..<asp:SqlDataSource ID="SqlDataSource1" runat="server" ..SelectCommand="SELECT ... EnableCaching="true" CacheDuration="Infinite" CacheExpirationPolicy="Absolute" SqlCacheDependency="CommandNotification" >
Hello, everyone:
I have an Excel table that has columns like,
Vendoe_Name
Level
Address
Email
Phone
I use the statemen
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]
and got output by column order,
Address
Email
Level
Phone
Vendoe_Name
How to make output by original column order like,
Vendoe_Name
Level
Address
Email
Phone
Any help will appreciated. Thanks
ZYT
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
Hi All,
OK, so I have an SSIS package that I build using the "test" database as the destination. Now I want to execute it using another database as the destination. This is basically for an installation script where the source is always the same, an access database.
So here I have:
"C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe" /file e:data_import.dtsx /CONNECTION DestinationConnectionOLEDB;""Data Source=(local);Initial Catalog=Test2DB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;""
Which works (sort of). It does try to import the data into "Test2DB", but what happens is all of the destination tables in the dtsx package have names like [test1db].[dbo].[tablename]
So my question is: is there a way to remove the databasename reference in the table names for all 195 tables in my database without doing it manually?
Thanks in advance.
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]
[code]....