Transact SQL :: Server Is Cutting Off Result-output Running A Powershell Script

May 7, 2015

I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:

iid int identity(1,1) primary key
,Laufwerk char(500),
EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"'
select * from #DrvLetter
SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):

View 2 Replies


Cutting And Pasting From PDF Output

Aug 22, 2007


We have some reports that are published as PDF files. The layout of the report is fairly free, using lists within lists.
Some of our users want to cut and paste some of the data. When they do so, the order and layout of the fields does not resemble the layout of the report. Any way around this.

Note that when the report is published as mhtml or excel, cutting and pasting from these formats is much better.


View 1 Replies View Related

Running A Stored Procedure Using Output Result.

May 12, 2008

Hey guys!

I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.

I've created a hierarchal listbox form that drills down From

Product - Colour - Year.

based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.

So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.

I want that product number to be used to populate the grid view. Is there away for me to do this?

Thanks in advanced everybody!

View 6 Replies View Related

SQL Server 2008 :: SSMS Truncating PowerShell Output?

Jun 3, 2015

When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).

EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.

Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?

View 6 Replies View Related

Setup And Upgrade :: Use Powershell To Output Database Names On Old Server On Text File?

May 28, 2015

I've been asked to create a new SQL server and restore an old server's DBs to the new server.

I'd like to generate a list of the DB names using powershell to distribute to their creators, in case some of these DBs are no longer needed.

View 2 Replies View Related

SQL 2012 :: How To Insert Powershell Output Into A Table

Mar 20, 2015

I'd like to know how to get windows events script below into a SQL Server Table.

Get-WinEvent -LogName application -MaxEvents 200 | where {$_.LevelDisplayName -eq "Error"}

View 1 Replies View Related

Transact SQL :: Multiple Queries On Various Database Using Powershell

Oct 19, 2015

As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.

I am looking to automate the process, like a single click to execute all the .SQL files.

The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.

My design for this requirement is, say each .SQL file need to contain a template like

@DBName = 'your Database name'
@Executeon = 'When to execute'

In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.

Is it feasible ? Or any other alternate easier way to proceed.

View 3 Replies View Related

Transact SQL :: Move Files From One Location To Another Using Powershell

Jul 12, 2015

I have a job which copies .txt files 24 hours 7 days a week to c:TempSource

What I am planning to do is copy the files from one location to another say c:TempTarget

So I have written the Powell shell script and when i put that in the sql agent job i get below error ;

A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.

$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder } 

View 12 Replies View Related

Transact SQL :: Update Query In Powershell From Excel Sheet

May 12, 2015

I am trying to update my SQL table using an update query in powershell from an excel sheet.

The query is as follows,

#building name from excelsheet
#update query is as,
 $CreateScript1= "UPDATE $DestinationTable SET $DestinationTable.Phone = '$SCell' WHERE RoomNumber = '$FCell' and buildingname
like ''$TCell'%'"

I want to use like operator in the query to compare building name from excel sheet with the building name in sql table. I am facing an error in the highlighted part. I am not sure if my query is right or wrong.

View 2 Replies View Related

Transact SQL :: Powershell For Pass Comma Separated List

Sep 8, 2015

I have a requirement, we need to pass comma separated list using powershell script.

How can we achieve the above scenario?

View 3 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):

USE AdventureWorks;


IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;


CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE


-- Columns returned by the function


FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL



-- Returns the first name, last name, job title, and contact type for the specified contact.



@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information


@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =


-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)


FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)


FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)


FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)



SET @ContactType =


-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'


-- Return the information to the caller



INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;





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.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Transact SQL :: Powershell Script To Fetch Database Usage Details For Multiple Servers (report)

Oct 28, 2015

Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.

Details: servername, databasename, datafile usage, logfile usage, free % age...etc.

View 3 Replies View Related

SQL Server SPROC Cutting Off Varchar Parameter

Feb 5, 2004


I am having trouble with a SPROC and I was wondering if anyone has had similar problems. I have a web form where users enter in a large amount of text, the web app saves that data via a SPROC into a varchar(8000) field in a SQL table. However, any amount of text beyond 255 gets cut off. I have double checked and tested my code and I dont seem to find the problem. Below is the beginning of my SPROC and as you can see, I have the correct data type and length. I have also verified the data type and length on the table and it is set at varchar(8000). Any ideas?

@Pros varchar(100) = null,
@Cons varchar(100) = null,
@Title varchar(100),
@Body varchar(8000) -- This is the parameter with problems


