Why is MS Access such complete garbage?


Every time I am forced to use this thing I just end up hating it. When Microsoft is giving SQL Server Express away for free why do they SELL Access Why not eliminate Access and just provide a nice GUI for SQL Server

My latest headache How do I create a foreign key that is also a primary key I can edit the relationship but I can't tell what the direction of the relationship is. In other words I have an Employee table and a Manager table. The managers are obviously limited to employees, so in the Manager table I want to create a foreign key relationship back to the Employee table.
Edited by davej, 18 November 2012 - 01:41 AM.
Posted On: Monday 31st of December 2012 01:53:27 AM Total Views:  207
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Procedure or function Recoms has too many arguments specified

my sql procedure is like below select * from tbl where ID in (@IDs) for example @IDs is "11,23,44,55,66,77,88,99,223,223,445,666" So it has alot of arguments.sometimes it can be too long that's the reason to get error message What must I do
VIEWS ON THIS POST

97

Posted on:

Friday 26th October 2012
View Replies!

Problems with server

Hi. I'm running MsSQL 2000 developers edittion on my home computer. It's running XP Pro. I mainly use it so I could play around with ASP.NET, webservices etc. Anyway after I create tables with Enterprise Manager, and restart the computer the tables I create disapear. Also the users I add to the database I created disapear too. One thing that remains is the database I created. Any idea what is going on I wasn't being able to find any info on this. Probably because I'm not even sure what too look for.
VIEWS ON THIS POST

85

Posted on:

Friday 26th October 2012
View Replies!

Problems with query statement

I have the following query: Code: SELECT COUNT(Hit.fileID) AS counter, Hit.fileID, FileDetail.* FROM Hit,FileDetail WHERE (FileDetail.fileID = Hit.fileID) GROUP BY Hit.fileID Tables look like this: Code: Hit: [ hitId - pri key fileId - for key date other stuff ] FileDetail: [ fileId - pri key file name file type ] I want to count the number of times that each file appears in the hits table. The output I require is : fileId, name, type, number of times in hit table.... I know this is probably pretty simple but I keep getting silly errors
VIEWS ON THIS POST

144

Posted on:

Friday 26th October 2012
View Replies!

Where And problems

Hi I am having problems with this MS SQL Where AND statement. The rest of the statement prior to this works fine. & "Where(companyProductNumber = {0} AND company_ID = {1})", Partnumber, companyID) The above code is all on one line. The & is from the continuation from the line before.
VIEWS ON THIS POST

75

Posted on:

Friday 26th October 2012
View Replies!

Changing the mssql password

I have SQL Server 2000 the Microsoft SQL Enterprise Mangaer version 8.0 and I don't know the password of sa account and I found this page where it shows how to change it and I tried it but it didnt work for me. Password didnt change So what to do Is there any way to see the current pw
VIEWS ON THIS POST

129

Posted on:

Friday 26th October 2012
View Replies!

Update Datepart ms access sql

Right now 80% of records are Last Eval = Eval Due which is wrong; My goal is to update Eval Due (3) years from Last Eval Date. date/time mmddyyyy (changing the year only)
VIEWS ON THIS POST

154

Posted on:

Friday 26th October 2012
View Replies!

Big problem with ms sql

i got a .bak file from someone. supposedly this is the backup of the ms sql server. unfortunately i do not have ms sql installed on my computer and i can't seem to open the file in notepad. how can i convert this file to either access so that i can view the contents of the db or how can i convert it to mysql server (which i have).
VIEWS ON THIS POST

156

Posted on:

Friday 26th October 2012
View Replies!

How can I modify this code

How can I modify this code to return a value which has decimal points that is to say returning 918.56 instead of 918 Code: DECLARE @val VARCHAR(12) SET @val = '918.56JOA' SELECT CONVERT(INT, LEFT(@val,PATINDEX('%[^0-9]%',@val+' ')-1))
VIEWS ON THIS POST

70

Posted on:

Wednesday 7th November 2012
View Replies!

SQL Data from query to Excell file

