Hello. I have now started to build my own community. And I have some questions on the database.
For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.
In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.
Now. All the users in this community will have their own profile page, Guestbooks ++.
So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).
I have noticed that the area of writing stored procedures for muti-user databases is a very specialised field and requires knowledge that's much more than the locking topics covered in 'online books' . I am sure there are some standard tips and tricks that are used in mutil-user databases for writing to tables. Most books have a chapter or two on locking, but I think this topic should be dealt with separately in a dedicated book to locking with extensive examples on locking. Does anyone know of such a dedicated book out there?
There is a new way to search through all community content relating to SQL Server Integration Services. Head over to:
http://search.live.com/macros/jamiet/ssis
to use this new Live Search macro.
This will search through this forum, wikis, SSIS websites and blogs. For a complete list and further details head over to: http://blogs.conchango.com/jamiethomson/archive/2007/04/10/SSIS_3A00_-Live-Search-Macro-for-SQL-Server-Integration-Services.aspx
I posted this question in the Community Server forums, but thought I would try here as well...
I am trying to use SQL Integration Services (SSIS) to dump out the images from Community Server and I'm getting zero length files. Here's what I'm trying....
I have a Data Flow Task with an OLEDB Data Source with this query (The TOP is for testing purposes) :
SELECT Top 10 cs_Posts.Body, cs_Posts.PostDate, cs_PostAttachments.FileName, cs_PostAttachments.[Content], cs_PostAttachments.ContentType, cs_PostAttachments.ContentSize, cs_Post_Categories.Name FROM cs_Posts INNER JOIN cs_PostAttachments ON cs_Posts.PostID = cs_PostAttachments.PostID INNER JOIN cs_Posts_InCategories ON cs_Posts.PostID = cs_Posts_InCategories.PostID INNER JOIN cs_Post_Categories ON cs_Posts_InCategories.CategoryID = cs_Post_Categories.CategoryID WHERE (cs_Posts.ApplicationPostType = 64)
Then I have an Export Column Transformation set to export cs_PostAttachments.[Content] with cs_PostAttachments.FileName as well the file name. Ultimately I'm going to break the filepath all down into folders by the category name, but for now I'm just trying to get the export working.
Anyway when I run it I end up with files with the right names, but they are all zero length files.
We found a SQL problem when searching in a large communty (GCN). Please, we need someone how can help with this problem. It's urgent.
For details see post: Page 1: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806 Page 2: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806&PageIndex=2
I am trying to install Microsoft SQL Server September 2005 Community Technology Preview.
I get the generic error saying "SQL Server 2005 setup has detected incompatibe components...."
So I checked the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSQLSetup0020_Core.log file
which says
C:SQLENT2005Serverssetup.exe Version: 2005.90.1314.0 Running: LoadResourcesAction at: 2005/9/11 13:3:29 Complete: LoadResourcesAction at: 2005/9/11 13:3:29, returned true Running: ParseBootstrapOptionsAction at: 2005/9/11 13:3:29 Loaded DLL:C:SQLENT2005Serversxmlrw.dll Version:2.0.3604.0 Complete: ParseBootstrapOptionsAction at: 2005/9/11 13:3:30, returned true Running: ValidateWinNTAction at: 2005/9/11 13:3:30 Complete: ValidateWinNTAction at: 2005/9/11 13:3:30, returned true Running: ValidateMinOSAction at: 2005/9/11 13:3:30 Complete: ValidateMinOSAction at: 2005/9/11 13:3:30, returned true Running: PerformSCCAction at: 2005/9/11 13:3:30 Complete: PerformSCCAction at: 2005/9/11 13:3:31, returned true Running: ActivateLoggingAction at: 2005/9/11 13:3:31 Complete: ActivateLoggingAction at: 2005/9/11 13:3:31, returned true Running: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31 Complete: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31, returned true Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions: Condition "EventCondition: __STP_LaunchPatchedBootstrap__1952" returned false. Running: PerformSCCAction2 at: 2005/9/11 13:3:31 Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435 Product "{53F5C3EE-05ED-4830-994B-50B2F0D50FCE}" versioned 9.00.1187.07 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1314 The Product Name is "Microsoft SQL Server Setup Support Files (English)" Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435 Error: Action "PerformSCCAction2" threw an exception during execution. Return Code: 70032 Message displayed to user SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
I understand that I need to uninstall "Microsoft SQL Server Setup Support Files (English)"
But when I try to do that I get "A network error occured while attempting to read from C:WinntInstallerSQLSupport.msi"
Is there any sql method that takes 3 parameter like, day, month and year . And return me the date. For example function(10,3,2007) and it returns 10-03-2007
userfeatureuserName featureIda 1a 5b 1b 5b 9c 5 c 9 menuid Pid Name1 Administrator2 1 Create User3 1 Delete USer4 1 View log5 WSR6 5 X7 5 X8 5 X9 Manager10 9 Y11 9 Y Using the above table i want to create a treeview ie based on the user login. Please let me know if there is any previous sample to this situation.
I've never done this before and I have all kinds of issues conflicting in my head (search rank, noise words, injection attacks ..etc). simply i need to search several columns in a table in the database using one search text (just like the simple search in Google). if multiple words are you used then the search should search for each of them. also manage to ignore noise words and other issues.
what is the best way of doing this? I looked at FTS in SQL 2000 but didn't know how to handle all the above mentioned issues. this should be simple, right? but i have been looking all day. I guess i don't know what im looking for because i've never implemented a web search b4.
I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)? BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET ANSI_WARNINGS OFF SET @where = '' IF @JobNoStart !='' SET @where =+ ' AND LJOB BETWEEN @JobNoStart AND @JobNoEnd' IF @OrderDateStart !='' SET @where =+ ' AND JOBDATE BETWEEN @OrderDateStart AND @OrderDateEnd' IF @DueDateStart !='' SET @where =+ ' AND DUEDATE BETWEEN @DueDateStart AND @DueDateEnd' IF @ProofDateStart !='' SET @where =+ ' AND PROOFDUE BETWEEN @ProofDateStart AND @ProofDateEnd' IF @CloseDateStart !='' SET @where =+ ' AND CLOSEDATE BETWEEN @CloseDateStart AND @CloseDateEnd' IF @CogsDateStart !='' SET @where =+ ' AND COGSDATE BETWEEN @CogsDateStart AND @CogsDateEnd' IF @ProductName !='' SET @where =+ ' AND PRODUCT = @ProductName' IF @CustomerNumber !='' SET @where =+ ' AND FCUSTNO = @CustomerNumber' IF @SalesPerson !='' SET @where =+ ' AND FSALESPN = @SalesPerson' IF @CSR !='' SET @where =+ ' AND JOBPER = @CSR' IF @Closed = 0 SET @where =+ ' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL' ELSE IF @Closed = 1 SET @where =+ ' AND CLOSEDATE IS NULL' ELSE IF @Closed = 2 SET @where =+ ' AND CLOSEDATE IS NOT NULL' IF @Canceled = 0 SET @where =+ ' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL' ELSE IF @Canceled = 1 SET @where =+ ' AND CANCDATE IS NOT NULL' ELSE IF @Canceled = 2 SET @where =+ ' AND CANCDATE IS NULL' IF @FinalShip = 0 SET @where =+ ' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL' ELSE IF @FinalShip = 1 SET @where =+ ' AND FINALSHIP IS NOT NULL' ELSE IF @FinalShip = 2 SET @where =+ ' AND FINALSHIP IS NULL' SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN WHERE LJOB IS NOT NULL @where
I am having some problems trying to build an sql statement from more than one statement.
Here is the statement
select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, ) Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in ( select id from payatwork..sysobjects where name like 'Employee_Profile') order by colorder, ')'
What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success.
I'm in the midst of planning how to build an NT box to host SQL 7.0 and was wondering if there is any advantage to segregating the RAID 5 Array (5 x 18GB drives) into numerous *logical* partitions to separate database and log files (I can't see what advantage there would be if the disks are all on the same array, but..)
If anyone has any pointers or links to recommended NT configurations for hosting SQL, I'd appreciate hearing them.
Help Please - JMail - SQL SPs - Confirmation Email
I'm at the last stage of my current project and an totally stuck.
I'm trying to build the body of a order processing request email. For security reasons I wish to use a SP to build and send the email. (therefore no sensitive data gets passed to the client)
I have the JMail Object running properly on the server. It collects and sends the email in the normal course of the transaction from the client. Problem is that I cannot figure out how to properly build the body of the email in the SP.
I'm looking to do add the following to a single SQL SP variable to stuff in the Jmail Object to be sent. The content of the variable should look something linke the following: -----------------------------------------
/*loop each record where /*customerID and orderID match passed arguments /***loopstart*** OrderDetails.SKU <tab> Product.Productname <tab><tab> Orderdetails.Qty <tab> OrderDetails.Price<cr> /***loopend***
I am working with SQL server 2000. The database is installed in my machine. Now I have got the ".ldf" and ".mdf" file pertaining to a database from someother server.
Is it possible to build the database present in the above said files in my server.
NOTE : I dont have a direct access to the remote server from where the above said files were obtained (otherwise I think the DTS utility would have come in handy).
This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:
Two tables:
Table 1: Orders Some of the fields: ID PropID WorkOrderNum OrderDesc DateCompleted
Table 2: OrderDetail ID OrderID TenantName
As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.
Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):
SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'
Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.
I have a SQL 2005 database containing the location of graphics files. I want to start learning how to write a C# application that will get a path from the DB and display the file. Any recommendations on sites where I can start learning how to do this?
I'm stuck and uinder a bit of a time crunch. I have 5 fields I want to get out of a sql database using a function that I'm writing. I figure it sounds like an array. basically I want to make an array, and fill it up with the results of a sql select, then read the array. This is what I have so far..... String TempHRAcctCode, TempJobDescription, TempHourlyRate, TempEmplID; Array TempArray; TempJobDescription = DDDept.SelectedItem.Text; (to get KeY Value) SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCONN"].ToString()); connection.Open(); SqlCommand command = new SqlCommand("Select HRAcctCode, HourlyRate , EmplID, ... FROM TimeMyProfile WHERE JobDescription = " + TempJobDescription + " ", connection); SqlDataReader TempDataReader = command.ExecuteReader; while TempDataReader.Read ( ... OK I GIVE UP! Thanks in advance
Hello, I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure! I have to try and link up four different tables to try and get my result. Here are the 4 table structures... Web_Users----------------User_IDName Tags_Table-----------------Tags_IDUser_IDGroup_IDTitle Created_Groups-----------------------Group_IDGroup_Name Tags_To_Groups------------------------Group_Link_IDGroup_IDTag_ID Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups. So this is what I'm trying to do... I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time. Does this make sense? Can anyone help? Thank you
Hi there, i have a query building question and was hoping that one of you would know the answer. Here is what i need to do :(i am using asp.net and ado.net) I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date. I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set. But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date. How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date. thanks in advance
Hi Everyone,I'm facing this problem now.Cannot open database "C:myDB" requested by the login. The login failed.Login failed for user 'myLaptopASPNET'.May i ask how can i resolve?Please feel free to let me know the information you need.Thank You!
I am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:==============================Column Name || Column Value-----------------||-----------------Column Name || Column Value -----------------||----------------- I hope I have explained myself properly. Any help would be greatly appreciated.
I have a question about re-creating the sql portion on either node a or b in a clustered situation. Is it possible to do this without affecting the working node and database ? or is the best solution to make a ghosted image of the node after install is complete and if the node fails just get to a point on the affected node where you can copy the image back?
During the set-up of my DB's and their tables, I was unable to setup a relationship between a table in one Database and another table in my other Database ( using the diagram ). Maybe my datastructure of multiple databases is not correct, or is their an option to set relations between multiple databases ?
PS in the future I planned to have some other databases on different servers.
I'm building a DTS package which needs to mail a list of users nightly
The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.
I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.
The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3....etc).
I'm a bit unsure on how to go about doing / writing this.
Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.
I have a table set up with my parameters and output file names.
Question 1: Process from DTS I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?
Question 2: Process from a stored proc I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).
I am trying to create a stored proc. I am passing in some variables which are used as the "data" side of where clause tests, but I also want to pass in a couple of variables to be the variable side of the where clause test, can it be done?
i.e. select id from table where a=1 and b=2 order by my_order_field
becomes
select id from table where a=@data1 and b=@data2 order by @my_variable_field
I have a (hopefully) simple question. I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited.
I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is.
Afterwards, we want to update the status, so I find the saved form, make my edits, and save it.
This is where my problem starts.
When I first save the letter, I have no problems whatsoever. When I save it a second time after making my edits, I get an error:
Microsoft OLE DB Provider for SQL Server error '80040e14' Line 1: Incorrect syntax near 'yearID'. /AuditFlowChart/UpdateAudit.asp, line 206
What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line
Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog)
I print out spStoredProcedure and I get the following
When I run the above thru Query Analyzer, I get the following:
Server: Msg 170, Level 15, State 1, Line 26 Line 26: Incorrect syntax near 'yearID'.
So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query?
I have a problem with Insert and Update Unicode data to database I can send Unicode data using string with N prefix (Ref 001)
Below I write some line of my SP But It does not write Unicode data to table, it writes like ???????? How to insert data using n prefix with Declared Variable? How can avoid this problem? Any other method to pass unicode data using SP? Please help me...
column UnitName nvarchar
Sample Stored Procedure (SP) SP header:
CREATE PROCEDURE ltrsp_AddEditUnit @UnitID char(4), @UnitName nvarchar(20), @UtrID int
Insert:
INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID)
Update: --- UPDATE ltrtb_Unit SET UnitName=@UnitName, UtrID=@UtrID
WHERE UnitID=@UnitID
Ref 001:
found a method to insert unicode data to table (Using N' prefix), but I want to add using declared variable
INSERT INTO ltrtb_UserLevel (UserLevel,Description) VALUES (1,N'Unicode String') From the URL, http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
But I need to know how to pass the data using stored procedure
I am trying to import part of the following file structure into a SQL2005 table The 1st 10 records are not needed (HEADER DATA), neither are the records that are from ‘TRAILER RECORD’ Can anyone assist me in designing a Simple SSIS to import this file to a table The LedgerKey will be from a Global variable Text within <> are for information and NOT contained in the file In this file there are three records to be imported, but in other files thjere can be upwards of 20,000 records
Table CREATE TABLE [dbo].[TempTransactions]( [LedgerKey] [varchar](20) NOT NULL, [Licence] [varchar](6) NOT NULL, [DestSortCode] [varchar](6) NOT NULL, [DestAccountNumber] [varchar](8) NOT NULL, [DestAccountName] [varchar](18) NULL, [TransValue] [varchar](11) NOT NULL, [DestBankRef] [varchar](18) NULL, [TransCode] [varchar](2) NOT NULL, [tranref] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY]
File < HEADER DATA NOT REQUIRED > "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" < HEADER DATA NOT REQUIRED > "11111122222222TEST ACCOUNT 1 BANK REG 0000000020017" "33333344444444TEST ACCOUNT 2 BANK REG 0000000150017" "55555566666666TEST ACCOUNT 3 BANK REG 0000000030017" <FIXED FIELDS LENGTHS 6,8,18,18,11,2 > <TRAILER RECORD NOT REQUIRED > "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" <TRAILER RECORD NOT REQUIRED >