View 5 Replies View Related

SQL Server 2005 JDBC Driver Output Parameter/Result Set Issue

Mar 21, 2006

I'm having an issue with the JDBC driver when I execute a stored procedure that both has a return value and also returns a result set. If I attempt to retrieve the return value (registered as an output parameter) after I execute the stored procedure, then any subsequent attempts to retrieve the result set always return null. Is this by design? If I use the result set first and then later get the return value that works; however, in my situation I need to first check the return value before I work on the result set. Am I'm I doing something wrong?


CallableStatement cs = connection.prepareCall("{? = call spGetCustomer(?, ?) }");
cs.registerOutputParameter(1, Types.INTEGER);

int retVal = cs.getInt(1);
ResultSet rs = cs.getResultSet(); // Always returns null, even though the SP actually returns a result set.

View 36 Replies View Related

Transact SQL :: How To Get Row Values For Result Set In Server

Jun 16, 2015

I have written this query

select name,number,city from student where name='abcd'
now name='abcd' is not there in student table so result is no record.

I need record for this query, all the columns as null or empty. how to do this one? Because am using this query in report so this query as null or empty i will replace as 0 for rows.

View 8 Replies View Related

Output File (.txt) Running Package From SQL Server Agent

Mar 19, 2008

I have created a package that runs without problem.
I run the package with the command dtexec /F "package_name.dtsx" > package_name.txt.

Then I run the same package from SQL Server Agent, everything is OK

Then, I tried to edit the command line to have the output file, but I got an error.

The command line is:
dtexec /F "package_name.dtsx" MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E > package_name.txt.

How can I do?


View 4 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'


View 3 Replies View Related

SQL Server 2014 :: How To Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run.But print statement dos not work correctly!

Note: I cannot use "go" in my query!