Hi I have a simple database in SQL Server 2005... I made a simple query like this.... Select CitiesName,CitiesSize From Cities I save this query.......Now i want to run it from excell 2003 or 2007.... I mean the 2 columns that query brings(Cities_Name, Cities_Size) bring it in excell file.... I know that i can input data in Excell from a Table... But can i input data from a query\t\t\t\t\t\t Because my table have many fields and i want only these 2.... Is there any other way\t\t
VIEWS ON THIS POST

105

Posted on:

Wednesday 7th November 2012
View Replies!

SQL databases synchronization

Please can somebody help me with the following: Our company has an enterprize site (a very large one). From time to time we need to make changes in it. In other words, we had to synchronize one database (local where we make all changes) and database on hosting. So, we did it manually (wrote scripts and inserted them in database on hosting). But as the databases grew it became impossible to synchronize everything manually. So we need some tool which could cope with this task. The problem is that, as I know, there are many tools which can do it, but we are tight in time and can't evaluate them all. Please share your ideas on what tool is worth evaluating
VIEWS ON THIS POST

143

Posted on:

Wednesday 7th November 2012
View Replies!

Help please - Conversion failed when converting the varchar to bit

Hi I am trying to bulk upload a csv file (with 47 columns) into SQL database but when the insert is executed it generates an error : "Conversion failed when converting the varchar value '""True""' to data type bit. " And thats the same with all fields that are not varchar - datetime, int etc etc. Here is the code i use: Code: csv_to_read="../vendorfeed/" & newsletter.Fields.Item("feedname").Value set fso = createobject("scripting.filesystemobject") set act = fso.opentextfile(server.mappath(csv_to_read),1,False) dim sline dim sSeg Do Until act.AtEndOfStream sline=act.readline sSeg=split(sline,",") dim strsql strsql="INSERT INTO vendor_products (productcode, smalldes, fulldes, description, vendorid, productcost, originalprice, currentprice, openingquantity, currentquantity, hideshow, categoryid, subcategoryid, showhometop, showhomesecondary, poshomesecondary, showsecondpage, showpickmonth, showfeature, possecondpage, productimage, productimagelarge, productimageth, manu_name, ManufacturerID, productweight, stock, Price, Break1, Price2, Break2, Price3, Break3, InStock, TimeKey, Special, dimensions, overridereturntext, overridereturn, overridepricemargin, overridepricecurrent, newpricemargin, newcpmargin, freeshipping, discontinued, productsource, pagetitle)" strsql=strsql & "VALUES('"&sSeg(0)&"', '"&sSeg(1)&"', '"&sSeg(2)&"', '"&sSeg(3)&"', '"&sSeg(4)&"', '"&sSeg(5)&"', '"&sSeg(6)&"', '"&sSeg(7)&"' , '"&sSeg(8)&"', '"&sSeg(9)&"', '"&sSeg(10)&"', '"&sSeg(11)&"', '"&sSeg(12)&"', '"&sSeg(13)&"', '"&sSeg(14)&"', '"&sSeg(15)&"', '"&sSeg(16)&"', '"&sSeg(17)&"', '"&sSeg(18)&"', '"&sSeg(19)&"', '"&sSeg(20)&"', '"&sSeg(21)&"', '"&sSeg(22)&"', '"&sSeg(23)&"', '"&sSeg(24)&"', '"&sSeg(25)&"', '"&sSeg(26)&"', '"&sSeg(27)&"', '"&sSeg(28)&"', '"&sSeg(29)&"', '"&sSeg(30)&"', '"&sSeg(31)&"', '"&sSeg(32)&"', '"&sSeg(33)&"', '"&sSeg(34)&"', '"&sSeg(35)&"', '"&sSeg(36)&"', '"&sSeg(37)&"', '"&sSeg(38)&"', '"&sSeg(39)&"', '"&sSeg(40)&"', '"&sSeg(41)&"', '"&sSeg(42)&"', '"&sSeg(43)&"', '"&sSeg(44)&"', '"&sSeg(45)&"', '"&sSeg(46)&"')" objconn.execute strsql loop act.close set act=nothing I know that this can be done and i have done it with access before and it worked but with sql it is not working Please help me.
VIEWS ON THIS POST

