Enviroment
O.S. = Windows Small Business 2003
DB = SQL 2000 Server with SP3a
No windows or third party firewalls that are set up.
Problem:
When performing a Link Table in MS Access 2003 it runs into this error message "The Microsoft Jet Database engine could not find the object 'zstblSetup' (the name of our table). Make sure the object exists and that you spell its name and path name correctly.
But when we try to another server but at this time the SQL 2000 is patch with SP4, the link table works fine in Ms Access 2003. So we decided to use SQL Profiler to trace the cause of the problem. It so happen when sp_columns N'zstblSetup' is called no result is returned. Going through the code in sp_columns, it calls spt_datatype_info. When calling spt_datatype_info, again no record is returned. We suspect that spt_datatype_info is the culprit of the problem that's why Link Table in MS Access 2003 doesn't work.
Calling the sp_columns and spt_datatype_info into the working server that I mentioned aboved can return results. The way I understand it spt_datatype_info holds the SQL data type info.
My question is if we apply SP4 in the server that is problematic will this problem be resolved? Or are there any possible reason to solve this problem?
I'm querying my database with a datetime constraint, only picking up records newer than the last ETL. Although I doubt it would ever happen I was going to put a check in the data flow to trap the case when NO rows were read due to inactivity in the OLAP. I set up a row count and tried to trap it. Apparently if you use an OLE DB source to get a result set and that result set is empty, the program flow bypasses everything and keeps running until it hits something causing an error, like an SQL Insert or Update command.
Is there any way to trap this condition? Currently it is going to cause the program to fail which will cause a snapshot restore (after kicking everyone out of the database).
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables: If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice. The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application. Has anybody suffered this problem and what solution was adopted? Thanks for any information you can provide.
hi, i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case. How to catch the resultset of SQLDataSource? -nero
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.
I have a bunch of stored procedures (for use with Goldmine CE 7). They work fine under SQL server 2000 but fail in SQL 2005 due to changes in the master database schema. I am porting them over to 2005.
My current problem is a reference to: master.dbo.spt_datatype_info.fixlen
spt_datatype_info no longer exists in 2005, so, Is there an equivalent data field in somewhere in SQL Server 2005?
We are converting to sql 2005 and it appears that the spt_datatype_info table does not exist in sql 2005. I use column SQL_DATA_TYPE in code from this table.
Does anybody know what view the information that use to be in spt_datatype_info is in?
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
SQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True") Dim cmd As New SqlCommand
Dim returnValue As Object cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)" cmd.CommandType = Data.CommandType.Text cmd.Connection = sqlConnection3 cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar) sqlConnection3.Open() cmd.Parameters("@Names").Value = test If test = "" Then Response.Write("An error occured") Exit Sub Else returnValue = cmd.ExecuteScalar() sqlConnection3.Close() Label6.Text = "Number " & returnValue
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)" when i see the datable in "data visualizer" i find no rows. It is confused eventhough it does not throw any error
Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.
Hi, I have a SQL Server 6.50 - 6.50.415 (Intel X86) and one database has 8Gb of data and 2Gb of log, the problem is that the log space used is never below 44%, even if I back it up this way:
DUMP TRANSACTION DBName TO DBNAME_log_930 VOLUME = 'SS0003' WITH NOUNLOAD , STATS = 10, INIT , NOSKIP
This should clean the log, right?
I check for open transactions via: dbcc opentran (GMS), but there aren't. The way I check for the log space used is: dbcc sqlperf(logspace) The recover interval is 5.
I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.
use monkey go
create table tab1 ( myColumn varchar(10) null ) go
insert into tab1 (myColumn) values ('') go
exec master..xp_cmdshell 'bcp monkey..tab1 out D:data.csv -c -T' go
drop table tab1 go
I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?
Okay, so i have a procedure where i'm trying to build a left join, and for some reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer i have no problem.
if @spsir='1' select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
if @spsir='1' select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
here is the execution:
exec search_orders_11 @spsir=1
i get the whole command completed sucessfully, but no result. am i missing something here...
I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data. Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!
I will be really thankfull if somebody can help me on this issue.
Is there any way to sort your results in a way that the empty results comes last? When I sort my recordset on a certain column I first get the empty values and the A, B, C etc. I would like to have it A, B, C, ..., Empty ones.
I am trying to import Data from an Excel file to a SQL DB table. there are around 106 rows and 2 columns. By default, the ssis is selecting around 200 columns and over 2000 rows. But all cells except for the 2 columns and 106 rows are empty. While I can specify that I need to read only 2 columns, I could not figure out a way to specify the rows. Any help?
I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.
If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.
I need to do the opposite, be able to filter it only if it's not empty.
tbl_store Data ID Enddate 1 01/02/2020 2 3 01/02/2010 4 5 01/02/2001
(empty enddate data is showing <null>) This is my query...but i want to get date empty records too. select * from tbl_store where Enddate> '02/20/2008'
Hi, I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing. Anyone got any suggestions to why? web.config: <!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;" providerName="System.Data.SqlClient" /> --> <add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;" providerName="System.Data.SqlClient" /> default.aspx.cs dbConnection.Open(); String ISPQuery = "SELECT STATEMENT"; SqlCommand command = new SqlCommand(ISPQuery, dbConnection); SqlDataReader reader = command.ExecuteReader();
