When registering a new SQL Server in EM using the wizard a window appears that is supposed to list available SQL Servers, however it does not.
So, my question is:
How is this list derived? and how can the list be managed?
Where is the List Servers option? Can anyone points me the link.
TIA
Jagan
I can't get my server to show with the DMO function ListAvailableSQLServers or with the osql -L flag. The OS is windows 2000, but the drive is fat32 and doesn't have Directory Service.
Do you need NTFS to have Directory Service, and does SQL Server need Directory Service to broadcast its available servers?
I've got Visual Studio .Net and had no luck when I searched for an answer.
Hi,
How can i fetch the list of SQL Servers available along with their status like whether it is active or inactive?
Hi,
Is there any function/API in vb to retrieve all the sql servers that are avaiable on a network? Basically, i wanted to implement this facility in my application where users can select a server from list of all avaiable servers on LAN.
Thanks!
Asif
I successfully installed SQL Server 2005 Express edition in my pc. I ran Visual Basic 6 and used and Ado object to build a connection string to access the server but, I could not see any server in the comobox list.
I always get connection failure messages say that the server does not exitst!!!
Any help please.
Thanks
Is there anyway that I can run a procedure that will give me all the indexes on tables that are accross multiple servers?
Thanks for any help.
Dianne
Hi all,
Does any of you know, how to get the list of all Active SQL Servers on the Network using VB ?
regards,
Anand
Is it possible to get an aggregate list of databases from multiple servers, without using SMO?
All target servers are running SQL Server 2005.
Ben Aminnia
Hi
I have 4 Servers registered in my system in SQL SERVER 2000.
How to get those list of servers.
SQL Server RS 2000 SP4
I'm trying to find the easiest way to get a list of every data source on a RS instance, including the name of the SQL Server that the data source points to. We want to make sure none of the data sources point at our primary OLTP server.
I looked at the DataSource table in the ReportServer DB, but of course the connection string info is encrypted. I'm hoping that I don't need to resort to writing .net code to hit the web service.
TIA,
-Peter
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies View RelatedI'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.
Hello all.
I have a database with literally hundereds of tables in it. Can anyone advise me of how to tell which ones are populated with data and which ones arent?
Thanks people
Hi All,
I ran the code below in QA, and @@ERROR never gets populated with any error code, despite the fact that an error occured. This event is very misleading and errors are never caught. Any advice greatly appreciated.
-- Create the table
create table test_table (
test_field int
)
-- Execute the code
DECLARE @err_status int, @row_count int
insert into test_table (
test_field
)
values (
'TEST STRING'
)
select @err_status = @@ERROR, @row_count = @@ROWCOUNT
IF @err_status <> 0 PRINT @err_status
-- Results:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'TEST STRING' to a column of data type int.
What does it mean "Tables are populated" in plain English?
Sorry my mother language is not English.
Thanks
hello:
i'm looking for a way to populate a new field in an existing table based on the id of that field with a url string.
for instance, table 'example'
fields x, y, z
I want to populate z based on id from field x with the following query:
http://x.y.z?id=<from field x>&abc=xxx,&abc=yyy
Is there a way to do this?
Thanks!
Hi,
I hav eplaced an expression for the flat file connection as below
@[User::FileDirectory] + @[User::FileName]
This is supposed to be used instead of the ConnectionString property of the flat file connection.
You can see that I have created two variables.
The variable
@[User::FileDirectory] is set to the directory. i.e. I have hardcoded the path to it and assigned it to this variable.
The variable @[User::FileName] is picked up automatically.
The question is:
When I go to the properties of the flat file connection, I delete the value inside the connectionstring property becuase there is now the expression which is set to the connectionstring.
But when I come back to this property then I am not sure why the connectionstring property gets populated with the directory that I hardcoded to the variable.
Many thanks
I have a table with two columns:
OwnerName Owner
John;Smith
Mary;Smith
OwnerName is populated. Owner is not.
I want to populate the Owner column with the OwnerName in alphabetical order. I have already created a function to do this.
Select fnGetOwner(OwnerName) from OwnerTable. This returns:
Smith, John
Smith, Mary
How do I populate the blank Owner field beside the OwnerName in the OwnerTable?
Hi,
I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.
I have seen the problem earlier and could not resolved it. Please let me know if any one can help.
Thanks in advance
Rohit
How can I change a field type whilst it is populated?
I have tried :
insert new_table
select *
from old_table
but I get :
Disallowed implicit conversion from datatype 'text' to datatype 'varchar'
Table: 'davy.dbo.new_table', Column: 'de_area'
Use the CONVERT function to run this query.
My table formats are as follows :
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area char(255) NULL
)
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area text(16) NULL
)
I need to query some hierarchical data. I've written a recursive query that allows me to examine a parent and all it's related children using an adjacency data model. The scenario is to allow users to track how columns are populated in an ETL process. I've set up the sample data so that there are two paths:
1. col1 -> col2 -> col3 -> col6
2. col4 - > col5
You can input a column name and get everything from that point downstream. The problem is, you need to be able to start at the bottom and work your way up. Basically, you should be able to put in col6 and see how the data got from col1 to col6. I'm not sure if it's a matter of rewriting the query or changing the schema to invert the relationships.
DECLARE @table_loads TABLE (column_id INT, parent_id INT, table_name VARCHAR(25), column_name VARCHAR(25))
DECLARE @column_name VARCHAR(10)
INSERT INTO @table_loads(column_id, parent_id, table_name, column_name)
SELECT 1,NULL,'table1','col1'
[code]...
What i want to do is count how many times the [Omnipay_Account] column is populated with a number per parentid.
In the [Omnipay_Account] column you can either have a null or a 15 digit number
Idea result layout would be three columns these columns would be
Every row, would need to be grouped via the parentid number
ParentId Omnipay MSIP
My query is
SELECT
[ParentID]
,[Omnipay_Account]
FROM [FDMS].[dbo].[Dim_Outlet]
Hi,
We have a lot of VB6 code that uses ADO 2.7 and stored procs wih Sql 2005. I have noticed recently that if I use the follow code:
Dim con As New ADODB.Connection
con.ConnectionString = "driver={SQL Server};server=(local);database=test;uid=sa;pwd="
con.Open
Dim com As New ADODB.Command
com.ActiveConnection = con
com.CommandText = "usp_GetSetting"
com.CommandType = adCmdStoredProc
com.Parameters.Append com.CreateParameter(...)
It will fail. the reason being the after setting the CommantText and Type ADO then seems to automatically go away and populate the Parameters collection from the databases metadata according to the SP we are calling.
I have never seen this before, I thought the Refresh method had to be called before the parameters collection get populated.
Can anyone help me please?
I am populating a table using a SQL command. very simple.
SELECT RISKID
,RISKIDREN
,RISKIDEND
,PREMIUMSUBTOTAL
,PREMIUMTOTAL
,SURCHARGE1
,SURCHARGE2
,SURCHARGE3
,SURCHARGE4
,SURCHARGE5
,COMMPREMIUM1
FROM PREMIUM
However, the first three columns are not being populated in the destination table. The other columns come over fine.
The SQL stmt. returns data as expected when run against the source database.
I deleted the source and destination and recreated the flow to prevent metadata mapping issues. In the source editor preview I see all of the columns and data. In the destination editor preview, the first three columns of data are null ???.
It appears that the columns are not mapping properly even though they are in the source and destination of the mapping editor.
I have made sure that the destination mapping contains all the columns in the UI.
The source and destination have the columns represented in the advanced editor metedata. I also checked the XML to verify that the columns are in the destination.
There is a row count between the source and destination. which should have no effect.
This is a part of a larger DW load where I have 10 other tables populated within the dataflow. I also do not get any validation, or error messages. So, I have eliminated truncation errors or the like.
I am really puzzled. Has anyone run accross anything like this?
I have a SSIS package with multiple objects executing ODBC SQL to Teradata. All of the SQL uses the same variable (today) to susbtitute today's date into the native teradata SQL.
Today is populated using the following expression:
"'" + (DT_WSTR, 4) DATEPART( "yyyy", GETDATE() ) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "mm", GETDATE() ) ,2) + "-" +
right("0" + (DT_WSTR, 2) DATEPART( "dd", GETDATE() ) ,2) + "'"
The problem is that some of the code uses the correct date, but others appear to use the date that the SSIS package is added to the scheduler. It is not always the same SQL statements which deriver the incorrect date. The code below is four of the SQL statements generated at the same time using this variable - two show the correct date (2006-08-22) and two the date the package was added to the schedule (2006-08-18):
SELECT MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) , COUNT ( * ) FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeDate = (dAtE'2006-08-22') ) GROUP BY MarketSegmentCode , TradeTypeInd , BroadcastUpdateAction , CurrencyCode ORDER BY TradeTypeInd , MarketSegmentCode
SELECT ParticipantCode , CASE WHEN ParticipantCode = ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END AS Counterparty , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM ProdMD_Midas_Base.v_TradeReport WHERE ( TradeTypeInd = 'O' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY ParticipantCode , ParticipantCodeBuyer , ParticipantCodeSeller , MarketSegmentCode , BroadcastUpdateAction , CurrencyCode
SELECT ParticipantCode , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS Bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-22') ) GROUP BY ParticipantCode , CurrencyCode , MarketSegmentCode
SELECT ( CASE ParticipantCode WHEN ParticipantCodeBuyer THEN ParticipantCodeSeller ELSE ParticipantCodeBuyer END ) AS Cparty , CurrencyCode , MarketSegmentCode , SUM ( TradePrice * TradeSize / 1000000 ) AS Consideration , COUNT ( * ) AS bargains FROM v_TradeReport WHERE ( TradeTypeInd = 'AT' ) AND ( TradeDate = (dAtE'2006-08-18') ) GROUP BY Cparty , CurrencyCode , MarketSegmentCode
Has anybody experienced similar problems with variables in SSIS packages and the scheduler?
Thanks
I am tring to figure out how to simplify the process of populating a database created by an application with the same database, only with data already in it. So far i have created a backup of the database and used that backup file with SQL server management express to overwrite the existing database with that backup file on a new computer so the program will have data when initally installed for Demonstration purposes. I was hoping there was an executable script that i could use, so that when someone wants a demonstration of our product, they can see its options and functionallity with data available. Maby i am going about this the wrong way, i need to know if there is a way that when our program is installed an executable can simply be run to populate our database with a backup of our sample database. Any imput would be helpful.
Thanks.
Isaias
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View 2 Replies View Related
I have an excel sheet that has a bunch of columns. Some of these columns have static data but there are a few of the columns that retrieve data by making calls to servers and returning values that populate these columns. Usually about 30 mins before the columns are populated.
I then need to to load the excel sheet into a table.
I was wondering if there was anyway by which we can make the connection to the excel sheet and then force a delay of about 30 mins before it starts retriving the data?
Is this possible in ssis? Or can I achieve it by some other means?
Thanks for any help in advance
Hi, I have a script task in SSIS which dynamically generates a string for a file name to be used as flat file source. I execute the task and it executed with success; but when I checked the result of the variable TotFileName from the Expression builder window for the flat file connection manager it was not populated with a file name like \MyServerMyDriveMyFolder200706daily.txt. So something might still be missing from the script below. Or is the way I do it correct? Can someone help with this? Thanks a lot!!
I have created package level variables ImportFolder (value like: \MyServerMyDriveMyFolder ) and TotFileName (value field empty) and make ImportFolder a ReadOnlyVariable and TotFileName a ReadWriteVariable. Then I use expression to set the property for flat file connection manager to use the TotFileName variable.
(Basically the idea is: if now is July 2007 then the filename should be 200706daily.txt; if now is Jan 2008 then the filename should be 200712daily.txt)
-----code------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim TotFileName As String
Dim TrueFileName As String
Dim sYear As String
Dim sMonth As String
Dim sDate As String
sYear = CStr(Year(Now()))
sMonth = CStr(Month(Now()) - 1)
If (Month(Now()) < 11 And Month(Now()) > 1) Then
sMonth = "0" & sMonth
End If
If (Month(Now()) = 1) Then
sMonth = "12"
sYear = CStr(Year(Now()) - 1)
End If
sDate = sYear & sMonth
TrueFileName = sDate & "daily.txt"
TotFileName = CStr(Dts.Variables.Item("ImportFolder").Value) & TrueFileName
Dts.Variables.Item("TotFileName").Value = TotFileName
Dts.TaskResult = Dts.Results.Success
End Sub
End Class