167

Posted on:

Wednesday 7th November 2012
View Replies!

Insert Error: SQL Server error '80040e14'

Hi!
VIEWS ON THIS POST

180

Posted on:

Wednesday 7th November 2012
View Replies!

Stored Procedure - Delete some rows (table)

How to I return the row count as result of deleting some rows from a table This this script correct below\t Here's my stored procedure script.. Code: CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw /*************************************************************** ** CREATED: 12/04/2006 **************************************************************** ** DESCRIPTION: Clean the tblSoldRaw by deleting any data that ** is over 90 days old. We need keep the database ** as small as possible so the performance won't ** suffer on the server's and the client's machine. ** **************************************************************** ** NOTES: Hooked up to BookItOut Data Importer Windows Service **************************************************************** ** MODIFICATIONS: ** ** DATE WHO MODIFICATION DESCRIPTION ** ------------------------------------------------------------- ** ***************************************************************/ /*************************************************************** ** Uses: tblSoldRaw ***************************************************************/ AS DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90) RETURN ROWCOUNT ERROR_HANDLER: RETURN -1 SET NOCOUNT OFF
VIEWS ON THIS POST

213

Posted on:

Wednesday 7th November 2012
View Replies!

Users - Need advice on making changes to user rights ,etc.

If I want to remove a login name from one of my databases and change it to dbo and 'sa' as a login, how do I go about doing that I keep reading, but I'm not but I'm not getting comfortable with making changes yet. Is there a good site that explains this facet of Sql Server really well for people who need it explained really well
VIEWS ON THIS POST

71

Posted on:

Wednesday 7th November 2012
View Replies!

Greatest value

Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it.
VIEWS ON THIS POST

72

Posted on:

Wednesday 7th November 2012
View Replies!

Problem in sql query and sys date

What's the question , You need to explain the first query a little better. You will need to specify a starting date to search for to see if 12 days have passed I have assumed that you wanted to use what ever the current system date is. This can be accessed through the GETDATE() function. First Query Select * from YourTable where ([date of submission] BETWEEN DATEADD("d",-12,GETDATE() and GETDATE()) Second query Select * from YourTable where (DATEDIFF("d", [date of submission], GETDATE()) > 12) , You will need to add some quotes around the d for the string to be built properly. Code: SqlSelect= "Select Room,FirstName,LastName,DraftDate,RoomNotes,DateOf Submission from Clients where DateOfSubmission BETWEEN DATEADD(""""d"""",-12,GETDATE() and GETDATE())"
VIEWS ON THIS POST

88

Posted on:

Wednesday 7th November 2012
View Replies!

Find date difference and delete record

I want to delete the records who older than 90 days. This code is not working.Can anyone help please create PROCEDURE dbo.deleteReservation @resID int, @d int, @diff int as set nocount on Declare CurRes CURSOR for SELECT DATEDIFF(d,reservationdate, getdate()),id from reservations OPEN CurRes FETCH NEXT FROM CurRes INTO @diff,@resID WHILE @@FETCH_STATUS = 0 BEGIN IF @diff > 1 BEGIN EXEC('DELETE FROM reservations WHERE id =' + @resID) End FETCH NEXT FROM CurRes INTO @diff,@resID END CLOSE CurRes DEALLOCATE CurRes go
VIEWS ON THIS POST

82

Posted on:

Wednesday 7th November 2012
View Replies!

best approach "Where In' or join

I have 2 sets Set1 has few UNIQUE items say workitem Ids 2,3,4 Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8 I need to look for Set1 in Set2 so i get 2,3,4 etc... which is the best way Set2 Left join Set1 OR Select item from Set2 where item in (Set1)
VIEWS ON THIS POST

57

Posted on:

Wednesday 7th November 2012
View Replies!

How to execute stored procedure on a different sql server instance same domain

