Could any one tell me whats the best way to put encypted data into a table.
i have a textfield that a single integer is entered into it. It is then encrypted using DES encrption algorithim.It converts the value to byte(Convert2ByteArray method) I pass the data to a stored procedure which converts it to char before inserting it into the table. when i checked the database all it entered was System.Byte[]. Does anyone know where im goin wrong?
I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.
input:
id seqno codevalue
1 A red
1 B red
2 C blue
2 A green
2 B violet
3 A green
desired output:
id Seq_A Seq_B Seq_C
1 red red null
2 green violet blue
3 green null null
what I am getting:
id Seq_A Seq_B Seq_C
1 red null null
1 null red null
2 green null null
2 null violet null
2 null null blue
3 green null null
I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.
It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.
I also have several other pivot transforms in the same data flow and they are working as expected.
I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.
I had an idea to put all my web design settings, css text and web content in the database..This way it would be easy for others to edit remotely. Do you guys think this would have an impact on performance if I do this?
We recently had a problem were a webserver lost connection to sql 2000 database after we put in a new router. When we failed back to old router it was able to connect to database again. It is using a non standard port 2125 to connect to sql database. Why would a new router cause connection problems to a database.
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
I have a simple Website and Database created in VWD exp.
I've just added a windows account to GoDaddy.com.
Unfortunately there is no way to port your database on to the GoDaddy server directly, but it says you must instead, recreate the database by copying and running SQL statements from a file called "personal-add.sql". (their "help" page: http://help.godaddy.com/article.php?article_id=695)
I can't find any such file. Can someone please let me know what I'm missing here?
I missed the days of 6.5 when I can take a database from one server create a device for LOAD on another sever and use the LOAD database command to put that database on another server that has MS SQL SERVER on it..
So what is the trick with 7.0 if DTS is out of the question??
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing. 1. Take the input row and check in the database. 2. If the row is not found in the database then treat it as new row. 3. If row is found in the database then a) Take the source row and prepare a concatenated string for all the columns b) Take the database row and prepare a concatenated string for all the columns c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do? Will it generates hash code based on all the columns values?
I'm pretty new to SQL and I've got a bit of a sticky problem. I've looked around on the net for a solution but possibly I just don't know what I should be searching on. I'm trying to join tables together where there is a one to many releationship - but I'm trying to put the results for each relationship on one row (which kind of results in dynamic columns).
Is this possible? There is no limit on how many rows in table 2 can be related to table 1. Can anyone point me in the right direction for what I should be looking at?
The reason I'm trying this is for SQL reporting services if that makes a difference? Thanks, Kelvin.
Hi all, I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000. The application is in production and everytime I want to do some changes, i do the changes on my development machine then I copy the application dll on the server. The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements. These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ??? thanks.
I just want to get the sum of a table's column into a variable, in a stored procedure. The best I can do is SET @TotalBalance = SELECT SUM(Balance) FROM AccountDetails Not good enough, of course.
I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900. Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _ "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery() Any other field types work fine, it's just dates that aren't working ???? Can someone please provide a code snippet showing me what I'm doing wrong??
set dateformat 'dmy' select count(c.id), e.name from call c left outer join employee e on c.req_id = e.id where c.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(c.id) desc
set dateformat 'dmy' select count(ch.id), e.name from call_hist ch left outer join employee e on ch.req_id = e.id where ch.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(ch.id) desc
the results:
42NULL 34Dirk Deloof 13Annick Leirman 11Ronny Loosen 9Geert Benoot 9Nicole Ferrari 8FLOCK 8Mosselmans Christoph 7Geert Pets 7Mireille Dutrieue 6johan 6Laurent De Schrijver 5Jeanette De SChrijve 5Marc De Vlieger 5minerva 5Pascal Saesen 5Rik Haghebaert 5Sonja Van Kerckhove 4Bcatron 4Luc Willems 3Brigit Brocken 3euroadmin 3Francine Kopp 3Luc Steyaert 3Marie-Rose Buysse 3Marnix Van Steirtege 3Mattias Denys 3Pieter Frooninckx 3Reserve 3Rik De Scheemaecker 3Thierry Linard 2Carlos Van Alboom 2Dorine Sierens 2Els Poelman 2Jean Claude Vermeir 2Katrien Colman 2Kim Impens 2Kris Lejeune 2MEDreserve01 2Roger De Wilde 1Agnes Lebon 1Carla Van Den Broeck 1Eric Vlaeminck
and
118NULL 58Marie-Rose Buysse 47Dirk Deloof 45Ronny Loosen 43Annick Leirman 41Geert Pets 38FLOCK 38Pascal Saesen 28Teamleiders afwerkin 24Kim Impens 22Ilse Soetens 22Rik Haghebaert 22Severine Balduck 21Teamleiders print 20Mosselmans Christoph 20Jeanette De SChrijve 19Geert Benoot 19Francine Kopp 18Geert Meuleman 17Rik De Scheemaecker 16johan 16Katrien Colman 15Gaby Eloot 14Kris Lejeune 14Gilbert Callebaut 14Laurent De Schrijver 13Els Poelman 13Luc Steyaert 11Marnix Van Steirtege 10Frans Hoogewijs 10Sonja Van Kerckhove 10Dorine Sierens 9Eric Vlaeminck 9Thierry Linard 7Frederic Denis 7Michel Poppe 6Carla Van Den Broeck 6Pieter Frooninckx 5Katlijn Poleyn 5MEDreserve01 5Mireille Dutrieue 5Agnes Lebon 4Guido Antoin 4Onderhoud 4minerva 4Jeanette Van Brussel 3Roger De Wilde 3Sofie Gabriels 3Verf2 3euroadmin 3Marc De Vlieger 2Luc Willems 2MEDRESERVE07 2Regina Decoster 2Monique Kohl 2MEDRESERVE04 2Portier 2Bcatron 2Pierre Hanet 2Tgabriels 2Isabelle Torrelle 2Nicole Ferrari 1Robert Zwaak 1Carlos Van Alboom 1testuser 1Brigit Brocken 1Reserve 1Opleiding 1Verf
How do I put these results in one? I need not two but one Query. Please help me. Thanks
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block ALTER PROC csp_ICASTransaction_AskDoc @ClientID INT AS DECLARE @ClientID INT SET @ClientID = 1 DECLARE @cClientID INT DECLARE @cMonth VARCHAR(20) DECLARE @cOccurance INT DECLARE @Counter INT DECLARE @AskDoc CURSOR SET @AskDoc = Cursor FOR select @ClientID, Month_Year, SUM(AskDocs) FROM zzenrolled$ WHERE ICASClientID = @ClientID GROUP BY Month_Year OPEN @AskDoc FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance /* SELECT @@Cursor_Rows PRINT @cClientID PRINT @cMonth PRINT @cOccurance */ DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT) WHILE (@@FETCH_Status = 0) BEGIN SET @Counter = 0 INSERT INTO @MyTable VALUES (@cClientID, CASE @cMonth WHEN 'Jan-03' THEN '20030101' WHEN 'Feb-03' THEN '20030201' WHEN 'Mar-03' THEN '20030301' WHEN 'Apr-03' THEN '20030401' WHEN 'May-03' THEN '20030501' WHEN 'Jun-03' THEN '20030601' WHEN 'Jul-03' THEN '20030701' WHEN 'Aug-03' THEN '20030801' WHEN 'Sep-03' THEN '20030901' WHEN 'Oct-03' THEN '20031001' WHEN 'Nov-03' THEN '20031101' WHEN 'Dec-03' THEN '20031201' WHEN 'Jan-04' THEN '20040101' WHEN 'Feb-04' THEN '20040201' WHEN 'Mar-04' THEN '20040301' WHEN 'Apr-04' THEN '20040401' WHEN 'May-04' THEN '20040501' WHEN 'Jun-04' THEN '20040601' WHEN 'Jul-04' THEN '20040701' WHEN 'Aug-04' THEN '20040801' WHEN 'Sep-04' THEN '20040901' WHEN 'Oct-04' THEN '20041001' WHEN 'Nov-04' THEN '20041101' WHEN 'Dec-04' THEN '20041201' WHEN 'Jan-05' THEN '20050101' WHEN 'Feb-05' THEN '20050201' WHEN 'Mar-05' THEN '20050301' WHEN 'Apr-05' THEN '20050401' WHEN 'May-05' THEN '20050501' WHEN 'Jun-05' THEN '20050601' WHEN 'Jul-05' THEN '20050701' WHEN 'Aug-05' THEN '20050801' WHEN 'Sep-05' THEN '20050901' WHEN 'Oct-05' THEN '20051001' WHEN 'Nov-05' THEN '20051101' WHEN 'Dec-05' THEN '20051201' WHEN 'Jan-06' THEN '20060101' WHEN 'Feb-06' THEN '20060201' WHEN 'Mar-06' THEN '20060301' WHEN 'Apr-06' THEN '20060401' WHEN 'May-06' THEN '20060501' WHEN 'Jun-06' THEN '20060601' WHEN 'Jul-06' THEN '20060701' WHEN 'Aug-06' THEN '20060801' WHEN 'Sep-06' THEN '20060901' WHEN 'Oct-06' THEN '20061001' WHEN 'Nov-06' THEN '20061101' WHEN 'Dec-06' THEN '20061201' WHEN 'Jan-07' THEN '20070101' WHEN 'Feb-07' THEN '20070201' WHEN 'Mar-07' THEN '20070301' WHEN 'Apr-07' THEN '20070401' WHEN 'May-07' THEN '20070501' WHEN 'Jun-07' THEN '20070601' WHEN 'Jul-07' THEN '20070701' WHEN 'Aug-07' THEN '20070801' WHEN 'Sep-07' THEN '20070901' WHEN 'Oct-07' THEN '20071001' WHEN 'Nov-07' THEN '20071101' WHEN 'Dec-07' THEN '20071201' END ,'3' )
SET @Counter = @Counter + 1 IF @cOccurance > @Counter FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance END CLOSE @AskDoc --INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance]) SELECT * FROM @MyTable DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"
When I try to put in a variable as follws
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"
I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please take a look at the statement and see if its well formed, String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";
I am creating a stored Procedure and I am getting an error which relates to DATENAME. SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls' FROM CALL_LOG_MASTER WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.
Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?
I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.
Here's what I want: I have a DTSX package run as a SQL Server agent job, that each time it runs I want it to create a new log file with the date & time in the filename. e.g. MyJobLog.20080311_103613.txt
I have implemented this using the SSIS log provider for Text Files, and defining the Connection String as an expression as follows:-
Here's the problem:- When the job runs, each time it tries to write to the log file it re-evaluates the filename, and as the seconds pass by the name changes! So instead of one file I end up with several:- e.g. MyJobLog.20080311_103613.txt MyJobLog.20080311_103614.txt MyJobLog.20080311_103615.txt
So the one execution of the job is now divided across three files! This is not what want!
Here's one way I fixed it:- One of my Jobs is run from an MS-DOS batch file, so I was able to pass the constructed date/time sting into the DTSX file as a parameter. This works perfectly.
Here's my problem:- Three more of my jobs are run as scheduled job from SQL Server Agent, so I can't pass in the date/time as a parameter each time (unless you know otherwise). And the clients' security policy won't allow the job to call an MS-DOS batch file which runs the DTSX package. Or is there really some other way to stop the Log filename from re-evaluating on every write?
The dataset2 control doesn't seem to want to place the text in the right spot. I put it right after the 1st Dataset and the user comments wound up all the way down in the footer of the site. I want them to appear in the white area just underneath the article text. Here's a link to one of my articles so you can see this. http://www.link-exchangers.com/view_full_article.aspx?aid=50 Down at the bottom left corner you'll see white space below the article. I'd like to have all the comments lined up going down the page. Is the dataset the best control for this task?
How can I program BCP to output text items in double quotes (")? Here is an example (please try it) that trys to output some columns from a table to csv file. However, due to the existence of commas within the fields, the comma separation gets messed up.
------------------------------------ USE [MASTER]
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1') DROP TABLE mcg1 go
CREATE TABLE mcg1 (pkINT IDENTITY(1,1) ,Address_1VARCHAR(100) ,CityVARCHAR(100)) go
Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout "C:mcg1.csv" -c -t,"'
------------------------------------ The output I get is below. You can see how the use of commas in the text makes the comma separate list all confused 100 Road1, Suburb1,BigCity1 200 Road2, Suburb2,BigCity2
Thus what I want is "100 Road1, Suburb1","BigCity1" "200 Road2, Suburb2","BigCity2"
You can do this OK in DTS by specifying the text identifier to be double-quotes. I do NOT want to use DTS and want to be able to do via a T-SQL procedure. Note that the real table I will export from has numeric datatypes and I would prefer NOT to wrap them in double-quotes too.
Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each text field in double quotes. I may have to use a format file in which case please provide the format file too.
I am retrieving the current month and setting a variable accordingly.
Code: DECLARE @currMonthNum INT SET @currMonthNum = DATEPART(MM, GETDATE())
My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).
Here is my attempt to do this...!
Code: IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9) SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.
I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).
how to display a zero in front of the months that require it?
