How I can do If instruction in DTS ?? example select @data=date form table where id=32 if @data='2007-10-22' copy data to exel from table2 (I have this in 3 elements(OLE DB Source,Data Conversion,Flat File Destination) else do nothing
I don't now how to do if instraction. Pleas help me.
Hi, I need something like a sequence in a datafield of my table. (unique number in the table) is a uniqueidentifier the right thing to choose? and if yes, how to I insert a new value into the that table:
a) from the SQL Enterprise Manager? b) from a JAVA program using jdbc?
Which is the best way to check the size of a database from Transact SQL ? (Selecting from Sysfiles gives a totally different result compared to right-clicking a database and selecting Properties. It's the result from the latter than I want to obtain through Transact sQL.)
I have written some code that selects all the databases on our server then uses a while loop to execute some dynamic sql to query a table in each database. The problem is some of the databases dont have the correct permissions so the query breaks. Is it possible to check to see if the database has the correct permissions before I execute the dynamic SQL and if it doesn't move onto the next one in the list?
I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
Hi all, We have been checking the database by dbcc checkdb,through a automated task on our production server (nightly task run everday)d we always getting the message given below.
Msg 625, Level 20, state 1 Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0.
If anyone knows about this error message, can you kindly post the message.
hi i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail. if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
I am fairly new to SQl and PHP and have a question. I have a database that we enter in when a person buys a season ticket for our baseball team. I want to check and see who bought in 2013 but has not yet bought in 2014. How do I do that?
THID is the ticket holder ID and there is a year. I can do the select * from Passes where Year = '2014' and get everyone who has bought in 2014. How do I compare that with the 2013 and show people who have not bought yet in 2014?
With this - Ticket holder 21 has bought in both years so I wouldn't want his name to come up. I would want to see that ticket holders 15 and 10 haven't yet bought in 2014.
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this... Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this: When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
is there a way to find out that SQL Server 2005 (Express) engine is performing startup checked of a selected database?
The problem we are faced to is that our users shutdown brutally their laptops running their database. When they start laptops again, they impatiently start the application that tries to connect to the database. However, they get an error message:
Cannot open database XXXXX requested by the login. The login failed.
When they try after a minute or two, it works.
What we would like to do is provide an information message that the SQL server is checking the database and that they must wait for a while. Is there is a specific error code for this, can anyone help? We are using .NET 2.0.
As you can see Col1 & Col2 ONLY do not make up the PK. I need to include Col3 as well. My concern is I am not sure if I have a row like.. 1...........2.......A......X reapeated lower down the order. So how do I figure the PK out?
Can someone show me some C# code for detecting if a SQL row exists or not? This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step. In my code I'm either going to INSERT or UPDATE a record. I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response. Apparently ExecuteNonQuery does not work with SELECT. I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.So...can anyone share the typical code they use to identify if a row exists or not within a database. I guess I was execting there to be some method available to do this sort of thing.
i want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething. i came up with this but when it's doing the second if i get error on the page. Dim ReturnVal As Integer Dim ReturnVal2 As IntegerDim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)", conn) 'To check if the # is registered.Dim cmd2 As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @ReportNumber)", conn) cmd.Parameters.AddWithValue("@ReportNumber", txtReport.Text)cmd2.Parameters.AddWithValue("@ReportNumber", txtReport.Text) conn.Open() ReturnVal = Convert.ToInt32(cmd.ExecuteScalar()) conn.Close() 'This checks if the # is correct. If ReturnVal > 0 Then 'This checks if its registered with someone else. conn.Open() ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar()) conn.Close() If ReturnVal2 <> 0 ThenServer.Transfer("regccinfo.aspx") Else lblError.Text = "the # is registered with someone else." End If Else lblError.Text = "the # does not exists." txtReport.Focus() End If
Good morning everyone I am writing a windows forms application that will work similar to the windows messenger popup. I need this application to display a message to the user whenever a record is added to table in my DB. Any help would be appreciated in explaining how I can check the DB for new records.
I've forgotten the character set that I've chosen when I was installing the SQL Server 7. Is there a way to check?
I'm currently using US English version of Windows NT4 and SQL7. But interestingly, all the data is in Japanes characters. It's actually for a Japanese website, and the front-end application is written in ASP. I remember reading somewhere that it is impossible to do certain type of sorting (by some particular order for the Japanese language) as it is limited by the choice of the language of the NT OS.
The type of sorting that I'm looking at is the grouping of 5-characters. One example is in http://www.forest.impress.co.jp/aiueo.html
Would this be possible with my current setup? Or would it help if I migrate over to Windows 2000? (I'd rather not move to Japanese NT4)
Your feedback and advice would be very much appreciated!
Just a brief question. I have a script which does a number of insert statements. What I would like to do is determine if the insert statements were all successful. Aside from checking @@ERROR after every insert, is there a way to check if all the insert statements completed successfully?
I want to do a check to see if a number falls within a range specified by the user. Basically, they enter in a min and max value and I return a hit if a number falls inbetween.
I have having trouble with my SQL statement:
Code:
SELECT DISTINCT * FROM Table WHERE ( (0=0) AND (min.value <= Table.Value <= max.value) )
(This isn't my actual code, but just the logic)
It will return all the results everytime. Does anyone have any suggestions on what I could be missing here?
I have a question I hope someone can help me with.
My situation: I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem: On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after: (a) both A and B have completed execution, and (b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
I need to check one table while updating another table suppose i need to check table1's value is whether less then 0 or not without if condition can i check it with update query? thank you
I have a table that I know has a null or nothing in a field. When I run this query to get a list of them nothing comes back at all but it doesn't error out. Any ideas?
select * from lab_test_add_conf where table_name = null order by lab_test_conf_id
This is going to sound ignorant but... I had a look here http://support.microsoft.com/?id=321185
And one thing isn't clear. How doe you know if the 32-bit or 64-bit version was installed?
------------------------ Me: What do you want to know from your data warehouse? Client: Err...Emm...Everything Me: OK, that's great. That's all I need to know. I'll see you when it's done.
i'm not fluent with sql and i have a question to ask..
if i want to check whether a product is available between two dates, how can i do so? for example, i want to check whether it's available between 5/23/2007 to 5/25/2007..
Our System currently has on the upwards of 300 stored procedures in it.
We have SP's that call SP's that call SP's and sometimes a Stored Procedure will return 10 ResultSets which are used by .NET.
Some of our developers do not check other stored procedures when they modify the SP parameters, and in doing so break the system until the testers find it.
is there a way to run a command to check ALL the stored procedures to see if they work, they match the SP they're calling, etc?
Hey all,prolly a simple solution, but why isn't the following string working inmy execute sql step within DTS? It produces results, just not the onesI want... What am I doing wrong?select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'It's displaying all the records? It should only be displaying thoserecords that do *not* contain letters or numbers.Thanks in advance!-Roy
Situation:Day 1Table contains 100 items of actions imported via FTP. One of the fieldsin the table can be updated to reflect an assigned unit code.Day 2Actions that may be duplicates of the table from Day 1 (with theexception of the updated assigned unit field) are imported forinclusion in the Table from Day 1.Question- What is the best way to insure that any new items are addedto the table and that no duplicates are added at the sametime?Assistance would be appreciated.
SQL Server allows for a user to have SELECT permission on a View withoutthat user requiring an associated SELECT permission on the underlying tablethat the VIEW accesses, but the user can still access the data through theView. A similar arrangement holds true for stored procedures.So based on these initial known behaviours, I have a couple of questions:1. If a stored procedure A executes stored procedure B, does the user of Arequire execute permission for B also? Or will access to B be permittedregardless because the user was given access to A?2. Similarly, if a stored procedure A accesses a View, does the user of Arequire permissions on the referenced View?I guess to paraphrase what I am trying to determine is whether SQL Serveronly checks permissions at the "entry" point of a particular function, orwhether permission checks are performed "intra-function". My openingexamples imply they are only checked "on entry", but I am wondering if thisbehaviour is entirely consistent. Perhaps the SQL Standard mandates this?
I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used:
IF @return <> 0 BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return
END ELSE PRINT 'Success'
This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be:
Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following:
Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied. Number:201 Severity:16 State:4
So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.