Is it possible to use a distributed query to execute a stored procedure on another SQL server without using RPC I have one dev server and production server. now, i want to execute a stored procedure which is there on prod from dev server. What are the ways we can accomplish this task\t I tried with Linked Server but it throwing me an error RPC not configured for the target server. EXEC [ServerName\SQL_01].ADVENTUREWORKS.DBO.TEST_SP Msg 7411, Level 16, State 1, Line 1 Server 'ServerName\SQL_01' is not configured for RPC.
VIEWS ON THIS POST

164

Posted on:

Saturday 10th November 2012
View Replies!

Upload file to SQL database using classic ASP

, I am trying to upload files (.doc/.pdf) to a SQL database (2005) but I am really struggling to find any step by step guides. This is what happens on my ASP form: User selects a document Document is currently upload to a temp file and sent to a email address However it also needs to be stored within a database field. I have set the field type within the database to Image. But I am unsure about how I save the binary data information when all I really have is a link to the document (for example C://) from the user. I also want to be able to download this document at a later date, this is also possible Thank you in advance for any help.
VIEWS ON THIS POST

160

Posted on:

Saturday 10th November 2012
View Replies!

Date/Time - Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

Hi I am getting this error: Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. This is my SQL statement (goDate is in 2/15/2009 format. I'm thinking that might be my problem.. It IS a date/time field in Access). SQL = "insert into calendar (event, cal_date, username) values " & _ "('" & new_event & "','" & goDate & "','" & my_usernm & "');" Thank you
VIEWS ON THIS POST

71

Posted on:

Saturday 10th November 2012
View Replies!

How to determine that given trace file is active or not

I have few queries reagarding active trace files: For a given trace file whose trace id is not known, is there any way/query to determine that the trace is active or not OR For a given trace file, is there any way/query to determine that the trace id of that trace file Pls respond asap.
VIEWS ON THIS POST

91

Posted on:

Saturday 10th November 2012
View Replies!

MLS Integration via IDX

Can someone help me in the right direction to integrate MLS database via IDX Here is the link for MLS http://therealestatehost.com/mls_integration.php/ any help is highly appreciated Todd
VIEWS ON THIS POST

31

Posted on:

Saturday 10th November 2012
View Replies!

Performing Base Conversions to create Unique Access Codes

there, I am trying to create a unique access code generator in order to identify individuals responding to an online survey. I issue them a web code and they type it in. Hex could work too, but I also like base 36 conversions because numbers under 100,000 give you a nice result (18575 becomes EBZ). But HOW The only functions I see online only take numbers between 1 and 36. I want to be able to give an integer in the thousands, not character-by-character. Also, if you have some other method of creating similar access codes, please share! references: http://www.tonymarston.net/php-mysql/converter.php
VIEWS ON THIS POST

95

Posted on:

Saturday 10th November 2012
View Replies!

Too many arguments specified

my sp is an update statement, residing in master. my sp: Code: CREATE PROCEDURE [dbo].[sp_UpdateContents] @strDB varchar(50) AS BEGIN DECLARE @strSQL nvarchar(4000) SET @strSQL = '@IDKEY int, @Contents varchar(8000), @Tags varchar(8000), @Title varchar(8000) AS UPDATE ' + @strDB + '.dbo.contents SET title=@Title, contents=@Contents, tags=@Tags WHERE idkey=@IDKEY' EXEC sp_executesql @strSQL END which is triggered from a function in my asp page: Code: Function updateContent() 'generate tags tagsep = request.form("tags") & " "& request("menuItem")&" "& sitetag tagsep = replace(tagsep," "," ") tagsep = replace(tagsep," "," ") tagsep = replace(tagsep," "," ") tagsep = replace(tagsep," "," ") tagsep = replace(tagsep,"$!te","") tagsep = replace(tagsep,"$","") tagsep = replace(tagsep,"*","") holdtag = tagsep tagsep = RemoveDupes(tagsep," ") length = len(tagsep) firstpos = instr(tagsep," ") do while firstpos > 1 newtag = newtag & " "&LCase(left(tagsep,firstpos-1)) tagsep = right(tagsep,length-firstpos) length = len(tagsep) firstpos = instr(tagsep," ") loop text = replace(request.form("user_content"),"'","''") text = replace(text,"[bold]","") text = replace(text,"[*bold]","") text = replace(text,"[center]","") text = replace(text,"[*center]","") text = replace(text,"[right]","") text = replace(text,"[*right]","") text = replace(text,"[ul]","") text = replace(text,"[*ul]","") text = replace(text,"[listitem]","") text = replace(text,"[*listitem]","") text = replace(text,"[heading]","") text = replace(text,"[*heading]","") text = replace(text,"[heading2]","") text = replace(text,"[*heading2]","") text = replace(text,"[heading3]","") text = replace(text,"[*heading3]","") text = replace(text,"[heading4]","") text = replace(text,"[*heading4]","") text = replace(text,"target=_blank","") text = replace(text,"
VIEWS ON THIS POST

66

Posted on:

Saturday 10th November 2012
View Replies!

Dumbest question ever, where is my SQL database?

I've installed the latest SQL server, ODBC, etc, and installed a program called Bullzip, which converts Access DB's to MySql. The program works great, but where is the database it generates I can't find it anywhere. I looked in the MySQL folder, nothing. I have another program called Dreamcoder for opening the databases and editing fields, it lists the database and all the fields, but I have no idea where it's getting it from. I need to upload the MySQL database to my server so that I can begin converting my Access based ASP code. Where do I find it, and where would I upload it to on a godaddy server
VIEWS ON THIS POST

230

Posted on:

Sunday 11th November 2012
View Replies!

Error msg: Operation must use an updateable query

I'm trying to insert data into a database with a web form and I get the message "Operation must use an updateable query" when I try the page using my own web server. When I upload the pages to a ASP.Net hosting service the page works fine. On my own server I've enabled write permissions on the database and database directory and it still doesn't work.I'm running windows XP with IIS 5.1 using an MSAccess database.Can someone suggest a possible solution please
VIEWS ON THIS POST

172

Posted on:

Sunday 11th November 2012
View Replies!

Uploading my DB to Server

I have made a website which depends heavily on it's Databse. The site is online but I can't get my DB on-line... My host has given me a DB. But there is nothing in this DB and I want to import my DB.mdf into it somehow. I have MS SQL server management studio and have got onto my host and found the db he has given me. I right click on it but there is no option for import... I can't figure this out. Is there a program for uploading the Tables and Record and sub proceedures into the Host's DB PLease help I have only have a day to get this right,
VIEWS ON THIS POST

55

Posted on:

Sunday 11th November 2012
View Replies!

Performance

Hiya, I'm seeing some very strange performance differences when running some ASP queries. Basically, a page could load in 5-10 seconds, yet refreshing it 2 seconds later results in 1-2+ minute load times. I've tested changing the number of results the query returns and using the top 5000 records and loaded in 20 seconds, yet changing the query back to the top 10 and refreshing the page took 70 seconds! This is at very quiet times (ie 7am in the morning) when no one but me is in the office! Our setup is client PC -> IIS server -> SQL server. I'm using OLEDB connections in an include file.
VIEWS ON THIS POST

41

Posted on:

Monday 12th November 2012
View Replies!

Sorting ntext

I am trying to sort a record set that's being returned from SQL server which contains various data types, e.g. int, nvarchar, money, datatime & ntext. The ntext field has up to 40,000 chars in some of the records, so I can't change the data type, and just before anyone says normalise it, I have. Any ideas please. Paul
VIEWS ON THIS POST

80

Posted on:

Thursday 15th November 2012
View Replies!

Finding database

I am currently doing a clear up of our SQL server at my company and have come across what seems to be a rogue MDF and LDF file. The name of these files does not seem to match the names of any of the databases showing in Enterprise Manager. I want to find out what database these MDF and LDF files are attached to. There are around 90 databases on our SQL server and it's gonna take me ages going through them all one by one looking at the properties to find these files. Does anyone know if there is a quick stored procedure I can use to achieve this Many
VIEWS ON THIS POST

106

Posted on:

Thursday 15th November 2012
View Replies!

How to change the COMPUTER NAME in SQL Server 2000 manager???

Hi PLEASE, I really need help. I have installed SQL Server 2000 Developer version on my Windows XP Professional. After I ran the "Enterprise Manager", under the "SQL Server Group", it must be my COMPUTER NAME (ie. andrew), but unfortunately, it is "local" (Below is the diagram). My question is how can I change it to my COMPUTER NAME Since my ASP.NET page can't connect to the SQL Server 2000 database. I have followed the instruction from this web page, http://www.stardeveloper.com/article...2041001&page=1 I am very appreciative for any help.
VIEWS ON THIS POST

76

Posted on:

Thursday 15th November 2012
View Replies!

Update Trigger

Im trying to write my first trigger to record a letter date, when the date is updated in one table i want it to pass this date to a new table which is needed for auditing purposes, however the trigger i have written for now works and passes some of the data but wont pass the date field i want to update, why Code below. Code: ALTER TRIGGER [dbo].[tr_Update_BookingLetterdate] ON [dbo].[tbl_property] AFTER UPDATE AS DECLARE @PROPREF VARCHAR(12) DECLARE @CONTRACT VARCHAR(3) DECLARE @SERVICEBOOKINGNR1LETTERDATE VARCHAR(50) DECLARE @SERVICEBOOKINGNR2LETTERDATE VARCHAR(50) DECLARE @SERVICEBOOKINGNR3LETTERDATE VARCHAR(50) DECLARE @NAFINALLETTERDATE VARCHAR(50) Select @PROPREF = PROPREF FROM tbl_property Select @CONTRACT = CONTRACT FROM tbl_property Select @SERVICEBOOKINGNR1LETTERDATE = SERVICEBOOKINGNR1LETTERDATE FROM tbl_property Select @SERVICEBOOKINGNR2LETTERDATE = SERVICEBOOKINGNR2LETTERDATE FROM tbl_property Select @SERVICEBOOKINGNR3LETTERDATE = SERVICEBOOKINGNR3LETTERDATE FROM tbl_property Select @NAFINALLETTERDATE = NAFINALLETTERDATE FROM tbl_property BEGIN SET NOCOUNT ON; INSERT INTO tbl_letterdates (propref, contract, LetterDate, lettertype) VALUES (@PROPREF, @CONTRACT, @SERVICEBOOKINGNR1LETTERDATE, 'Letter 1') INSERT INTO tbl_letterdates (propref, contract, LetterDate, lettertype) VALUES (@PROPREF, @CONTRACT, @SERVICEBOOKINGNR2LETTERDATE, 'Letter 2') INSERT INTO tbl_letterdates (propref, contract, LetterDate, lettertype) VALUES (@PROPREF, @CONTRACT, @SERVICEBOOKINGNR3LETTERDATE, 'Letter 3') INSERT INTO tbl_letterdates (propref, contract, LetterDate, lettertype) VALUES (@PROPREF, @CONTRACT, @NAFINALLETTERDATE, 'Final Letter') END
VIEWS ON THIS POST

174

Posted on:

Tuesday 20th November 2012
View Replies!

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

I keep getting the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired /admin/functions_web.asp, line 277 This application has been up for 5 years and we never had any problems and all of a sudden we have started getting this error. We have the latest MDAC version. I have increased the scripting and connection time and we still get this error. Please help!
VIEWS ON THIS POST

143

Posted on:

Tuesday 20th November 2012
View Replies!

Delete stored procedure that will update also

Hi to , all i want to do is to delete stored procedure that will also update a row in the table does anyone have any idea I know how to delete a stored procedure but i don't know how to include an update parameter in it.
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

Microsoft SQL server setting

hi , i need to migrate my web site from windows 2000 IIS 5 to a new server windows 2003 IIS 6, to configure the microsoft sql server for the new server, where can i look for the info that i needed to setup the new server to be able to run my web siteor anyone can teach me how to do the setting
VIEWS ON THIS POST

114

Posted on:

Tuesday 20th November 2012
View Replies!

Order Listbox items while save

The following piece of code allows me to insert multiple rows in the database. But the problem is varaiable @Protocols has values 27,1,3 but it inserts in the database as 1,3,27(in different rows). Can anybody provide a solution as to how to save the values as 27,1,3(in different rows) into the database. BEGIN SET @SQLString = 'INSERT INTO ERP_TASKS_LOG( ' + '[NRC_ID], ' + '[ProjectNum], ' + '[ERP_ID], ' + '[ProtocolNum], '+ '[TaskDtTime]) ' + 'SELECT ' + CONVERT(NVARCHAR(100), @NRC_ID, 126) + ', ' + CAST(@ProjectNum AS NVARCHAR(100)) + ', ' + '''' + @ERP_ID + ''', ' + '[ProtocolNum], getDate() ' + 'FROM lu_PROTOCOLS ' + 'WHERE [ProtocolNum] IN (' + @Protocols + ')' -- Execute it --PRINT @SQLString EXECUTE sp_executesql @SQLString END
VIEWS ON THIS POST

80

Posted on:

Tuesday 20th November 2012
View Replies!

Loop in TSQL

is there anything that would allow me (from inside a view or stored procedure) to run a stored procedure for each individual row in a select statement for example: if "select id from table" returns id -- 1 2 3 could i do some kind of loop or whatever that would be the equivilant of exec mystoredproc @param = 1 exec mystoredproc @param = 2 exec mystoredproc @param = 3
VIEWS ON THIS POST

31

Posted on:

Tuesday 20th November 2012
View Replies!

selecting certain data out of a field

Somewhat similar to my earlier question...hopefully easier. I have a field named "EditMemo" that contains data such as: Edit1=438799& It will always start with "Edit1=" and end with "&". How can I just select the important data (the "438799") from these fields ...Hopefully that question makes sense
VIEWS ON THIS POST

108

Posted on:

Tuesday 20th November 2012
View Replies!

Please help! Sql tables & USerID

Hi ! I tried a post earlier but I think I scared away! I'm a complete newbie to Sql so please bear with me! I have created a new table on my database, but I want the UserID column from my Users table to be the same ID. I have made the necessary Primary and Foreign key implements! However when I INSERT the other elements from an ASP form, I still get an error saying "Cannot insert a Null value" in the UserID column. Does this update automatically when I insert (from ASP) the others, or do I have to set a trigger or new query\t I know it may sound stupid and simple to those in the know, but I'm baffled. Trust me I have tried all the tutorials....... I promise! Maybe I'm just missing something completely! Any help...greatly appreciated! PS My login etc all work fine.......... so I know my link up with the database is working. I can also insert other values. It's just the FK/PK issue not playing properly!
VIEWS ON THIS POST

148

Posted on:

Tuesday 20th November 2012
View Replies!

SELECT string truncation problem!

Hi Pals, Need some help. I have big sql which is more than 4000 characters. Basically the sql is formed by UNION operator. For that reason i have declared the datatype as VARCHAR(MAX). and then tried to assign the value to a variable. and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated. What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.) Why is the sql is getting truncated What could be the problem How could we resolve this problem Do we need to take 2 string variables and do that or else do we have any other solution for this. Basically i am inserting the result fetched by the big SELECT stmt inside my stored procedure. Any Thoughts Any suggestions will be greatly appreciated.
VIEWS ON THIS POST

145

Posted on:

Tuesday 20th November 2012
View Replies!

SQL rights in Access Front end

I have an Access front end with a linked table from my SQL server. The ODBC is set to authenticate using the network login ID. In the SQL server I set the permissions for my user account (in sql) to just "Select" I can go into Access and change, update, etc What am I doing wrong
VIEWS ON THIS POST

42

Posted on:

Tuesday 20th November 2012
View Replies!

How do you create an upload on a form so that the file gets sent to the database?

, I have a form written in JScript and ASP and I need to create an upload area for files of any type, ...etc. The data in the form currently gets sent to a SQL database. How does the uploaded file get to the sql database I read somewhere that the field type is "image" The other challenge is that an email gets sent to a person with a link to view the web based form with all the data. But how is this person going to view the uploaded file Ideally I'd like for them to receive the file as an attachment. Is this possible Please help! Thank you, Barnes
VIEWS ON THIS POST

339

Posted on:

Tuesday 20th November 2012
View Replies!

Efficient ways to reset lost sql sa password

Have you ever forgot or lost your SQL Server sa password Did you hava any ideals to reset your lost sa password Or choosed to reinstall MS SQL Server on your PC again I once forgot my SQL sa password and I reinstalled SQL Server at last, because I failed to find some good methods to solve the problem. What annoying and terrible experience it is! In order to avoid reinstalling SQL Server for a second time, i collect some methods and try them by myself, to reset SQL Server Password for sa account. Method 1: Reset SQL Server sa password by Windows Authentication If Builtin/Administrator is present in SQL Server, you can login with an ID which is member of Administrators group and reset sa password in SQL Server. Just do as follows: Step 1. Login into SQL server using Windows Authentication. Step 2. In Object Explorer, open Security folder, open Logins folder. Right Click on sa account and go to Properties. Step 3. Type a new SQL sa password, and confirm it. Click OK to finish. This way is easilily to reset the sql server sa or other account password, but you need to have the valid Builtin/Administrator account.
VIEWS ON THIS POST

103

Posted on:

Monday 26th November 2012
View Replies!

Help new with Triggers

Hoping someone can help me. I would like to write a trigger that updates a field in one table when a field in a table is updated automatically. Example TableA, Field1 gets updated from a 1 to 2 I would Like TableB Field1 to be updated to match whatever TableA Field1 value is any help would be great.. I am new to writing triggers.
VIEWS ON THIS POST

113

Posted on:

Monday 26th November 2012
View Replies!

SQL tweaking

I have a pretty simple query that I need to tweak a little but I can't seem to figure out how. Here's the query: Code: SELECT s_name, count( showsongs.song_id ) AS songcount, showsongs.song_id FROM showsongs, song, shows WHERE showsongs.song_id = song.song_id AND showsongs.show_id = shows.show_id AND year( date ) =2006 GROUP BY s_name ORDER BY songcount DESC , s_name ASC I have a website that tracks setlists of a band. This query takes a list of their songs and orders them by the number of times the song was played. Well, the problem is sometimes they will play a song twice in a show. They do a lot of segues from one song into another so a lot of times the setlist will go something like Song A > Song B > Song A > Song C. Well, currently Song A gets counted twice for that one show and I only want it counted one. The appropriate tables and field are: showsongs(showsong_id, song_id(fk), show_id(fk), songnumber) song(song_id, s_name, album_id(fk)) shows(show_id, date, venue_id(fk), tour_id(fk), nosetlist)
VIEWS ON THIS POST

46

Posted on:

Monday 26th November 2012
View Replies!

Insert/update/select data from excel!

hi , how can we select/insert/update data from a specific cell in the excel sheet through sql query. the query could be in stored procedure. is this task be possible in DTS.
VIEWS ON THIS POST

175

Posted on:

Monday 26th November 2012
View Replies!

Login and authentication problem.

Login and authentication problem. Currently, I have two seperate mssql db's, one of which supports the primary functions of my website, and another which is solely for a forum I have recently decided to add. I did this so that one db won't slow down the other. My question is, how do I go about authenticating to both databases with only 1 login form, so that users are not forced to register twice
VIEWS ON THIS POST

129

Posted on:

Monday 26th November 2012
View Replies!

embedded SQL in C

I have this code that is supposed to change the telephone number of a particular tuple, but for some reason it seems like it selects the tuple with EID and then store the Telephone number in my variable phone , instead of changing the telephone number to phone.
VIEWS ON THIS POST

30

Posted on:

Saturday 29th December 2012
View Replies!

What is an SRO ?

Saw this question: http://answers.yahoo...26205109AAbgNHF
VIEWS ON THIS POST

55

Posted on:

Saturday 29th December 2012
View Replies!