Our Business partners request me to read the field names from a SQL table dynamically so that my SSIS package will not get impacted if Web Service hosts make a change.
Is there a way to evaluate a string at runtime that contains a field name ?
Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this: EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc... I'm basically looping through a large list of parameters that correspond to column names. However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared. Is there a way to convert "@' + @current_column " into the actual value of the parameter?
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ??? Thanks so much for any help... Nancy
Hi,I am evaluating Lumigent's Entegra for doing security and businessaudit of some of the critical database(s) in the company I work for. Iwould like to know what has been your experience in using this productfor doing similar audits in your company, if you have also done suchaudits.Thanks,Sanjeev
I have been looking all over for some info about other people having this problem, but haven't found anything.
I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:
Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!
Hi all€”I'm new to expressions, and am trying to write one where I can evaluate if a field is null, to handle it by inserting a default value if a null value is found. Given this table definition:
Hello all, I currently have a pain in the butt with a subquery that needs to be evaluated, and if the result gives me a null value, I want to re-evaluate the condition and fill the column with the proper information. I'll try to explain it as best as I can:
The query retrieves information from 2 tables basically, but i do need some inner joins of diferent tables in order to follow the relations.. I got 2 conditions that need to be acomplished, the 'typeof' (intId_Tipo) has to be diferent from 6 and a keycode equals to 3.
For the "general query" i do filter this just fine, but in the subquery is where I get stucked: Since the evaluation that I do in oder to find the initial point is just by substracting 1, there are some cases where the type called intId_Tipo is actually 6 and the subquery returns me a null value. In this particular case I want to substract 2..if not I just want to substract 1. I added a SQL CASE in oder to evaluate the null value, but it is not evaluating properly, why? I'm not sure, I need your help and recomendation guys
Here's the query that I currently have: (the comments were made in order to take the next screenshot)
Code Snippet SELECT --h.dt_Lectura, --h.int_Velocidad, rp.int_VelMaxima, (SELECT intpunto FROM tblRecorrido_Puntos WHERE intPunto = case when (intPunto) is null then rp.intPunto - 2 else rp.intPunto -1 END AND rp.intId_Ruta_Ramal =intId_Ruta_Ramal AND intId_Tipo =rp.intId_Tipo) as PuntoInicial, rp.intPunto as PuntoFinal --rp.strDescripcion_Punto as DescripcionPuntoFinal, --1 as Contador --a.idRuta_Guid, a.idRamal_Guid,a.idUnidad_Guid, a.idOperador_Guid
FROM tblHistorico h INNER JOIN tblAsignaciones a ON a.id=h.intIdAsignaciones INNER JOIN tblRutaRamal_Generado rrg ON rrg.id_Ruta = a.intRuta_Asignada INNER JOIN tblRecorrido_Puntos rp ON (rp.intId_Ruta_Ramal =rrg.intId_Ruta_Ramal AND rp.intPunto=h.int_PtoDest)
WHERE ( h.int_ClaveTDE =3 and rp.intId_Tipo <> 6 )
ORDER BY rp.intPunto ASC
Here's the screenshot of the query result: http://img218.imageshack.us/my.php?image=consultahd0.jpg
I want to do some error checking on the parameters found in a SQLDataSource before I run the insert. The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to? Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these? Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>" SelectCommand="SELECT [ContactID], [FirstName], [LastName], , [Address], [Phone], [Grade], [Contacted], [ListServe] FROM [Contact]" UpdateCommand="UPDATE Contact SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Address = @Address, Phone = @Phone, Grade = @Grade WHERE ContactID = @ContactID" DeleteCommand="DELETE FROM [Contact] WHERE ContactID = @ContactID" InsertCommand="INSERT INTO [Contact] ([FirstName],[LastName],,[Address],[Phone],[Grade],[Contacted],[ListServe]) VALUES (@FirstName,@LastName,@Email,@Address,@Phone,@Grade,0,0)"> <UpdateParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Grade" Type="String" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter Name="FirstName" Type="String" ControlID="txtContactFirst" PropertyName="Text" /> <asp:ControlParameter Name="LastName" Type="String" ControlID="txtContactLast" PropertyName="Text" /> <asp:ControlParameter Name="Email" Type="String" ControlID="txtContactEmail" PropertyName="Text" /> <asp:ControlParameter Name="Address" Type="String" ControlID="txtContactAddress" PropertyName="Text" /> <asp:ControlParameter Name="Phone" Type="String" ControlID="txtContactPhone" PropertyName="Text" /> <asp:ControlParameter Name="Grade" Type="String" ControlID="ddlContactGrade" PropertyName="SelectedValue" /> </InsertParameters> </asp:SqlDataSource> An Event is fired when I click add on a button which looks similar to this: //Add btnAddContact_Clickprotected void btnAddContact_Click(object sender, EventArgs e) { InsertRow(sqlContact); }The InsertRow() function is then what i'm using to evaluate the values... So how can get the values those controlparameters actually are in order to evaluate them before I actually insert. Or is there a better way to do it?
Hi I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded, ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode, ResearchStudent.RegistrationDate, StudentExamination.Decision, IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate, ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID) LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID) LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)] And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1)) AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False)) ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.
SELECT COALESCE(CEOAnnualBonus, CEOBonus) AS CEOBonusCombined FROM tbenchmarktemp WHERE Ticker='F'
I want to create the most effective way (google) to have content displayed on a web page. Currently, we are updating to SQL and populating to web via calling SQL statements hardcoded to page. I want to know if this is ok to do, or should we be reading from a static XML file that is updated? I think the XML is the way to go being that i may have answered my own question, but i was curious to what the experts on asp.net had to say. Thanks,Nick
I'm just using TEXTBOX control to save data into my DB. My question is, if I have multiple lines of sentences, it does not store as what it looks like. For example;
Hello, Mike
It stores into the database as "Hello, Mike" and if I read from the database, it does not have seperate lines and shows it like this; Hello, Mike
How can I store the contents into database and make the contents as original? (Sorry for the poor English, but I hope you understand it.)
Hi All,I have a job coming up which involves me creating a vacancy system for our website.I (think) I'll only need one table:ID (PK)JobTitleJobLocationSalaryHoursJobDescriptionValidFrom (Date/Time)ValidTo (Date/Time)I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?Thanks in advance,Brett
HiI have developed a sql procedure for updating some xml content using SQL 2005 xQuery features.For example:Declare @xmlContent = '<books><book name="asp"/>< book name = "sql"/></books>'--Now I want to add attribute "book_id" to the xml content.SET @xmlContent.modify('insert attribute book_id{"1" } into (/books/book[@name="asp"])[1] ')SET @xmlContent.modify('insert attribute book_id{"2" } into (/books/book[@name="sql"])[1] ')SELECT @xmlContent--OUTPUT will be<books><book name="asp" book_id="1"/><book name="sql" book_id="2"/></books>QUESTIONQ1. Do we have any extension or plugin such that this SQl 2005 xquery feature can be incorporated in SQL 2000?Q2. If the answer for Q1. is no (as I expect), then do we have any TSQL feature in SQL 2000 for modifying xml content?I found all examples in SQl 2000 related with "openxml" function which performs the select kind of operations only. Like selecting data in XML format OR converting xml data in to tabular format. But I couldn't find any xml modification TSQL in SQl 2000 which can add element/attributes or change values.one workaround could be to consider xml content as string and use string functions to modify the content.But is there any more sensible approach available in SQL 2000?
Okay, moving to developing a small site that will be storing articles - that is a site largely consisting of html marked up articles with images. We already have an SQL Server so it is becoming the back-end.
So, since I'm not an SQL Server expert, some general advice on this would be appreciated? The articles, as I say in HTML (largely just markup for titles, and general text) would be stored in the DB? As Text datatypes?
The problem is, I have a table with one column of varchar(8000) datatype. It has got 1000's of records. Through Query Analyzer, I am not able to view my records after 257 columns in my table, even I increased my textsize by using 'set textsize' command.
Any other setting is available to correct this. Any help is appreciated.
I have a particular production database that on a few recent occasions has suddenly had one of it's tables empty of content. Now there are no jobs or triggers in place neither is any stored procedures that could perform a delete or alter table operation in place. The permissions have been set not to allow this either. The datatype is text only. What is intriguing is the QA server has an exact mirror and this dosen't happen. Has anyone come across this sort of phenomenon?
I'm the project lead for SQL Server Express in Microsoft (as well as owning the Storage Engine). I'm going to make sure that threads on this forum get answered - I see a bunch with no answer so far.
Now to the point - we’re in the process of assessing the content that exists to help people learn and use SQL Server Express. This includes demos, tutorials, whitepapers, Webcasts, Starter Kits, and any other type of content you could imagine. So this is a great chance to TELL US WHAT YOU WANT! We’ll integrate your feedback and make sure that we try and address the most requested areas of content. Please be as specific as you can.
Thanks in advance for your feedback.
Paul Randal Lead Program Manager, Microsoft SQL Server Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
I have a field 'LastName' in a SQL 2000 table 'customers' that is populated with all our customer's full names in this order:
Smith John M
I need to flip the information around so that it can be exported into a CSV file for an automated email notification program to notify them their items are in and having it in first, last, MI format is much more professional.
I've been playing with charindex and stuff routines and am spinning my wheels. Is there a canned routine for something like this - this must have been done a hundreed times out there in SQL somewhere (or it's so easy to do that everyone (but me) just cranks it from scratch).
hi,i have a sqlserver CE and a sqlserver database.The tables are exactly the same on both databases.the sqlserver CE database Content will be synchronized with thesqlserver database with insert orders..is there a way to merge the two databases?this would be great cause different content will be inserted in bothdatabases. After a synchronisation both databases should have the samecontent..i hope you understood my problem. my english is not very well..christian
HiI have set up a SQL database to contain alist FAQ's for our company andthen plan to pull this info off using a web page.So far I have entered the data but I am unable to control how it isdisplayed inside SQL ie I cannot enter new blank lines I have triedusing lots of spaces but this does not work when I use the website todisplay the info.Is there a way of formatting and editing the data in the sql databaseas I am unable to do this, if I try to edit the data in the database Ihave to copy it to notepad delete what is in the database, edit thetext in notepad then paste it back in.There must be a better way.Please helpalamb200
HiI've developed a simple web service that lets people easily set up their ownwebsites, and keep them up-to-date themselves.I'm storing all the content, including images in the SQL 2000 database. Sofar it's working well enough. Is there likely to be a performance orreliability problem as the system grows?You can see it in operation here: www.up-to-the-minute.comIt's early days and any comments and ideas are welcome.John SouthPangbourne UK
Hello,On our corporate website, we will be using email notifications forvarious things. I would like for our marketing guy to be able to editthe email templates over the web so that I don't have to keep updatingthem myself for every little change. I want to store the templateswithin the database (since they will be small). The templates will beHTML based and not more than a few thousand bytes in length. Should Iuse a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'mnot sure.Thanks,Will