Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?
Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?
Thanks for all your help!
Jim Work
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
View 2 Replies View RelatedHi!
Short version:
What would you recommend using as developer tools to make a fairly complex DB appear on the web?
Currently I'm thinking of SQL2005 Express Edition and Visual Web Developer 2005EE combined with either VB2005EE or C#2005EE. Seems like a rather steep lerning curve though since I'm used to Pascal and an old old query language called RDO that came with digital RDB on VMS.
Thing is, I know what I want to do, I have disigned the table layout and I know just about what I want it to look like. "All" that's left is to make it presentable... :-)
/Jens
PS: Do you know of any example that would show how to work with two (three) tables in a many-to-many relationship with e.g. VB2005EE and SQLserver2005EE?
I am new to sql.
Question1: How do i run a CHECK against serveral words.
e.g. check("name" is either bill or timmy or sally or jessy)
Question2: What is the best variable to use for time.
Hi all,
I did a few searches but did not find this specific scenario. Can anyone state with confidence whether this is possible (and if so, how)? Scenario:
-One table with a couple million rows (one column indicates which Country the record belongs to)
-Need to create an Excel 2007 file dynamically (for each Country) using SSIS 2005. Filename should include the Country Name (Sweden_Affliliated.xlsx). I have a table that contains a distinct list of all the countries. Each worksheet will have the same structure / schema across all files.
What seems to be working:
**I understand how to use an Execute SQL task to get the list of Country Names and bind to an object variable.
**I understand how to set variable mappings for a String variable to contain the "current Country" in a ForEach Loop.
**I understand how to set the OLE DB Data Flow Source to use a SQL command from a String variable that has the CountryName dynamically embedded within it.
**I understand I need to convert my varchar to Unicode using a Data Conversion task in my scenario.
**I understand that in order to write to an Excel 2007 file I need to use an OLE DB Destination with an Extended Property value of "Excel 12.0" and the ServerName property should contain a path to the file with no quotation marks.
Problems I have:
**OLE DB Destination: How do I set up the mappings when the file does not exist yet?
What I want to avoid, is having to create a template source XLSX file and using a File Copy task (I have gone this route before, but it would be best if I did not require a template). Is there a way to configure the SSIS package without using a File Copy Task? Creating the Excel file on the fly?
Thanks for any assistance in advance,
Brian
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)
View 1 Replies View RelatedHi,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.
View 2 Replies View RelatedI am new to SQL Server and have a few questions:
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?
Thanks
Hello-
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
myOutDB_cls.mdf_file = "c:program filescommon filesideas" & myProjName & ".mdf"
myOutDB_cls.ldf_file = "c:program filescommon filesideas" & myProjName & ".ldf"
'Go and create the output database with tables
myOutDB_cls.Create_DB_File(retMsg) 'create the project database
End Sub
'This is myOutDB_cls definition
Public Sub New(ByRef retMsg As String)
ConnectionString = "Data Source=.SQLExpress"
conn = New SqlConnection(ConnectionString)
Open_Connection(retMsg) 'Used as a test
End Sub
'Opens connection
Private Sub Open_Connection(ByRef retMsg As String)
Try
conn.Open()
Catch ex As Exception
retMsg = ex.Message & "----" & "Open_Connection"
Exit Sub
End Try
retMsg = "SUCCESS"
End Sub
Public Sub Create_DB_File(ByRef retMsg As String)
Dim sql as string = vbnullstring
Try
If conn.State <> ConnectionState.Open Then ' Open the connection if not open
conn.Open()
End If
sql = "CREATE DATABASE " & fileName & " ON PRIMARY" + "(Name=test_data, filename = '" & mdf_file & "', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=" & fileName & "_log, filename='" & ldf_file & "',size=3," + "maxsize=20,filegrowth=1)"
Execute_SQL_Stmt(sql, retMsg)
retMsg = "SUCCESS"
Catch ex As Exception
retMsg = ex.Message & "----" & "Create_DB_File"
Finally
conn.Close()
End Try
End Sub
Hi,
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())
Return Digest
End Function
The above code is taken from this URL
http://aspnet.4guysfromrolla.com/articles/083105-1.aspx
Thanks
Please see this and Help me€¦
My Table:
Table1
SYMBOL TIME PRICE
EUR A0-FX 2008-05-09 11:37:31.203 1.54035
EUR A0-FX 2008-05-09 11:37:30.030 1.54034
EUR A0-FX 2008-05-09 11:37:28.860 1.54033
EUR A0-FX 2008-05-09 11:37:41.673 1.54032
EUR A0-FX 2008-05-09 11:37:59.720 1.54031
EUR A0-FX 2008-05-09 11:38:09.000 1.54033
EUR A0-FX 2008-05-09 11:38:35.877 1.54032
EUR A0-FX 2008-05-09 11:38:59.767 1.54041
Stored Procedure:
;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);
OutPut:
SYMBOL TIME CLOSE
EUR A0-FX 11:37 1.54031
EUR A0-FX 11:38 1.54041
€¦..
...
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
group by a.TIME, a.CLOSE
having count(distinct b.TIME) = 5;
Please help me how to do€¦
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!
Jordan
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!
How do you create a SQL database (Sql Express 2005) in real time using VB.Net 2005 ASP.net 2.0?
View 2 Replies View RelatedIs 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.
View 4 Replies View RelatedHello 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.
Thank you in advance
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?
Thanks,
Erick
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.
View 3 Replies View RelatedHi, 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?
View 1 Replies View RelatedHi All,
Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?
This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)
Hello all - newbie post forthcoming....
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:
sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"
The response I get is.... "Data Type Mismatch in Criteria Expression"
The post data does not have quotes around the data - I am thinking this is the problem.
Any help would be appreciated!
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.
Compiler Error Message: BC30203: Identifier expected.
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.
what is the best way to create a MS SQL 2000 database from an existing MS ACCESS 2003 database
with all datas in it ?
thank you