I have the following function: CREATE FUNCTION fGetTransactionStatusLog ( @TransactionID int ) RETURNS varchar(8000) AS BEGIN declare StatusChanges cursor for select NewStatusID, FirstName + ' ' + LastName AS UserName, Stamp, CAST(Notes AS varchar(8000)) AS Notes from TransactionStatusChanges tsc left join Us...
So basically we have lots of SharePoint usage log files generated by our SharePoint 2007 site and we would like to make sense of them. For that we're thinking of reading the log files and dumping into a database with the appropriate columns and all. Now I was going to make an SSIS package to read all the text files and extract the data w...
I have a table which contains my server status create table ServerStatus ( ServerId int, StartTime datetime, Seconds int, [State] char(8) ) I would like a query that given a start and end date will summarize the time the server spends in each state during that time. I would also like the query to return the amount o...
Hello, We have an application where people can type in multiple langugaes. Though we only have one database to store all the data. We have text columns as nvarchar ( assuming we only want text data to be in multiple languages and not all dates etc.) Do you think this is a feasbile solution and are there any other points to consider bes...
Are there any free tools for scripting MSSQL table data? I'll gladly write one, but am hoping it's been done, and the app matured a bit before/ ...
Hello, We have two Tables: Document: id, title, document_type_id, showon_id DocumentType: id, name Relationship: DocumentType hasMany Documents. (Document.document_type_id = DocumentType.id) We wish to retrieve a list of all document types for one given ShowOn_Id. We see two possiblities: SELECT DocumentType.* FROM DocumentType W...
An advantage by using Windows Authentication is that I do not need to provide a username and password to log into the server as the user currently logged into Windows. But in a specific case I would like to log into the SQL Server as another user, using the same Windows Authenticated ODBC connection. So, the question: Is it possible to ...
In certain areas in my application I need data from several tables in the database (I have an application, the application has many attributes, each attribute has several definitions, and each definition has a value). I need the data from all these tables for an application. Should I use a view (one that would be rather large) or subque...
Hi, I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string? [WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate) command.CommandText = @"SELECT id, startDateTime, en...
I am currently working with 2 sql 2000 servers one I can query but not add any databases to which lead to the second one having lots of queries that use the first as a linked server. I would like to improve performance while still querying live data. Is it possible to mirror the live data to the second server, that way the queries would...
I'm trying to call the sql statement below but get the following error. System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '+@buildingIDs+' to data type int. @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN ('+@buildingIDs+') AND startDateTime <= @fromDate"; buildingID ...
I've got a column in a database table (SQL Server 2005) that contains data like this: TQ7394 SZ910284 T r1534 su8472 I would like to update this column so that the first two characters are uppercase. I would also like to remove any spaces between the first two characters. So T q1234 would become TQ1234. The solution should be able to...
Often we need to add a non-nullable column to a table, and it is quite a mission. Using a default constraint as is doesn’t work, so we have to create nullable columns, update them to default values, then make them non-nullable. Is there not an easier way to do this? ...
Are there any tools available for validating a database schema against a set of design rules, naming conventions, etc. I'm not talking about comparing one database to another (as covered by this question). I want to be able to say "What in this database doesn't meet this set of rules". Some examples of the type of rules I'm talking a...
In applying for jobs via agents I sometimes get blocked by an agent who says do you know software package X. When I reply that I know the similar package Y they might say unless you know X I cannot put you forward. The problem is that some of these agents don't know what they talking about, they are merely being used by their clients as...
Specifically MSSQL 2005. ...
Hello, i want to import data from mssql, run it through some sort of regexp to fitler out stuff, and import it into mysql. I then, for each query, wish to display a relevant image from a third database. What would be the easiest way to do this, importing and linking wise? Thankyou Clarification: It is a php application to filter data ...
Looking into System.Data.DbType there is no SqlVariant type there. SqlDataReader, for example, provides the GetString method for reading into string variable. What is the appropriate way to retrieve data from the database field of type sql_variant, presumably into object? The aim is to read data stored as sql_variant type in database. ...
SSIS - I have data loaded and various transformations on the data complete, the problem is there is a parent/child relationship managed in the data - best explained by an example each row has (column names are made up) row_key parent_row_key row_name parent_row_name some rows have row_key == parent_row_key (their own parent) some rows ...
I need to generate multiple random values under SQL Server 2005 and somehow this simply wont work with Random(Value) as ( select rand() Value union all select rand() from Random )select top 10 * from Random Whats the preffered workaround? ...