I need to convert the following .NET code to a SQL Server 2005 user defined function. Let me know where to start
Here is the code in ASP.NET using VB
Shared Function GetDigest(ByVal tamperProofParams As String) As String
Dim Digest As String = String.Empty
Dim input As String = String.Concat(SecretSalt, tamperProofParams, SecretSalt)
'The array of bytes that will contain the encrypted value of input
Dim hashedDataBytes As Byte()
'The encoder class used to convert strPlainText to an array of bytes
Dim encoder As New System.Text.UTF8Encoding
'Create an instance of the MD5CryptoServiceProvider class
Dim md5Hasher As New System.Security.Cryptography.MD5CryptoServiceProvider
'Call ComputeHash, passing in the plain-text string as an array of bytes
'The return value is the encrypted value, as an array of bytes
hashedDataBytes = md5Hasher.ComputeHash(encoder.GetBytes(input))
'Base-64 Encode the results and strip off ending '==', if it exists
Digest = Convert.ToBase64String(hashedDataBytes).TrimEnd("=".ToCharArray())
Hi,I have a User Defined Datatype, which I want to use to define anotherdata-type. I tried the obvious:EXEC sp_addtype@typename = UDT_OBJECT_ID,@phystype = 'NUMERIC (5)',@nulltype = 'NULL'GOEXEC sp_addtype@typename = UDT_TRACKING_NUM,@phystype = UDT_OBJECT_ID,@nulltype = 'NOT NULL'GObut that didn't work :Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line 186The data type UDT_OBJECT_ID does not exist.Has anyone done this before,Thanks,Rowland.
1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:
Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated. If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action Delete the rows that were queried and updated from the 'Scheduling' table
I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?
2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?
I am using VB .NET 2003 with SQL Server Express 2005. I am writing a program that creates a new database each time it is ran (through "Test_My_Connection" Sub below). On my development PC I am able to create a database on the fly using the code below without any issue:
Once I deploy to any computer I get the following error on the Create_DB_File below: "An attempt to attach an auto-named database for file c:Program FilesCommon FilesIdeasmyTestDB.mdf failed. As database with that name exists, or specified file cannot be opened, or it is located on UNC share."
I can create any database file I want with this code on my design PC, as long as it is in the "c:program filescommon filesideas" directory, if I move the directory I get the error on my design PC also. Do I need to register the directory I am using as a data directory to allow my PC or deployment computers to create databases? I don't know how I got it to work in the "common files" directory for my development PC but it does. Any thoughts? Code is below, with connection string...
Thanks,
Brian
'This will be run each time a new project is started Private Sub Test_My_Connection
Dim myProjName as String ="myTestDB" myOutDB_cls = New OutputDBCls(retMsg) If retMsg <> "SUCCESS" then
'Deal with it End If
'Database files to be created by "Create_DB_File" Sub
;WITH cte AS ( SELECT SYMBOL, [Time], Price, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2 FROM Table1 WHERE SYMBOL='EUR A0-FX' )
SELECT SYMBOL='EUR A0-FX',CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS [Time],MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close] FROM cte
GROUP BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS DATETIME);
This code is for finding Average of Previous 5 CLOSE values. I want to add this query into my Stored Procedure. I am getting Error if I create another CTE.
select a.symbol,a.TIME,a.CLOSE,avg(b.CLOSE1) as average, CASE WHEN A.CLOSE>AVG(B.CLOSE) THEN 'BUY' ELSE 'SELL' END AS ACTION
from cte as a inner join cte as b on b.rn between a.rn - 5 and a.rn - 1
I am trying to populate a new SDF database to be deployed with my CF application, and am totally bummed to find out I have to manually code my own custom app just to import the data from the existing desktop DB with ado.net code.
I don't want to write code so that I can write code!
Custom apps to support the development of custom apps!
I ask the help with creation of ERD diagram similar data base of EBAY.com. Someone can knows where it is possible to look the similar diagram. Thanks for your help!
Is there a way for me to create a column in SQL? What Im looking to do is create a view with columns from a specific table. In addition to this I would like to append a column to the view which would contain data based on the data from a pre-existing column. Table AColumn 1Column 2Column 3 View AColumn 1Column 2Column 3Column 4 Now here is the Row structureColumn 1 Column 2 Column 3 Column 4Test Test A ONTest Test B OFF So if Column 3 contains "A" as its field data than Column 4 will contain "ON" and if Column 3 contains "B" than Column 4 will contain "OFF" Is this possible? TIA, Stue.
Alright, so let me explain the details first.I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes. so in short, how do i make a dynamic sql statement.
Hello everyone, I'm created a new SQL Server 2005 database for a new project and I'm facing a little situation regarding FKs. I guess that's what you get when you move from MySql 4 to Sql Server 2005 ;) Here's the situation: I have a table called contact, assigned to the schema person, and that table handles all the base information regarding users. Informations such as FirstName, LastName, etc, are stored in the [Person].[Contact]. So far so good. As a design decision I've decided to keep track of the nick names (Alias) that a given member had over-time. In order to do this I've designed the [Person].[GlobalAlias] table like this: Id uniqueidentifier [NOT NULL] [PK]ContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]Alias nvarchar(30) [NOT NULL]AddedByContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]AddedDate datetime [NOT NULL]AddedObs nvarchar(MAX) [NULL]RemovedByContactId uniqueidentifier [NULL] [FK Person.Contact Id]RemovedDate datetime [NULL]RemovedObs nvarchar(MAX) [NULL]ModifiedDate datetime [NOT NULL]Status bit [NOT NULL] This table seems to work great dispite the fact that it generates an error when I try to set the FKs. For the column [Person].[GlobalAlias] ContactId I set a FK to the column [Person].[Contact] Id with cascade on delete and on update. Now, when I go to the [Person].[GlobalAlias] AddedByMembershipId and try to add a FK to the column [Person].[Contact] Id it works as long as I define NO ACTION on delete and on update. Obviously, this could originate rows where the [Person].[Contact] that added a [Person].[GlobalAlias] was deleted and there's still a reference to that ID in the [Person].[GlobalAlias] AddedByMembershipId Here's the error that I'm getting from the database: 'Contact (Person)' table saved successfully'GlobalAlias (Person)' table- Unable to create relationship 'FK_GlobalAlias_AddedByContactId_Contact_Id'. Introducing FOREIGN KEY constraint 'FK_GlobalAlias_AddedByContactId_Contact_Id' on table 'GlobalAlias' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors. How could I solve this? Perhaps the only way is to normalize it even further and create a history table? Best regards,DBA
Can anybody run me through the process to create a CSV file from an SQLexpress database using BCP.exe ? Or is there a simpler way to create a CSV for that matter? Really important and would be much appreciated.
i have a table which has 2 columns 1 'report' 2 'part' now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50' i want to create a view that will put them together in format like this: 1111110001 1111110002 1111110003 .. and on it needs to be in 10 digits. is there anyway i can create a View or may be a column in the table which can create the #'s in this format.
Hello All, I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:
1 protected void Page_Load(object sender, EventArgs e) 2 { 3 // connection string to your database 4 SqlConnection mySqlConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;"); 5 6 // create command object to execute query 7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); 8 9 // set command text to the INSERT sql query 10 mySqlCommand.CommandText = "SELECT MAX(PONumber)FROM ItemMaster;"; 11 12 // open connection 13 mySqlConnection.Open(); 14 15 // execute query 16 17 int newPO1 = (int) mySqlCommand.ExecuteScalar(); 18 int newPO = newPO1 + 1; 19 20 // close connection 21 mySqlConnection.Close(); 22 23 //Response.Write(newPO); 24 Session["newPO"] = newPO.ToString(); 25 26 }
I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:
System.InvalidCastException: Specified cast is not valid.Source Error:
Line 30: // execute query Line 31: Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar(); Line 33: int newPO = newPO1 + 1; Line 34:
I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.
I have never work with triggers before, so I need a hand on how to create a trigger when a row is updated on a table. Lets says I have this table call "Events" and I want to send the new row to another table call "History". When a new row is inserted I want to select that row and inserted in the History table. Can someone give me a hand with this?
Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message: CS1026: ) expected". And it is coming from the following line; SqlCommand command = new SqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData + ")"; Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting. public static void SQLExecute(string strTableName, string strData) {DataSet dsetResponse = new DataSet(); // create connection objectstrConnection = ConfigurationManager.AppSettings["strConnectionString"]; SqlConnection conn = new SqlConnection(strConnection);SqlCommand command = new SqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData + ")";command.Fill(dsetResponse,"table"); conn.Close();return dsetResponse; }
I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table. tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload please guide me i dont have an idea. thanks for spending ur valuable time for me.
I'm trying to create programmatically a table in my db on sql server 2005.1. I created a string "CREATE TABLE ....." and then tried to execute it using my ADO. That didn't work, so I copied the string into a query in sql server and got the message "CREATE TABLE sql is not supported". Why's that? 2. I created a stored procedure in my db that will get the "CREATE TABLE...." string and execute it. I thought it would look something like this: parameter @createString varchar(MAX) BEGIN BEGIN EXEC @createString or @createString END END Both these options didn't work.How can I make it work properly?Thanks.
Hi, i have been able to create a query in asp.net in the sever explorer. I want to create an statement as a new column. In Access i would do it like this (Agg: [Scored]-[Against]) But how do i call a new column Agg in SQL and then tell it to take Against away from Scored?
Sorry, I'm a newbie out there when it comes to creating database. I currently have an ASP.Net project which requires a database. I spent the past few days thinking about how to create a proper database for the application but to no avail. I think tis thread might be out of point for tis forum, but i am really in need of help.
Here goes the storyline of my database:
The project is an online project management system, meant for students to submit their deliverables (reports, eg. Minutes, agenda, project monitor chart) Lecturers will access the web application to grade the student’s submission. The application must allow administrators to add in new subjects. When a new subject is added, the subject leader will set a dateline for a particular module to be submitted. Eg, minutes dateline. Once the dateline is due, the system will inform the lecturers in charge of who/which group did not submit the module. Lecturers and subject leaders can then generate warning letters to be sent via email to the students involved. Lecturers and subject leaders will be able to make announcements through the system as well.
A student can either submit his/her work as an individual or as a group. A student will be studying more than 1 subject. A subject will consist of more than 1 module. Every module will have a dateline for its submission. Students can make more than 1 submission for each module. Students belong to different groups/classes for different subjects. For different modules, there will be different fields to be inserted into the database. Example, in the “Minutes module�, the student will have to be able to key in the objectives, the date of the meeting, the venue, its members, absent members, actual minutes, action by and meeting end time.
In the “Project monitor chart module�, the student will have to key in the week/date, the name of the member, task involved, as well as the status.
When a new subject is created, the subject leader will have to specify the percentage of each different module. (Eg, Exams – 40%, Term test 15%, and so on.)
A subject will also be taught by more than 1 lecturer. So subject leader will specify the lecturers teaching that particular subject.
I hope someone out there can help me out on the construction of a database for the above storyline. Thanks a lot.
I need a module to help me create a SQLServer database table. Is there a module that allows users to specify parameters in the creation of a SQLserver table?
I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:
I am trying to connect to a database on an MS Sql server, I typed the code exactly like what is shown in the tutorial and I keep getting an error... (Error in line 6)
Line 4: Dim oODBCConnection As OdbcConnection Line 5: Dim sConnString As String = _ Line 6: "Driver={SQL Server};" &_ Line 7: "Server=209.151.130.8;" &_ Line 8: "Database=blah;" &_ ...(Error in line 6)
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
I'm trying to create an sql server login, which should have access to and may see only one database. So what I do, is create a login and set the db's owner to this login. I also give it the appropriate rights like public, datareader, datawriter etc. on this db.
Furthermore, the login has no rights on other databases on the sql server. However, the login can see the other databases if he registers the sql server with the provided login data. This is what we would like to prevent: he may only see his own database.
How could I tackle this problem? Any suggestions are welcome.
Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.-Daticus
Just a quick question, Is it better to build an Indexindicies after creating a new table or after populating the table with the data. Which is quicker? What about statistics? This table is dropped/and then recreated on a weekly basis. It is a reporting database.
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created. Table 1 EmpID Text 123456 A1, A2, A3
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