/* My query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'


View 2 Replies View Related

SQL Server Admin 2014 :: Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!

Note: I cannot use "go" in my query!

/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[Code] .....

View 1 Replies View Related

Transact SQL :: How To Get Running Total In Server

Oct 21, 2015

I need to get a cumulative total for row by row basis. I need this grouped on name, id, year and month. ID is not a auto incremented number. ID is  a unique number same as name. The out put I need is as below,

Name     ID     Year   Month  Value  RunningValue
XX         11     2013  Jan      25          25
xx          11     2013  Feb      50          75
yy          22     2015   Jan     100        100
yy          22     2015   Mar     200        300

How I could get this query written? I am unable to use SQL Server 2012 version syntaxes. Writing a cursor would slow the process down because there is a large data set.

View 12 Replies View Related

Transact SQL :: Running Total In Server 2008

Nov 20, 2015

Is it possible to assign to a variable, then add to it later on?  When I run the below, all I get is 3 rows affected I never see the value printed.  What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers
= 6
Create Table #Test ( randarnumbers int )
Insert Into #Test Values ('2'), ('1'), ('3')
Declare @sumofallnumbers int, @nbr int
Declare c1 Cursor For
Select randarnumbers
FROM #Test


View 6 Replies View Related

How Can I Output Result To A Log File?

Oct 21, 2005

Hi all,

In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that?

Thanks first.

View 1 Replies View Related

Problem With Output The Result

Nov 17, 2006

deepak bhardwaj writes "myself deepak . i have a problem with displaying output.i want to the output just like this

3 mths 6 mths 9 mths 1 yr

Google Ad 8 6 3 0

Yahoo Ad, 4 6 3 3

Friend 3 45 3 1

Print Ad 2 3 3 2

here 3 mths etc. is the column name and 8,4,3,2 are the no of hits for perticuler add .
plz give the solution. its urgent

View 3 Replies View Related

Output Query Result To A CSV File

Aug 29, 2007

I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ?
Thanking you...

View 4 Replies View Related

Query Result To Output File

Jan 24, 2001

Hi All

I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.

Thanks in advance


View 1 Replies View Related

How Do I Write An Output Parameter (result) When..

Feb 24, 2006

..calling a stored procedure.SQL

I am using the C programming language... I have two input variables and one output...Not sure how to do the output


This is in a CSTRING format....

View 1 Replies View Related

Cutting To A Certain Word

Nov 29, 2006

Hi there, i need to know how to cut a string to the nearest word. For example, i've got an article and i need to extract just a part of the beginning, i could use LEFT([content], 250) but there is little chance this will cut on a word. Therefore i need to know if there is a function that will cut to the nearest word in T-SQL or i will simply put a summary field in the database. (I prefer to generate the summary on the fly if possible)

View 3 Replies View Related

Output Result Of Query To Existing File

May 9, 2007

Hi,I need to output result of my query to txt file. So I'm using -oparameter, for example:osql.exe -s (local) -d database1 -U sa -P sa -i 'c:\queryFile.sql' -o'c:\output.txt'But it clears existing output.txt file first and then outputs theresult, while I need to append the result of my query without clearingexisting file content. Is it possible ?

View 1 Replies View Related

Powershell Sql Server Performance

Mar 31, 2008

I am trying to get performance data using powershell. When I run the command "get-wmiobject -list" I get classes like
Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks. However when I run the command

get-wmiobject -query "select * from Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks" powershell gives me an error. Why is this happening? any help in this regard is appreciated.


View 2 Replies View Related

Trying To Set Output Variable To Row Count Result Of SQL Execute Task

Nov 5, 2007

I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!

SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")

View 6 Replies View Related

Result Output To File + Parameters In Query + Rownumbers

Jan 23, 2008

i am using sql server 2000.

1. can i save the sql result to an ascii file using tsql commands. i don't want to use the menu option.

2. how can i use parameters in query statements. pls give me some exampls there any way to get the row numbers along with the query results

thanks & regards

View 7 Replies View Related

Different Result Running SQL Query Directly Or Using SqlCommand

Sep 11, 2006

Hi, when running the following stored procedure: ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]@LeidinggevendeID int,@Start int = 1,@Limit int = 25,@Sofinummer int = NULL,@Achternaam nvarchar(128) = NULL,@Functie nvarchar(64) = NULL
ROW_NUMBER() OVER (ORDER BY Achternaam) AS RowNumber,Persoon.*FROM PersoonINNER JOIN DienstverbandON Persoon.ID = Dienstverband.PersoonIDINNER JOIN BedrijfsonderdeelON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelIDINNER JOIN LeidinggevendeON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelIDWHERE
Leidinggevende.Begindatum <= getdate()AND (Leidinggevende.Einddatum > getdate()OR Leidinggevende.Einddatum IS NULL
AND Leidinggevende.PersoonID = @LeidinggevendeIDAND
Sofinummer = @Sofinummer
OR @Sofinummer IS NULL
Achternaam LIKE @AchternaamOR AchternaamPartner LIKE @AchternaamOR @Achternaam IS NULL
SELECT *FROM OrderedWHERE RowNumber between @Start and (@Start + @Limit - 1)  When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code: [DataObject(true)] public class PersoonFactory { [DataObjectMethod(DataObjectMethodType.Select, false)] public static IList WerknemersBijLeidinggevende(int ldgID, int start, int max) { IList list = new List(); SqlDataReader rdr = null; SqlConnection connection = DatabaseProvider.Connection; SqlCommand command = new SqlCommand("GetWerknemersBijLeidinggevende", connection); command.Parameters.AddWithValue("LeidinggevendeID", ldgID); command.CommandType = CommandType.StoredProcedure; try
rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read()) { Persoon pers = new Persoon(); pers.ID = rdr["ID"] as int?; pers.Achternaam = rdr["Achternaam"] as string; pers.AchternaamPartner = rdr["AchternaamPartner"] as string; pers.Achtertitels = rdr["Achtertitels"] as string; pers.DatumOverlijden = rdr["DatumOverlijden"] as DateTime?; pers.Geboortedatum = rdr["Geboortedatum"] as DateTime?; pers.Geslacht = rdr["Geslacht"] as string; pers.Middentitels = rdr["Middentitels"] as string; pers.Naamgebruik = (int)rdr["Naamgebruik"]; pers.Sofinummer = rdr["Sofinummer"] as string; pers.Voorletters = rdr["Voorletters"] as string; pers.Voortitels = rdr["Voortitels"] as string; pers.Voorvoegsel = rdr["Voorvoegsel"] as string; pers.VoorvoegselPartner = rdr["VoorvoegselPartner"] as string; list.Add(pers); } } catch
throw; } finally
if (rdr != null) rdr.Close(); else connection.Close(); } return list; }  I get 0 rows all of a sudden. Any idea why? 

View 7 Replies View Related

T-SQL (SS2K8) :: Joining Two Tables - Output Result In The Order By DivID

Mar 25, 2014

I have two tables to be joined


ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL

[Code] ...

As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID

View 5 Replies View Related

Copyrights 2005-15, All rights reserved